High CPU usage

Hi, my site has very high CPU usage, my hosting manager says it is because of many slow queries, what can it be?
Thank you

Below my hosting manager message:

The high CPU usage is caused as your staging2 instance generates a lot of slow MySQL queries. A MySQL query that runs for more than 1 second is considered slow on our servers. You have queries that run for more than 37 seconds.
=== TOP 10 of 418 (total) Slow Queries for the past 24 hours =====================================================
1. Executed 1h 37m 56s ago for 37.943578 sec on Database –> dbpnrmtehcqyh9
Date: 2020-03-03 12:10:12 Query_time: 37.943578 Rows_examined: 14: Rows_sent 0 Lock_time: 0.000161 Query_chars: 81
SELECT meta_id FROM wpML_postmeta WHERE meta_key = ‘_stock’ AND post_id = 46746;
——————————————————————————————————————-
2. Executed 1h 37m 56s ago for 37.714166 sec on Database –> dbpnrmtehcqyh9
Date: 2020-03-03 12:10:12 Query_time: 37.714166 Rows_examined: 11: Rows_sent 4 Lock_time: 0.050301 Query_chars: 780
SELECT wpml_translations.translation_id, wpml_translations.element_id, wpml_translations.language_code, wpml_translations.source_language_code, wpml_translations.trid, wpml_translations.element_type FROM wpML_icl_translations wpml_translations JOIN w
……
idt.trid = (SELECT trid FROM wpML_icl_translations wpml_translations JOIN wpML_term_taxonomy tax ON wpml_translations.element_id = tax.term_taxonomy_id AND wpml_translations.element_type = CONCAT(‘tax_’, tax.taxonomy) WHERE element_id = 24 LIMIT 1);
——————————————————————————————————————-
3. Executed 1h 37m 56s ago for 12.923978 sec on Database –> dbpnrmtehcqyh9
Date: 2020-03-03 12:10:12 Query_time: 12.923978 Rows_examined: 1022: Rows_sent 1022 Lock_time: 0.050432 Query_chars: 75
SELECT option_name, option_value FROM wpML_options WHERE autoload = ‘yes’;
——————————————————————————————————————-
4. Executed 2h 22m 42s ago for 8.410144 sec on Database –> dbd7qbzbxmcdq5
Date: 2020-03-03 11:25:26 Query_time: 8.410144 Rows_examined: 139866: Rows_sent 69933 Lock_time: 0.001999 Query_chars: 4475
SELECT post_id, meta_key, meta_value FROM wpML_postmeta WHERE post_id IN (45169,45155,45033,45026,43454,43447,43519,43512,43491,43484,43557,43544,43621,43614,43808,43801,43869,43862,43841,43834,43899,43892,43928,43920,43959,43952,43993,43980,40993,40
……
,15101,13082,15107,13147,15196,3863,15198,13353,15201,13356,15206,13378,15209,13367,15833,20809,20795,25874,25854,22953,22940,23156,23137,23250,23231,26731,26714,26641,26627,26517,26504,26445,26426,16233,9408,16407,10089,16502) ORDER BY meta_id ASC;
——————————————————————————————————————-
5. Executed 4h 18m 33s ago for 7.908381 sec on Database –> dbpnrmtehcqyh9
Date: 2020-03-03 09:29:35 Query_time: 7.908381 Rows_examined: 0: Rows_sent 0 Lock_time: 0.000075 Query_chars: 81
SELECT option_value FROM wpML_options WHERE option_name = ‘evc_options’ LIMIT 1;
——————————————————————————————————————-
6. Executed 17h 50m 13s ago for 7.116124 sec on Database –> dbd7qbzbxmcdq5
Date: 2020-03-02 19:57:55 Query_time: 7.116124 Rows_examined: 139866: Rows_sent 69933 Lock_time: 0.000779 Query_chars: 4475
SELECT post_id, meta_key, meta_value FROM wpML_postmeta WHERE post_id IN (45169,45155,45033,45026,43454,43447,43519,43512,43491,43484,43557,43544,43621,43614,43808,43801,43869,43862,43841,43834,43899,43892,43928,43920,43959,43952,43993,43980,40993,40
……
,15101,13082,15107,13147,15196,3863,15198,13353,15201,13356,15206,13378,15209,13367,15833,20809,20795,25874,25854,22953,22940,23156,23137,23250,23231,26731,26714,26641,26627,26517,26504,26445,26426,16233,9408,16407,10089,16502) ORDER BY meta_id ASC;
——————————————————————————————————————-
7. Executed 5h 4m 40s ago for 6.736759 sec on Database –> dbpnrmtehcqyh9
Date: 2020-03-03 08:43:28 Query_time: 6.736759 Rows_examined: 37: Rows_sent 2 Lock_time: 0.000459 Query_chars: 875
SELECT wpML_posts.ID FROM wpML_posts INNER JOIN wpML_postmeta ON ( wpML_posts.ID = wpML_postmeta.post_id ) JOIN wpML_icl_translations wpml_translations ON wpML_posts.ID = wpml_translations.element_id AND wpml_translations.element_type = CONCAT(‘po
……
‘vc_grid_item’ ) ) OR wpML_posts.post_type NOT IN (‘post’,’page’,’attachment’,’wp_block’,’cms_block’,’portfolio’,’popup’,’product’,’product_variation’,’br_labels’,’vc_grid_item’ ) ) GROUP BY wpML_posts.ID ORDER BY wpML_postmeta.meta_value+0 ASC;
——————————————————————————————————————-
8. Executed 50m 37s ago for 6.680412 sec on Database –> dbd7qbzbxmcdq5
Date: 2020-03-03 12:57:31 Query_time: 6.680412 Rows_examined: 139866: Rows_sent 69933 Lock_time: 0.002173 Query_chars: 4475
SELECT post_id, meta_key, meta_value FROM wpML_postmeta WHERE post_id IN (45169,45155,45033,45026,43454,43447,43519,43512,43491,43484,43557,43544,43621,43614,43808,43801,43869,43862,43841,43834,43899,43892,43928,43920,43959,43952,43993,43980,40993,40
……
,15101,13082,15107,13147,15196,3863,15198,13353,15201,13356,15206,13378,15209,13367,15833,20809,20795,25874,25854,22953,22940,23156,23137,23250,23231,26731,26714,26641,26627,26517,26504,26445,26426,16233,9408,16407,10089,16502) ORDER BY meta_id ASC;
——————————————————————————————————————-
9. Executed 4h 1m 58s ago for 6.240067 sec on Database –> dbpnrmtehcqyh9
Date: 2020-03-03 09:46:10 Query_time: 6.240067 Rows_examined: 2: Rows_sent 1 Lock_time: 0.000429 Query_chars: 98
SELECT meta_id FROM wpML_postmeta WHERE meta_key = ‘_wp_attachment_metadata’ AND post_id = 46684;
——————————————————————————————————————-
10. Executed 4h 1m 58s ago for 6.213886 sec on Database –> dbpnrmtehcqyh9
Date: 2020-03-03 09:46:10 Query_time: 6.213886 Rows_examined: 1: Rows_sent 1 Lock_time: 0.000116 Query_chars: 193
SELECT wpML_posts.ID FROM wpML_posts WHERE 1=1 AND wpML_posts.post_type = ‘woodmart_size_guide’ AND ((wpML_posts.post_status = ‘publish’)) ORDER BY wpML_posts.post_date DESC LIMIT 0, 200;
——————————————————————————————————————-

=== Top 3 Similar SQL Queries and their query time =================================================================
Executed: 126 time for minumum: 1.004527 sec, maximum: 7.908381 sec
SELECT option_value FROM wpML_options WHERE option_name = ‘_transient_woodmart_swatches_cache_4197’ LIMIT 1;
——————————————————————————————————————-
Executed: 90 time for minumum: 1.003690 sec, maximum: 6.240067 sec
SELECT DISTINCT meta_key FROM wpML_postmeta WHERE meta_key NOT BETWEEN ‘_’ AND ‘_z’ HAVING meta_key NOT LIKE ‘\\_%’ ORDER BY meta_key LIMIT 30;
——————————————————————————————————————-
Executed: 58 time for minumum: 1.000737 sec, maximum: 6.213886 sec
SELECT p.ID FROM wpML_posts as p LEFT JOIN wpML_postmeta as pm ON ( p.ID = pm.post_id ) WHERE p.post_type IN ( ‘product’, ‘product_variation’ ) AND p.post_status = ‘publish’ AND pm.meta_key = ‘_sku’ AND pm.meta_value = ‘078 pelle1’;
——————————————————————————————————————-

You should optimize your website for more speed. I am afraid we cannot assist you with that task as it falls beyond the scope of our support.

Attachments:

You must be logged in to view attached files.

Hello,

Could you please check how it works with some default WordPress themes like TwentyTwenty or WooCommerce Storefront to understand is it our theme issue or not? All these queries are not related to our theme except only one

SELECT option_value FROM wpML_options WHERE option_name = ‘_transient_woodmart_swatches_cache_4197’ LIMIT 1;

But it is one of the simplest queries and it should be extremely fast on most servers. What exactly should you do to optimize this query?

Regards

[Author]

Posts