Jump to content

Statistics grinds site to a halt


keat

Recommended Posts

If i open up statistics, not only does the admin side take time to refresh, but it also seems to affect the customer side.

I'm guessing something to do with MySql data crunching.

Is there anything I can do to speed this up.

Link to comment
Share on other sites

load the page in debug mode.

Capture the SQL Queries being run.

manually run each one in via PhpMyAdmin against your database to isolate those which are being a problem. The root cause will likely be the volume of data and a long running query causing a lock on a table. Once we know the query it may be able to be improved via additional indexes.

use MySQL EXPLAIN to generate the execution plan for the problem SQL query, share both the query and plan :)

 

 

Link to comment
Share on other sites

In debug mode, I see a number of timings against the queries, most of which are pretty rapid, but two stick out.

 

[18] SELECT sum(O.quantity) AS quan, O.product_id, I.name FROM `CubeCart_order_inventory` AS O INNER JOIN `CubeCart_order_summary` AS S ON S.cart_order_id = O.cart_order_id INNER JOIN `CubeCart_inventory` AS I ON O.product_id = I.product_id WHERE (S.`status` = 2 OR S.`status` = 3) GROUP BY I.product_id DESC ORDER BY `quan` DESC LIMIT 15 OFFSET 0 -- (12.1980421543 sec) [NOT CACHED]
[19] SELECT sum(O.quantity) AS quan, O.product_id, I.name FROM `CubeCart_order_inventory` AS O INNER JOIN `CubeCart_order_summary` AS S ON S.cart_order_id = O.cart_order_id INNER JOIN `CubeCart_inventory` AS I ON O.product_id = I.product_id WHERE (S.`status` = 2 OR S.`status` = 3) GROUP BY I.product_id DESC ORDER BY `quan` DESC -- (11.7274589539 sec) [NOT CACHED]

 

Not surprising that the lag is centered around order_sumarry (258mb) and order_inventory (78mb)
 

 

I also see an error, but the timings on this are small, so I guess this isn't an issue.

 

[31] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('[Notice] /home/xxxx/public_html/admin/sources/statistics.index.inc.php:307 - Undefined index: plugin_tabs','1495019008'); -- (0.140591859818 sec) [ERROR - NOT CACHED]
[32] DELETE FROM `CubeCart_system_error_log` WHERE time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) ; -- (0.000190019607544 sec) [ERROR - NOT CACHED]

Link to comment
Share on other sites

Don't trust the timings reported in debug move. run the SELECT queries manually and validate. I've had i t reported miliseconds before when in reality it's taking minutes.

can you run the same queries with the word EXPLAIN before them. need both query run times (from phpmyadmin) and also the results of the EXPLAIN query. I am out of the office for the afternoon so my replies may be delayed.

 

Link to comment
Share on other sites

Putting the word 'EXPLIAN' before the query resulted in the following.

1     SIMPLE     I     ALL     PRIMARY     NULL    NULL    NULL    4378     Using temporary; Using filesort
1     SIMPLE     O     ref     productId,cart_order_id     productId     4     cart_ccv6.I.product_id     96     
1     SIMPLE     S     eq_ref     cart_order_id,status     cart_order_id     56     cart_ccv6.O.cart_order_id     1     Using where

and

1     SIMPLE     I     ALL     PRIMARY     NULL    NULL    NULL    4378     Using temporary; Using filesort
1     SIMPLE     O     ref     productId,cart_order_id     productId     4     cart_ccv6.I.product_id     96     
1     SIMPLE     S     eq_ref     cart_order_id,status     cart_order_id     56     cart_ccv6.O.cart_order_id     1     Using where

 

 

running the actual query

 

SELECT sum(O.quantity) AS quan, O.product_id, I.name FROM `CubeCart_order_inventory` AS O INNER JOIN `CubeCart_order_summary` AS S ON S.cart_order_id = O.cart_order_id INNER JOIN `CubeCart_inventory` AS I ON O.product_id = I.product_id WHERE (S.`status` = 2 OR S.`status` = 3) GROUP BY I.product_id DESC ORDER BY `quan` DESC LIMIT 15 OFFSET 0

took 11.47 seconds, so not far off the benchmark figure quoted in debug.

Wit the word Explain in front, the results were almost instantaneous.

Link to comment
Share on other sites

It looks like your database is missing some indexes - this could explain why your query is taking so long to run.

Here are my results:

id select_type table type possible_keys key key_len ref rows Extra  
1 SIMPLE S range cart_order_id_2,status,cart_order_id status 1 NULL redacted Using index condition; Using temporary; Using file...
1 SIMPLE O ref product_id,cart_order_id cart_order_id 56 S.cart_order_id 1 NULL
1 SIMPLE I eq_ref PRIMARY PRIMARY 4 O.product_id 1 NULL

Note the key 'status' in the first row with a key_length of 1; also note that row 2 returns rows on a 1-to-1 basis, whereas yours returned 96 products.

I recommend you go through each of your tables and compare them to CubeCart's database setup file. You may also be able to check the indexes from your Admin's Maintenance tab.

By the way, running the actual query only takes 0.0053 seconds for me.

Other options to consider if fixing your table indexes doesn't work:

1. Create a summary table (and regularly update it) containing a running tally of quantities sold so that the database doesn't have to actually SUM potentially tens of thousands of rows. This wouldn't be a bad idea for core CubeCart to implement.

2. If you have control over the server hardware, you could swap in an SSD drive to store the database on, increase RAM, etc. to improve query speeds. If you cannot manage the hardware, you may still be able to contact your host and see if they have better options for you.

3. Partition the table (look up table partitioning) so that there aren't such huge amounts of data to query against, especially since the Dashboard only cares about the most recent ones. I don't think this is actually a viable solution in your case, but it is a common practice when dealing with large amounts of data.

Edited by bsandall
Link to comment
Share on other sites

You are definately missing indexes. There should be at least "possible keys", but there are none in your plan.

quickest way to fix/check manually would be to do a clean install of the latest version of CubeCart to a new DB, then do a side by side compare of each table indexes. create any that are missing. Chances are if you are missing one you are missing more elsewhere.

in PhpMyAdmin, open the table, go to the "Structure" tab, then expand "Indexes".

If you have indexes and still see those long query times there is something else going on and it may require some tuning. report your results back here. happy to help tune queries. I am a DB guy :D

Link to comment
Share on other sites

I created a clean install of 6.1.7 and then compared the indexes side by side.

I did find a few issues.

The customer table had no unique index for email, and I had to remove a number of duplicates to create this index.

Options_matrix although had an options identifier, it was tagged as a second entry under ProductID

and the reviews table was missing every index, so I copied the table from the clean install.

Other than that, i could spot any issues, and none of this fixed the problem.

Its taking about 35 seconds to open the statistics tab.

 

id select_type table type possible_keys key key_len ref rows Extra  
1 SIMPLE I ALL PRIMARY NULL NULL NULL 4378 Using temporary; Using filesort
1 SIMPLE O ref productId,cart_order_id productId 4 xxx_ccv6.I.product_id 96  
1 SIMPLE S eq_ref cart_order_id,status cart_order_id 56 xxxx_ccv6.O.cart_order_id 1 Using where
Link to comment
Share on other sites

I just spotted that order_inventory on the live site has an index Keyname for productId (note no underscore and uppercase I)

The clean copy is product_id.

 

Didn't seem to help with the speed though.

 

1 SIMPLE I ALL PRIMARY NULL NULL NULL 4378 Using temporary; Using filesort
1 SIMPLE O ref cart_order_id,product_id product_id 4 xxx_ccv6.I.product_id 97  
1 SIMPLE S eq_ref cart_order_id,status cart_order_id 56 xxx_ccv6.O.cart_order_id 1 Using where
Link to comment
Share on other sites

Drop all indexes on the CubeCart_order_summary table then run this SQL query:

ALTER TABLE `CubeCart_order_summary`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `cart_order_id` (`cart_order_id`),
  ADD KEY `customer_id` (`customer_id`),
  ADD KEY `status` (`status`),
  ADD KEY `email` (`email`),
  ADD KEY `order_date` (`order_date`);

 

Drop all indexes on the CubeCart_inventory table then run this SQL query:

ALTER TABLE `CubeCart_inventory`
  ADD PRIMARY KEY (`product_id`),
  ADD KEY `status` (`status`),
  ADD KEY `popularity` (`popularity`);
ALTER TABLE `CubeCart_inventory` ADD FULLTEXT KEY `fulltext` (`product_code`,`description`,`name`);

 

Drop all indexes on the CubeCart_order_inventory table then run this SQL query:

ALTER TABLE `CubeCart_order_inventory`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `cart_order_id` (`cart_order_id`),
  ADD KEY `options_identifier` (`options_identifier`);

 

this will force rebuild indexes and correct any issues/missing etc. If that doesn't help, let me know. There are some other possibilities. :)

Edited by Noodleman
Link to comment
Share on other sites

Best do this on a copy first .. (says nervously)

I've taken a snap shot of the indexes on order_sum and order_inventory.

 

indexes.jpg

 

 

I'm unable to drop the existing promary key.

sql complains about one auto column, must be defined as a key.

Link to comment
Share on other sites

do you mean columns?

If so, it will be additional fields added by modules and custom code over time. it's not unusual. :)

Here is your SQL query formatted

 SELECT Sum(O.quantity) AS quan,
       O.product_id,
       I.name
FROM   `cubecart_order_inventory` AS O
       INNER JOIN `cubecart_order_summary` AS S
               ON S.cart_order_id = O.cart_order_id
       INNER JOIN `cubecart_inventory` AS I
               ON O.product_id = I.product_id
WHERE  ( S.`status` = 2
          OR S.`status` = 3 )
GROUP  BY I.product_id DESC
ORDER  BY `quan` DESC
LIMIT  15 offset 0  

We need to look closer at the join conditions and where clauses.

Check the structure of each column. let me know what each is setup/defined as. (For example, INT(11) or VARCHAR). It might be easier to just copy/paste a screen shot of your table "Structure" from PhpMyadmin for each table.

CubeCart_order_inventory.cart_order_id
CubeCart_order_inventory.product_id
CubeCart_order_summary.cart_order_id
CubeCart_order_summary.status
CubeCart_inventory.product_id

 

 

Edited by Noodleman
Link to comment
Share on other sites

invent.jpg

http://www.cabletiefan.co.uk/webimages/sumcartid.jpg

sumstatus.jpg

http://www.cabletifan.co.uk/webimages/orderinv.jpg

 

 

the forum doesn't seem to want to add all the images, so you may have to click and collect (so to speak)

 

 

http://www.cabletiefan.co.uk/webimages/orderinv.jpg

 

this having no edit feature is annoying.

http://www.cabletiefan.co.uk/webimages/orderinv.jpg

 

Link to comment
Share on other sites

Thanks

I spotted an issue, although this won't be the cause of your issue.

CubeCart_order_inventory.product_id  and CubeCart_inventory.product_id  have a different default value. This will prevent the use of indexes being used when doing joins on this field. explain may tell you it's using it, but it lies. :)

As a test, change CubeCart_order_inventory.product_id to have a default value of "None"

ALTER TABLE `CubeCart_order_inventory` CHANGE `product_id` `product_id` INT(10) UNSIGNED NOT NULL;

 

Also, there is an issue with CubeCart_order_inventory.cart_order_id which in your screen shot is showing a default value, but there shouldn't be one. Run this SQL code to align the field definitions:

ALTER TABLE `CubeCart_order_inventory` CHANGE `cart_order_id` `cart_order_id` VARCHAR(18) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL;

 

Then, run your query again with EXPLAIN before it and share the new plan. Also run the normal query.. how long does it take?

 

 

5 minutes ago, keat said:

Are we 100% vertain that this isn't just related to sheer volume of data. Order summary id is showing 59,683, so potentially 59,000 orders to trawl through.

59,000 is minimal. If the indexes and columns are defined correctly it should take miliseconds. I've got queries against tables will several million records and they perform just fine. :)

hmm, I did some further checking. I think I know what the issue actually is. would you be willing to export your order summary/inventory and inventory tables and send to me so I can import into my own DB server, sort it out and let you know the fix?

the personal data can be updated/masked.

Link to comment
Share on other sites

opening statistics still took around 30 seconds or more.

id select_type table type possible_keys key key_len ref rows Extra  
1 SIMPLE I ALL PRIMARY NULL NULL NULL 4378 Using temporary; Using filesort
1 SIMPLE O ref cart_order_id,product_id product_id  
   

xxx.I.product_id

 

97  
1 SIMPLE S eq_ref cart_order_id,status cart_order_id 56
   
xxx.cart_order_id
1 Using where

 

 

And running the single query still took 13 seconds.

Link to comment
Share on other sites

The updated plan is still showing that table I (CubeCart_inventory) is not making use of the primary key which is being called as part of the join "O.product_id = I.product_id"

So, the question becomes why?

Try running this query. how long does it take?

SELECT Sum(O.quantity) AS quan,
       O.product_id,
       I.name

FROM   `CubeCart_order_inventory` AS O
       INNER JOIN `CubeCart_order_summary` AS S
               ON S.cart_order_id = O.cart_order_id
       INNER JOIN `CubeCart_inventory` AS I
				FORCE INDEX FOR JOIN (PRIMARY) 
               ON O.product_id = I.product_id 	
WHERE  ( S.`status` = 2
          OR S.`status` = 3 )
GROUP  BY I.product_id DESC
ORDER  BY `quan` DESC
LIMIT  15 offset 0  

what does the plan of this SQL query look like?

Edited by Noodleman
Link to comment
Share on other sites

I've been running the query whilst in the actual database, and not from the root of PHPMyAdmin.

I had to modify the above query slightly to get it to run (remove the cc6_ prefix), nothing much changed, still took 13 seconds or more, and 'explain' didn't look any different.

Thinking that maybe I ought to be running in the root of PHPMyAdmin, i readded the dbsname_cc6_ prefix, but then got the following error.

 

3 errors were found during analysis.

 

  1. Unrecognized keyword. (near "FORCE" at position 298)
  2. Unrecognized keyword. (near "INDEX" at position 304)
  3. Unrecognized keyword. (near "FOR" at position 310)
Link to comment
Share on other sites

Here's the breakdown of the issue.

Indexes are being used and are improving performance to the level you are reporting. without them it would be much worse.

The SQL query we are focused on derives the quantity for each product, then sorts by that quantity. this is an expensive query. There isn't much that can be done to improve this SQL query.

@Al Brookbanks, this may be a candidate for an improvement. The queries probably shouldn't run on page load in this case, but perhaps on demand via a "refresh stats" button on the statistics page. This can then run the queries, gather the results from each and store the results in a database statistics table. the reports can then pull the derived values from the table rather than calculate it every time.  This isn't too much of an issue as it's only going to impact stores with a large data set. 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...