1

Very similar (almost exact) to this question: Poor performance on midsized site / Queries without index?

CPU keeps getting pegged to 100% for MySQL, which makes the admin interface very slow (the site is cached using fastCGI, so generally the site still performs well regardless).

The troublesome query:

FROM (SELECT `elements`.`id` AS `elementsId`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`
FROM `elements` `elements`
JOIN `elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `content` `content` ON content.elementId = elements.id
LEFT JOIN `relations` `sources1` ON (sources1.targetId = elements.id)
LEFT JOIN `relations` `targets1` ON (targets1.sourceId = elements.id)
JOIN `entries` `entries` ON entries.id = elements.id
JOIN `sections` `sections` ON sections.id = entries.sectionId
LEFT JOIN `structures` `structures` ON structures.id = sections.structureId
LEFT JOIN `structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
WHERE (((((elements_i18n.locale = 'en_us') AND (content.locale = 'en_us')) AND (elements.archived = 0)) AND ((elements.enabled = 1) AND (elements_i18n.enabled = 1) AND (entries.postDate <= '2017-12-29 15:41:28') AND ((entries.expiryDate is null) OR (entries.expiryDate > '2017-12-29 15:41:28')))) AND (elements_i18n.enabled = 1)) AND ((sources1.sourceId='52') OR (targets1.targetId='52'))
GROUP BY `elements`.`id`) derivedElementsTable

What are CPU has looked like:

enter image description here

I'm not sure where I should start hunting this down. This appears to only happen for the en_us locale, I haven't recorded another locale showing this. This is on a staging site on Digital Ocean, so it's getting zero traffic. It's also being managed by Forge on PHP 7.1.

Could I get any advice for hunting down the php that might be leading to this? It's not happening as a result of any clear action.

hisnameisjimmy
  • 273
  • 3
  • 9
  • My first guess would be it's coming from an element query in one of your templates (although could be a plugin, too) that involves relations and a call to .total(). The .total() is because that's the only place a derivedElementsTable is used. – Brad Bell Dec 29 '17 at 21:49

1 Answers1

2

I wouldn't be too worried about MySQL using 100% CPU, especially on a multi-core VPS. But a good way to track down things like this is to enable the MySQL slow query log

You can set whatever thresholds you want, and then peruse the log at your convenience to see the queries that are actually slowing things down.

andrew.welch
  • 11,551
  • 22
  • 31
  • The main issue with MySQL pegging the CPU is that it makes the admin interface unresponsive. I was able to see the queries that were doing it, one of which I posted in my question above, so it wasn't a matter of finding the query. I really just needed direction on how to find the template code that might be producing that result. Ultimately, I rebooted the server and that seemed to clear things up, and I haven't seen it recur. Hopefully that's the end of it. – hisnameisjimmy Jan 02 '18 at 13:41
  • Just from experience hunting these things down, what you think is the problem query isn't always; thus the slow query log suggestion. FWIW. – andrew.welch Jan 03 '18 at 02:08
  • Hmm, ok, I'll have to look into it more. We have a site with about 8k pages, with multiple locales, and I think part of it also may have been our cacheing strategy, maybe related to this? https://craftcms.stackexchange.com/questions/2059/long-deleting-stale-template-caches – hisnameisjimmy Jan 08 '18 at 11:51