Massive Performance Issues, Playa and FieldFrame

Actually, both Lockwesmonster and you, Robin have posted helpful information.

I like lockwesmonster’s idea, but wonder about having to call weblog:entries on the embedded templates if you just need to display related content. In our case, we’ll likely just have to have lots of messy non-advanced conditionals to avoid parsing expensive FieldFrame nGen File Field fields.

Robin, over time (this past week) I have figured out that the site is actually faster (with a much larger exp_relationships table) due to the relationship caching. I no longer desire to disable it. (I do, however, think that a view or a dynamically-built table cache of relationship data would be faster for relationships)

I think what gets me the most is those situations where EE is building this relationship cache (rel_data and reverse_rel_data) when the item is being accessed. This in turn causes slow response times for visitors to the page.

I wonder… would it be possible to craft an extension that causes the relevant rel_data and reverse_rel_data caches to be rebuilt at the time of entry creation/update? This would ensure the best response times for visitors at the (far more acceptable, in my opinion) expense of a short delay when committing changes.

Someone better at extension development than me has got to know how to do this, or if it’s at least impossible or not.

After implementing the technique I described above, I went from one page having around 1500 queries to having around 20. I know with Playa 2, related entries queries can pile up, especially if the related entries use nGen File b/c each one of those look up file upload preferences.

I struggled with this problem earlier this week, and here is a technical support thread that led me down the path to figuring out my solution.

http://ellislab.com/forums/viewthread/119098/

Hee- yep, 1500 queries would indeed grind things to a halt!

Note- the relationship (and reverse) data are created upon publish- search through cp.publish.php for compile_relationship and you’ll see what’s up. However- when you ‘clear all caches’ in the cp? You basically wipe all of the rel_data (and reverse_data). Once it’s been rebuilt it’s only going to get wiped by clearing caches.

But anyway- in the normal run of things, EE won’t be creating the data on the fly. It’s done on publish already.

Note- I won’t swear how the extensions may impact that, but I suspect they follow along with the same logic and do the creation bit on publish. But- since the cache has been cleared, it has to be rebuilt. That happens as the need arises.

Excellent. Thank you so much Robin. This is the technical information I really needed.

So, when I publish a new entry, the relationship cache is not cleared, it’s just updated with the new relevant information? The only time the relationship cache is cleared is when I either use the Clear all Caches or Clear Relationship Caches options?

That explains why the entire site was super slow after clearing all caches while chasing invisible gremlins.

So, when I publish a new entry, the relationship cache is not cleared, it’s just updated with the new relevant information? The only time the relationship cache is cleared is when I either use the Clear all Caches or Clear Relationship Caches options?

Yep- on publish, the row for the relationship is created and the rel_data is populated with the serialized array (and any reverse related). 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. Typically, you’d only need to clear the relationship data if you ended up with a serialized array error (sometimes happens on a server move).

That said- normally, rebuilding it shouldn’t have much impact. Clearly it is here- and it sounds like the extensions may be having an impact on that. But once the caches are all rebuilt, this shouldn’t affect things again (unless you clear the cache). And new entries a. won’t clear all caches; b. will have the cached data for the relevant entries created on publish.

So- keep an eye on it, watch the template debugging. But in the general run of things- this shouldn’t affect the site during normal operation. As those caches are rebuilt via viewing each entry, the performance hit should go away. (Though I can’t be sure how the extensions may be affecting that. Still, I suspect they operate the same way.)

Thanks again Robin. This would be great information for a Blog post and/or wiki article. If I had this knowledge a week ago, it would have saved me a lot of fruitless testing and troubleshooting.

Still, good to have now!

Robin, can you check on the behavior of rel_cache refreshing?

I just visited some pages this morning and had the occasional page take up to 20 seconds to load. I turned on SQL Query display and sure enough, the delays are caused by the rebuilding of rel_cache. I know for a fact that relationship caches were not cleared, so this just doesn’t make any sense.

I have attached two TXT files (ZIPped because they are huge) containing the SQL queries of the same page. The slow page was my first load, the fast one the refresh. Obviously, the refresh is perfectly acceptable performance at 1.4 seconds. The slow one, however, is way too slow for my tastes at 10 seconds.

Is there any way to find out why these pages’ rel_cache and reverse rel_cache are being rebuilt upon page load? This was not happening yesterday and it concerns me because I just came across a great many pages on the site that are initially slow. Once they’ve been accessed one time, they are quick but since we can’t seem to nail down the rules for needing the relationship caches rebuilt, I never know if the pages will be slow or fast.

Sorry for the delay- did a bit of digging. You can spot where the relationship table is cleared by looking for $FNS->clear_caching(). Now- that only clears the relationship table in two cases: clear_caching(‘relationships’, etc… clear_caching(‘all’, ”, TRUE)

The main spots cache will clear (ignoring utilities and explicitly clearing it there): 1. Editing/adding categories, category fields; adding/editing custom channel fields. That’s a bit vague- specifically you need to worry with these functions: update_category_fields() delete_category_field() delete_category_group() delete_field_group() update_weblog_fields() delete_field()

It makes sense- if you add a new category custom field, or delete a custom field, or the like- the cached relationship would need to be adjusted to take that into account.

So- if you’re still adding/deleting fields- custom or category- that could be the culprit.

2. The other likely place is in the multi-editor on the edit page (multi-cat editor too). If you use the multi-editor(s) and any of the entries involved are either a parent or a child? It clears the relationship cache.

Those two spots look by far the likeliest- and it’s also possible a third party addition might be in play.

But based on the usage patterns- does this give us a hint?

Hello,

I had same problem. I just updated my fieldframe’s version and now it’s ok.

Hope this help.

Tom

We just uncovered something that might have been the root of our performance issues.

Apparently an older version of Playa we were once using (we have since upgraded) wasn’t removing entries from exp_relationships. Because of this, we have many, many duplicate fields in this table.

I have a feeling that when an entry is updated and then accessed for the first time, the rel_data and reverse_rel_data caches are rebuilt. If they are rebuilt using wildcards in the SQL, it’s very likely that EE is running the cache rebuilding queries many times more than is needed.

Can anyone confirm the SQL that’s actually used to determine which rel_data fields to update?