[Closed] Very slow queries

Hello,

I hope this email finds you well.

We are currently experiencing performance issues on our website, particularly related to slow queries and large database tables associated with the WPML plugin. Below are the details of the large tables that seem to be causing the most significant impact:

wp_icl_translate: 1859.59 MB
wp_icl_translation_status: 1196.06 MB
wp_postmeta: 1095.48 MB
wp_posts: 570.16 MB
wp_icl_string_translations: 260.89 MB
Given that these tables are quite large, we believe they may be contributing to the overall slowness of our site. We would greatly appreciate any guidance you can provide on how to optimize these tables and improve the performance of the queries related to the WPML plugin.

In our search for solutions, we came across the idea of analyzing slow SQL queries and selectively adding indexes to optimize performance. We would appreciate your advice on how best to approach this with WPML, including any specific indexing strategies that could be applied.

Please let us know if you have any recommendations or solutions for optimizing these database tables and queries. We are keen to implement the necessary changes to enhance our website's performance.

Thank you in advance for your support.

Hello Team,

Thank you for your response.

We are indeed addressing the performance of the entire website. Our server resources are being overutilized due to large MySQL queries and slow queries, as I previously mentioned.

To troubleshoot, we installed the Health Check & Troubleshooting plugin. When we disabled all plugins, the site loaded very quickly. Upon enabling all plugins except WPML and its related plugins, the website continued to load quickly. However, once the WPML plugins were enabled, the site slowed down noticeably both in terms of loading time and overall performance.

We also tried enabling only the WPML plugins while disabling all others, and again noticed significant slowness. This is the core issue we're experiencing.

Additionally, we're experiencing slowness within the WordPress dashboard, particularly when performing operations like creating, translating, and updating posts. Sometimes, updating or publishing posts fails, especially for lengthy articles in the "Price Prediction" category.

You can replicate these results on the staging site using the credentials I provided earlier. Please take a look and let us know your feedback.

Thank you for your assistance.

Best regards,
Coin Edition

Dear WPML Support Team,

I hope this message finds you well.

I am writing to follow up on the progress of our ongoing performance issue related to the WPML plugin. We understand that debugging such issues takes time, and we appreciate your efforts in addressing this matter. Could you please provide an update on the current status of your investigation?

Additionally, we have identified some errors related to the WPML plugin(11 instances), particularly with its REST API, which may be contributing to the performance issues we are experiencing. Below are the details for your reference:

Error Log Snippet:
[Tue Sep 03 11:31:47.501902 2024] [proxy_fcgi:error] [pid 2606133:tid 127777716045504] [client 162.158.62.205:47768] AH01071: Got error 'PHP message: PHP Warning: Undefined array key 0 in /var/www/html/wp-content/plugins/wpml-media-translation/classes/class-wpml-media-attachment-by-url-query.php on line 99...'

Additional Findings:
Plugin-Specific Issues (WPML): We have encountered multiple errors associated with the WPML plugin, especially with its REST API. A notable example is below:

162.158.154.97 - - [03/Sep/2024:04:37:16 +0000] "POST /wp-json/wpml/tm/v1/ate/jobs/retry HTTP/1.1" 500 3169 "hidden link" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36"

We hope these insights are helpful in further analyzing the root cause of the performance issues. Please let us know if there are any additional steps we should take on our end or if you require more information.

We look forward to your feedback and appreciate your continued assistance.

Thank you once again for your support.

Best regards,
Coin Edition

Hello Team,

I hope this message finds you well.

We are currently using WPML for 8+ languages on our website, relying heavily on your automatic translation engine. We spend thousands of dollars each month on these services, as WPML is a crucial part of our multilingual functionality.

Recently, As part of our ongoing efforts to resolve performance issues on our website, our server management team has identified that WPML is making frequent requests to Redis. These frequent cache accesses appear to be contributing to performance degradation, particularly involving high-volume read and write operations to the cache.

Key Issues:
Frequent Redis Requests by WPML: The high volume of cache accesses by WPML is overloading Redis, causing slowdowns and affecting the overall user experience.

Questions:
Compatibility with Redis Object Cache: Can you please confirm if Redis object cache is fully compatible with WPML? We want to ensure that there are no conflicts or known issues that might be contributing to these performance problems.

Scalability Concerns: Our website has over 55,000 posts across multiple languages. Can WPML handle this scale effectively when integrated with Redis, or are there any limitations we should be aware of?

Next Steps:
Optimization of WPML’s Interaction with Redis: Our team recommends optimizing WPML’s caching interactions to reduce the number of Redis calls or improve how caching is handled. We would greatly appreciate your guidance on specific optimizations, settings, or adjustments we can make to address this issue.

Given our significant investment in WPML’s services, we are eager to resolve these performance challenges. Your assistance in resolving the performance and Redis object cache issues would be highly valuable to us.

Thank you for your attention to this matter. We look forward to your prompt response.

Best regards,

Coin Edition