slow down on mass price update

This is the technical support forum for WPML - the multilingual WordPress plugin.

Everyone can read, but only WPML clients can post here. WPML team is replying on the forum 6 days per week, 22 hours per day.

Tagged: Compatibility

This topic contains 113 replies, has 3 voices.

Last updated by Sumit 1 year, 4 months ago.

Assisted by: Sumit.

Author	Posts
March 21, 2023 at 6:54 pm #13297635
Itamar Supporter Languages: English (English ) Hebrew (עברית ) Timezone: Asia/Jerusalem (GMT+02:00)	Thanks for the FTP access details. Our second-tier supporter tested the issue on his testing site with one product to see if anything was wrong from WPML's side. On your staging site, our second-tier supporter intends to fire a request with 100 products, like the example you gave us. In any case, I've sent the access details and your reply to our second-tier supporter, and I'll update you here once I have his reply. Regards, Itamar.
March 21, 2023 at 7:10 pm #13297683
louisN-4	we are also happy if your higher level support wants, to connect them via email with our hosting compay, so they can show them the issue
March 24, 2023 at 10:40 am #13322253
louisN-4	we are now doing many string translations and attributes since we are adding a new language, and again we also saw spike in cpu usage of our mysql due to wpml could it be that lsk_translation table is not indexed? we have a massive 150,000+ products site
March 24, 2023 at 10:54 am #13322333
louisN-4	From the mysql slow query log: # Time: 2023-03-24T10:46:12.965339Z # User@Host: kidenoad_db[kidenoad_db] @ [172.16.39.34] Id: 5031723 # Query_time: 27.126256 Lock_time: 0.000001 Rows_sent: 34 Rows_examined: 6532895 SET timestamp=1679654745; SELECT SQL_CALC_FOUND_ROWS p1.ID, p1.post_parent FROM lsk_icl_translations t INNER JOIN lsk_posts p1 ON t.element_id = p1.ID LEFT JOIN lsk_icl_translations tt ON t.trid = tt.trid WHERE t.element_type = 'post_attachment' AND t.source_language_code IS null GROUP BY p1.ID, p1.post_parent HAVING count(tt.language_code) < 4 LIMIT 34; # Time: 2023-03-24T10:46:13.314929Z # User@Host: kidenoad_db[kidenoad_db] @ [172.16.39.34] Id: 5031720 # Query_time: 27.504664 Lock_time: 0.000000 Rows_sent: 34 Rows_examined: 6532895 SET timestamp=1679654745; SELECT SQL_CALC_FOUND_ROWS p1.ID, p1.post_parent FROM lsk_icl_translations t INNER JOIN lsk_posts p1 ON t.element_id = p1.ID LEFT JOIN lsk_icl_translations tt ON t.trid = tt.trid WHERE t.element_type = 'post_attachment' AND t.source_language_code IS null GROUP BY p1.ID, p1.post_parent HAVING count(tt.language_code) < 4 LIMIT 34; # Time: 2023-03-24T10:46:14.988275Z # User@Host: kidenoad_db[kidenoad_db] @ [172.16.39.34] Id: 5031728 # Query_time: 27.047800 Lock_time: 0.000001 Rows_sent: 34 Rows_examined: 6532895 SET timestamp=1679654747; SELECT SQL_CALC_FOUND_ROWS p1.ID, p1.post_parent FROM lsk_icl_translations t INNER JOIN lsk_posts p1 ON t.element_id = p1.ID LEFT JOIN lsk_icl_translations tt ON t.trid = tt.trid WHERE t.element_type = 'post_attachment' AND t.source_language_code IS null GROUP BY p1.ID, p1.post_parent HAVING count(tt.language_code) < 4 LIMIT 34; # Time: 2023-03-24T10:46:16.590071Z # User@Host: kidenoad_db[kidenoad_db] @ [172.16.39.34] Id: 5031739 # Query_time: 25.587700 Lock_time: 0.000001 Rows_sent: 34 Rows_examined: 6532895 SET timestamp=1679654751; SELECT SQL_CALC_FOUND_ROWS p1.ID, p1.post_parent FROM lsk_icl_translations t INNER JOIN lsk_posts p1 ON t.element_id = p1.ID LEFT JOIN lsk_icl_translations tt ON t.trid = tt.trid WHERE t.element_type = 'post_attachment' AND t.source_language_code IS null GROUP BY p1.ID, p1.post_parent HAVING count(tt.language_code) < 4 LIMIT 34; ^C
March 24, 2023 at 4:52 pm #13325465
Sumit Supporter Languages: English (English ) Timezone: Asia/Kolkata (GMT+05:30)	Hi, Based on the above queries it seems that media files are not duplicated fully and due to that our batch process of duplicate media is triggered. Also, this batch process does not finish sometimes and keeps running due to some corrupted media files. i.e. media files exist in WPML tables but do not exist in the posts table. This problem can be fixed by removing ghost entries from WPML and then duplicating media files. https://wpml.org/documentation/support/wpml-troubleshooting-options/ I have done that on your staging site and I can see media language is set. I also deleted untranslated strings from string translation to free up some space in tables https://wpml.org/documentation/getting-started-guide/string-translation/#deleting-strings Also, there was one attribute that was set to display as translated (please see the screenshot) I have changed this to translated only. Setting post type and taxonomies to display as translated loads extra queries. I also need to see the database so I tried to install the Adminer plugin but I can not upload the plugin because of permission issues. #1 Please upload the WordPress Adminer plugin or other plugins to see the database or provide PHPMyAdmin access. #2 Do some testing on the site and let me know if you see the slow queries again. I have enabled the private reply to share the details. Thanks
March 27, 2023 at 11:54 am #13338131
louisN-4	I am waiting for you to fix the problem and provide answer for next time how to not have duplicate translations on brand logos
March 27, 2023 at 12:00 pm #13338155
Itamar Supporter Languages: English (English ) Hebrew (עברית ) Timezone: Asia/Jerusalem (GMT+02:00)	Hi, This ticket is now assigned to Sumit, our second-tier supporter. He will check the issue and will get back to you. Thank you for your patience. Itamar.
March 28, 2023 at 11:14 am #13348437
Sumit Supporter Languages: English (English ) Timezone: Asia/Kolkata (GMT+05:30)	Hi, I did some changes to the staging site as I mentioned here https://wpml.org/forums/topic/slow-down-on-mass-price-update/page/2/#post-13325465 Please provide me feedback on these changes if you still see the problem because those queries you mentioned are already known to us and we know the fix and applied to your site. Also, let's stick to the staging site first I would request you to grant me permission of uploading plugins. The plugin you uploaded is saying Session mismatch. Go back and click Enter phpMyAdmin button again. If you still experience issue, try to open a ticket at Support pages and provide some details. So if you allow me to upload plugins I can test and see which DB plugin is working, and that can be done on the staging site only I think. About the indexes on icl_translation table. I am not sure which index you are talking about we already have indexes on all WPML tables. Please see the example of icl_translations table. (screenshot) Thanks
March 28, 2023 at 11:25 am #13348563
louisN-4	please open me a private box
March 28, 2023 at 11:46 am #13348919
Sumit Supporter Languages: English (English ) Timezone: Asia/Kolkata (GMT+05:30)	Hi sorry, enabled it.
March 28, 2023 at 12:06 pm #13349027
Sumit Supporter Languages: English (English ) Timezone: Asia/Kolkata (GMT+05:30)	Hi, Thanks for the details. Could you check again the link you provided? I see no blank page on the above link. Please see the screenshot. Also, please make sure you have a complete DB and site files backup of the live site if you want me to make changes there. I will try not to break anything but it is good practice. Thanks
March 28, 2023 at 12:20 pm #13349115
Sumit Supporter Languages: English (English ) Timezone: Asia/Kolkata (GMT+05:30)	Thanks! It is now working. Yes, I won't install anything. I will update you soon.
March 28, 2023 at 4:48 pm #13351689
louisN-4	whats up
March 28, 2023 at 5:15 pm #13351751
Sumit Supporter Languages: English (English ) Timezone: Asia/Kolkata (GMT+05:30)	Hi, I made the same changes as described here https://wpml.org/forums/topic/slow-down-on-mass-price-update/page/2/#post-13325465 on the live site. I have some additional information, please note:- #1 I noticed wp server stats plugin continuously pinging the server every second. This will result in high CPU and memory uses. I understand you want to see the real-time states but that should be done from server dashboard, not from WP dashboard. Please note even this is just one line code to get the uses but every request loads the whole WordPress it just like refreshing the home page again and again. Even the plugin author warned about it Refresh Interval This denotes the interval time after which the shell commands will execute again to give you the current load details. By default, it is set to 200ms, but if you are seeing CPU load increase after installing this plugin, try to increase the interval time to 1000ms, 2000ms, 3000ms or more until you see a normal CPU load. Generally, it is not recommended to change the value unless you are having extremely high CPU load due to this plugin. I would suggest disabling this plugin till we are debugging this issue to see the real impact of site. #2 There are 5562365 total images in the database including all four languages. (In the dashboard you will only see 4th part of this number). But only 3125248 are registered in the WPML. To find duplicate images, I found few of them has 400 to 300 copies of single images. Some example images with IDs are 2340 hidden link 2344 hidden link 2348 hidden link I am guessing that WPML is trying to duplicate them but failed due to the wrong language code in the database. That should be he (lowercase) but it is (HE) uppercase. (please see the screenshot) All of the images have the same issue. I am not sure about the reason but it could be migration of the website or database search replacement. But I corrected them by running these queries. UPDATE `lsk_icl_translations` SET language_code = 'he' WHERE BINARY `language_code` = 'HE'; UPDATE `lsk_icl_translations` SET source_language_code = 'he' WHERE BINARY source_language_code = 'HE'; #3 Please check this plugin https://wordpress.org/plugins/media-cleaner/ and clean the media so that we can reduce the database and avoid having duplicate entries in WP and WPML tables. #4 After cleaning up the database, please go to WPML > Support > Troubleshooting and click "Remove ghost entries from the translation tables" to clean the WPML tables. Then you can go to WPML > Settings > Media Translation and click Start to translate the media in all languages. (This will take long time, leave it after starting) #5 You can remove WPML CMS Nav and WPML Media plugin if not being used. Please see the uses of CMS Nav https://wpml.org/documentation/getting-started-guide/site-navigation/ And WPML media is only needed if you are using different images per language. https://wpml.org/documentation/getting-started-guide/media-translation/ Please let me know the results of these steps. Thanks
March 28, 2023 at 7:14 pm #13352341
louisN-4	1. thanks for the tip about wp-stats, i have changed it from 200ms to 5000ms to reduce load, although we don't have any server resource issues at all, our webserver is separated from the db via private tunnel 2. thanks for the HE -> he adjusting 3. media cleaner is running soon - i have asked knownhost, i will let you know when its done, i expect it to run for many hours 4. when i translated brands, i simply went to wpml translation and translated them automatically, HOWEVER images were not translated, so i had to go to brands and assign feature image to each - we have 2 type of brands A. brands as you know them: /wp-admin/edit.php?post_type=brand B. woocommerce brands plugin, not related to the translation as for 4, the media cleaner will resolve? 4 issue is this right? - https://wpml.org/forums/topic/slow-down-on-mass-price-update/page/2/#post-13322333 what about the issue which is similar but not the same, the first message on this forum ticket: https://wpml.org/forums/topic/slow-down-on-mass-price-update/#post-13220995 5. i can't remove wpml media now, since the site has many products and i can't check the side effects to remove it, is that ok if it stays? 6. wpml cms nav, i am pretty sure we use 7. to clean the database, which tables you want us to clear, we can use hidden link which we already have, or directly on the phpmyadmin by you