MailPoet subscriber click tracking causes excessive DB writes, leading to performance problems
The issue is especially noticeable on popular newsletters, such as those sent by pcp.gc.cuny.edu.
It appears that MailPoet is about to come out with a totally rewritten version of the plugin: https://wordpress.org/support/topic/the-mailpoet-3-beta-is-now-available/ I took a very brief look at the beta, and it does appear that the technique used for tracking clicks has been totally refactored, in a way that might reduce issues.
For the time being, I've written a bit of code that forces MailPoet to skip the recording of subscriber clicks. I'd like to propose leaving that code in place until the stable MailPoet 3 is available, at which point we can run some tests with the new system.
#1 Updated by Boone Gorges about 5 years ago
- Target version changed from 1.10.3 to 1.10.2
Ray, you're copied as an FYI, in case I go into a coma and you have to pick up where I left off.
I spent most of the day working on this today. As I was in the middle of rolling out the fix, pcp.gc.cuny.edu published another newsletter, which crashed the site. So what I'd hoped would be a reasonably painless deploy ended up being fairly horrific.
Anyway, here's how the caching layer works:
- I added a custom action to MailPoet's email model, so that I can detect whenever an email is being created or updated
- Hooked to that action, I have an mu-plugin (wp-content/mu-plugins/assets/mailpoet.php) that uses wp_remote_get() to fetch a static copy of the HTML generated by MailPoet
- That HTML is saved to a directory with the following format: wp-content/blogs.dir/1/mailpoet-cache/[domain]/[email_id].html. Eg: wp-content/blogs.dir/1/mailpoet-cache/pcp.gc.cuny.edu/399.html
- There's a rule in .htaccess that detects requests of the form ?wysija-page=1&email_id=399, that do not contain 'controller=email'. This is pretty broad, but it catches all cases we're interested in. ('controller=email' is a way to fetch the raw email content, which we need when generating the cache)
- These requests go to a small PHP script wp-content/mailpoet-cache.php that loads the static cache. This probably could have been skipped with a fancier rewrite rule, but I was having problems with multiple backreferences in separate RewriteCond lines, and the site was in the process of crashing, so I went with this
I wrote a script to crawl each site on the Commons that runs MailPoet, in order to generate the static versions of all existing emails. This is in my user's ~/wp-cli-scripts directory. The first time I ran the script, it threatened to DOS the server, so I throttled it to generating one email every 30 seconds. When pcp.gc.cuny.edu sent out their newsletter this afternoon, I had to stop this process and skip straight to generating the cached version of the new email. Now I'm running the script again. It will take a few hours to complete. In the meantime, people clicking through to old newsletters may get 404s. There's nothing I can do to speed this up, unfortunately.
I'll continue to monitor the import process as well as site traffic/performance, and will update this ticket when things have stabilized.
#3 Updated by Boone Gorges about 5 years ago
- Status changed from New to Resolved
The update routine has nearly finished. Performance problems look like they're taken care of, and traffic continues to flow to these newsletters.
I've excluded wysija-newsletters from our automatic plugin update routines, so we don't overwrite the custom action I added.
I will refrain from sending an upstream enhancement request, because they're in the process of releasing a rewrite. See #6821 for further discussion.