FIX HUGE SQL tables: wp_actionscheduler_actions & wp_actionscheduler_logs [WooCommerce tips]

WooCommerce it has become a widely used module online shops. SEO, product, inventory management, clean and intuitive code, simple administration interface and thousands of plugins developed for Woo, are just some of the arguments for which it is worth a chance when you think of developing an online store.

Like any CMS, Woo is no exception to the oddities that can occur in various usage or interaction scenarios with other plugins. WordPress.
On a resource server hardware quite generous, I noticed that database service (mysqld) begins to require almost 80-90% of RAM. A pretty serious problem, because I just didn't understand where error 110 comes from periodically (110: Connection timed out).
On closer inspection of SQL processes, I found that a database has two tables with fairly large volumes: wp_actionscheduler_actions si wp_actionscheduler_logs.

Normally scheduled actions but by WooCommerce Action Scheduler they should be deleted automatically after they are executed. This doesn't always happen, and they get stuck in wp_actionsscheduler_actions with the status: failed, canceled, pending or complete.

In the image above, the tables "wp_actionsscheduler"They only have just over 15 MB. I'm sorry I wasn't inspired to take a screenshot when they had 1.2 GB. Even so, 15 MB is quite a lot for a table that contains the programmed actions of WooCommerce.
These "swollen" tables result from that WP-Cron does not delete entries who have status "failed","canceled"And"complete“. Normally these entries should be automatically deleted from the database.
The scheduled actions and their state can be seen very easily and in WooCommerce →  Status →  Scheduled Actions.

How to clean the gutters "failed","canceled"And"complete"In wp_actionscheduler_actions si wp_actionscheduler_logs

We access the database through phpMyAdmin, then at SQL we execute the command lines in turn:

DELETE FROM `wp_actionscheduler_actions` WHERE `status` = 'canceled'

DELETE FROM `wp_actionscheduler_actions` WHERE `status` = 'complete'

DELETE FROM `wp_actionscheduler_actions` WHERE `status` = 'failed'

Once this table is cleaned, it does not mean that the problem is solved. As I said above, the main cause is the disabling of the WP-Cron service for various reasons. Thus entries with "zombie" status can no longer be deleted.
It is very important to know that if you have an online store on WooCommerce, and it is connected to Facebook Shops through the plugin "Facebook forum WooCommerce", it automatically synchronizes the products from WooCommerce with your Facebook Shops account. And he does it every 15 minutes. These SQL entries if not controlled, you can get several hundreds of thousands of lines"wc_facebook_regenerate_feed"In"wp_actionscheduler_actions".

This interval is ok for stores that have a large number of orders and the stock of Facebook Shops products needs to be constantly updated. If you still think that these syncs between Facebook and your store can be done once every 24 hours, the line of code below can help.

Open the functions.php file of the theme WordPress / WooCommerce on which your store runs and add:

add_filter( 'wc_facebook_feed_generation_interval', function(){ return HOUR_IN_SECONDS * 24; } );

Next we can set an interval of one week for automatic cleaning:

add_filter( 'action_scheduler_retention_period', 'wpb_action_scheduler_purge' );
function wpb_action_scheduler_purge() {
 return WEEK_IN_SECONDS;
}

Once you save these changes you will have no problems with giant tables for "wp_actionscheduler_actions".