No products in the cart.
Listen to the automated audio version of this article:
I love WordPress. I have used it since it was called B2/Cafelog, probably around 2002. Then I used its public .com version for my successful food-blog and the self-hosted one for many of my clients, including big names like JP Morgan or Bank Of New York Mellon. According to the latest stats, in 2020 WordPress powers 35% of the entire Internet. It means it’s installed on just shy of 500 million websites. And it’s not just blogs, but also companies’ websites, like CNN, UPS, Best Buy…
The same goes for e-commerce. My store uses WooCommerce, which was acquired by Automattic (the company that owns WordPress) in 2015. And WooCommerce is also installed in over 30% of all the online shops you purchase from.
Most of the functionalities you can integrate in WordPress are added via plugins. And while WordPress is a simple and very reliable platform, and it always improves, using plugins is not always straightforward.
I’m going to get to the point in a moment.
WooCommerce Bookings
I always had an online solution for my bookings. I sometimes announce meetups on my Instagram @fabienb, but always used an online service to manage workshops and such. But I was not keen on having my stuff spread online in different places. I wanted to gather everything under my WordPress roof. So I paid for WooCommerce Bookings (not cheap, but I used the Black Friday discount).
I set WooCommerce Bookings to manage my 1-1 Masterclass. And then, after the Covid-19 outbreak, I also used it to promote my online sessions. It works like a charm: you open the page, select a date and a time slot, apply your discount (if you’re a newsletter subscriber) and then book. Easy peasy.
On my end, I get a notification immediately, and we both get reminders, etc.
Google Calendar sync
Among the settings in WooCommerce Bookings, there is the option to sync with Google Calendar. All the bookings are automatically added to my calendar. Even better, if I go to attend an event and I set it in my calendar, then the plugin makes that time slot unavailable for bookings. A simple and perfect example of automation.
It’s only a little overzealous.
Here Comes the Problem
I woke up to an alarming email this morning: “your database is over 3Gb in size, if you don’t reduce the size your write permissions will be suspended”
WAAAAAAT? How did I reach 3Gb of database data?
Only over a week ago, I was happy to have optimised this WordPress blog to reach a rating of A(97%) on Pagespeed. Improving from an E(54%) after I installed the 90 plugins I have here. But suddenly, it all started going slow again. And I was getting strange database connection errors.
I thought this was due to some new update of one of the plugins (these things happen), and I was hoping for a fix. It never occurred to me to go check the database. Until I received the email.
A note on Hostinger, my hosting provider
Before I dive in, allow me to thank my hosting provider Hostinger. Aside from being one of the best Wordpress-optimised hosts in the world (just google them and see for yourself), their customer care was in touch with me the whole time. I figured out the solution myself, but they were also looking for a fix on their end, and the communication was prompt and flawless. So, a huge thank you, and I definitely recommend their service.
OK, so let’s dive in now…
Connecting the Dots: Database Bloat
The problem with having a large database is that WordPress takes longer to scan it. So, any request on the front-end, like simply opening a page, takes much more time and is more prone to issues. I was getting database connection errors, most likely because it was taking so long it was going into timeout.
I quickly checked my database with PHPMyAdmin, which is conveniently available in the well-designed Hostinger admin panel. To my big surprise, I found 2 database tables that were way over 1Gb each. That’s enormous! And both tables were linked to the WooCommerce Bookings plugin (all of the above will start to make sense now).
Unfortunately, not much information is available online about this specific issue. There are articles about optimising a WordPress database, but they’re all general information and not related to the Bookings plugin.
I found only one person writing about this specific issue, and I’m glad I found him! Introducing Neil of WP Beaches in Sydney.
In a blog post, Neil mentions those 2 tables and the steps to clean them. In doing so, I figured out exactly what caused my problem.
Connecting the Dots: Overzealous Sync
WooCommerce Bookings doesn’t really know when you will be adding an item to your calendar, therefore it needs to check constantly for an update. And I just found it does this EVERY 5 MINUTES!
There are no settings to change the interval (but I’ll forward this post to WooCommerce to ask them to add it), so you’re left to what the plugin thinks is best. There is also no way to set an expiry date on the actions taken: the default is 30 days to clear the database of any completed action. But one action every 5 minutes for 30 days amounts to over 8K actions, not counting the times the syncing fails, which is another row in the table. No wonder the database reached 1Gb!
Fixing it
So here’s how you clean up the database.
wp_actionscheduler_logs
This one only needs to be emptied. And that’s easily done with PHPMyAdmin by clicking on the ‘Empty’ link and confirming the action.
wp_actionscheduler_actions
This is also easy to clear, but will take a few more steps.
In PHPMyAdmin, open the SQL tab and copy the following code. Do this one line at a time, and click Go each time to run the code.
DELETE FROM `wp_actionscheduler_actions` WHERE `status` = 'canceled'DELETE FROM `wp_actionscheduler_actions` WHERE `status` = 'failed'DELETE FROM `wp_actionscheduler_actions` WHERE `status` = 'complete'Cancelled, failed and completed actions no longer need to be available as they are already passed. So these are safe to remove.
Then there’s another one, for all pending actions:
DELETE FROM `wp_actionscheduler_actions` WHERE `status` = 'pending'This really depends on what actions you have pending at the moment. But all of mine were generated by the issue caused by the syncing, so I ran this as well. If you don’t want to delete all your pending actions, you’ll have to go through them manually from within WooCommerce.
You can find all the actions from the WooCommerce menu in Wordpress. WooCommerce > Status > Scheduled Actions.
Neil also wrote a very simple PHP snippet that you can add to your theme to reduce the 30 days clearance default to just 7 days:
add_filter( 'action_scheduler_retention_period', 'wpb_action_scheduler_purge' );
/*
* Change Action Scheduler default purge to 1 week
*/
function wpb_action_scheduler_purge() {
return WEEK_IN_SECONDS;
}The script needs to be added to functions.php. Better if you have a child theme or something you created yourselves so the file is not overwritten by future theme updates.
You can also use a snippets plugin for WordPress, but that means adding more stuff to your database…
This was all it took to clear the database tables of the useless data.
Neil, thank you so much!
Next Steps
Obviously, this is only a temporary fix. If I want to use the 2-way synchronisation with Google Calendar, I’ll have to face the issue again soon.
This a very useful feature to me, so I want it enabled. Right now, I’m opting to only have Bookings sync with Google Calendar and not the other way around. And I will reach out to the WooCommerce Support Team to see if a simple extra option could be implemented. One that would allow selecting a check interval less aggressive than 5 minutes. And maybe also one to set a different time to clear the actions.
If I could set these to a daily check and 7 days clearance, I should never have to worry about it. And neither is anybody else :) You’re welcome.
Hopefully I’ll have good news to update this article soon.
update Feb 2024: I just found out that this happens with another useful Wordpress plugin as well, called Back in Stock Notifier. I opened a ticket on the Wordpress support forum to try and resolve the issue with the developers. Hopefully something can be done there.
Have you had issues with WooCommerce Bookings or other plugins yourselves? Or with the increasing database size? Let me know!
