MySQL Performance Problem
MySQL doesn't seem to work very well when a joined table has significant breadth in more than one dimension. The following example illustrates the catastrophic performance degradation. To create this example, run the
Perl code. To get a large table that presents the problem effectively, use the following invocation, which will take many hours to complete.
./process.pl -panelists=300 -samples=40000000
In this example, the query
/**/ select SQL_NO_CACHE * from device join sample using (device_id) where panelist_id = 57 and occurred between '2005-07-01 00:00:00' and '2005-07-01 04:00:00';
performs well when the time window is small. We change the right end of the date range to one of the values from the first column, and the query takes an amount of time to execute as indicated in the second column. Notice that the performance degrades catastrophically at around 4 hours.
Thus, we get to the problem: Why should a query over 4 hours of data take 40 or 400 times longer than a query over 1 hour of data?
On pd2 MySQL seems to cache results even when it is told not too. SQL_NO_CACHE and /**/ at the beginning are both ways to tell MySQL not to cache. Nevertheless, we get terribly inconsistent behavior on pd2. [Note: I just found out about RESET QUERY CACHE, which seems to do what we need.]
Table 1. Performance on s0004
end datetime
in SQL query
| query performance (sec)
|
2005-07-01 00:30:00
| 0.13
|
2005-07-01 01:00:00
| 0.22
|
2005-07-01 02:00:00
| 0.66
|
2005-07-01 03:00:00
| 1.00
|
| 2005-07-01 03:30:00 | 1.16 |
| 2005-07-01 03:45:00 | 1.26 |
| 2005-07-01 03:46:00 | 1.26 |
| 2005-07-01 03:47:00 | 1.27 |
| 2005-07-01 03:55:00 | 100.00 |
| 2005-07-01 04:00:00 | 100.00 |
Table 2. Performance on pd2
end datetime
in SQL query
| query performance (sec)
|
2005-07-01 00:30:00
| 0.17
|
2005-07-01 01:00:00
| 0.48
|
2005-07-01 02:00:00
| 0.81
|
2005-07-01 03:00:00
| 23.26
|
| 2005-07-01 03:30:00 | 19.10
|
| 2005-07-01 03:45:00 | 26.43
|
| 2005-07-01 03:46:00 |
|
| 2005-07-01 03:47:00 |
|
| 2005-07-01 03:55:00 |
|
| 2005-07-01 04:00:00 | 19.68
|
mysql> show index from device;
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| device | 0 | PRIMARY | 1 | device_id | A | 3000 | NULL | NULL | | BTREE | |
| device | 1 | panelist_id | 1 | panelist_id | A | 300 | NULL | NULL | | BTREE | |
| device | 1 | panelist_id_device_id | 1 | panelist_id | A | 300 | NULL | NULL | | BTREE | |
| device | 1 | panelist_id_device_id | 2 | device_id | A | 3000 | NULL | NULL | | BTREE | |
| device | 1 | device_id_panelist_id | 1 | device_id | A | 3000 | NULL | NULL | | BTREE | |
| device | 1 | device_id_panelist_id | 2 | panelist_id | A | 3000 | NULL | NULL | | BTREE | |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)
mysql> show index from sample;
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sample | 0 | PRIMARY | 1 | sample_id | A | 40000000 | NULL | NULL | | BTREE | |
| sample | 1 | device_id | 1 | device_id | A | 3000 | NULL | NULL | | BTREE | |
| sample | 1 | occurred | 1 | occurred | A | 4000000 | NULL | NULL | | BTREE | |
| sample | 1 | device_id_occurred | 1 | device_id | A | 3000 | NULL | NULL | | BTREE | |
| sample | 1 | device_id_occurred | 2 | occurred | A | 40000000 | NULL | NULL | | BTREE | |
| sample | 1 | occurred_device_id | 1 | occurred | A | 4000000 | NULL | NULL | | BTREE | |
| sample | 1 | occurred_device_id | 2 | device_id | A | 40000000 | NULL | NULL | | BTREE | |
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)
The following query takes .41s, returning 154 rows, when run without "explain".
mysql> explain select * from device join sample using (device_id) where panelist_id = 37 and occurred between '2005-07-01 00:00:00' and '2005-07-01 01:00:00';
+----+-------------+--------+-------+----------------------------------------------------------------------+-----------------------+---------+------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------------------------------------------------------------+-----------------------+---------+------------------------------+-------+-------------+
| 1 | SIMPLE | sample | range | device_id,occurred,device_id_occurred,occurred_device_id,device_id_t | occurred_device_id | 8 | NULL | 30485 | Using where |
| 1 | SIMPLE | device | ref | PRIMARY,panelist_id,panelist_id_device_id,device_id_panelist_id | device_id_panelist_id | 8 | bench.sample.device_id,const | 1 | Using where |
+----+-------------+--------+-------+----------------------------------------------------------------------+-----------------------+---------+------------------------------+-------+-------------+
2 rows in set (0.00 sec)
The following query takes 34.57s, returning 1482 rows, when run without "explain". Note that the order of the join is reversed. That's a query over 12x as much "occurred" range, returning roughly 10x as many rows, and taking roughly 84x as long.
mysql> explain select * from device join sample using (device_id) where panelist_id = 37 and occurred between '2005-07-01 00:00:00' and '2005-07-01 12:00:00';
+----+-------------+--------+------+----------------------------------------------------------------------+-------------+---------+------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------------------------------------------------------------+-------------+---------+------------------------+-------+-------------+
| 1 | SIMPLE | device | ref | PRIMARY,panelist_id,panelist_id_device_id,device_id_panelist_id | panelist_id | 4 | const | 10 | Using where |
| 1 | SIMPLE | sample | ref | device_id,occurred,device_id_occurred,occurred_device_id,device_id_t | device_id_t | 4 | bench.device.device_id | 13333 | Using where |
+----+-------------+--------+------+----------------------------------------------------------------------+-------------+---------+------------------------+-------+-------------+
2 rows in set (0.03 sec)
The following query takes 2.86s, which is more in line with what it should be.
mysql> explain select straight_join * from sample join device using (device_id) where panelist_id = 37 and occurred between '2005-07-01 00:00:00' and '2005-07-01 12:00:00';
+----+-------------+--------+-------+----------------------------------------------------------------------+-----------------------+---------+------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------------------------------------------------------------+-----------------------+---------+------------------------------+--------+-------------+
| 1 | SIMPLE | sample | range | device_id,occurred,device_id_occurred,occurred_device_id,device_id_t | occurred_device_id | 8 | NULL | 365724 | Using where |
| 1 | SIMPLE | device | ref | PRIMARY,panelist_id,panelist_id_device_id,device_id_panelist_id | device_id_panelist_id | 8 | bench.sample.device_id,const | 1 | Using where |
+----+-------------+--------+-------+----------------------------------------------------------------------+-----------------------+---------+------------------------------+--------+-------------+
2 rows in set (0.00 sec)
I don't think the problem is that the optimizer does the join in the wrong order. I think that it should be able to resolve the query quickly using either join order, but that it picks the wrong index in the case where it joins the device table first. Notice that it chose device_id_t with key_len=4, when the right choice would have been device_id_occured with key_len=12.
In the following query, I have replaced "occurred" (a datetime) with "t" (unix time). Why isn't key_len=8 in the second row? Why won't it use the compound index the way it should?
mysql> explain select straight_join * from device join sample using (device_id) where panelist_id = 38 and t between unix_timestamp('2005-07-01 00:00:00') and unix_timestamp('2005-07-01 12:00:00');
+----+-------------+--------+------+-----------------------------------------------------------------+-------------+---------+------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------------------------------------------------------+-------------+---------+------------------------+-------+-------------+
| 1 | SIMPLE | device | ref | PRIMARY,panelist_id,panelist_id_device_id,device_id_panelist_id | panelist_id | 4 | const | 10 | Using where |
| 1 | SIMPLE | sample | ref | device_id,device_id_occurred,device_id_t | device_id_t | 4 | bench.device.device_id | 13333 | Using where |
+----+-------------+--------+------+-----------------------------------------------------------------+-------------+---------+------------------------+-------+-------------+
2 rows in set (0.00 sec)