Body: <p>I read these two posts</p>
  <p><a href="https://craftcms.stackexchange.com/questions/23320/mysql-cpu-pegged-on-specific-query">MySQL CPU pegged on specific query</a>
  <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>and and am in a similar situation although for us it actually breaks our site and causes outages. Our craft application is hosted on Platform SH and has Fastly sitting on the edge for some caching and gzipping. Our site has gone down from time to time and when looking at metrics we see the node our sql db is on gets pegged and redlines (uses up to 100% of cpu) after a while it'll cause an outage and return a 503.</p>
  <p>From platforms's support engineer this is one that was seen run several times per second</p>
  <p><code>SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_accountId`, `content`.`field_accredValue`, `content`.`field_address2`, `content`.`field_alertLink`, `content`.`field_alertEnabled`, `content`.`field_person_alertEnabled`, `content`.`field_altText`, `content`.`field_articleUrl`, `content`.`field_associated_form`, `content`.`field_attendEventLink`, `content`.`field_bannerCopy`, `content`.`field_bannerHeadline`, `content`.`field_bannerTitle`, `content`.`field_biography`, `content`.`field_callout`, `content`.`field_city`, `content`.`field_commentsEnabled`, `content`.`field_companyDescription`, `content`.`field_companyName`, `content`.`field_errorContentTitle`, `content`.`field_contributeDescription`, `content`.`field_cookieCta`, `content`.`field_cookieDescription`, `content`.`field_cookieLinkTitle`, `content`.`field_country`, `content`.`field_cta`, `content`.`field_deadline`, `content`.`field_description`, `content`.`field_detail`, `content`.`field_email`, `content`.`field_employees`, `content`.`field_excludeArticles`, `content`.`field_excludeFromInc`, `content`.`field_expirationDate`, `content`.`field_eyebrow`, `content`.`field_facebook`, `content`.`field_fancyTitle`, `content`.`field_featuredContentTitle`, `content`.`field_firstName`, `content`.`field_founded`, `content`.`field_foundingMbr`, `content`.`field_gatedEntryId`, `content`.`field_heading`, `content`.`field_errorHeadlineContentTitle`, `content`.`field_heroDescription`, `content`.`field_jobTitle`, `content`.`field_lastName`, `content`.`field_linkedin`, `content`.`field_location`, `content`.`field_member`, `content`.`field_memberSince`, `content`.`field_message1`, `content`.`field_message2`, `content`.`field_message3`, `content`.`field_monthCreated`, `content`.`field_naicsCode`, `content`.`field_notes`, `content`.`field_optInToCo`, `content`.`field_partnerEyebrowLink`, `content`.`field_partnerName`, `content`.`field_phone`, `content`.`field_preview`, `content`.`field_professionalTitle`, `content`.`field_publishedBy`, `content`.`field_publishedDate`, `content`.`field_revenueRange`, `content`.`field_sectionSelector`, `content`.`field_seo`, `content`.`field_sideFeatHeadline`, `content`.`field_sideFeatLink`, `content`.`field_sideFeatTitle`, `content`.`field_simpleWysiwyg`, `content`.`field_siteDescription`, `content`.`field_source`, `content`.`field_state`, `content`.`field_streetAddress`, `content`.`field_subscribeToCo`, `content`.`field_subscribeToMo`, `content`.`field_summary`, `content`.`field_themeDescription`, `content`.`field_themeTitle`, `content`.`field_themeVideo`, `content`.`field_toggle`, `content`.`field_tooltip`, `content`.`field_twitter`, `content`.`field_ugcTitle`, `content`.`field_customCode`, `content`.`field_visibility`, `content`.`field_website`, `content`.`field_yearCreated`, `content`.`field_youtubeLink`, `content`.`field_zip`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level` FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId` FROM `elements` `elements` INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id` INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id` INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id` INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (`structureelements`.`structureId`=1) WHERE (`elements`.`archived`=0) AND (((`elements`.`enabled`=1) AND (`elements_sites`.`enabled`=1)) AND (`entries`.`postDate` &lt;= '2021-06-04 21:58:00') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` &gt; '2021-06-04 21:58:00'))) AND (`elements`.`dateDeleted` IS NULL) AND ((`structureelements`.`lft` &lt; 20696) AND (`structureelements`.`rgt` &gt; 20697) AND (`structureelements`.`root`=1)) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL) ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC LIMIT 1) `subquery` INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId` INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId` INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId` INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId` INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId`=1) ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC;</code></p>
  <p>Couldn't figure out where in templates this might be getting called. I believe it is work being done in the control panel itself that causes these outages.</p>
  <p>Additionally we also have cron jobs that we now run on weekends that also cause cpu to spike and occasionally will take down the site when resaving all entries under a certain section. Is there a way to make craft resave/entries --section 'sectionHandle' less strenous? Unclear since that is just a native craft command and not something custom.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-06-13T15:03:53.807Z
Comments.Text: Just curious what is the reason you're running a resave periodically?  That can certainly tax the CPU and give MySQL a workout. How does platform.sh run mysql - are you running it on the same instance/box? What plan do you have?  I would hazard a guess that your hosting is under-provisioned for what you need.
Comments.UserDisplayName: RitterKnight
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-06-14T13:56:42.863Z
Comments.Text: Thanks @RitterKnight. For whatever reason we got feedback from our writers that after a while search wouldn't quite work. typing in the exact words of a title wouldn't even show the article. i noticed if you resave any particular article and reindexes it would make it searchable again so I have a cron job to run this job every saturday but for all articles.
Comments.UserDisplayName: zhua89
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-06-15T02:59:19.840Z
Comments.Text: @zhua88: that's pretty strange. are you having any other data loss? it sounds to me like there might be more than one problem with how mysql  is setup?
Comments.UserDisplayName: RitterKnight
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-06-15T14:36:01.850Z
Comments.Text: Additionally I just received some insight that our queries are doing a full table scan which apparently usually means there is an index missing on the tables
  
  `Time: 210612  9:28:35
  # User@Host: b4mhz722nsjh4[b4mhz722nsjh4] @  [192.168.0.7]
  # Thread_id: 2408679  Schema: b4mhz722nsjh4  QC_hit: No
  # Query_time: 0.567324  Lock_time: 0.000205  Rows_sent: 1  Rows_examined: 142606
  # Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes`
  
  Haven't found any documentation or craft commands yet that could alleviate that issue. @RitterKnight
Comments.UserDisplayName: zhua89
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-06-15T17:50:34.280Z
Comments.Text: If It were me I'd send a copy of your db to support@craftcms.com and see if support can help.  Could be your setup, could be a bug.
Comments.UserDisplayName: RitterKnight
Tags: <database><performance><mysql>
Title: Certain Queries Pegging CPU and Causing Production Site to go Down
ThreadText: # Certain Queries Pegging CPU and Causing Production Site to go Down
  
  (Asked by: username_0 on 2021-06-10)
  
  I read these two posts
  
  [MySQL CPU pegged on specific query](https://craftcms.stackexchange.com/questions/23320/mysql-cpu-pegged-on-specific-query)
  [Poor performance on midsized site / Queries without index?](https://craftcms.stackexchange.com/questions/19909/poor-performance-on-midsized-site-queries-without-index)
  
  and and am in a similar situation although for us it actually breaks our site and causes outages. Our craft application is hosted on Platform SH and has Fastly sitting on the edge for some caching and gzipping. Our site has gone down from time to time and when looking at metrics we see the node our sql db is on gets pegged and redlines (uses up to 100% of cpu) after a while it'll cause an outage and return a 503.
  
  From platforms's support engineer this is one that was seen run several times per second
  
  `SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_accountId`, `content`.`field_accredValue`, `content`.`field_address2`, `content`.`field_alertLink`, `content`.`field_alertEnabled`, `content`.`field_person_alertEnabled`, `content`.`field_altText`, `content`.`field_articleUrl`, `content`.`field_associated_form`, `content`.`field_attendEventLink`, `content`.`field_bannerCopy`, `content`.`field_bannerHeadline`, `content`.`field_bannerTitle`, `content`.`field_biography`, `content`.`field_callout`, `content`.`field_city`, `content`.`field_commentsEnabled`, `content`.`field_companyDescription`, `content`.`field_companyName`, `content`.`field_errorContentTitle`, `content`.`field_contributeDescription`, `content`.`field_cookieCta`, `content`.`field_cookieDescription`, `content`.`field_cookieLinkTitle`, `content`.`field_country`, `content`.`field_cta`, `content`.`field_deadline`, `content`.`field_description`, `content`.`field_detail`, `content`.`field_email`, `content`.`field_employees`, `content`.`field_excludeArticles`, `content`.`field_excludeFromInc`, `content`.`field_expirationDate`, `content`.`field_eyebrow`, `content`.`field_facebook`, `content`.`field_fancyTitle`, `content`.`field_featuredContentTitle`, `content`.`field_firstName`, `content`.`field_founded`, `content`.`field_foundingMbr`, `content`.`field_gatedEntryId`, `content`.`field_heading`, `content`.`field_errorHeadlineContentTitle`, `content`.`field_heroDescription`, `content`.`field_jobTitle`, `content`.`field_lastName`, `content`.`field_linkedin`, `content`.`field_location`, `content`.`field_member`, `content`.`field_memberSince`, `content`.`field_message1`, `content`.`field_message2`, `content`.`field_message3`, `content`.`field_monthCreated`, `content`.`field_naicsCode`, `content`.`field_notes`, `content`.`field_optInToCo`, `content`.`field_partnerEyebrowLink`, `content`.`field_partnerName`, `content`.`field_phone`, `content`.`field_preview`, `content`.`field_professionalTitle`, `content`.`field_publishedBy`, `content`.`field_publishedDate`, `content`.`field_revenueRange`, `content`.`field_sectionSelector`, `content`.`field_seo`, `content`.`field_sideFeatHeadline`, `content`.`field_sideFeatLink`, `content`.`field_sideFeatTitle`, `content`.`field_simpleWysiwyg`, `content`.`field_siteDescription`, `content`.`field_source`, `content`.`field_state`, `content`.`field_streetAddress`, `content`.`field_subscribeToCo`, `content`.`field_subscribeToMo`, `content`.`field_summary`, `content`.`field_themeDescription`, `content`.`field_themeTitle`, `content`.`field_themeVideo`, `content`.`field_toggle`, `content`.`field_tooltip`, `content`.`field_twitter`, `content`.`field_ugcTitle`, `content`.`field_customCode`, `content`.`field_visibility`, `content`.`field_website`, `content`.`field_yearCreated`, `content`.`field_youtubeLink`, `content`.`field_zip`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level` FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId` FROM `elements` `elements` INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id` INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id` INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id` INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (`structureelements`.`structureId`=1) WHERE (`elements`.`archived`=0) AND (((`elements`.`enabled`=1) AND (`elements_sites`.`enabled`=1)) AND (`entries`.`postDate` <= '2021-06-04 21:58:00') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2021-06-04 21:58:00'))) AND (`elements`.`dateDeleted` IS NULL) AND ((`structureelements`.`lft` < 20696) AND (`structureelements`.`rgt` > 20697) AND (`structureelements`.`root`=1)) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL) ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC LIMIT 1) `subquery` INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId` INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId` INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId` INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId` INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId`=1) ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC;`
  
  Couldn't figure out where in templates this might be getting called. I believe it is work being done in the control panel itself that causes these outages.
  
  Additionally we also have cron jobs that we now run on weekends that also cause cpu to spike and occasionally will take down the site when resaving all entries under a certain section. Is there a way to make craft resave/entries --section 'sectionHandle' less strenous? Unclear since that is just a native craft command and not something custom.
  
  **Tags:** database, performance, mysql
  
  > **Comments:**
  > **username_1**: Just curious what is the reason you're running a resave periodically?  That can certainly tax the CPU and give MySQL a workout. How does platform.sh run mysql - are you running it on the same instance/box? What plan do you have?  I would hazard a guess that your hosting is under-provisioned for what you need.  
  > **username_0**: Thanks @username_1. For whatever reason we got feedback from our writers that after a while search wouldn't quite work. typing in the exact words of a title wouldn't even show the article. i noticed if you resave any particular article and reindexes it would make it searchable again so I have a cron job to run this job every saturday but for all articles.  
  > **username_1**: @zhua88: that's pretty strange. are you having any other data loss? it sounds to me like there might be more than one problem with how mysql  is setup?  
  > **username_0**: Additionally I just received some insight that our queries are doing a full table scan which apparently usually means there is an index missing on the tables  `Time: 210612  9:28:35 # User@Host: b4mhz722nsjh4[b4mhz722nsjh4] @  [192.168.0.7] # Thread_id: 2408679  Schema: b4mhz722nsjh4  QC_hit: No # Query_time: 0.567324  Lock_time: 0.000205  Rows_sent: 1  Rows_examined: 142606 # Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes`  Haven't found any documentation or craft commands yet that could alleviate that issue. @username_1  
  > **username_1**: If It were me I'd send a copy of your db to support@craftcms.com and see if support can help.  Could be your setup, could be a bug.  
  
  ---
  
  ## 0 answers
  
  