My Cs-Cart Is To Slow

Hi,

I have a new server and i have the same issues.. why is my cs-cart slow. page open sometimes in 15sec.

I have more as 10000 Categories, (12600).) but no products.only the defaults products from cs-cart.

I need help

Hi,

I have a new server and i have the same issues.. why is my cs-cart slow. page open sometimes in 15sec.

I have more as 10000 Categories, (12600).) but no products.only the defaults products from cs-cart.

I need help

Hello,

This seems to be related to the incorrect configuration of CS-Cart. Please contact us at sales@poppedweb.com and we'll take a look at what we can mean for you in this situation.

Best wishes,

Also check any blocks that make use of excessive categories or products that appear on pages/product details. CS-Cart will burn up query time building that out on every page load.

My site was slow and I changed to AWS. It is very fast now but I don't know if it is the right choice.

I have only been on AWS for about 2 weeks. Today my site went down and I have no idea what to do. It is very different than using a standard hosting company. The backend is very confusing for me. Also the price seems very expensive.

I will probably go back to a regular hosting company.

My site was slow and I changed to AWS. It is very fast now but I don't know if it is the right choice.

I have only been on AWS for about 2 weeks. Today my site went down and I have no idea what to do. It is very different than using a standard hosting company. The backend is very confusing for me. Also the price seems very expensive.

I will probably go back to a regular hosting company.

Hello,

If you need any assistance, you can contact us at info@poppedweb.com.

Kind regards,

Hello!

Regarding AWS cloud hosting, if you allow me. This service is probably the best choice for an eCommerce business but is very complicated to configure. Hundreds of services with even more settings to them - it's easy to get lost right on the homepage.

We offer a business solution for CS-Cart and Multi-Vendor owners which includes AWS cloud hosting with entire server support - optimized server settings, 24/7 monitoring, software updates, backups, load speed optimization, SSL and much more. We can make your server perform better without additional hardware and save your money.

We have servers in data centers around the Globe: London, North Virginia (USA), Ireland, Paris, Mumbai, Singapore, and Sydney.

If you want to save your time on servers and dedicate it to your business growth - delegate it to professionals.

Reach us for a free consultation on our website.

Good luck!

I know this is a late reply -- and an older topic, but we are having the same issue.

We currently have about 8000 categories and under on primary category we have over 1 Million products. We have 4 top level categories across the main category bar. When a user select 3 of them, performance is fine (low product counts) but for the one, it can take 15-20 seconds to return.

I have extracted the query that is used (actually 2) in order to understand it and see what can be done. Unfortunately the query used ends up making essentially a Full Table Scan.

Here is one of Offending queries. I removed some of the data just so it would fit.

SELECT SQL_CALC_FOUND_ROWS products.product_id, descr1.product as product, companies.company as company_name 
FROM cscart_products as products  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
AND descr1.lang_code = 'en'  
LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 
LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id  
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')   
WHERE 1  
AND cscart_categories.category_id IN (420, 2242,  <....8000 more ID''s here>  )
AND products.product_id NOT IN (183291, 183292, <....about 100 more ID''s here>  ) 
AND companies.status = 'A'  
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) 
AND products.approved = 'Y' 
AND products.product_type IN ('P', 'C') 
GROUP BY products.product_id   
ORDER BY product asc, products.product_id ASC  
LIMIT 0, 12

To try and work through this, i have even reduced/cut out each of the tables not required for the SELECT fields (yeah, it ruins some of the conditions, but just trying to find raw speed). Unfortunately, this only helped some, shaved a few seconds off of 20 when I need 1 or 2 second response times.

Once the query is run, it caches up the result so i have had to add in the SQL_NO_CACHE to the select when running the raw SQL against the database.

We expect our database to grow up to around 10-15M products so need to get this resolved or at least some options. We are currently setting up a "pinger" that will hit our poorly performing pages on a regular basis in order to keep the data cached and the response reasonable for the users, but this really is not a good solution.

Any help or options would be appreciated.

It is of paramount importance to have your MySQL on a separate SSD. I have almost 1TB SSD and 32 GB RAM, but still my home page is slow. Thanks to eComLabs invaluable - but unfortunately scattered all over the place - suggestions, I have disabled the gallery in the product grids and now the CS-Cart is lightning fast.

I wish it was as simple as that. I am running the query above and one that i stripped down to just the 3 needed tables (in the SELECT statement) and still see some pretty bad performance. The EXPLAIN plan indicates it has to iterate over all 1.2M records....

I wish it was as simple as that. I am running the query above and one that i stripped down to just the 3 needed tables (in the SELECT statement) and still see some pretty bad performance. The EXPLAIN plan indicates it has to iterate over all 1.2M records....

They key is in not selecting any tables containing TEXT columns in the first place since they will require to be read from disk. For that reason, it is recommended that you first query all the products and after that all the appropriate product descriptions.

Anyhow, you could also try optimizing by means of indices, this would involve modifying the query to use the FORCE INDEX query parameter in some cases.

Unfortunately we are not wanting to edit the actual code for the product. We had to use an Addon for the Search portion of the site and they set hooks to update the query and do some other optimizations.

I have checked and the database is not actually hitting the disk from what i can tell -- except to write out during the GROUP BY and ORDER operation (I have even removed that from the direct DB query to remove the write).

I have been tinkering with the INDEXES and creating new, testing, removing, etc, just nothing really dramatic occurs. I have even used the FORCE INDEX. Below is my stripped down query and I think the bottom line is there is a lot of data, but this still should be much quicker...

Time to try some different Types of indexes and the sorting order with them....

SELECT  SQL_CALC_FOUND_ROWS products.product_id, descr1.product as product, companies.company as company_name 
FROM cscart_products as products  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
AND descr1.lang_code = 'en'  
LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id  
WHERE 1  
 AND products.product_id NOT IN (183291, ... ) 
AND companies.status = 'A'  
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 products.approved = 'Y' 
AND products.product_type IN ('P', 'C') 
GROUP BY products.product_id   
ORDER BY product asc, products.product_id ASC  
LIMIT 0, 12;

Unfortunately we are not wanting to edit the actual code for the product. We had to use an Addon for the Search portion of the site and they set hooks to update the query and do some other optimizations.

I have checked and the database is not actually hitting the disk from what i can tell -- except to write out during the GROUP BY and ORDER operation (I have even removed that from the direct DB query to remove the write).

I have been tinkering with the INDEXES and creating new, testing, removing, etc, just nothing really dramatic occurs. I have even used the FORCE INDEX. Below is my stripped down query and I think the bottom line is there is a lot of data, but this still should be much quicker...

Time to try some different Types of indexes and the sorting order with them....

SELECT  SQL_CALC_FOUND_ROWS products.product_id, descr1.product as product, companies.company as company_name 
FROM cscart_products as products  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
AND descr1.lang_code = 'en'  
LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id  
WHERE 1  
 AND products.product_id NOT IN (183291, ... ) 
AND companies.status = 'A'  
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 products.approved = 'Y' 
AND products.product_type IN ('P', 'C') 
GROUP BY products.product_id   
ORDER BY product asc, products.product_id ASC  
LIMIT 0, 12;

Could you try running the query without the product descriptions table?

I didn't want to do that since it was a key element of the query, but did it to see what would happen. Query time went from about 8 seconds (for the stripped down query above) to just under 3 seconds. So that does help -- BUT, i also have to take out the ORDER BY PRODUCT. Below is the EXPLAIN plan for the query without the Product Description table.

This got me thinking -- The page that comes up when you click the Category actually needs more information, not just the product description. It needs the images, prices, etc. This really seems like a Wasted query, but that really is for the cs-cart developers to define and make clear.

EXPLAIN PLAN:

+----+-------------+-----------+------------+--------+-------------------------------------------------+---------+---------+----------------------------+---------+----------+----------------------------------------------+
| id | select_type | table     | partitions | type   | possible_keys                                   | key     | key_len | ref                        | rows    | filtered | Extra                                        |
+----+-------------+-----------+------------+--------+-------------------------------------------------+---------+---------+----------------------------+---------+----------+----------------------------------------------+
|  1 | SIMPLE      | products  | NULL       | ALL    | PRIMARY,status,idx_cscart_products_product_type | NULL    | NULL    | NULL                       | 1248412 |     9.97 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | companies | NULL       | eq_ref | PRIMARY                                         | PRIMARY | 4       | gvcart.products.company_id |       1 |    10.00 | Using where                                  |
+----+-------------+-----------+------------+--------+-------------------------------------------------+---------+---------+----------------------------+---------+----------+----------------------------------------------+

Just to be complete, here is the Complete Query explain plan -- note it has to look at all rows.

+----+-------------+---------------------+------------+--------+-------------------------------------------------+-----------+---------+----------------------------------------+---------+----------+----------------------------------------------+
| id | select_type | table               | partitions | type   | possible_keys                                   | key       | key_len | ref                                    | rows    | filtered | Extra                                        |
+----+-------------+---------------------+------------+--------+-------------------------------------------------+-----------+---------+----------------------------------------+---------+----------+----------------------------------------------+
|  1 | SIMPLE      | products            | NULL       | ALL    | PRIMARY,status,idx_cscart_products_product_type | NULL      | NULL    | NULL                                   | 1248412 |     9.97 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | companies           | NULL       | eq_ref | PRIMARY                                         | PRIMARY   | 4       | gvcart.products.company_id             |       1 |    10.00 | Using where                                  |
|  1 | SIMPLE      | prices              | NULL       | ref    | usergroup,product_id,lower_limit,usergroup_id   | usergroup | 3       | gvcart.products.product_id             |       1 |    30.00 | Using where; Using index                     |
|  1 | SIMPLE      | products_categories | NULL       | ref    | PRIMARY,pt                                      | pt        | 3       | gvcart.products.product_id             |       2 |   100.00 | Using where                                  |
|  1 | SIMPLE      | cscart_categories   | NULL       | eq_ref | PRIMARY,c_status,p_category_id                  | PRIMARY   | 3       | gvcart.products_categories.category_id |       1 |    20.00 | Using where                                  |
|  1 | SIMPLE      | descr1              | NULL       | eq_ref | PRIMARY,product_id                              | PRIMARY   | 9       | gvcart.products.product_id,const       |       1 |   100.00 | NULL                                         |
+----+-------------+---------------------+------------+--------+-------------------------------------------------+-----------+---------+----------------------------------------+---------+----------+----------------------------------------------+

I wish it was as simple as that. I am running the query above and one that i stripped down to just the 3 needed tables (in the SELECT statement) and still see some pretty bad performance. The EXPLAIN plan indicates it has to iterate over all 1.2M records....

With 15M products, you will need a quantum computer...

I didn't want to do that since it was a key element of the query, but did it to see what would happen. Query time went from about 8 seconds (for the stripped down query above) to just under 3 seconds. So that does help -- BUT, i also have to take out the ORDER BY PRODUCT. Below is the EXPLAIN plan for the query without the Product Description table.

Well, seeing that it is still not using an index, I suggest you contact us here, so we can take a look at what we can do for you: sales@poppedweb.com

With 15M products, you will need a quantum computer...

No, he should actually add indices / indexes to sort his table and use a cluster to distribute them across the stack accordingly

Well, seeing that it is still not using an index, I suggest you contact us here, so we can take a look at what we can do for you: sales@poppedweb.com

No, he should actually add indices / indexes to sort his table and use a cluster to distribute them across the stack accordingly

Yep, i'm thinking the index type and sorting will be a key. Clustering should help as well, hard part is getting that all figured out and the best way of getting this done without needing application changes.

Yep, i'm thinking the index type and sorting will be a key. Clustering should help as well, hard part is getting that all figured out and the best way of getting this done without needing application changes.

We do offer Amazon services. Seeing that you want the simplest solution, I suggest using a Aurora Serverless application. Once that is configured accordingly it will be automatically scaled and optimized accordingly to your actual needs. The best yet, it requires no application changes.

Well we continue to battle this.

Unfortunately i think there is just some very poor use of some of the queries throughout the system.

We added the Speedup Addon and it seemed to work initially but we are seeing 10 second searches with single word searches. Going to 2 or 3 words it can get painful.

I have setup the database in Aurora Serverless, upgraded the instance type within AWS, no real difference. With 8000 categories and a million products expecting to go to more, we have to really buckle down on some options.

What i think is going to be required is somehow integrating a tool like ElasticSearch and then updating the application to take advantage of this. So, anyone with any thoughts on this and the level of difficulty or addons supporting this, let me know -- willing to work with anyone on this, just need some concrete options.

It is of paramount importance to have your MySQL on a separate SSD. I have almost 1TB SSD and 32 GB RAM, but still my home page is slow. Thanks to eComLabs invaluable - but unfortunately scattered all over the place - suggestions, I have disabled the gallery in the product grids and now the CS-Cart is lightning fast.

Hi Imago .. It seems we share similar journey with cs-cart :) would you please more about (disabling the gallery in the product grids) ?

Hi Imago .. It seems we share similar journey with cs-cart :) would you please more about (disabling the gallery in the product grids) ?

Sure. Here you are

https://forum.cs-cart.com/topic/54057-how-to-change-product-list-grid-layout/#entry310918