Ecommerce Shopify WordPress Discussion

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. 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_postmeta wp_term_relationships
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.

February 4, 2024

Our products

The help you need, when you need it

App by Turbo Engine

3 apps • 5.0 average rating

Turbosify Speed Booster

5.0 (7)
Get better conversions by optimizing shopify store Google page speed Installed

Turbosify Translator for Wordpress Woocommerce

5.0 (74) Free Wordpress Woocommerce Plugin
Translate your wordpress website to multiple language within 1 click, no configuration needed, no No technical required

Grow your business here

Whether you want to sell products down the street or around the world, we have all the tools you need.