Hello,
I am working on site which has 13528 records in uc_order_products (from the ubercart 6 module).
Nid count is 5611.
To get the most purchased products, I have a query like below:
SELECT DISTINCT(node.nid) AS nid, uc_order_products.qty AS uc_order_products_qty FROM node LEFT JOIN uc_order_products ON node.nid = uc_order_products.nid WHERE (node.type in ('product')) AND (node.status <> 0) GROUP BY nid ORDER BY uc_order_products_qty DESC;However this causes our linode server with 1024 MB ram to crash so quickly.
By crash, I mean,
1. the CPU load taken by mysqld goes to 99% (as seen by top command in shell)
2. Linode alerts me "Your Linode has exceeded the notification threshold (1000) for disk io rate by averaging 2406.76 for the last 2 hours. ".
3. the website becomes totally inaccessible and pingdom reports the server is down.
While the above is being executed, I can see in SHOW PROCESSLIST the following message
mysql> show processlist;
+-------+------+-----------+------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 35863 | root | localhost | db | Query | 5 | Copying to tmp table | SELECT DISTINCT(node.nid) AS nid, uc_order_products.qty AS uc_order_products_qty FROM node LEFT JOI |
| 35873 | root | localhost | db | Query | 0 | NULL | show processlist |
+-------+------+-----------+------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)Copying to tmp table is the state in which the mysqld cpu load and disk i/o goes too high. I was wondering how mysql causes increased io rate, but clarified by this post: http://danosipov.com/blog/?p=75
EXPLAIN for the above SQL is below:
mysql> explain SELECT DISTINCT(node.nid) AS nid, uc_order_products.qty AS uc_order_products_qty
-> FROM node
-> LEFT JOIN uc_order_products ON node.nid = uc_order_products.nid WHERE (node.type in ('product')) AND (node.status <> 0) ORDER BY uc_order_products_qty DESC;
+----+-------------+-------------------+-------+----------------------------+------------------+---------+------+-------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+-------+----------------------------+------------------+---------+------+-------+-----------------------------------------------------------+
| 1 | SIMPLE | node | index | node_status_type,node_type | node_status_type | 106 | NULL | 5611 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | uc_order_products | ALL | NULL | NULL | NULL | NULL | 13528 | |
+----+-------------+-------------------+-------+----------------------------+------------------+---------+------+-------+-----------------------------------------------------------+
2 rows in set (0.03 sec)So here is what I am looking for:
1. How do I optimize the above query so that it does not crash the site as mentioned above. Is 13000 records in uc_order_products so high?
or
is there a better way to find the most downloaded product from ubercart tables without this problem?
Kindly advice,
thanks for reading
neokrish
Comments
It looks like you are doing a
It looks like you are doing a few things in there that aren't necessary. I would write the query like:
SELECT node.nid AS nid,uc_order_products.qty AS uc_order_products_qty
FROM node
LEFT JOIN uc_order_products
ON node.nid = uc_order_products.nid
WHERE node.type = 'product'
AND node.status != 0
GROUP BY nid
ORDER BY uc_order_products_qty DESC;
The IN statement you are using is unnecessary when you are only interested in a single node type, similarly the DISTINCT is unnecessary since you are grouping by nid.
I believe this will give you the same results more quickly, but suggest you test to confirm.
Thanks Beanjammin for your
Thanks Beanjammin for your reply. You mentioned good points, indeed. Actually the query is run from the Views module which misses these optimizations.
I have tested the query that you have rewritten but still it's the same load. thanks for your help!
Looks like a views query
That query looks like one created with a view, so there may not be much optimization possible with rewriting the query.
What I would focus on:
I notice in your explain output that there's no keys on uc_order_products (as noted by the NULL values in the columns). I would try to add an index on uc_order_products.nid and see how that query improves. When you retry, again paste the output of your EXPLAIN query again.
The "Using filesort" and "using temporary" in your explain for the first table, if I'm not mistaken, suggests that you're ordering results differently than the order of the index. This means MySQL has to copy the rows to a temp table to resort them. It's been a while since I was involved in the mechanics, but someone here may be able to chime in with specifics if they think this area is a red flag.
Hope that helps.
thanks blazindrop. Your
thanks blazindrop. Your solution works like a charm!!
Before adding the index on nid, the query resulted in:
5500 rows in set (4 min 22.63 sec)After adding the index on nid, the query resulted in
5500 rows in set (0.10 sec)Amazing speed. There is no load in mysql (mysqld appeared nowhere at the top in the
topcommand).I haven't tried your second optimization yet. do you have pointers for me (a link or book etc) to begin with? I will google for resources for the topic you mentioned and get back to you when I have done your second step.
thanks again blazindrop for your help. I am grateful.
neokrish
Great improvement - take it full circle
This seems like a great improvement. To take it "full circle" you should create an issue in the ubercart queue so that this becomes part of the ubercart package on everyone's sites.
knaddison blog | Morris Animal Foundation