[PS 1.6] Very slow shop due to configuration query ?

[Tiphaine Desmasures]

Hi everyone, I'm running a new version of my shop for about 6 months (1.6.1.17), everything was working fine until last week. It became to be REALLY slow sometimes so I activated dev_mode and debug_profiling and here's the result :

Load Time	131025 ms - You'd better run your shop on a toaster
Querying Time	130185 ms
Queries	228
Memory Peak Usage	21.8 Mb

So I checked queries:

SELECT SQL_NO_CACHE c.`name`, cl.`id_lang`, IF(cl.`id_lang` IS NULL, c.`value`, cl.`value`) AS value, c.id_shop_group, c.id_shop FROM `ps16_configuration` c LEFT JOIN `ps16_configuration_lang` cl ON (c.`id_configuration` = cl.`id_configuration`)

126063.7

2634

It happened AFTER I cleared cache. My cache options are:

Smarty cache : yes, using files

CCC : everything activated

Cache : yes, Memcached par PHP::Memcached

 

My store is rnunning on a pretty powerful dedicated server with 20Gb RAM and SSD (can't remember the CPU but I guess it's a 16core bi-Xeon). It's not happening all the time and that's strange.

 

Any idea ?

Regards.

 

 

 

Edited January 8, 2018 by FLORENT GERAULT (see edit history)

[Tiphaine Desmasures]

Up ?

[selectshop.at]

Check your server configuration. You never should give the whole memory on the setting memory_limit. This will slow down your server. i.e. you will get long time-outs. More than 512M is not necessary.

https://www.prestashop.com/forums/topic/633856-server-requirements-tested-in-production-for-ps-16/

For cache options read carefully what I wrote there. memcache you can activate as optional module for to have some load balancing, but this is also not necessary if your server is well configured.

[Tiphaine Desmasures]

Thank you for the answer, I didn't touch server settings, I checked php.ini and memory_limit is not set (-1). I tried to change it to 512 but i'm a linux newbie and I can't edit it with vi (adding some strange characters even after pressing 'insert'). So I'll ignore this point for now cause I have no time to spend on installing other text editor or whatever it would need (I've already spent time trying to fix it, without success), except if you think it is THE reason why a query takes 80sec to load 2000 rows.

Memcached could cause this problem ?

I've read some users facing the same problem I do but they had 200k+ rows configuration tables, wich is not my case, mine is 1300... And it's working fine like 90% of times.

[selectshop.at]

1)vYou don't need memcache. Better is to run on nginx. For memory_limit, if it is set to -1 it will fit as well.

2) Check server error logs for errors.

3) Disable overrides and non native modules.

4) BTW you din't mentioned which php version you are using and which mySQL. Other settings on server are fulfilling the what I mentioned ?

[Tiphaine Desmasures]

1) I installed vim and was able to modify the memory limit, I've set it to 1024mb but no changes after reboot?(fixed)

I've read a bit about nginx, it's an apache alternative? Well I don't have the required time/knowledge to put it on a production server !

2) I can't see anything really interesting in the logs..

3) No change.

4) Php version: 5.6.17-0+deb8u1, mySQL : 5.5.47-0+deb8u1, other settings are ok.

Edited January 9, 2018 by FLORENT GERAULT (see edit history)

[siomosp]

Hello!

Did you manage to find the solution?

It happens to me also suddenly 

Query time ~ 7 sec , normaly it was < 1 sec!

Thank you

Panagiotis

[musicmaster]

22 hours ago, siomosp said:

Hello!

Did you manage to find the solution?

It happens to me also suddenly 

Query time ~ 7 sec , normaly it was < 1 sec!

Thank you

Panagiotis

Do as Florent and switch on mysql profiling (in Defines.inc.php and look what might have caused it.

In the case that Florent gives my follow up question would be to check the size of her configuration tables. Those are rather small tables so they shouldn't cause trouble.

Of course you should also check your server statistics. If some rogue search engine is sending lots of queries that will slow things down. Not to mention the case that some spammer has hacked your system and is using it for his own purposes.