Long running query craft_blitz_elementcaches

[Thijmen]

Hi,

We've updated to 4.4 and after updating we noticed that our DB has some severe load. We think it is because of a query to craft_blitz_elementcaches. The where clause is so big, when I save the query to a file it's filesize is 2.5 MB. Is there a way to improve this? We're now attempting to disable trackElements and see the performance, but I was curious about your input.

Thanks in advance, appreciated!

[bencroker]

Did you refresh the cache after updating to 4.4.0, as per the changelog?
https://github.com/putyourlightson/craft-blitz/blob/develop/CHANGELOG.md#440---2023-03-01

And do you have any idea what type of action is triggering such a large database query?

[Thijmen]

I thought I did that, did it again to be sure. The query looks like this:

SELECT `craft_blitz_elementcaches`.`cacheId`
FROM `craft_blitz_elementcaches`
LEFT JOIN `craft_blitz_elementfieldcaches` ON `craft_blitz_elementcaches`.`cacheId` = `craft_blitz_elementfieldcaches`.`cacheId` AND `craft_blitz_elementcaches`.`elementId` = `craft_blitz_elementfieldcaches`.`elementId`
WHERE (`craft_blitz_elementcaches`.`elementId`=2806947) OR (`craft_blitz_elementcaches`.`elementId`=2806829) OR (`craft_blitz_elementcaches`.`elementId`=2806280) OR (`craft_blitz_elementcaches`.`elementId`=2806281) OR (`craft_blitz_elementcaches`.`elementId`=2805169) OR (`craft_blitz_elementcaches`.`elementId`=2803598) OR (`craft_blitz_elementcaches`.`elementId`=2801831) OR (`craft_blitz_elementcaches`.`elementId`=2801143) OR (`craft_blitz_elementcaches`.`elementId`=2800732) OR (`craft_blitz_elementcaches`.`elementId`=2655198) OR (`craft_blitz_elementcaches`.`elementId`=2800572) OR (`craft_blitz_elementcaches`.`elementId`=2797996) OR (`craft_blitz_elementcaches`.`elementId`=2797486) OR (`craft_blitz_elementcaches`.`elementId`=2797278) OR (`craft_blitz_elementcaches`.`elementId`=2797217) OR (`craft_blitz_elementcaches`.`elementId`=2797162) OR (`craft_blitz_elementcaches`.`elementId`=2784394) OR (`craft_blitz_elementcaches`.`elementId`=2797039) OR (`craft_blitz_elementcaches`.`elementId`=2796956) OR (`craft_blitz_elementcaches`.`elementId`=2796866) OR (`craft_blitz_elementcaches`.`elementId`=2796681) OR (`craft_blitz_elementcaches`.`elementId`=2784489) OR (`craft_blitz_elementcaches`.`elementId`=2796376) OR (`craft_blitz_elementcaches`.`elementId`=2796273) OR (`craft_blitz_elementcaches`.`elementId`=2789732) OR (`craft_blitz_elementcaches`.`elementId`=2787502) OR (`craft_blitz_elementcaches`.`elementId`=2791558) OR (`craft_blitz_elementcaches`.`elementId`=2776970) OR (`craft_blitz_elementcaches`.`elementId`=2794472) OR (`craft_blitz_elementcaches`.`elementId`=2791421) OR (`craft_blitz_elementcaches`.`elementId`=2789269) OR (`craft_blitz_elementcaches`.`elementId`=2784487) OR (`craft_blitz_elementcaches`.`elementId`=2783337) OR (`craft_blitz_elementcaches`.`elementId`=2785306) OR  ........

I wish I knew what type of action was triggering that, we have a lot of users and during the day a lot of visitors. What can I do to assist?

[bencroker]

This is part of the refresh cache process, but having so many element IDs might indicate that a bulk element action is taking place. How are you catching the element query above?

[Thijmen]

I was catching the query in the process list in my Database Tool (Navicat). Via that way, I saw that it was a very, very long running process and thought: hey, that's weird, let me inspect. Once I saw how many bytes the query was, I created the issue here.

[bencroker]

Can you check what types of elements those are (2806947, 2806829, ...) by looking in the elements table? If they are entries, perhaps you could check which section they belong to by looking in the entries table.

[Thijmen]

They all seem to be craft\elements\Entry.

[Thijmen]

Yeah they all belong it seems to the same section, which are "articles" in our case.

[Thijmen]

Is there anything I can do to prevent this in the future? Any other grasps? If only there was a way to identify what causes an uncache event.

[bencroker]

Any chance SEOmatic setting changes might be triggering this? Specifically, when its container caches are invalidated.

[bencroker]

Any update on this @Thijmen?

[Thijmen]

Hi @bencroker sorry for the late response. We do use SEOmatic indeed. I have no clue about container caches, I am sorry. We're running 4.0.16 and we are aiming to update SEOmatic in the future.

Any more grasps?

[bencroker]

Well one explanation is that users changing SEOmatic settings is triggering full Blitz refresh jobs. But I’ve addressed part of the issue by optimising the blitz_elementcaches query in 2fbd93c for the next release, so hopefully that will help.

[bencroker]

Released in version 4.5.0.

[Thijmen]

Thanks Ben, appreciate your work!