yasir01 Posted June 20, 2013 Share Posted June 20, 2013 I am building an online shoe store using prestshop version 1.5.4. Each of my product i.e. shoe has 4 attributes i.e. shoe colour, shoe size, shoe width and shoe brand and shoe contains minimum 5 images. I have also enabled "Layered navigation" module to filter the products by colours, size and price range. Once i have added 3000 products prestshop performance gone down and on home page almost takes 1.5 minute to load although home page only contains featured products. When i click on a category page almost it take 2.5 minutes to load all the products in that category and when i apply colour or price filter it takes even more then 2.5 minutes to filter products. When i deeply looked into DB and found that for 3000 products i have almost 900000 records in "ps_product_attribute_combination" and this table is the bottle neck causing performance issue. After digging into code i found that prestshop is inserting 800 records in "ps_product_attribute_combination" table for a product having Colour: 5 ,Size: 10, Width: 4, Brand: 1 and here is the explanation of the product combination formula Add product with following attributes: Colour: 5, Size: 10, Width: 4, Brand: 1 Prestashop will add following data in tables 20 records in ps_attribute, ps_attribute_lang if attribute not added before 200 records in ps_product_attribute (formula: (color*size*width*Brand) i.e (5*10*4*1)). 800 record in ps_product_attribute_combination table (formula: (color*size*width*Brand)*number of attributes i.e (5*10*4*1)*4). For Images: If each color have 5 images 4000 image combinations in ps_product_attribute_image table As i have to add almost 60000 products in DB so almost there will be 48000000 records in "ps_product_attribute_combination" table and even more records in "ps_product_attribute_image" table and this is huge problem for me. This is a big question on prestashop performance. It is performing worse for 3000 products then just imagine how ot will perform for 60000 products? Can any body please suggest me how can i improve its performance with 60000 products and each product having different attributes? FYI: i have disabled all unnecessary modules and even enabled APC cache. 1 Link to comment Share on other sites More sharing options...
tomerg3 Posted June 21, 2013 Share Posted June 21, 2013 Have you enabled _PS_DEBUG_PROFILING_ in /config/defines.inc.php? It lets you see all the queries / calls and their times. I don't think the combinations table would have anything to do with the homepage taking so long to load. Link to comment Share on other sites More sharing options...
Dreamtheme Posted June 21, 2013 Share Posted June 21, 2013 You should start with, what is your hosting/server, is it dedicated one? for 60k products you better get dedicated server and CDN for sure. Link to comment Share on other sites More sharing options...
Trip Posted June 21, 2013 Share Posted June 21, 2013 Last but not least, afai tested none of the "extra" caching options is working in a way it it improving speed so better make A/B tests (with APC) on your own but as far as I know there is some priority at least on fixing memcached hopefully soon. Can't wait to see how much impact this will have on performance. Best regards, Trip Link to comment Share on other sites More sharing options...
yasir01 Posted June 21, 2013 Author Share Posted June 21, 2013 (edited) After enabling _PS_DEBUG_PROFILING_ in /config/defines.inc.php i got profiling information. I have attached document showing complete profiling information. As it says total Load time: 194.2s I have just added 3000 products with different attributes as explained in my question. home-page Load-time.doc Edited June 21, 2013 by yasir01 (see edit history) Link to comment Share on other sites More sharing options...
tczaude Posted June 21, 2013 Share Posted June 21, 2013 (edited) You have dedicated hosting or simple ?? What Storage Engine You use Edited June 21, 2013 by tczaude (see edit history) Link to comment Share on other sites More sharing options...
yasir01 Posted June 21, 2013 Author Share Posted June 21, 2013 (edited) You have dedicated hosting or simple ?? What Storage Engine You use It is running on a windows dedicated server with 8 gb ram. Storage engine is InnoDB Edited June 21, 2013 by yasir01 (see edit history) Link to comment Share on other sites More sharing options...
tczaude Posted June 21, 2013 Share Posted June 21, 2013 Ok sometimes i see people use MyIsam yet but for select beter is InnoDB. At first give me Your parameters for storage engine. And first rebuild all tables rebuild indexes in innodb ALTER TABLE comand. Link to comment Share on other sites More sharing options...
tczaude Posted June 21, 2013 Share Posted June 21, 2013 Dreamtheme goot speek Do You use any CDN ?? Link to comment Share on other sites More sharing options...
yasir01 Posted June 21, 2013 Author Share Posted June 21, 2013 Dreamtheme goot speek Do You use any CDN ?? I am not using any CDN Link to comment Share on other sites More sharing options...
tczaude Posted June 21, 2013 Share Posted June 21, 2013 try at start with cloudflare is for free Link to comment Share on other sites More sharing options...
saad.dplit Posted June 22, 2013 Share Posted June 22, 2013 @yasir01 I second you that Prestashop's default queries really bog down if you add few custom attributes and each attributes have lot of values. So normally for a product with 5 colors, Prestashop's default schema would have 5 records in the ps_product_attribute_combination table while for the 3 attributes you have added each with many values, the records are 200. This is an exponentially large no. of records and thus the in-built queries (homepage, listings page, search) involving many joins are just getting prohibitively slow. So it seems that Prestashop is not handling this scenario efficiently and few customizations might be required to handle the additional attributes you have added. Link to comment Share on other sites More sharing options...
saad.dplit Posted June 27, 2013 Share Posted June 27, 2013 @yasir01 I second you that Prestashop's default queries really bog down if you add few custom attributes and each attributes have lot of values. So normally for a product with 5 colors, Prestashop's default schema would have 5 records in the ps_product_attribute_combination table while for the 3 attributes you have added each with many values, the records are 200. This is an exponentially large no. of records and thus the in-built queries (homepage, listings page, search) involving many joins are just getting prohibitively slow. So it seems that Prestashop is not handling this scenario efficiently and few customizations might be required to handle the additional attributes you have added. Anyone else had a similar problem or experienced Prestashop limitations like this? Link to comment Share on other sites More sharing options...
tczaude Posted June 27, 2013 Share Posted June 27, 2013 Hi i worked with some around 70 attributes for some products. I must tell this is hard way but to do. At first You must precise where is problem on server www or on database server. About this tell You profiling from prestashop Link to comment Share on other sites More sharing options...
saad.dplit Posted June 27, 2013 Share Posted June 27, 2013 Hi i worked with some around 70 attributes for some products. I must tell this is hard way but to do. At first You must precise where is problem on server www or on database server. About this tell You profiling from prestashop Adding attributes does not seem a problem but if each attribute has lot of values and there are lot of products (at least more than 5000), then problem magnifies because each attribute's no. of values are multiplied i.e. suppose if each attribute has 10 values and there are 1000 products, then total records going in ps_product_attribute_combination table will be 1000 x 70 x 10 = 700,000 as opposed to 1000 x 10 = 10,000 had there been no custom attributes. So @tczaude, were you using that much of data loads? Link to comment Share on other sites More sharing options...
tczaude Posted June 27, 2013 Share Posted June 27, 2013 No problem how much You have attributes. This is only data. For editing You must use more memory and CPU. How much i don't know is many ways to resolve this problem if You have fast response from Mysql you must give more memory to PHP if You have long response from mysql is more ways where is problem. But first we must know where We must find something Link to comment Share on other sites More sharing options...
.png.022b5452a8f28f552bc9430097a16da2.png)
Recommended Posts