Over at SharewareOnSale, we run WooCommerce on top of WordPress. WordPress is a blogging-platform-gone-CMS and WooCommerce is an eCommerce package that expands the abilities of WordPress. Over the past few days, we have had an issue with SharewareOnSale being extremely slow due to excessive load on its server. The cause of the excessive load on the server? MySQL.

Over half a dozen system admins and I banged our heads against the wall for a few days to figure out why MySQL was using so much CPU — over 600% CPU on an E3-1240v2 machine. We optimized this, changed this, etc. but could not fix the problem… until one system admin pointed out the fact that there was one query that was running hundreds of times at any given moment and taking hundreds of seconds to complete each. The query was identified as the following:

SELECT COUNT( order_items.order_item_id )
       FROM wp_woocommerce_order_items as order_items
       LEFT JOIN wp_woocommerce_order_itemmeta AS itemmeta ON order_items.order_item_id = itemmeta.order_item_id
       LEFT JOIN wp_postmeta AS postmeta ON order_items.order_id = postmeta.post_id
       LEFT JOIN wp_term_relationships AS rel ON postmeta.post_id = rel.object_ID
       LEFT JOIN wp_term_taxonomy AS tax USING( term_taxonomy_id )
       LEFT JOIN wp_terms AS term USING( term_id )
       WHERE    term.slug IN ('completed','processing','on-hold')
       AND    tax.taxonomy        = 'shop_order_status'
       AND        (
                   (
                       itemmeta.meta_key = '_variation_id'
                       AND itemmeta.meta_value = 'YYY'
                   ) OR (
                       itemmeta.meta_key = '_product_id'
                       AND itemmeta.meta_value = 'YYY'
                   )
       )
       AND    (
                   (
                       postmeta.meta_key = '_billing_email'
                       AND postmeta.meta_value IN ( 'XXX@gmail.com' )
                   ) OR (
                       postmeta.meta_key = '_customer_user'
                       AND postmeta.meta_value = '0' AND postmeta.meta_value > 0
                   )
               )

This is the query that caused MySQL to use over 600% CPU on SharewareOnSale’s server. Once stopped, CPU use of MySQL on the server dropped significantly and load was to a manageable level. But how to stop it? Read on to learn more.

How to fix or stop high MySQL CPU and speed up WooCommerce websites

Fixing high MySQL CPU use caused by WooCommerce is actually easier than you may think. The above shown query is called by WooCommerce using the function woocommerce_customer_bought_product(). This function is used by the WooCommerce reviews/comment form to place the “(verified owner)” text next to the name of anyone that leaves a comment or review and has downloaded or bought the product.

When you have WooCommerce’s “(verified owner)” feature enabled, every time a logged in user visits a product page on your WooCommerce website, function woocommerce_customer_bought_product() is called — regardless of if they actually post a comment or not. If you have a high traffic website, this constant querying of your database is what causes MySQL to use high amounts of CPU.

So, then, how do we stop this abnoxious behavior by WooCommerce? It is actually very easy. To disable WooCommerce’s “(verified owner)” feature — and thus fixing high MySQL CPU use by WooCommerce — do the following:

• Log into your WooCommerce website and go to Settings -> Products.
• From there, go to Product Data -> Product Ratings.
• Finally, uncheck the checkbox next to “Show ‘verified owner’ label for customer reviews”. If you don’t see “Show ‘verified owner’ label for customer reviews”, check the checkbox next to “Enable ratings on reviews”, then uncheck the checkbox next to “Show ‘verified owner’ label for customer reviews”, and then uncheck the checkbox next to “Enable ratings on reviews”.
• Now simply hit the Save changes button at the bottom and you are done.

Done! Be sure to clear any caches you may have (e.g. clear W3 Total Cache or WP Super Cache page caches).

CONCLUSION

The solution the problem was easy; identifying the problem, however, took many brains. However, I’ve told you how to do it… so go do it. Enjoy!