Woocommerce Performance Issues-CodePudding

Wondering if anyone can offer some fresh perspective on some performance issues we're experiencing with a Woocommerce web site.

The site is a single application and database sat on a Digital Ocean droplet - the box is spec'd as 16 cores / 32 GB RAM / 200GB disk.

Over recent months we've seen some sporadic performance on the server actions undertaken:

MySQLTuner.pl installed and made suggested changes to the MySQL configuration
MySQL slow logging enabled
Index applied to queries identified in slow query log

Performance settled down however a few months further on we are beginning to experience spikes in performance again at busy times (circa 300 unique visitors per hour) with the server CPU sat at 100% and the server becoming unresponsive - this can last a couple of minutes currently.

Over recent days we've implemented:

Cloudflare to proxy all traffic
All speed optimisations have been applied
Automatic Platform Optimization for Wordpress installed and up and running
Query Monitor installed
Query Monitor identified only 1 slow query with a 0.8sec execution
Increased WP memory limit from 40MB to 256MB

Looking back through the the MySQL slow log I can see the query below, using the EXPLAIN syntax it looks like all tables are indexed however due to the way its compiled I’m not sure it can be optimised further or in fact where to trace back the query is being ran from - this doesn’t appear in Query Monitor slow logs.

    Count: 1546  Time=20.98s (32437s)  Lock=3.03s (4679s)  Rows_sent=249.1 (385062), Rows_examined=2654.6 (4104056), Rows_affected=0.0 (0), user@localhost
      SELECT DISTINCT wp_posts.ID FROM wp_posts INNER JOIN (
      SELECT DISTINCT post_id FROM wp_postmeta 
      WHERE post_id NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'S')
      AND post_id NOT IN(SELECT DISTINCT tr.object_id FROM wp_termmeta 
      INNER JOIN wp_term_taxonomy tt on wp_termmeta.term_id = tt.term_id
      INNER JOIN wp_term_relationships tr on tt.term_taxonomy_id = tr.term_taxonomy_id
      WHERE tt.taxonomy = 'S' AND meta_key='S')
      UNION ALL
      SELECT  post_id FROM wp_postmeta 
      WHERE (meta_key = 'S' AND meta_value = 'S') OR (meta_key = 'S' AND meta_value IN ('S'))
      UNION ALL 
      SELECT  tr.object_id FROM wp_termmeta 
      INNER JOIN wp_term_taxonomy tt on wp_termmeta.term_id = tt.term_id
      INNER JOIN wp_term_relationships tr on tt.term_taxonomy_id = tr.term_taxonomy_id
      WHERE tt.taxonomy = 'S' 
      AND ( (meta_key='S' AND meta_value='S') OR (meta_key = 'S' AND meta_value IN ('S')) )
      AND tr.object_id NOT IN (SELECT DISTINCT post_id FROM wp_postmeta WHERE meta_key = 'S')
      ) as rfilter on wp_posts.ID = rfilter.post_id WHERE post_type = 'S'

Ideally I want to find a way through this issue before then further looking in to database clustering and load balancing traffic.

Any help would be greatly appreciated.

CodePudding user response：

Thanks for providing the slowlog summary.

This plugin will help with many postmeta usages: https://wordpress.org/plugins/index-wp-mysql-for-speed/

To dig further, let me start by reformatting the query:

SELECT  DISTINCT wp_posts.ID
    FROM  wp_posts
    INNER JOIN  
      (
        SELECT  DISTINCT post_id
            FROM  wp_postmeta
            WHERE  post_id NOT IN (
                SELECT  post_id
                    FROM  wp_postmeta
                    WHERE  meta_key = 'S'
                          )
              AND  post_id NOT IN (
                SELECT  DISTINCT tr.object_id
                    FROM  wp_termmeta
                    INNER JOIN  wp_term_taxonomy tt  
                           ON wp_termmeta.term_id = tt.term_id
                    INNER JOIN  wp_term_relationships tr
                           ON tt.term_taxonomy_id = tr.term_taxonomy_id
                    WHERE  tt.taxonomy = 'S'
                      AND  meta_key='S'
                          )
        UNION  ALL 
        SELECT  post_id
            FROM  wp_postmeta
            WHERE  (meta_key = 'S' AND  meta_value = 'S' )
               OR  (meta_key = 'S' AND  meta_value IN ('S') )
        UNION  ALL 
        SELECT  tr.object_id
            FROM  wp_termmeta
            INNER JOIN  wp_term_taxonomy tt 
                   ON wp_termmeta.term_id = tt.term_id
            INNER JOIN  wp_term_relationships tr 
                   ON tt.term_taxonomy_id = tr.term_taxonomy_id
            WHERE  tt.taxonomy = 'S'
              AND  ( (meta_key='S  AND  meta_value='S')
                 OR  (meta_key = 'S' AND  meta_value IN ('S')) 
                   )
              AND  tr.object_id NOT IN (
                SELECT  DISTINCT post_id
                    FROM  wp_postmeta
                    WHERE  meta_key = 'S') 
      ) as rfilter  ON wp_posts.ID = rfilter.post_id
    WHERE  post_type = 'S'

Let me go through what I can see in it...

DISTINCT Usually requires a pass over the data. If there are only a few rows before dedupping, then this is not an issue. Some of the 4 DISTINCTs might be redundant.
IN (SELECT ...) -- This construct is usually inefficient. NOT IN .. is often even worse. It's hard to predict the optimal way to improved on it. NOT EXISTS ( SELECT 1 ... ) and LEFT JOIN .. IS NULL are the likely candidates.
The plugin will add some indexes to wp_metadata to help.
OR -- In general it inhibits the use of indexes. In both uses in this query, it seems to be unnecessary. Cleaning that up will let the plugin do its job.
(I may not have spotted everything, since not all columns are qualified by a table name or alias.)
meta_value = 'S' and meta_value IN ('S') are handled identically.

One example:

post_id NOT IN ( SELECT  post_id
                FROM  wp_postmeta
                WHERE  meta_key = 'S' )

-->

NOT EXISTS( SELECT 1
                FROM  wp_postmeta
                WHERE  meta_key = 'S'
                  AND  post_id = wp_posts.post_id )

And the plugin adds the optimal index for that subquery.