Order synchronization performance issues in large stores

[Konamiman]

The problem

There's an open issue concerning the performance the database query to count orders pending synchronization (#40506): on sites with a large number of orders the query takes so long that WordPress hangs.

I've tested with a sandbox of about 9 million orders in its database, and indeed trying to run the query showcased in the issue causes the database connection to timeout after one minute.

Here's an improved version of the same query suggested by @vedanshujain:

SELECT COUNT(1) FROM wp_posts posts 
LEFT JOIN wp_wc_orders orders ON posts.id=orders.id 
WHERE 
  posts.post_type in ('shop_order', 'shop_order_refund', 'shop_subscription') 
  AND posts.post_status != 'auto-draft' 
  AND (orders.id IS NULL OR orders.date_updated_gmt < posts.post_modified_gmt)

This query runs in 45 seconds in the aforementioned sandbox, which is still too much.

It's not mentioned in the issue, but the queries for retrieving the ids of the orders requiring synchronization can potentially take too long too:

--- Orders missing in the orders table
SELECT posts.ID 
FROM wp_posts posts 
LEFT JOIN wp_wc_orders orders ON posts.ID = orders.id 
WHERE posts.post_type IN ('shop_order', 'shop_order_refund', 'shop_subscription') 
  AND posts.post_status != 'auto-draft' 
  AND orders.id IS NULL 
ORDER BY posts.ID ASC

--- Orders missing in the posts table

SELECT posts.ID 
FROM wp_posts posts 
INNER JOIN wp_wc_orders orders ON posts.id=orders.id 
WHERE posts.post_type = 'shop_order_placehold' 
  AND orders.status not in ( 'auto-draft' ) 
  AND orders.type IN ('shop_order', 'shop_order_refund', 'shop_subscription') 
  ORDER BY posts.id ASC

--- Orders existing in both tables but having a different updates date
--- (comparison operator is ">" if orders table is authoritative, "<" if posts table is authoritative

SELECT orders.id 
FROM wp_wc_orders orders
JOIN wp_posts posts on posts.ID = orders.id
WHERE posts.post_type IN ('shop_order', 'shop_order_refund', 'shop_subscription')
  AND orders.date_updated_gmt < posts.post_modified_gmt 
  ORDER BY orders.id ASC

This post only discusses the query that count the orders, with the understanding that the queries to get the order ids need to be addressed too.

Some context

The query to count orders pending synchronization runs in the following cases:

• An administrator opens the WooCommerce - Settings - Advanced - Features page in the admin area of the store. If there's at least one order pending synchronization, changing the authoritative table isn't allowed; thus the count is needed in order to disable the authoritative table change UI.
• On any attempt to manually change the woocommerce_custom_orders_table_enabled option (which would otherwise circumvent the above): if there's at least one order pending synchronization, trying to change the value of the option throws an exception (side note: Add the wc_allow_changing_orders_storage_while_sync_is_pending filter #39988 will actually allow to circumvent this by using a dedicated filter).
• When running the wp wc cot count/sync/enable/disable commands.

When trying to come up with a solution it's useful to take in account the following:

1. The problem only manifests when the "Keep orders and posts table synchronized" option is disabled (if it's enabled, changes in the authoritative table transfer immediately to the backup table).
2. It's an arbitrary value, but we can consider that the problem manifests on shops with more than 100000 orders in its database. When limiting the counting query to 100000 posts (by using id > and id < clauses, as using LIMIT doesn't help) the query execution time stays under one second.
3. Switching the authoritative data table is an operation that administrators won't want to perform often, and they won't be surprised if doing so is temporarily not allowed (as it currently happens when it's known that there are orders pending synchronization).

The (rough idea of a) solution

A possible solution could be doing the counting process in chunks of 100000 orders. This would require first obtaining the minimum and maximum order ids in the database, then doing a series of queries with WHERE id>X AND id<X+100000, with X increasing by 100000 every time.

When running the CLI tool this is pretty straightforward, but when changing the authoritative table via UI multiple requests (and some kind of feedback for the user) would be needed. Scheduled actions could be used in the same way as it's done now for synchronizing orders, but a more responsive approach could be turning the authoritative table selection part of the settings page into a "widget" that calls (via AJAX) a dedicated REST API endpoint for counting orders requiring synchronization, passing the minimum and maximum order ids to scan. Once the counting has completed, the UI to change the authoritative table can then be enabled if the resulting value is zero (it would always appear as disabled initially).

The obtained count could be cached and reused the next time the settings page is opened: it would be valid until any order is created, modified or deleted.

Maybe this AJAX + REST API approach could be used for the orders synchronization too, if the user is willing to keep the settings page open while the process is running (and if the page is closed, the process could continue anyway via scheduled actions).

And this is what's in my mind for now regarding this problem. Comments welcome!

[lsinger]

Thanks Néstor for starting this discussion. I wonder if there's any need or benefit to knowing the exact number of orders to be synchronized -- is it maybe sufficient to say something like "more than 10,000 orders are waiting to be synchronized"? We could still use an incremental approach to the counting like you're suggesting but could be done much sooner.

In addition, I was wondering that maybe that counting process could live in a scheduled action that simply updates the cached value. Then the query wouldn't really depend on someone needing a value right now. We could update it every five minutes, for example. Not sure how beneficial that would actually be though?

[Konamiman]

Indeed we know that changing the authoritative table isn't possible as soon as we find the first order needing synchronization, and we can say so as soon as we find out. But we can't know if the change is possible until after we have scanned the entire table, so we need to continue with the entire process anyway (until the user cancels manually of course).

counting process could live in a scheduled action that simply updates the cached value

Yes, but my reasoning is that one of these two will happen:

1. User wants to be able to switch between tables immediately. Then he will keep automatic sync on (as soon as an order is modified, changes propagate to both tables).
2. User normally wants to use only one of the tables. At the moment he wants to switch, he's probably ok with having the settings page open for a while while sync proceeds.

Scheduled actions are definitely a possibility, but Ajax processing is probably more clear and transparent.

[glagonikas]

to clarify, are you saying this is an issue limited to the admin screen?

We're currently in the process of migrating to HPOS and this query seems to run constantly during migration, so it's not a one-off thing.

See below for an extract from our Query Analytics tool.

Migration was fast intially, but started to slow down as it went nearer to the 1m mark and it's now even slower with loads of orders still to go.

The two queries on my above screenshot are

SELECT
  (
    (
      SELECT
        COUNT (?)
      FROM
        `wp_posts` `posts`
        LEFT JOIN `wp_wc_orders` `orders` ON `posts`.`id` = `orders`.`id`
      WHERE
        `posts`.`post_type` IN (...)
        AND `posts`.`post_status` != ?
        AND `orders`.`id` IS NULL
    ) + (
      SELECT
        COUNT (?)
      FROM
        (
          SELECT
            `orders`.`id`
          FROM
            `wp_wc_orders` `orders`
            JOIN `wp_posts` `posts` ON `posts`.`ID` = `orders`.`id`
          WHERE
            `posts`.`post_type` IN (...)
            AND `orders`.`date_updated_gmt` < `posts`.`post_modified_gmt`
        ) `x`
    )
  ) `count`

and

SELECT
  `posts`.`ID`
FROM
  `wp_posts` `posts`
  LEFT JOIN `wp_wc_orders` `orders` ON `posts`.`ID` = `orders`.`id`
WHERE
  `posts`.`post_type` IN (...)
  AND `posts`.`post_status` != ?
  AND `orders`.`id` IS NULL
ORDER BY
  `posts`.`ID` ASC
LIMIT
  ?

[Konamiman]

to clarify, are you saying this is an issue limited to the admin screen?

No, sorry if this is was what it seemed I was saying. What I meant is that one possible solution would involve doing the count in a series of partial queries, each of which would be triggered from the admin screen via AJAX calls. Of course the good old scheduled actions could also be used for this.

[glagonikas]

my second query can be rewritten from

SELECT posts.ID FROM wp_posts posts
LEFT JOIN wp_wc_orders orders ON posts.ID = orders.id
WHERE
  posts.post_type IN ('shop_order', 'shop_order_refund', 'shop_subscription')
  AND posts.post_status != 'auto-draft'
  AND orders.id IS NULL
ORDER BY posts.ID ASC
LIMIT 250

to the uglier

SELECT posts.ID FROM wp_posts posts
WHERE
  posts.post_type IN ('shop_order', 'shop_order_refund', 'shop_subscription')
  AND posts.post_status != 'auto-draft'
  AND posts.ID NOT IN (
    SELECT orders.id
    FROM wp_wc_orders orders
  )
ORDER BY posts.ID ASC
LIMIT 250

This went from 5.4s to 3s on our live data. Still not great, but a reasonable improvement.

Not sure if @OllieJones can help with those queries? (sorry for cold-calling!)

[OllieJones]

Which DBMS? Which version?

Both these query patterns (LEFT JOIN ... IS NULL and NOT IN (...something) are known as antijoins. It looks like the query's designed to avoid migrating records that have already been migrated.

Depending on the version of DBMS software, the query planner probably comes up with identical or very similar ways to satisfy them. It's really hard to know what the query planner cooked up without seeing the output of EXPLAIN, or better, ANALYZE. (ANALYZE actually performs the query),

The difference between 3s and 5.4s can often be explained by the first attempt pulling a whole mess of data into the buffer pool, and the second attempt using that same buffered data.

I wish I had a more definitive answer. Bulk data ops can be slow.

[glagonikas]

Thanks Ollie. in our case it's MariaDB 10.5.20.

We've finally completed the migration, so hopefully it won't be an issue for us going forwards, however, I'm slightly worried about the problem of this discussion as described here #40757 (comment) and more specifically the following which we might come across later

The problem only manifests when the "Keep orders and posts table synchronized" option is disabled (if it's enabled, changes in the authoritative table transfer immediately to the backup table).