Thread

We are getting more frequent reports of performance issues with our production web site. I believe I know where the problem lies, but don’t know how to resolve or at least reduce the impact of the issue.

First, the required background information:

The site is hosted at EngineHosting on dedicated servers:
* Web servers are two single processor/quad core Intel Xeon X3210 (2.13GHz), 4G ram, 160gig 7200rpm SATA.
* MySQL server is a single processor/quad core 2GHz Xeon E5405, 4G ram, 2x10,000 RPM SAS drives in RAID 1 with 256Mb hardware RAID controller.

Software Information:
* ExpressionEngine 1.6.7 build 20090211
* Multiple Site Manager installed with 10 sites
* MySQL DB version 5.0.45-log (I assume that means EngineHosting turned off SQL logging for us in an attempt to give more speed)
* DB Size: 1.1Gb

http://www.pbinsight.com
The site gets about 250,000 visits every 30 days.

Most pages normally load (the time taken for EE to render the templates) in under a second. More complicated pages load in under 2 seconds.

The problem:
Sometimes, almost randomly, pages can take anywhere from 10 to 80 seconds to load. When this occurs, the site seems to just stall. Through regression testing and weeks of investigation, we see that the issue seems to revolve around relationships. Our product and solutions content includes quite a few bits of related content added via Playa 2.

I believe these pauses are due to the on-demand rebuilding of EE’s relationship caches. Our exp_relationships table has 11,815 records and is a whopping 1.1Gb. exp_weblog_data, by comparison is only 6.5Mb. exp_weblog_titles is only 750Kb.

Across this entire site, there are only 1836 entries.

We also notice the speed issues sometimes in the EE console, when committing changes to records that have these related fields. This tells me that the cache refresh and rebuilding process is occurring when we really want it to, at the time of data entry. It’s when the cache is rebuilt while the site visitors are requesting pages that’s the problem.

Does anyone have any ideas how to resolve this issue? I believe it’s only going to get worse as we add more and more content and related content to the site. I don’t believe this issue is related to Playa as Playa is using the built in relationship features of EE. The culprit, in my mind, is the on-demand rebuilding of relationship caches. There has got to be a way to make the building of the cache records more efficient and faster.

Have you seen this thread? Mitchel of Solspace chimes in at #11, explaining a few of the issues you seem to be experimenting. I, too, think this might be an issue with Playa under heavy load.

Ingmar, I haven’t seen that thread until now, but thank you for posting it.

In my situation though, Playa isn’t causing the large delays we’re experiencing. Under normal conditions, pages load in under 2 seconds. This shows that Playa, while maybe not as fast as other alternatives, is fine.

Here’s a page that has quite a bit of related content both in the sidebar and on the resources tab:
http://www.pbinsight.com/products/location-intelligence/applications/mapping-analytical/mapinfo-professional/

It renders in 1.4s.

When the relationship caches rebuild on-demand, it can take over a minute. That’s the issue we’re trying to resolve.

Currently Playa is just using EE’s built-in function to clear all relationship caches when there’s a change. It sounds like it would help if I instead manually remove only the caches for relationships that have changed.

That would be awesome. Of course, this change would likely go into a new version of Playa not the old, modified 2.x version we’re using so that category/weblog prefiltering is enabled.

If you come up with the code, any way you could share it?

Haha… yeah we can coordinate that privately.

iso100, does that shed some light on the issue? If you want me to, I can move this thread to “General”, it’s just that it’s probably no an issue for Tech Support as such…

Isn’t it still an issue with the core of EE regarding the performance issues around refreshing the relationships cache?

Do you notice a pattern of when this happens?  Does it happen on the same page, or something with a similar weblog?  Does it happen only after you publish or update an entry?

-greg

There doesn’t appear to be a pattern. I’m working with the site most of the day and don’t notice it that often. It appears to be completely random.

When we update entries with lots of related content, I can actually experience the pauses while in the EE console. The changes just take a long time to commit. If I attempt to access the live site’s view of that same record, I get the same results… the page just hangs during the request while waiting for EE to render the template and send the data to the browser.

Once it’s done doing what it was doing, the page is snappy. If I (gasp) clear all relationship caches (I did this by accident once) the entire site slows to a crawl and takes between 10-90 seconds for each page to render, as each request triggers the on-demand rebuilding of that entry’s relationship caches (normally done when an entry is saved within EE console).

The worst offender weblogs are Solutions and Products. Both of them have up to 260 or so entries, each with many, many custom fields… and are related to other weblogs (Resources, Events, etc) via multiple related entries fields. I have a feeling that the combination of many custom fields along with many relationships to other weblogs also with many fields causes a performance snowball effect.

The troubling thing is that once the relationship cache is up to date, the pages are quick. It’s this on-demand rebuilding of the relationship cache that kills performance.

iso1000 - I’m a little bit confused; we’ve helped you with this issue extensively before, and the conclusion was that EE builds the cache when you publish or edit, so it shouldn’t be happening “randomly” at all. 

What kind of actions are you doing right before triggering this slow down? Are you clearing the relationship cache?  Publishing/editing entries that contain relationships?

As Robin said in your original thread:

While I’d have to search to be 100% sure, the only way I recall the whole exp_relationship table having the rel_data wiped (and thus need to rebuild) is via the ‘clear cache’ interface in utilities.

So this really should not pose an ongoing problem for you.

As noted above, Playa is clearing the relationship cache as well. I think Playa’s the culprit here. I’m working on a patch that should speed things up.

I was about to reply that there are multiple other content editors adding and editing content on the site all day, and that it was possible that while EE is busy running the queries and PHP code required to rebuild the relationship cache on content edit it also affected the performance of normal web visits to affected entries/templates.

Now that Brandon has replied though… it sounds possible that there may be another option. If Playa clears the relationship cache and predetermined (read:seemingly random) times when templates are merely _viewed_ then we may have figured out the cause.

I would strongly suggest we keep this thread open pending the installation and testing of the patch Brandon is (so graciously) working on for me. If it turns out that we still have the issue, it might be worth continued troubleshooting.

Maybe Ingmar/Lisa/Robin could answer this: Is it possible that a large, lengthy and expensive cache rebuilding process (triggered on content edit/save) could affect the rendering of templates on a live EE installation? I still wonder if a highly active EE installation in terms of content edits and updates could cause performance issues to web visitors.

This is just one of many performance enhancements I’m working/planning on. I’m going to release them as Playa 2.2, so they won’t be publicly available for some time. But if anyone does have a site where they’re finding Playa 2 to be slowing things down, talk to me privately, and I’d be happy to get you advanced access through GitHub.

Well, but publish/edit shouldn’t be rebuilding the entire cache.  That only happens if you clear relationship caches via the Clear Caches utility.  I don’t believe that a lot of Publish/Editing should be affecting this; but honestly?  It’s kind of easier for you to test than for me.  Ask your editors to hold off for 10 minutes, edit an entry during the day then while that is publishing, reload your front-page.  That should be a fairly good and quick way to test this.