Question 335378 in Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress – Why MariaDB on my Woocommerce website consumes too much CPU?

I have a Woo website with like 1 million product, type simple. (are they too many for a Woo website?)

Code and database are on same server

Server: 16 cores and 62 GB RAM.

MariaDB: mysql Ver 15.1 Distrib 10.5.23-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Sometimes the CPU gets full and website becomes unresponsive.

I can see via htop MariaDB taking all of CPU.
enter image description here

It has CLoudflare installed.

Access logs shows traffic to /wp-json/wp/v2/product/ID-HERE endpoints from amazonbot, example.

162.158.87.126 - - [03/Feb/2024:15:52:35 +0100] "GET /wp-json/wp/v2/product_tag/766 HTTP/2.0" 504 164 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Version/8.0.2 Safari/600.2.5 (Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot)" "3.224.220.101,3.224.220.101"

Does that make sense? or should I disable Woo API at all because I dont use it for any purpose.

SHOW PROCESSLIST shows these kind of queries hung for over 2000 seconds

SELECT
    post_modified_gmt 
FROM
    wp_posts 
WHERE
    post_status = 'publish' 
    AND post_type IN ( 'post', 'page', 'attachment', 'e-landing-page', 'elementor_library', 'product' ) 
ORDER BY
    post_modified_gmt DESC 
    LIMIT 1

and

SELECT
    wp_posts.ID 
FROM
    wp_posts
    LEFT JOIN wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id ) 
WHERE
    1 = 1 
    AND ( wp_term_relationships.term_taxonomy_id IN ( 2, 3, 4, 5 ) ) 
    AND wp_posts.post_type = 'product' 
    AND ((
            wp_posts.post_status = 'publish' 
        )) 
GROUP BY
    wp_posts.ID 
ORDER BY
    wp_posts.post_date DESC 
    LIMIT 0,
    1

I have following INDEXes

wp_posts

wp_posts

wp_postmeta

wp_psotmeta

wp_term_relationships

wp_term_relationships

EDIT in response to Wilson

  1. SELECT COUNT(*) FROM information_schema.tables;

    +———-+
    | COUNT(*) |
    +———-+
    | 602 |
    +———-+
    1 row in set (0.004 sec)

  2. SHOW GLOBAL STATUS; https://pastebin.com/UnCJ3Hr3

  3. STATUS; https://pastebin.com/2JMRL51B

  4. SHOW ENGINE INNODB STATUS; https://pastebin.com/Ze69SqLw

  5. top -b -n 1 https://pastebin.com/EqFuchH4

  6. top -b -n 1 -H https://pastebin.com/aqx8rrvs

  7. ulimit -a https://pastebin.com/T0HT5Afh

  8. iostat -xm 5 3 https://pastebin.com/nE3AwTvQ

  9. df -h https://pastebin.com/1grfXYMd

  10. df -i https://pastebin.com/1wnUJxpY

  11. free -h https://pastebin.com/ND3E4iR8

  12. cat /proc/meminfo https://pastebin.com/ehGtav7F

  13. lsb_release -a https://pastebin.com/RZj2FFFz

  14. SHOW GLOBAL VARIABLES; https://justpaste.it/baovl

  15. lsblk https://pastebin.com/i9sLVur2

  16. SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM information_schema.tables LIMIT 100000; https://pastebin.com/RYs0u9dj

Everything is provided except htop as its already in screenshot above.
PS: during the all things I provided, the CPU never exceeded 80%, and average load was around 60%

Tags:

2

Answers


  1. 0

    Rewrite the first query as

    SELECT  MAX(post_modified_gmt)
    FROM  wp_posts
    WHERE  post_status = 'publish'
      AND  post_type IN ( 'post', 'page', 'attachment', 'e-landing-page',
                          'elementor_library', 'product' );

    and have this "covering" index:

    INDEX(post_status,        -- first because of '='
      post_type,          -- next,
      post_modified_gmt)  -- to make it "covering"

    You are using 10.5. The "191 kludge" is no longer needed as of MariaDB 10.2.2 (MySQL 5.7.7). Removing that "prefixing" form serveral of the indexes it will help many queries.

    Login or Signup to reply.
  2. 0

    Rate Per Second = RPS

    Suggestions to consider for your OS adjustment and my.cnf [mysqld] section

    Your ulimit -a report indicates Open Files limit is 1024.
    From your OS Command Prompt,

    ulimit -n 64000 and press Enter to change this dynamic variable.  No restart required. 
to make this persistent, here is link to an overview. 


    Increasing ulimit and file descriptors limit on Linux
      

Do not set to 500000 as the overview suggests, use 64000 and MySQL will be able to open_files_limit of 32,768 to avoid open starvation that exists today. Expect opened_tables RPS of 934 to come down significantly.

    my.cnf [mysqld] suggestions,

    read_buffer_size=1M  # from 8M to reduce handler_read_next RPS of 178,608
read_rnd_buffer_size=16K  # from 4M to reduce handler_read_rnd_next RPS of 163,840 
innodb_old_blocks_pct=1  # from 37 percent of innodb_buffer_pool_size reserved
innodb_buffer_pool_instances=4  #  from 1 to reduce mutex contention
innodb_buffer_pool_size=38G  # from 5G to reduce innodb_buffer_pool_reads RPS of 59,698

    There are many more performance improving opportunities. Please view profile.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search