0

I read these two posts

MySQL CPU pegged on specific query 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.

5
  • 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. – RitterKnight Jun 13 at 15:03
  • 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. – zhua89 Jun 14 at 13:56
  • @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? – RitterKnight Jun 15 at 2:59
  • 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 – zhua89 2 days ago
  • 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. – RitterKnight 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.