Receive a 10% discount voucher when you subscribe to the newsletter (valid on top of any other existing offer)! SUBSCRIBE HERE

Search

I Almost Blew Up This WordPress Blog With 1 Wrong Setting

Wordpress Problem Solved

I love WordPress. I use it since it was still called B2/Cafelog, probably around 2002. Then I used its public .com version for my successful food-blog and the self-hosted one for a lot 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 as 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 for workshops and such I always used an online service. 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 used it to create my online sessions as well. 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 you book. Easy peasy.
On my end, I get a notification immediately and then we both get reminders, etc.

Shop at fabienb.blog

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 in my calendar, then the plugin makes that time slot unavailable for bookings. 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 information?

Only over a week ago, I was happy that I had optimised this WordPress blog to reach a rating of A(97%) on Pagespeed, improving from a 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.

Hostinger Logo purpleA note on Hostinger, my hosting provider
Before I dive in, allow me to thank my hosting provider Hostinger. Aside of 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 looking on their end as well and the communication was prompt and flawless. So, huge thank you and definitely recommended.

OK, so let’s dive in now…

Connecting the Dots, Database Bloat

The problem with having a very large database is that WordPress takes a longer time 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 in timeout.

I quickly went to check 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, obviously, 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 specifically and the steps to take 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!

Wordpress WooCommerce Bookings admin issue

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.

Wordpress WooCommerce Bookings admin issue PHPMyAdmin
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.

Wordpress WooCommerce Bookings admin issue

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.

This was all it took to clear the database tables from the useless data.
Neil, thank you so much!

Nest 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 round. And I’m going to reach out to the WooCommerce Support Team to see if a simple extra option could be implemented. One that would allow to select 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 anybody else 🙂 You’re welcome.
Hopefully I’ll have good news to update this article soon.

Have you had issues with WooCommerce Bookings yourselves? Or with the increasing database size? Let me know in the comments!


Help Support this Blog

If you like this post then you can see more of my work and follow me on Instagram , Twitter , Linkedin and my Facebook Page .
I also started a new YouTube channel that you may find interesting.

If you want to receive regular updates and exclusive content, notices of occasional special offers, etc, then please sign up for the newsletter.

If you want to find out more about my photo gear, I have created a dedicated list on Amazon and Kit.co



More to come!

Disclosure: please know that some of the links in this blog are affiliate links and if you go through them to make a purchase I will earn a commission. Always keep in mind that I link these companies and their products because of their quality and not because of the commission I receive from your purchases.
The decision is yours, and whether or not you decide to buy something is completely up to you. Purchasing via these links will make no difference to the cost to you and sometimes you might even get a discount, but the commission I receive will help me maintain this blog.
Thank you!

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.