{"id":14284064,"date":"2021-04-06T17:00:36","date_gmt":"2021-04-06T17:00:36","guid":{"rendered":"https:\/\/wordpress.org\/support\/topic\/multisite-cron-issues\/"},"modified":"2021-04-06T17:01:56","modified_gmt":"2021-04-06T17:01:56","slug":"multisite-cron-issues","status":"publish","type":"topic","link":"https:\/\/wordpress.org\/support\/topic\/multisite-cron-issues\/","title":{"rendered":"Multisite Cron Issues"},"content":{"rendered":"<p>Hi<\/p>\n<p>I&#8217;ve had some issues on the site with long mysql query times on the cron tasks.  Even if you setup the cron to run externally as mentioned in the <a href=\"https:\/\/deliciousbrains.com\/wp-offload-ses\/doc\/cron-setup\/\">documentation<\/a> the requests often time out.<\/p>\n<p>We are running a fairly big (150+) multisite &#8211; but this shouldn&#8217;t really be an issue.  I had a look at the queries and the database tables and I figured out that the indexes need to be changed slightly.<\/p>\n<p><strong>Queries causing issues<\/strong><\/p>\n<pre><code>\nUPDATE wp_oses_attachments attachments\nLEFT JOIN wp_oses_email_attachments email_attachments ON email_attachments.attachment_id = attachments.id\nSET attachments.gc = ?\nWHERE email_attachments.attachment_id IS NULL\n\n443462 ms (call count 2)\n\nDELETE wp_oses_email_attachments\nFROM wp_oses_email_attachments\nLEFT JOIN wp_oses_emails ON wp_oses_email_attachments.email_id = wp_oses_emails.email_id\nWHERE wp_oses_emails.email_id IS NULL\n\n102463 ms (call count 2)\n\nDELETE wp_oses_emails, wp_oses_clicks FROM wp_oses_emails\nLEFT JOIN wp_oses_clicks ON wp_oses_emails.email_id = wp_oses_clicks.email_id\nWHERE wp_oses_emails.email_created &lt; NOW() - INTERVAL ? DAY\nAND wp_oses_emails.subsite_id = ?\n\n1127 ms (call count 2)\n<\/code><\/pre>\n<p><strong>Tables and row counts<\/strong><\/p>\n<pre><code>\nwp_oses_emails = 15754\nwp_oses_attachments = 4051\nwp_oses_email_attachments = 4677\n<\/code><\/pre>\n<p><strong>Changes (based on the queries) I suggest making:<\/strong><\/p>\n<ul>\n<li>wp_oses_emails: adding a basic index on the subsite_id column (not composite)<\/li>\n<li>wp_oses_email_attachments: removing the composite index completely, adding two separate indexes for email_id and attachment_id.  No need to have a index on the filename column.<\/li>\n<\/ul>\n<p>The cleanup cron used to average around 5-10 minutes to run per site, now it runs in .1s per site.<\/p>\n<p>Hope this helps someone!<\/p>\n<p>Cheers,<br \/>\nMichael<\/p>\n","protected":false},"template":"","class_list":["post-14284064","topic","type-topic","status-publish","hentry"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic\/14284064","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic"}],"about":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/types\/topic"}],"version-history":[{"count":1,"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic\/14284064\/revisions"}],"predecessor-version":[{"id":14284071,"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic\/14284064\/revisions\/14284071"}],"wp:attachment":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/media?parent=14284064"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}