Multisite Cron Issues

Hi

I’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 documentation the requests often time out.

We are running a fairly big (150+) multisite – but this shouldn’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.

Queries causing issues

UPDATE wp_oses_attachments attachments
LEFT JOIN wp_oses_email_attachments email_attachments ON email_attachments.attachment_id = attachments.id
SET attachments.gc = ?
WHERE email_attachments.attachment_id IS NULL

443462 ms (call count 2)

DELETE wp_oses_email_attachments
FROM wp_oses_email_attachments
LEFT JOIN wp_oses_emails ON wp_oses_email_attachments.email_id = wp_oses_emails.email_id
WHERE wp_oses_emails.email_id IS NULL

102463 ms (call count 2)

DELETE wp_oses_emails, wp_oses_clicks FROM wp_oses_emails
LEFT JOIN wp_oses_clicks ON wp_oses_emails.email_id = wp_oses_clicks.email_id
WHERE wp_oses_emails.email_created < NOW() - INTERVAL ? DAY
AND wp_oses_emails.subsite_id = ?

1127 ms (call count 2)

Tables and row counts

wp_oses_emails = 15754
wp_oses_attachments = 4051
wp_oses_email_attachments = 4677

Changes (based on the queries) I suggest making:

• wp_oses_emails: adding a basic index on the subsite_id column (not composite)
• 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.

The cleanup cron used to average around 5-10 minutes to run per site, now it runs in .1s per site.

Hope this helps someone!

Cheers,
Michael

• This topic was modified 3 years, 7 months ago by michaelcartersa. Reason: formatting