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 1 reply, has 2 voices.

Last updated by Raja Mohammed 6 months ago.

Assigned support staff: Raja Mohammed.

Author Posts
April 17, 2019 at 8:48 am

evarestS

Hi,

On a website running WPML, we have the problem that WPML runs a lot of slow queries. These queries are all saved into the mysql-slow.log, which in turn adds up to 20GB of logs (after which the server stalls as it is filled).

This is part of the log that we get:

SET timestamp=1555483408;
SELECT
				s.id,
				st.status,
				s.domain_name_context_md5 AS ctx ,
				st.value AS translated,
				st.mo_string AS mo_string,
				s.value AS original,
				s.gettext_context
			FROM wp_xxx_icl_strings s
			LEFT JOIN wp_xxx_icl_string_translations st
				ON s.id=st.string_id
					AND st.language='fr'
					AND s.language!='fr'
			WHERE s.context = 'WordPress'
			 LIMIT 1000 OFFSET 0;
# Time: 2019-04-17T06:46:34.417231Z
# User@Host: xxx[xxx] @ localhost []  Id: 70218
# Query_time: 0.111734  Lock_time: 0.000293 Rows_sent: 3343  Rows_examined: 13374
SET timestamp=1555483594;
SELECT DISTINCT 
						s.id,
						s.name,
						s.context,
						st.status,
						s.gettext_context, 
						st.value AS tra,
						st.mo_string AS mo_string,
						s.value AS orig
					FROM wp_xxx_icl_string_pages sp
					INNER JOIN wp_xxx_icl_string_urls su
						ON su.id = sp.url_id
					INNER JOIN wp_xxx_icl_strings s
						ON s.id = sp.string_id
					LEFT JOIN wp_xxx_icl_string_translations st
						ON s.id=st.string_id
							AND st.language=su.language
							AND s.language!=su.language
					WHERE (su.language='fr' and su.url='tag/([^/]+)/?$') or (su.language='fr' and su.url IS NULL);

We have tons of queries like this. The string translations table is about 45000 entries big, and becomes ever bigger.

I read already that WPML suggests deactivating this plugin, but that is not an option as then part of the site will not be translated. I also tried the suggestion to uncheck "Adjust IDs for multilingual functionality". Also this did not work.

To summarize:
The String Translation table/system slows down our website to a crawl. The String Translation Table seems to be quite big (46.000 entries). It is unclear how to fix this without loosing a lot of translations.

Attached, you find the server monitoring, which clearly shows that the log is filling up the server. Additionally, there is quite a high load on the CPU, which I think is also related to this issue.

I hope this is clear to you! Let me know if you need additional info.

April 17, 2019 at 12:18 pm #3630677

Raja Mohammed
Supporter

Languages: English (English )

Timezone: Asia/Kolkata (GMT+05:30)

Hi there,

Sorry that you are dealing with a performance issue due to our plugin.

Let me try to reproduce the issue with the provided duplicator package, I will update you soon with my finding.

If the string translation table is causing the issue it is more likely any of your plugin or theme is registering a duplicate string, please run the below sql query in check if you get any duplicate records.

SELECT COUNT(value) as rows_count, value FROM `wp_icl_strings` GROUP BY value ORDER BY rows_count DESC

please change the table prefix (wp_) as per your site configuration.

Let me know the results, please

Kind regards
Raja

The topic ‘[Closed] String Table of WPML fills up mysql-slow log’ is closed to new replies.