Extremly Slow Sql Query When 120 000 Products

Hi all!





I have about 120 000 products across 9000 categories, with more then 800 000 references between them (each product belongs to 7 categories in average).



Latest CS-Cart (4.1.3 at the moment) runs on VPS with 4GB RAM, single-core configuration. Cache is stored in the database (mySQL with myISAM tables and mySQLi mode).



Memory_limit for PHP 5.5 is set for 2GB.



Every page takes about 5-10 seconds to load. Bottleneck is SQL queries, that sometimes take up to 30 seconds to build up their data.



For examples, there are such monsters as:



SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100)) as price, GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids, products_categories.position, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) as product, IF(shared_descr.product_id IS NOT NULL, shared_descr.short_description, descr1.short_description) as short_description, IF(shared_descr.product_id IS NOT NULL, IF(shared_descr.short_description = '', shared_descr.full_description, ''), IF(descr1.short_description = '', descr1.full_description, '')) as full_description, IF(shared_descr.product_id IS NOT NULL, shared_descr.meta_keywords, descr1.meta_keywords) as meta_keywords, IF(shared_descr.product_id IS NOT NULL, shared_descr.meta_description, descr1.meta_description) as meta_description, IF(shared_descr.product_id IS NOT NULL, shared_descr.search_words, descr1.search_words) as search_words, IF(shared_descr.product_id IS NOT NULL, shared_descr.promo_text, descr1.promo_text) as promo_text, IF(shared_prices.product_id IS NOT NULL,MIN(IF(shared_prices.percentage_discount = 0, shared_prices.price, shared_prices.price - (shared_prices.price * shared_prices.percentage_discount)/100)),MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100))) as price, 100 - ((prices.price * 100) / list_price) AS sales_discount FROM cscart_products as products LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'ru' LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id AND shared_descr.company_id = 1 AND shared_descr.lang_code = 'ru' LEFT JOIN cscart_ult_product_prices as shared_prices ON shared_prices.product_id = products.product_id AND shared_prices.lower_limit = 1 AND shared_prices.usergroup_id IN (0, 0, 1) AND shared_prices.company_id = 1 WHERE 1 AND cscart_categories.company_id = 1 AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND prices.usergroup_id IN (0, 0, 1) GROUP BY products.product_id HAVING sales_discount > 0 ORDER BY sales_discount desc LIMIT 0, 8




Any advice how to handle such load?

I foresee, that these queries ate too complicated, and optimizing mySQL engine would not help much. Are there any other options to consider?

[quote name='Zahhar' timestamp='1395771636' post='180141']

Hi all!





I have about 120 000 products across 9000 categories, with more then 800 000 references between them (each product belongs to 7 categories in average).



Latest CS-Cart (4.1.3 at the moment) runs on VPS with 4GB RAM, single-core configuration. Cache is stored in the database (mySQL with myISAM tables and mySQLi mode).



Memory_limit for PHP 5.5 is set for 2GB.



Every page takes about 5-10 seconds to load. Bottleneck is SQL queries, that sometimes take up to 30 seconds to build up their data.



For examples, there are such monsters as:



SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100)) as price, GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids, products_categories.position, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) as product, IF(shared_descr.product_id IS NOT NULL, shared_descr.short_description, descr1.short_description) as short_description, IF(shared_descr.product_id IS NOT NULL, IF(shared_descr.short_description = '', shared_descr.full_description, ''), IF(descr1.short_description = '', descr1.full_description, '')) as full_description, IF(shared_descr.product_id IS NOT NULL, shared_descr.meta_keywords, descr1.meta_keywords) as meta_keywords, IF(shared_descr.product_id IS NOT NULL, shared_descr.meta_description, descr1.meta_description) as meta_description, IF(shared_descr.product_id IS NOT NULL, shared_descr.search_words, descr1.search_words) as search_words, IF(shared_descr.product_id IS NOT NULL, shared_descr.promo_text, descr1.promo_text) as promo_text, IF(shared_prices.product_id IS NOT NULL,MIN(IF(shared_prices.percentage_discount = 0, shared_prices.price, shared_prices.price - (shared_prices.price * shared_prices.percentage_discount)/100)),MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100))) as price, 100 - ((prices.price * 100) / list_price) AS sales_discount FROM cscart_products as products LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'ru' LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id AND shared_descr.company_id = 1 AND shared_descr.lang_code = 'ru' LEFT JOIN cscart_ult_product_prices as shared_prices ON shared_prices.product_id = products.product_id AND shared_prices.lower_limit = 1 AND shared_prices.usergroup_id IN (0, 0, 1) AND shared_prices.company_id = 1 WHERE 1 AND cscart_categories.company_id = 1 AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND prices.usergroup_id IN (0, 0, 1) GROUP BY products.product_id HAVING sales_discount > 0 ORDER BY sales_discount desc LIMIT 0, 8




Any advice how to handle such load?

I foresee, that these queries ate too complicated, and optimizing MySQL engine would not help much. Are there any other options to consider?

[/quote]

I am no expert in this, but with this number of products and categories even the best VPS with SSD and as much RAM as possible would be slow,



I would also suspect that storing the cache in the database would also be causing extra load on the SQL.



One thing that might help would be to turn on allow persistent sql connections. I took 3 seconds off my page load time on a 5000 product store on a VPS with about the same specs. but this will most likely come with it's own CPU load issue on a VPS of this spec an your 120 000 products



But with a store of this size only a dedicated server with as much CPU and RAM that you can throw at it will solve your issues.



Alan

Have you enabled the “slow query log” within mySQL? This will help dig into the exact query that killing the performance.

Planet MySQL :: Planet MySQL - Archives - Tools and tips for analysis of MySQL’s Slow Quer...

MySQL :: MySQL 8.0 Reference Manual :: 5.4.5 The Slow Query Log



Also, Percona has a few tools that can take that slow query log, rank your lowest queries and really help to identify the biggest offenders:

Identifying the load with the help of pt-query-digest and Percona Server - Percona Database Performance Blog

pt-query-digest — Percona Toolkit Documentation



And one thing we've used when needed is Percona's free analyer tool:

MySQL Tools and Management Software to Perform System Tasks by Percona

Suggest you move the cache to 'file' so that you reduce db contention. Then you probably want to do the query logging suggested above. But you will first want to remove the contention between the lookups and the cached data.

Hi Zahhar,



The issue can be hardly fixed by MySQL tuning or optimization, however it is possible to integrate your store with advanced cache or/and search systems.

For example, you may go ahead with the default “Searchanise” module which may the product queries much lighter or integrate your store with search engines like Elastica or Sphinx.



If you need more additional information on it, please do not hesitate to contact us on [url=“http://www.simtechdev.com/”]http://www.simtechdev.com/[/url]

So cs-cart (Simtech Development) is stating that there is no DB conflict in using mysql as the backend cache on large sites?

[quote name='tbirnseth' timestamp='1396474266' post='180798']

So cs-cart (Simtech Development) is stating that there is no DB conflict in using mysql as the backend cache on large sites?

[/quote]



Actually, by default all installations of CS-Cart uses the “file” backend cache.



As for the “mysql” backend cache type, it hardly works fine in case the site database is big because if main product queries take time, the “mysql” backend cache will only slow them down.





In addition, CS-Cart software supports the “redis” cache type and it should be faster even than the “file” one. For this type of cache it is necessary to install Redis on the server where CS-Cart installed.

Hi and thank you all!



What is done so far:


  1. Cache store changed back to default = file
  2. Database is tuned with http://mysqltuner.com/ script
  3. Time limits are increased to 3600 to prevent timeouts in admin panel.



    It made tinks little bit better, but it is still enough. I see main problem in CS-Cart architecture, that makes a real huge amount of very complicated queries (according to slow_query_log), so mySQL can't process them.



    Now thinking about separating mySQL to another VPS. It will give me more free RAM on current server to allocate it for redis cache store. Then I'll try to enable Searchanise.

Zahhar,



What did you endup doing? I'm having similar issues with v4. Extremely slow first time loads. Up to 20 seconds sometimes. After that it's 2-4 seconds for page loads. Some pages are loading more than 500 queries and I was told that is what's causing the problem. Haven't been able to get any help from cs-cart support yet.

Hi Novitas! Currently nothing new beyond named in my last post on April, 3rd. We working on design and development of additional functionality and decided just to move into 2-core server with even more memory (8GB instead of 4GB), to make use of Redis cache. I will report as far as I get any performance results.

I installed Redis and tried to switch from file to redis, however the site crashed. Any pointers? How did you get redis to work?



Would be very interested to know if switching to that 2-core server helped. Is that a dedicated server or vps?

Got redis to work, but didn't see a huge (if any) improvement. I really like all the features in cs-cart, however if I'm spending my time trying to optimize it to a workable speed, configuring servers, etc, and still not coming close to the speed of other carts/hosted solutions…what's the point?

Novitas, can you share with me clues about configuring Redis for CS-Cart? What is tech. parameters of your server, and number of products / relations between products and categories? What solutions did you tried with the same amount of products on the similar hardware?



As for me, I see point of CS-Cart in its unlimited customization possibilities without much pain. Also, its backend is good (it is far from perfect, especially compared to previous version, now I see 2 steps backwards, but still it is better then UI of its rivals), and we have developers support for reasonable price. It makes sense for me.

Zahhar,



I turned off redis and switched back to “file”. I didn't see any improvement.