This thread is resolved. Here is a description of the problem and solution.
Problem: The client has installed the WPML plugin and followed steps to reduce the size of the icl_translate_job and other WPML tables by removing suggested job IDs and optimizing the tables. Despite these efforts, the table remains over 200MB, causing issues with database copying.
Solution: We reviewed the situation and found that a table size of 200MB for a site with 26 languages and extensive content is reasonable.
Upon further investigation, we used a sample query to analyze the contents of the translation_id and confirmed that the large size is justified due to the substantial content stored, with no duplicate elements found.
If this solution does not apply or is outdated, or if the problem persists, we recommend checking related known issues at https://wpml.org/known-issues/, verifying the version of the permanent fix, and confirming that you have installed the latest versions of themes and plugins. If needed, please open a new support ticket at WPML support forum.
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.
Our next available supporter will start replying to tickets in about 3.88 hours from now. Thank you for your understanding.
Background of the issue:
I have installed the WPML plugin and followed the steps suggested by Shekhar Bhandari to reduce the size of the icl_translate_job and other WPML tables. This included removing all suggested job IDs and optimizing the tables.
Symptoms:
The table is still over 200MB and causes a problem with making a copy of the database.
Questions:
Why is the table still over 200MB after following the suggested steps?
How can I further reduce the size of the icl_translate_job and other WPML tables?
Looks like that with 26 languages and 200MB size is not bad. The size depends on the number of pages and content in the pages.
However, if you still think it needs more improvement, I 'll need to take a closer look at your site. So I request temporary access (WP-Admin and FTP), preferably to a test site where the problem has been replicated.
Your next answer will be private, to share this information safely.
Also provide detailed steps to reproduce the issue.
IMPORTANT: Please take a complete backup of the site to avoid data loss. I may need to activate/deactivate plugins also.
Thank you for the updates, I was able to login and observe the problem.
I installed 2 plugins (Adminer and WP phpMYAdmin) to check database, I can see the table at a little over 200MB.
However, I need to investigate further and then escalate to the relevant team. For this, can you please create a test or staging site, by cloning your live site? So we can then investigate in detail without compromising the live site.
I've enabled your next reply as private to provide access to the test/staging site.
I've escalated the issue to our 2nd tier team for further investigation. Please keep the access open to the staging site. I'll get back to you as soon as there's more informatin.
Our team investigated the issue and has following updates:
Looking for example at the contents of translation_id 17743 we can see a whole lot in the BODY - about 50kb of base64 encoded data - this is roughly 39K characters -> which is Pretty big.
The translation for example has about 8k characters.
However this is what we can see for the post_content even if we check the posts table.
We can see the top 10 posts, using the following sample query:
SELECT t.translation_id, t.*, ts.total_size_kb
FROM wp4m_icl_translations t
JOIN (
SELECT translation_id,
(LENGTH(translation_package) + IFNULL(LENGTH(_prevstate), 0)) / 1024 AS total_size_kb
FROM wp4m_icl_translation_status
ORDER BY total_size_kb DESC
LIMIT 10
) ts ON t.translation_id = ts.translation_id;
So the size is justified. There appear to be no duplicate elements. The changes represent the post contents of the original post.
