wpp_cache_event Locking Up My Database

Hi mattjo,

The cronjob deletes from the wp_popularpostsdatacache table all entries that meet any of the following conditions:

1. No views registered within the last 30 days.
2. Trashed / deleted posts (no need to store info on a post that no longer exists).

The reason this was implemented is simple: without it, the wp_popularpostsdatacache table would grow exponentially which would result in a possible performance hit on your database (and likely a warning from your hosting, too). Also, the event is scheduled to run at midnight (server time) when most sites have low to no activity.

About your second question: according to the WordPress documentation, it is possible to set a custom interval (default options are hourly, twicedaily and daily) using the wp_get_schedules() filter.

Hey

Thanks for the quick reply.

So I have this plugin called Chrontrol and it lets me edit cron jobs. have you heard of it? Can I just use that plugin to set the custom interval, seems easier that way. For instance, I can set it to daily or weekly. What do you think?

Also, why is this cron job causing my whole database to lock up? I’ve been using the plugin for a year and this only started about a month ago. My server is very good, I have more than enough room, RAM, memory, database connections etc. So I’m not sure why it does that.

Don’t know, actually you’re the first one reporting issues with the schedule event. Unless you have thousands of posts / pages in the trash, there should be no reason for a database lock-up. I did make a couple of changes to the scheduled event a few updates ago (added the ability to delete views data from trashed posts), so I’ll look into the queries and see if I can improve things a little bit more.

About the Chrontrol plugin, sounds very interesting! Please try to change the event to weekly and let me know how it goes – but keep in mind that if you disable the plugin (for whatever reason) it’ll delete the cron job and create a new one upon reactivation, so my guess is that you’ll have to edit it again.

Will weekly be often enough though? I don’t want the table to grow exponentially like you said.

Also, my server admin asked me to ask you if the plugin/cron job will work with innodb.

That depends on your site’s traffic. How many daily visitors you get per day?

About innodb, yes that should be fine. That’s what I use on my development blog and haven’t had any issues so far. Make sure you backup your database before switching to innodb, though (I don’t think you’ll have any issues with it, but better safe than sorry).

Daily visitors is about 35-40k but we’ve had days with spikes up to 80k and been fine. 35k-40k though is average

That’s quite a lot of visitors. Try with a shorter interval instead, say every two-three days and see how it goes. One week might be just too much.

I just double checked, Chrontrol only lets you do hourly, daily or weekly. I can’t do every two days.

Then you’d need to use the code I posted earlier (and also modify the plugin to use the custom interval). If you need help with it, let me know and I’ll try to find some spare time.