Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-01-02T13:41:40.607Z
Answers.Text: 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.
Answers.UserDisplayName: hisnameisjimmy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-01-03T02:08:38.263Z
Answers.Text: Just from experience hunting these things down, what you think is the problem query isn't always; thus the slow query log suggestion. FWIW.
Answers.UserDisplayName: andrew.welch
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-01-08T11:51:43.690Z
Answers.Text: 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
Answers.UserDisplayName: hisnameisjimmy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-31T18:49:48.940Z
Answers.LastActivityDate: 2017-12-31T18:49:48.940Z
Answers.UserDisplayName: andrew.welch
Body: <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>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-12-29T21:49:26.780Z
Comments.Text: 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.
Comments.UserDisplayName: Brad Bell
Tags: <performance><mysql><query>
Title: MySQL CPU pegged on specific query
ThreadText: # 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?](https://craftcms.stackexchange.com/questions/19909/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](https://i.sstatic.net/0fN6J.png)](https://i.sstatic.net/0fN6J.png)
  
  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  
  
  ---
  
  