MYSQL problem with 5.5x update

  • Resolved SYNC4489

    (@sync4489)


    3 years, 4 months ago

    Hi,

    updating from 5.4.2 to WC 5.5.1 makes the mysql process to rocket up to 1200% – 1300% CPU usage.

    these queries accumulate in the mysql processes:

    SELECT SQL_CALC_FOUND_ROWS  fzpHHDKt_posts.*, low_stock_amount_meta.meta_value AS low_stock_amount, MAX( product_lookup.date_created ) AS last_order_date FROM fzpHHDKt_posts  LEFT JOIN fzpHHDKt_wc_product_meta_lookup wc_product_meta_lookup ON fzpHHDKt_posts.ID = wc_product_meta_lookup.product_id  LEFT JOIN fzpHHDKt_postmeta AS low_stock_amount_meta ON fzpHHDKt_posts.ID = low_stock_amount_meta.post_id AND low_stock_amount_meta.meta_key = '_low_stock_amount'  LEFT JOIN fzpHHDKt_wc_order_product_lookup product_lookup ON fzpHHDKt_posts.ID = CASE
				WHEN fzpHHDKt_posts.post_type = 'product' THEN product_lookup.product_id
				WHEN fzpHHDKt_posts.post_type = 'product_variation' THEN product_lookup.variation_id
			END WHERE 1=1  AND fzpHHDKt_posts.post_type IN ('product', 'product_variation') AND ((fzpHHDKt_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 <= 1
				)
			) GROUP BY fzpHHDKt_posts.ID ORDER BY fzpHHDKt_posts.post_date DESC, fzpHHDKt_posts.ID DESC LIMIT 0, 1

    Reverting to 5.4.2 immediately solves the problem, and these queries do not show anymore in the mysql processes…

Viewing 6 replies - 1 through 6 (of 6 total)

  • I think we hit this same issue this morning, it screwed the pooch on our regular product release ??

    Our forensics so far have found that one of these queries gets lodged in the mysql every time we load the edit.php?post_type=product&all_posts=1 page… they run busy, so drive mysql to use all the CPU. And they persist, forever. If you try to run the query manually it just hangs similarly.

    After a lot of fossicking we have got rid of the issue with a hack in: ./woocommerce/packages/woocommerce-admin/src/API/Products.php

    At the top of the get_items function we have inserted return ""; – suddenly our whole website is both working again and way faster than it was previously. I gather this is part of the REST API that’s presumably used in some AJAX shit? Anyway – I’ve no idea what I’ve broken but all core site functions seem to work, and customers can buy product again… hurrah.

    Certainly more investigation and a proper fix needed here though.

    I am facing similar issue, may not be able to explain in technical terms but in layman terms, after the 5.5.1 update i am facing below issues:

    1. Over all in the front end user experience is fine, website loads fine and i didnt notice any delay.
    2. From the admin panel, when i click on a specific order to open the order details, it takes ages. I have observed similar issue for product as well. Order status change, product updates take way longer than what it was before the update.
    3. So from what i understand every edit query is taking up lot of CPU.

    Woocommerce have a proper bug tracker entry for this here: https://github.com/woocommerce/woocommerce-admin/issues/7358

    Thread Starter SYNC4489

    (@sync4489)

    I confirm that the temporary fix found at

    https://gist.github.com/fitimvata/09b177f8c822c8a2ffedf987058f0fe3

    as suggested in the official bug tracker solves the problem with WC 5.5.1

    Thank you so much @sync4489 and @jgbyvan that was very helpful.

    I think i will move to 5.4.2 to solve it for now. I am sure they will fix it soon.

    Plugin Support Gabriel – a11n

    (@gabrielfuentes)

    Hi all ??

    I’m glad to hear that the issue is fixed for all of you now. ??

    I’m going to mark this as resolved – if you have any further questions, you can start a new thread.

    Have a great weekend!

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘MYSQL problem with 5.5x update’ is closed to new replies.

Tags