Jump to content

 

VerDan

Member Since 13 Nov 2018
Offline Last Active Yesterday, 04:36 PM
-----

Posts I've Made

In Topic: My Cs-Cart Is To Slow

13 January 2019 - 12:58 PM

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.


In Topic: My Cs-Cart Is To Slow

12 January 2019 - 07:32 PM

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                                         |
+----+-------------+---------------------+------------+--------+-------------------------------------------------+-----------+---------+----------------------------------------+---------+----------+----------------------------------------------+

In Topic: My Cs-Cart Is To Slow

12 January 2019 - 07:07 PM

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, ...<about 100 productids> ) 
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;

In Topic: My Cs-Cart Is To Slow

12 January 2019 - 06:28 PM

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.... 


In Topic: Updating Mysql Or Changing To Mariadb On Existing Installation

12 January 2019 - 05:38 PM

We actually setup our system on Dedicated host with 12 Cores and 16GB of memory.   It was also running on MariaDB.

 

Performance was pretty dismal so I did an experiment and loaded up the system on a mini AWS instance -- 1 core, 2GB of RAM running MySQL.

 

Oddly enough, performance was 5 times better on the AWS instance, at least for a single user.   The hosting company checked, reviewed, checked some more and based on what I could see, it was configured correctly.

 

So, the Hosted system was CentOS and MariaDB and the AWS was Ubuntu and MySQL.

 

One day i will find out why the performance was that much poorer, but for now we are trying to figure out why we have performance issues going into one category (another post her on the forum).