Skip Navigation

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.

This topic contains 8 replies, has 2 voices.

Last updated by Waqas Bin Hasan 2 months ago.

Assisted by: Waqas Bin Hasan.

Author Posts
September 24, 2024 at 12:40 pm #16214862

lukaszN-8

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?

September 24, 2024 at 12:54 pm #16215017

Waqas Bin Hasan
Supporter

Languages: English (English )

Timezone: Asia/Karachi (GMT+05:00)

Hi,

Reference to our recent chat, I'm checking with our 2nd tier team and 'll update you accordingly.

Regards.

September 26, 2024 at 9:09 am #16223896

Waqas Bin Hasan
Supporter

Languages: English (English )

Timezone: Asia/Karachi (GMT+05:00)

Thank you for your patience and cooperation.

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.

See https://wpml.org/purchase/support-policy/privacy-and-security-when-providing-debug-information-for-support/ for details on privacy and security.

September 27, 2024 at 5:11 am #16228046

Waqas Bin Hasan
Supporter

Languages: English (English )

Timezone: Asia/Karachi (GMT+05:00)

Thank you for the access, I'm try to login but looks like site isn't responding (ERR_CONNECTION_TIMED_OUT).

However, I'll try again after some time and 'll update you accordingly.

September 27, 2024 at 5:28 am #16228059

lukaszN-8

Hi,
Try port 21 because I can connect with this data without any problems.

September 27, 2024 at 9:38 am #16229230

Waqas Bin Hasan
Supporter

Languages: English (English )

Timezone: Asia/Karachi (GMT+05:00)

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.

September 30, 2024 at 5:26 am #16234233

Waqas Bin Hasan
Supporter

Languages: English (English )

Timezone: Asia/Karachi (GMT+05:00)

Thank you for the access to the staging site, I was able to login.

I am working on this and 'll get back to you as soon as I find something or have a solution.

September 30, 2024 at 7:08 am #16234577

Waqas Bin Hasan
Supporter

Languages: English (English )

Timezone: Asia/Karachi (GMT+05:00)

Thank you for your patience and cooperation.

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.

September 30, 2024 at 1:18 pm #16236385

Waqas Bin Hasan
Supporter

Languages: English (English )

Timezone: Asia/Karachi (GMT+05:00)

Thank you for your patience and cooperation.

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.