search

deliciousbrains / wp-offload-ses-lite

WP Offload SES Lite sends all outgoing WordPress emails through Amazon Simple Email Service (SES) instead of the local wp_mail() function.
https://wordpress.org/plugins/wp-ses/
14 stars 11 forks source link

Fix exponential growth of garbage cleaning query run time #32

Open dr-yd opened 2 years ago

dr-yd commented 2 years ago

Grouping by attachment ID before joining produces the same behavior as intended and reduced the query run time from 55min at 100% CPU to 700ms for our use case with 42k attachments and 130k email_attachment entries.

mattgrshaw commented 2 years ago

@dr-yd Thanks for the PR! We'll take a look at this for our next release.

mark-barnes-sp commented 2 years ago

Our server hasn't been brought down, but we're experiencing lots of database errors with SQL statements like this one: INSERT INTO wp_oses_email_attachments (email_id, attachment_id, filename) VALUES (311693, 31692, '') Lock wait timeout exceeded; try restarting transaction

A slow update statement on the wp_oses_email_attachments would explain that error. We have ~32k records in wp_oses_attachments and wp_oses_email_attachments. I haven't tested the PR, but I'd be very glad to see improvement in this query.