<p>I wouldn't be <em>too</em> worried about MySQL using 100% CPU, especially on a multi-core VPS. But a good way to track down things like this is to <a href="https://ma.ttias.be/mysql-slow-query-log-without-restart/" rel="nofollow noreferrer">enable the MySQL slow query log</a></p>

<p>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.</p>

CC BY-SA 3.0
2018-01-02T13:41:40.607Z
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
CC BY-SA 3.0
2018-01-03T02:08:38.263Z
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
CC BY-SA 3.0
2018-01-08T11:51:43.690Z
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
CC BY-SA 3.0
2017-12-31T18:49:48.940Z
2017-12-31T18:49:48.940Z
andrew.welch
<p>Very similar (almost exact) to this question: <a href="https://craftcms.stackexchange.com/questions/19909/poor-performance-on-midsized-site-queries-without-index">Poor performance on midsized site / Queries without index?</a></p>

<p>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).</p>

<p>The troublesome query:</p>

<pre><code>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 &lt;= '2017-12-29 15:41:28') AND ((entries.expiryDate is null) OR (entries.expiryDate &gt; '2017-12-29 15:41:28')))) AND (elements_i18n.enabled = 1)) AND ((sources1.sourceId='52') OR (targets1.targetId='52'))
GROUP BY `elements`.`id`) derivedElementsTable
</code></pre>

<p>What are CPU has looked like:</p>

<p><a href="https://i.sstatic.net/0fN6J.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/0fN6J.png" alt="enter image description here"></a></p>

<p>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.</p>

<p>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.</p>

CC BY-SA 3.0
2017-12-29T21:49:26.780Z
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
<performance><mysql><query>
MySQL CPU pegged on specific query
# MySQL CPU pegged on specific query

(Asked by: username_0 on 2017-12-29)

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:

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.

**Tags:** performance, mysql, query
> **Comments:**
> **username_1**: 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.  

---

## 1 answers

### Answer by username_2 on 2017-12-31. Score: 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](https://ma.ttias.be/mysql-slow-query-log-without-restart/)

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.

> **Comments:**
> **username_0**: 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.  
> **username_2**: Just from experience hunting these things down, what you think is the problem query isn't always; thus the slow query log suggestion. FWIW.  
> **username_0**: 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  

---

