0

When I visit the woocommerce orders page in WordPress, MySQL RDS CPU usage goes to 100% but the website is working perfectly fine. In 'active sessions' section, "wait/io/tables/sql/handler" is showing cpu usage to 99%. I looked at the performance insights of the database and saw this query:

SELECT SQL_CALC_FOUND_ROWS  hbm_posts.*, low_stock_amount_meta.meta_value AS low_stock_amount, MAX( product_lookup.date_created ) AS last_order_date FROM hbm_posts  LEFT JOIN hbm_wc_product_meta_lookup wc_product_meta_lookup ON hbm_posts.ID = wc_product_meta_lookup.product_id  LEFT JOIN hbm_postmeta AS low_stock_amount_meta ON hbm_posts.ID = low_stock_amount_meta.post_id AND low_stock_amount_meta.meta_key = '_low_stock_amount'  LEFT JOIN hbm_wc_order_product_lookup product_lookup ON hbm_posts.ID = CASE
                WHEN hbm_posts.post_type = 'product' THEN product_lookup.product_id
                WHEN hbm_posts.post_type = 'product_variation' THEN product_lookup.variation_id
            END WHERE 1=1  AND hbm_posts.post_type IN ('product', 'product_variation') AND ((hbm_posts.post_status = 'publish'))
            AND wc_product_meta_lookup.stock_quantity IS NOT NULL
            AND wc_product_meta_lookup.stock_status IN('instock','outofstock')
            AND (
                (
                    low_stock_amount_meta.meta_value > ''
                    AND wc_product_meta_lookup.stock_quantity <= CAST(low_stock_amount_meta.meta_value AS SIGNED)
                )
                OR (
                    (
                        low_stock_amount_meta.meta_value IS NULL OR low_stock_amount_meta.meta_value <= ''
                    )
                    AND wc_product_meta_lookup.stock_quantity <= 2
                )
            ) GROUP BY hbmSELECT SQL_CALC_FOUND_ROWS  hbm_posts.*, low_stock_amount_meta.meta_value AS low_stock_amount, MAX( product_lookup.date_created ) AS last_order_date FROM hbm_posts  LEFT JOIN hbm_wc_product_meta_lookup wc_product_meta_lookup ON hbm_posts.ID = wc_product_meta_lookup.product_id  LEFT JOIN hbm_postmeta AS low_stock_amount_meta ON hbm_posts.ID = low_stock_amount_meta.post_id AND low_stock_amount_meta.meta_key = '_low_stock_amount'  LEFT JOIN hbm_wc_order_product_lookup product_lookup ON hbm_posts.ID = CASE
            WHEN hbm_posts.post_type = 'product' THEN product_lookup.product_id
            WHEN hbm_posts.post_type = 'product_variation' THEN product_lookup.variation_id
        END WHERE 1=1  AND hbm_posts.post_type IN ('product', 'product_variation') AND ((hbm_posts.post_status = 'publish'))
        AND wc_product_meta_lookup.stock_quantity IS NOT NULL
        AND wc_product_meta_lookup.stock_status IN('instock','outofstock')
        AND (
            (
                low_stock_amount_meta.meta_value > ''
                AND wc_product_meta_lookup.stock_quantity <= CAST(low_stock_amount_meta.meta_value AS SIGNED)
            )
            OR (
                (
                    low_stock_amount_meta.meta_value IS NULL OR low_stock_amount_meta.meta_value <= ''
                )
                AND wc_product_meta_lookup.stock_quantity <= 2
            )
        ) GROUP BY hbm_posts.ID ORDER BY hbm_posts.post_date DESC, hbm_posts.ID DESC LIMIT 0, 1_posts.ID ORDER BY hbm_posts.post_date DESC, hbm_posts.ID DESC LIMIT 0, 1

I have tried tracing back the query in WordPress, but can't find it anywhere. I searched it with 'string locator', saw query logs in 'query monitor', tried disabling all plugins, and also tried "define('SAVEQUERIES', true);" as stated in this post:

https://stackoverflow.com/questions/4660692/is-it-possible-to-print-a-log-of-all-database-queries-for-a-page-request-in-word

What can I do to trace back this? Mysql version of server and client is 5.7.34

4
  • The RDS logs will generally only show you the first 512 characters. This is often enough to trace the origin of the query in the offending application. That said, WooCommerce (and WordPress) queries do seem to get assembled almost on a character-by-character basis in the source code at times ...
    – matigo
    Jul 19, 2021 at 10:04
  • if you see that, it means that the statement wasn't parsed it has stayed at the preprepared and wasn't filled with data. {$wpdb->prefix}_posts LEFT JOIN {$wpdb->prefix}_wc_product_meta_lookup with help of a text editor and see if you find that query
    – nbk
    Jul 19, 2021 at 11:34
  • I executed show full processlist; in mysql and saw that query was actually complete. (I have updated the question with the query). But the queries are stuck in Sending Data state since hours. Jul 19, 2021 at 11:41
  • Finally knew that the issue was with WooCommerce update. If anyone is facing same issue, here is the reference: github.com/woocommerce/woocommerce-admin/issues/7358 Jul 20, 2021 at 12:50

1 Answer 1

0

Just saw this now, so a very late answer.

This particular query gets assembled by software in the WooCommerce plugin/subsystem, as do many others like this.

How to find the source of a query in the code base? Install and activate John Blackbourne's wonderful Query Monitor plugin. Then after a page loads, look for your query in the query-list output. It will come with a traceback.

(Don't leave the Query Monitor plugin active for long on a production system; it puts a noticeable extra burden on the web server(s) running WordPress.)

The problem with these two queries is unusable indexing in the WordPress standard schema. The JOINs in the queries you showed need a compound index on wp_postmeta (here hbm_postmeta). The index should be on (post_id, meta_key, meta_value(prefix)) for these two and many many other queries in WordPress's ecosystem.

Here's a plugin to handle that reindexing (by me and Rick James). Here's the database indexing rationale behind the reindexing. tl;dr well-chosen compound primary keys driving clustered indexing.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.