Last week I played with queries from TPC-H benchmarks, particularly comparing MySQL 6.0.4-alpha with 5.1. MySQL 6.0 is interesting here, as there is a lot of new changes in optimizer, which should affect execution plan of TPC-H queries. In reality only two queries (from 22) have significantly better execution time (about them in next posts), but I want to write about is queries that execute slower in new MySQL 6.0 version.
Query is pretty simple
|
1 2 3 4 5 |
SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem WHERE l_shipdate >= date '1995-01-01' AND l_shipdate < date '1995-01-01' + interval '1' year AND l_discount BETWEEN 0.09 - 0.01 AND 0.09 + 0.01 AND l_quantity < 24; |
with execution plain (in 5.1)
|
1 2 3 4 5 |
+----+-------------+----------+-------+---------------+---------------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------------+---------+------+---------+-------------+ | 1 | SIMPLE | lineitem | range | li_shp_dt_idx | li_shp_dt_idx | 3 | NULL | 8272067 | Using where | +----+-------------+----------+-------+---------------+---------------+---------+------+---------+-------------+ |
against table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE TABLE `lineitem` ( `l_shipdate` date NOT NULL, `l_orderkey` int(11) NOT NULL, `l_partkey` int(11) NOT NULL, `l_suppkey` int(11) NOT NULL, `l_linenumber` int(11) NOT NULL, `l_quantity` decimal(15,2) NOT NULL, `l_extendedprice` decimal(15,2) NOT NULL, `l_discount` decimal(15,2) NOT NULL, `l_tax` decimal(15,2) NOT NULL, `l_returnflag` char(1) NOT NULL, `l_linestatus` char(1) NOT NULL, `l_commitdate` date NOT NULL, `l_receiptdate` date NOT NULL, `l_shipinstruct` char(25) NOT NULL, `l_comment` varchar(44) NOT NULL, `l_shipmode` char(10) NOT NULL, PRIMARY KEY (`l_orderkey`,`l_linenumber`), KEY `lineitem_fk2` (`l_suppkey`), KEY `lineitem_fk3` (`l_partkey`,`l_suppkey`), KEY `li_shp_dt_idx` (`l_shipdate`), KEY `li_com_dt_idx` (`l_commitdate`), KEY `li_rcpt_dt_idx` (`l_receiptdate`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 with 59986052 rows |
So this query takes 32.27 sec to execute in 5.1, and 39.39 sec in 6.0 (after warpm-ups and fully cached in key_cache)
What is difference in 6.0? Here is execution plain for 6.0:
|
1 2 3 4 5 |
+----+-------------+----------+-------+---------------+---------------+---------+------+---------+-----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------------+---------+------+---------+-----------------------------------------------+ | 1 | SIMPLE | lineitem | range | li_shp_dt_idx | li_shp_dt_idx | 3 | NULL | 8272067 | Using index condition; Using where; Using MRR | +----+-------------+----------+-------+---------------+---------------+---------+------+---------+-----------------------------------------------+ |
See there are new Extra notes
USING INDEX condition; USING MRR.
It would be interesting to know what is ‘Using Index condition’, what I was able to find is Baron’s bug with no mention in MySQL 6.0 manual. No mentions about Using MRR also, but I guess it is Multi-Range-Read feature which allows to read data not row-by-row but in batches reads.
So let us see how perform 5.1 and 6.0 in cold mode (I dropped OS caches by
|
1 |
echo 1 > /proc/sys/vm/drop_caches |
and restarting mysqld)
5.1 – time 1 hour 14 min 36.90 sec
6.0 – time 1 hour 6 min 2.21 sec
So difference is not so big we could speak about big improvements with using MRR here.
What is interesting is iostat -dx 5 output during query execution.
For 5.1 I had:
|
1 2 3 4 5 6 7 8 9 10 11 |
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 2.06 310.70 0.82 2493.83 23.05 1246.91 11.52 8.08 1.02 3.28 3.28 102.04 dm-0 0.00 0.00 310.70 0.62 2493.83 4.94 1246.91 2.47 8.03 1.02 3.29 3.28 102.18 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.21 301.23 0.41 2441.07 4.93 1220.53 2.46 8.11 1.02 3.39 3.39 102.14 dm-0 0.00 0.00 301.23 0.62 2441.07 4.93 1220.53 2.46 8.10 1.02 3.39 3.39 102.18 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.41 317.83 0.82 2570.49 9.84 1285.25 4.92 8.10 1.02 3.20 3.20 102.01 dm-0 0.00 0.00 317.83 |