MySql problems with ps_feature_shop

[subarui650hp]

Hi all,

 

I have a shop with 10.000 product.  Store loads fine, but when I try to enter a category mysql cpu load goes to 100% or more and doesn't change any category.

 

The problems it's in ps_feature_shop table. It's a innodb table with utf_8_general_ci encoding. It has 500.000 rows.

 

When a drop 350.000-400.000 rows, categories loads fine. 

 

First i thought the problem could be my hosting and i installed prestashop in my computer (quad core 3ghz, 4 gb ram, ssd... ) but the behavior is the same.

 

I tried differents configuracion for mysql in my.cnf file, same behavior.

 

Im using prestashop 1.5.5 

 

I need help.

 

Thanks.

Edited October 4, 2013 by subarui650hp (see edit history)

[vekia]

hello

 

have you got possibiliy to check slow queries log?

if so - it will be helpfull to localize the issue that you have

[subarui650hp]

hello

 

have you got possibiliy to check slow queries log?

if so - it will be helpfull to localize the issue that you have

Hello and thanks again i checked log and this is the result. When i click in a category there are a few queries but these two take long time

 

# Query_time: 16.327520  Lock_time: 0.000180 Rows_sent: 109262  Rows_examined: 2203097

SET timestamp=1380982686;

SELECT name, value, pf.id_feature

                                FROM ps_feature_product pf

                                LEFT JOIN ps_feature_lang fl ON (fl.id_feature = pf.id_feature AND fl.id_lang = 4)

                                LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = pf.id_feature_value AND fvl.id_lang = 4)

                                LEFT JOIN ps_feature f ON (f.id_feature = pf.id_feature AND fl.id_lang = 4)

                                 INNER JOIN ps_feature_shop feature_shop

                ON (feature_shop.id_feature = f.id_feature AND feature_shop.id_shop = 1)

                                WHERE pf.id_product = 11000

                                ORDER BY f.position ASC;

# User@Host: pshop[pshop] @ localhost []

# Query_time: 0.000463  Lock_time: 0.000095 Rows_sent: 0  Rows_examined: 0

SET timestamp=1380982686;

SELECT name, value, pf.id_feature

                                FROM ps_feature_product pf

                                LEFT JOIN ps_feature_lang fl ON (fl.id_feature = pf.id_feature AND fl.id_lang = 4)

                                LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = pf.id_feature_value AND fvl.id_lang = 4)

                                LEFT JOIN ps_feature f ON (f.id_feature = pf.id_feature AND fl.id_lang = 4)

                                 INNER JOIN ps_feature_shop feature_shop

                ON (feature_shop.id_feature = f.id_feature AND feature_shop.id_shop = 1)

                                WHERE pf.id_product = 13019

                                ORDER BY f.position ASC;

# Time: 131005 16:18:24

# User@Host: pshop[pshop] @ localhost []

# Query_time: 18.162235  Lock_time: 0.000082 Rows_sent: 112816  Rows_examined: 2382519

SET timestamp=1380982704;

SELECT name, value, pf.id_feature

                                FROM ps_feature_product pf

                                LEFT JOIN ps_feature_lang fl ON (fl.id_feature = pf.id_feature AND fl.id_lang = 4)

                                LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = pf.id_feature_value AND fvl.id_lang = 4)

                                LEFT JOIN ps_feature f ON (f.id_feature = pf.id_feature AND fl.id_lang = 4)

                                 INNER JOIN ps_feature_shop feature_shop

                ON (feature_shop.id_feature = f.id_feature AND feature_shop.id_shop = 1)

                                WHERE pf.id_product = 12568

                                ORDER BY f.position ASC;

 

Those product are the first listed in the category. I pasted here the last part of the log, the log stops and category doesnt load, then mysql down to 0% cpu load

Edited October 5, 2013 by subarui650hp (see edit history)

[tuk66]

The problem have to be in the query. It is not possible to get so many features for only one product - "Rows_sent: 109262".

 

Try to use EXPLAIN SELECT ... directly in phpMyAdmin to know more about using indices and records returned.