we are using woocommerce api to update pricing, and our site started to be slower when we do this,
we enabled slow query log on mysql and found below paste
also our hosting said the problem is:
"
The query is for a JOIN on these tables:
---
FROM lsk_icl_translations wpml_translations
"
but i think 4.5.14 is newer than 4.5.5 you suggested there - so maybe my issue is different
/usr/sbin/mysqld, Version: 8.0.32 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2023-03-10T15:58:27.174772Z
# User@Host: XXXX [XXXXX] @ [XXX.XXX.XXX.XXX] Id: 317
# Query_time: 2.296675 Lock_time: 0.000001 Rows_sent: 946 Rows_examined: 2182999
use db;
SET timestamp=1678463904;
SELECT SQL_CALC_FOUND_ROWS lsk_posts.* FROM lsk_posts LEFT JOIN lsk_term_relationships ON (lsk_posts.ID = lsk_term_relationships.object_id) INNER JOIN lsk_postmeta ON ( lsk_posts.ID = lsk_postmeta.post_id ) INNER JOIN lsk_postmeta AS mt1 ON ( lsk_posts.ID = mt1.post_id ) INNER JOIN lsk_postmeta AS mt2 ON ( lsk_posts.ID = mt2.post_id ) LEFT JOIN lsk_icl_translations wpml_translations
ON lsk_posts.ID = wpml_translations.element_id
AND wpml_translations.element_type = CONCAT('post_', lsk_posts.post_type) WHERE 1=1 AND (
lsk_term_relationships.term_taxonomy_id IN (350,353,363,367,368,369,426,430,431,433,434,435,436,437,439,441,442,444,445,447,484,494,495,496,497,498,499,500,501,503,504,506,507,508,509,510,971,978,979,980,981,982,986,1317,1405,1419,1420,1421,15728,15729,37738,105983)
) AND (
( lsk_postmeta.meta_key = '_stock_status' AND lsk_postmeta.meta_value = 'instock' )
AND
( mt1.meta_key = '_price' AND CAST(mt1.meta_value AS DECIMAL(18,3)) >= '19.9' )
AND
( mt2.meta_key = '_price' AND CAST(mt2.meta_value AS DECIMAL(18,3)) <= '19.9' )
) AND lsk_posts.post_type = 'product' AND ((lsk_posts.post_status = 'publish')) AND ( ( ( wpml_translations.language_code = 'he' OR 0 ) AND lsk_posts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','product','product_variation','wpcf7_contact_form','ufaq','br_product_tab','br_tabs_location','brand','review' ) ) OR lsk_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','product','product_variation','wpcf7_contact_form','ufaq','br_product_tab','br_tabs_location','brand','review' ) ) GROUP BY lsk_posts.ID ORDER BY lsk_posts.post_date DESC LIMIT 0, 1000;
# Time: 2023-03-10T16:00:07.384092Z
# User@Host: db [db] @ [172.16.39.34] Id: 491
# Query_time: 3.557269 Lock_time: 0.000001 Rows_sent: 50 Rows_examined: 2834834
SET timestamp=1678464003;
SELECT SQL_CALC_FOUND_ROWS lsk_posts.ID FROM lsk_posts LEFT JOIN lsk_postmeta ON ( lsk_posts.ID = lsk_postmeta.post_id ) LEFT JOIN lsk_postmeta AS mt1 ON ( lsk_posts.ID = mt1.post_id AND mt1.meta_key = '_yith_wfbt_data' ) WHERE 1=1 AND (
( lsk_postmeta.meta_key = '_stock_status' AND lsk_postmeta.meta_value = 'instock' )
AND
mt1.post_id IS NULL
) AND ((lsk_posts.post_type = 'product' AND (lsk_posts.post_status = 'publish' OR lsk_posts.post_status = 'acf-disabled'))) GROUP BY lsk_posts.ID ORDER BY lsk_posts.ID ASC LIMIT 0, 50;
# Time: 2023-03-10T16:01:00.234640Z
# User@Host: db [db] @ [172.16.39.34] Id: 589
# Query_time: 3.699011 Lock_time: 0.000001 Rows_sent: 844670 Rows_examined: 4373239
SET timestamp=1678464056;
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 lsk_icl_translations wpml_translations
JOIN lsk_posts p
ON wpml_translations.element_id = p.ID
AND wpml_translations.element_type = CONCAT('post_', p.post_type)
JOIN lsk_icl_translations tridt
ON tridt.element_type = wpml_translations.element_type
AND tridt.trid = wpml_translations.trid
WHERE tridt.element_id IN (52132,52137,52151,52427,52430,52435,52448,52454,52457,52569,52728,52734,52737,52773,52776,52779,52789,52792,52795,52798,52809,52812,52815,53209,53212,53235,53628,53670,53673,53676,53679,53685,53688,53709,53773,53782,53791,53828,53833,53847,53866,53880,53927,53930,53933,53942,53945,53948,53963,53966,54003,54015,54021,54024,54035,54040,54056,54062,54065,54080,54083,54086,54089,54094,54097,54118,54124,54127,54150,54157,54165,54172,54175,54182,54185,54191,54194,54197,54203,54258,54281,54289,54292,54304,54315,54365,54371,54380,54411,54414,54417,54426,54429,54443,54446,54452,54455,54458,54461,54464,54467,54470,54
Our second-tier supporter says that to test this, we need to check the API request. He added that he could see that the query had a huge amount of IDs. But he is not sure how it is triggered.
Can you please provide us with the API request URL with this data?
In case we need access to the site to see this, I'm enabling a private message for the following reply.
Privacy and Security Policy
We have strict policies regarding privacy and access to your information. Please see: https://wpml.org/purchase/support-policy/privacy-and-security-when-providing-debug-information-for-support/ **IMPORTANT**
- - Please backup the site files and database before providing us access. --
-- If you have a staging site where the problem can be reproduced, it is better to share access to the staging site.--
Thanks for adding those details and sending me the access information.
I've passed this information on to our second-tier supporter and await his reply.
In addition, I've noticed that WooCommerce and our plugins, among many other plugins, are not updated on your site. We have recently released WPML 4.6.0 and Strings Translation 3.2.4. We also have new versions for Advanced Custom Fields Multilingual, Contact Form 7 Multilingual, Media translation, and WooCommerce Multilingual & Multicurrency.
Can you please update our plugins to their latest versions and see if the problem persists?
In this specific case, it is important also to update WooCommerce to check if the issue persists in the latest versions.
**** Important! Please make a full site backup (files and DB) before you proceed with those steps****
*** If your site is live, you might want to try those procedures in a staging environment. ***
Regards,
Itamar.
We use WooCommerce.NET, which is a popular .Net wrapper for the Woocommerce API.
As per the official Woocommerce documentation, the endpoint used to update the prices is this one:
hidden link
For example:
hidden link id>
Using the wrapper, we just post the product ID and the new regular + sale price (in fact for all the other API calls I mention below we also send the absolute minimum parameters necessary, which is usually just the ID and the new value).
I'm not sure if its relevant, but for the sake of providing as much info as possible I'll elaborate on the rest of the updating process:
If it's a variable product, we have to get the ID for each variation and update each one separately:
hidden link hidden link
We also have to repeat it for 3 different languages, as we've found the new changes are not applied automatically to other languages. The product and variation IDs are different for each language, so we have to get that too:
hidden link hidden link
Due to these complexities, the products are updated one by one at the moment, not in a batch.
We also update stock, which is a whole other process but similar to the above.
I'm not sure if it's relevant but we also make regular API calls to simply retrieve product details, 100 at a time:
Will do... if you're looking for a code example, here is one:
Dim rest As New WooCommerceNET.RestAPI("<em><u>hidden link</u></em>", strConsumerKey, strConsumerSecret)
Dim wc As New WCObject(rest)
Await wc.Product.Update(strProductID, New Product With {
.regular_price = strNewRegularPrice,
.sale_price = strNewSalePrice
}).ConfigureAwait(False)
From the slow query logs
The first query sends 1000 rows at the time
># Query_time: 2.296675 Lock_time: 0.000001 Rows_sent: 946 Rows_examined: 2182999
From the 2nd query 844670 rows at a time.
># Query_time: 3.699011 Lock_time: 0.000001 Rows_sent: 844670 Rows_examined: 4373239
From all the API operations it doesn’t seem WPML should fire these queries.
I would suggest escalating this with the staging site or CW site and some steps to see the slowness i.e. at what point they feel the site is slow so we can try to find who is triggering these slow queries.
We would like to ask for access details to your staging site and the steps to replicate this problem, as the second-tier supporter explained above.
I'm enabling a private message for the following reply.
I have an update from our second-tier supporter. He tested a similar scenario on a test site and started testing things on your staging site. But once he deactivated WPML, he got a fatal error. He is what our second-tier supporter wrote:
I made some tests on the regular website by setting the slow query time to 0.10 second. long_query_time = 0.1
Then I
Updated a product price
Listed some products
I did not find any slow query logged to the system. That means it works fine as regular product operations.
I am testing the issue on the staging site by listing 100 products at a time.
hidden link
But to measure the performance, I disabled WPML and saw a fatal error on the site.
Could you ask the client to provide FTP details so I can continue working on the site?
I need to enable the error logging to see the error, and then I will disable the plugin causing the fatal error.
Please share with us FTP access t your saying site. For this, I'm enabling a private message to the following reply.
its not 1 plugin which is causing the issue, its wpml sql query that is causing the slowdown, its not even an error, since the site doesn't have error when the WOOCOMMERCE API QUERIES are done,
its WPML Tables which are causing the slowdown, as we explained when we first opened the ticket, based on the intense tests our hosting has done
