Clearing a Clogged Action Scheduler Queue – WooCommerce

Nick

Here are some handy queries and approaches to clearing a clogged Action Scheduler queue.

Option 1: Speed Up Processing

The best approach is to just speed up the processing of actions so that the queue completes.

There are a few approaches for this, from simplest to hardest:

  1. update WordPress, WooCommerce & Subscriptions. Specifically, if Subscriptions 2.5.0, WooCommerce 3.6, and WordPress 5.1 are running, processing speeds should be much higher than with prior versions.
  2. the simplest approach for this is to install the Action Scheduler high volume plugin: http://github.com/Prospress/action-scheduler-high-volume/
  3. if the above doesn’t work, because it’s not processing actions fast enough, then it may be necessary to setup WP CLI runners. Two helpful guides on that:
    1. A general guide to using WP CLI: https://actionscheduler.org/wp-cli/
    2. A specific guide for running Action Scheduler via WP CLI on Pantheon: https://pantheon.io/blog/high-performance-background-processing-woocommerce-pantheon (the general approach is transferrable to other hosts)

Option 2: Delete Data

If it’s not possible to speed up processing enough to clear the pending actions, then it may be necessary to clear the queue by deleting pending scheduled webhook actions.

This is only safe when you know the pending webhook actions aren’t necessary. For example, reporting plugins like Metorik use Webhooks as a backup data sync method, so they canbe safely synced. Some services will require these webhooks, in which case, it’s not safe to simply delete them.

To delete the actions:

  1. trashing pending scheduled webhook actions
  2. deleting all trashed scheduled webhook actions (which includes both those above, and previously completed or canceled actions)
  3. deleting logs and metadata for those deleted scheduled webhook actions

The queries to do this:

1. Trash pending scheduled webhook actions 

UPDATE `wp_posts`
SET `post_status` = 'trash'
WHERE `post_type` = 'scheduled-action'
AND `post_title` = 'woocommerce_deliver_webhook_async'
AND `post_status` = 'pending'

2. Delete trashed scheduled webhook actions

DELETE FROM `wp_posts`
WHERE `post_type` = 'scheduled-action'
AND `post_status` = 'trash'
AND `post_title` = 'woocommerce_deliver_webhook_async'

3. Delete logs and metadata for deleted scheduled webhook actions

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL
DELETE comments
FROM wp_comments AS comments
LEFT JOIN wp_posts wp ON wp.ID = comments.comment_post_ID
WHERE wp.ID IS NULL

DELETE comments
FROM wp_commentmeta AS commentmeta
LEFT JOIN wp_comments comments ON comments.comment_ID = commentmeta.comment_id
WHERE comments.comment_ID IS NULL

Other Helpful Queries

Query to find actions processed in a 1-hour window (update dates to suit):

SELECT `post_title`,COUNT(*)
FROM `wp_posts`
WHERE `post_type` = 'scheduled-action'
AND `post_status` = 'publish'
AND `post_modified_gmt` > '2019-02-08 06:30:00'
AND `post_modified_gmt` < '2019-02-08 07:30:01'
GROUP BY `post_title`

Query to find pending actions in the past:

SELECT `post_title`, COUNT(*)
FROM `wp_posts`
WHERE `post_type` = 'scheduled-action'
AND `post_status` = 'pending'
AND `post_date_gmt` < '2019-02-08 00:00:00'
GROUP BY `post_title`