Very slow order status change

Hi,

since a few days when we change the status of an order, or we generate an invoice it just takes forever to complete the operation.
The website is quite a heavy woocommerce with roughly 25k orders and more than 10k products, but the problem happened suddenly without a gradual slowdown.
It’s hosted on a dedicated server, with 64GB of RAM and 8 core, OpenLitespeed.

All the tables are InnoDB, and MariaDB has this configuration which never posed a problem:

innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 8G
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_log_file_size = 1G

enabling the slow-query log, we can see that everytime we change the orders status there are tens of this query logged (with different dates and meta_id value), that takes roughly one second EACH:

SELECT SUM(mt_qty.meta_value) AS QTY
FROM wp_posts AS orders

LEFT JOIN wp_woocommerce_order_items AS items ON (orders.ID = items.order_id)
LEFT JOIN wp_woocommerce_order_itemmeta AS mt_id ON (items.order_item_id = mt_id.order_item_id)
LEFT JOIN wp_woocommerce_order_itemmeta AS mt_qty ON (items.order_item_id = mt_qty.order_item_id AND mt_qty.meta_key = '_qty')

WHERE orders.ID IN (
	SELECT ID FROM wp_posts
	WHERE post_date_gmt >= '2020-11-03 09:28:53' AND post_date_gmt <= '2020-11-04 09:28:53'
	AND post_type = 'shop_order' AND post_status IN ('wc-processing', 'wc-completed')
) 

AND mt_id.meta_key IN ('_product_id', '_variation_id')
AND mt_id.meta_value = 160302
GROUP BY mt_id.meta_value
HAVING (QTY IS NOT NULL);

Any advice on how to optimize the queries called or the table(s) ?

Thanks in advance for the collaboration

• This topic was modified 4 years, 4 months ago by SYNC4489.