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: 

This topic contains 19 replies, has 4 voices.

Last updated by developerO 1 year, 6 months ago.

Assigned support staff: Andrey.

Author Posts
March 6, 2018 at 3:45 pm #1537870

Nicolas

We have performance issue with multiple sites using WPML, in most case the site is only slower and we can find a workaround. But recently one site (using woocommerce) is having huge performance issue which make it almost un-usable during traffic peeks.

We followed everything recommanded to improve the performances, but it isn't helping that much. The only thing that seems to do a difference is caching, but as soon as any user hit a page which is not cached (checkout pages) the loading is really long.

After some analysis with the hosting team, it seems that the main problem is an overload of sql queries.
Using Debug object here are the number of queries for the home page :
Production site (all extensions activated and custom theme) : 305 queries / 164 queries from wpml
Minimum site with only wpml enabled and Twenty Sixteen theme) : 84 queries / 60 from wpml

In the case of the minimum site, the following queri from the function WPML_Element_Translation->maybe_populate_cache() is executed 38 times (with different element_id) :
SELECT t.translation_id, t.element_id, t.language_code, t.source_language_code, t.trid, t.element_type FROM llb_icl_translations t JOIN llb_posts p ON t.element_id = p.ID AND t.element_type = CONCAT('post_', p.post_type) JOIN llb_icl_translations tridt ON tridt.element_type = t.element_type AND tridt.trid = t.trid WHERE tridt.trid = (SELECT trid FROM llb_icl_translations t JOIN llb_posts p ON t.element_id = p.ID AND t.element_type = CONCAT('post_', p.post_type) WHERE element_id = 851 LIMIT 1)

What is the purpose of those queries and why are they executed that many times ?

In both case, wpml is sending most of the queries. Is there a way to reduce the amount of queries wpml execute ?

We like this plugin but we need our sites to be faster, as I said we're using it on multiple site and would like to keep it.

March 7, 2018 at 9:15 am #1538701

Yvette
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/Madrid (GMT+02:00)

Hello

I´ll answer your questions as you have asked them.

1. What is the purpose of those queries and why are they executed that many times ?

The query you posted is reading the icl_translations table where the "directory" of elements that are translated is located and associates a group of objects as translations of each other.

Here is a link that explains our table structures:
https://wpml.org/documentation/support/wpml-tables/

2. You asked:
Is there a way to reduce the amount of queries wpml execute ?

If you follow the steps from this link
https://wpml.org/faq/how-to-debug-performance-problems/

and send me the details as outlined in the article, I can get someone here to see if this query can be improved.

3. Question.
I searched our source code for the call "maybe_populate_cache()" but could not find anything.
Is this a call you tracked or is this coming from a report of some type from your hosting provider?

4. Technical Information on you environment
Could you please provide information about your environment by following thsese steps: http://wpml.org/faq/provide-debug-information-faster-support/

March 7, 2018 at 10:19 am #1538784

Nicolas

Thank for the explanation.

The debugs logs are to big to be hosted on pastbin, is there a way I could send you theses files ?

March 7, 2018 at 2:26 pm #1539217

Yvette
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/Madrid (GMT+02:00)

Can you upload them to a filesharing service like Dropbox, WeTransfer, or similar?
I am opening the private area for a downloadable link to the platform of your choice.

Thanks

March 12, 2018 at 1:55 pm #1543621

Yvette
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/Madrid (GMT+02:00)

Hello

Could you please install and activate the Duplicator plugin so that I can take a "reduced" snapshot of your site for our performance team?

Please let me know when this is done and I will log in and take the snapshot.
Thank you for your collaboration.

March 13, 2018 at 7:52 am #1544349

Nicolas

Hi, the plugin is installed and enabled

March 13, 2018 at 3:30 pm #1544932

Yvette
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/Madrid (GMT+02:00)

I have been trying in vain to make the snapshot. It may be due to my remote location and the network timing out.

Could you please provide a zip copy of your system files? and separately, please send a database dump. With these two things, I should be able to get by.

Alternatively, you can also try to create the package yourself.
- Go to Duplicator -> Packages
- Select "Create new"

You can use the parameters that are saved to see if you have better luck.

I am opening the private area again for either a successful Duplicator package or the zip of your system files with a database dump.

Thank you.

March 16, 2018 at 5:56 am #1547703

Yvette
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/Madrid (GMT+02:00)

Thank you again for your collaboration. All of the information has been forwarded to the team and the status of this ticket is "Escalated to Performance"

March 22, 2018 at 4:27 pm #1554155

Andrey
Supporter

Languages: English (English ) Russian (Русский )

Timezone: Europe/Kiev (GMT+03:00)

Yvette has passed over your ticket to me. I hope this is fine.

I took a look at your website and can see that's been optimized as much as possible if talking about WPML plugin. I'd like to mention that's normal for WPML to have additional quires in order to get multilingual content.
Using WPML's interface there's no way to reduce the number of queries it executes despite the fact what has been done already.
You may also try to exclude the strings from the text domains to be auto-registered in WPML >> String Translation, if the strings exist that you don't want to be registered.

Regarding the query you have asked, the purpose of it to get a translation of an element. The number of quires might depend on different factors like the number elements you are using on the page, the impact of your theme and the other plugins installed on your site. Checking with our WPML team I have confirmed that we have not had issues with queries recently as they are pretty much as optimized as possible it could be.

I would suggest updating your MySQL version to recommended ones as well as increasing your PHP memory limit.
https://wordpress.org/about/requirements/

March 26, 2018 at 9:20 am #1556324

Nicolas

So there is no way to use wpml in an efficient way on a site that have an average of 6k pageview/day ?
There is no way to have less than 2k+ sql request/pageview ?

That is kind of disappointing ...

We'll have to find an alternative to wpml.

March 27, 2018 at 8:30 am #1557515

Andrey
Supporter

Languages: English (English ) Russian (Русский )

Timezone: Europe/Kiev (GMT+03:00)

Thank you for your feedback. I'd like to say that from WPML perspective you have done all possible and yes, there's no way to reduce more the SQL queries from the WPML settings menu.

I would encourage you to examine your custom theme to see if there's something that can be improved about performance as well as the influence of 3rd party plugins.

The other option is to check the possibility of improving your current server like I wrote above. Increasing the PHP memory limit will also bring the performance improvement.

I hope this helps.

March 27, 2018 at 8:39 am #1557526

Nicolas

What I dont understand, is why on a page which have less than 100 strings to translate, there is almost 1000 sql request ?

March 27, 2018 at 10:10 am #1557637

Andrey
Supporter

Languages: English (English ) Russian (Русский )

Timezone: Europe/Kiev (GMT+03:00)

Are they all call coming from WPML? Does the same happen with those 100 strings when using any default theme?

The strings are not directly equal to the number of SQL queries, it may vary on different factors. Most likely the theme you use calls for different functions, as well as the other plugins, can make queries also.
You can also try to disable t he option "Adjust IDs for multilingual functionality " in WPML >> Languages > Make themes work multilingual, which might save some queries but in this case, you might get some invalid content or not.

March 27, 2018 at 10:13 am #1557640

Nicolas

We've checked with the IT team who manage the server, the problem is from the sql request (millions a day) which overload the CPU.
We tried to increase memory, but it doesn't help, we also tried to add more cpu, but the CPU usage juste scale when increasing ...
But again, it can't be reasonable to have that much sql request for a website of that size.

March 27, 2018 at 1:02 pm #1557872

Nicolas

A side question, for example if I take the following request :
SELECT language, value, status FROM icl_string_translations WHERE string_id=1

In debug object log, I have the following amount of query on each tab :
DB Queries : 11
Plug DB Queries : 66
WP_Content DB Queries : 86

How many time did the request got executed ? 86 or 163 ?

I can see that this request is used in some wpml plugins but also by some other plugins/template (the call seems to be hooked to wp action).
If there is any way we could work with our plugins/template to optimise the amount of request, I would be glad to do it. I just do not know where to work on.