Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.1.73, 5.2.15, 5.3.13, 5.5(EOL), 10.0(EOL)
-
centos 6 x86_64
-
5.5.44
Description
I have a 5.5.29 and 5.5.42 with the same data. 5.5.42 was just created from the 5.5.29 instance.
The obs query plan changes the result time significantly.
5.5.29 uses the query:
explain SELECT obsloc.sScreenName AS ObsLocation, obsloc.sScreenName AS SiteName, obsloc.sState AS SiteState, obsloc.sDisplayState AS SiteDisplayState, obsloc.nLat AS SiteLat, obsloc.nLong AS SiteLong, obsloc.iElevation AS SiteElevation, DATE_FORMAT( obs.dtDate, '%Y-%m-%d' ) AS ObsDate, climate.nMeanMinTemp FROM locations obsloc LEFT JOIN obs_daily obs ON obs.sLocType = obsloc.sType AND obs.dtDate >= '2015-03-14' AND obs.dtDate <= '2015-03-14' LEFT JOIN climatology climate ON climate.iMonth=MONTH(obs.dtDate) ORDER BY obs.dtDate limit 300;
|
+------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+-----------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+-----------------------------------------------------------------------------------------+
|
| 1 | SIMPLE | obsloc | ALL | NULL | NULL | NULL | NULL | 121555 | Using temporary; Using filesort |
|
| 1 | SIMPLE | obs | range | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2 | 3 | NULL | 5989 | Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | climate | ref | idx_climatology_3 | idx_climatology_3 | 4 | func | 2117 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |
|
5.5.42 on the same database and query has the plan
+------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+----------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+----------------------------------------------------------------------------------+
|
| 1 | SIMPLE | obsloc | ALL | NULL | NULL | NULL | NULL | 102188 | Using temporary; Using filesort |
|
| 1 | SIMPLE | obs | range | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2 | 3 | NULL | 5989 | Range checked for each record (index map: 0x6); Rowid-ordered scan |
|
| 1 | SIMPLE | climate | ref | idx_climatology_3 | idx_climatology_3 | 4 | func | 5041 | Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan |
|
+------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+----------------------------------------------------------------------------------+
|
On a significantly larger query this is the difference between
5.5.29
SELECT ...
|
FROM locations loc
|
INNER JOIN location_time_zones tz
|
ON tz.sLocType = loc.sType
|
AND tz.sLocCode = loc.sCode
|
LEFT OUTER JOIN locations c
|
ON c.sType = 'CCODE'
|
AND c.sCode = loc.sCountryCode
|
INNER JOIN location_mappings_2008 xsite
|
ON xsite.custom_code = 'TWC'
|
AND xsite.type1 = loc.sType
|
AND xsite.code1 = loc.sCode
|
AND xsite.context = 'obsdaily'
|
LEFT OUTER JOIN locations obsloc
|
ON obsloc.sType = xsite.type2
|
AND obsloc.sCode = xsite.code2
|
LEFT OUTER JOIN obs_daily obs
|
ON obs.sLocType = obsloc.sType
|
AND obs.dtDate >= '2015-03-14'
|
AND obs.dtDate <= '2015-03-14'
|
LEFT OUTER JOIN climatology climate
|
ON climate.sLocType = xsite.type2
|
AND climate.sLocCode = xsite.code2
|
AND climate.iMonth = MONTH( obs.dtDate )
|
WHERE loc.sType = 'TWCID'
|
AND loc.sCode = '9764'
|
ORDER BY obs.dtDate;
|
|
|
running the query on 5.5.29
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
| 1 | SIMPLE | loc | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | Using temporary; Using filesort |
|
| 1 | SIMPLE | tz | const | idx_location_time_zones_1 | idx_location_time_zones_1 | 44 | const,const | 1 | Using index |
|
| 1 | SIMPLE | c | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | |
|
| 1 | SIMPLE | xsite | ref | idx_location_mappings_2008_1,idx_location_mappings_2008_2 | idx_location_mappings_2008_1 | 128 | const,const,const,const | 1 | Using where; Using index |
|
| 1 | SIMPLE | obsloc | eq_ref | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | weather.xsite.type2,weather.xsite.code2 | 1 | Using where |
|
| 1 | SIMPLE | obs | range | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2 | 3 | NULL | 5989 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | climate | eq_ref | idx_climatology_1,idx_climatology_2,idx_climatology_3 | idx_climatology_1 | 48 | weather.xsite.type2,weather.xsite.code2,func | 1 | Using where |
|
|
|
returns 2009 rows in set (0.10 sec)
|
|
|
where as 5.5.42:
|
|
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
| 1 | SIMPLE | loc | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | Using temporary; Using filesort |
|
| 1 | SIMPLE | tz | const | idx_location_time_zones_1 | idx_location_time_zones_1 | 44 | const,const | 1 | Using index |
|
| 1 | SIMPLE | c | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | |
|
| 1 | SIMPLE | xsite | ref | idx_location_mappings_2008_1,idx_location_mappings_2008_2 | idx_location_mappings_2008_1 | 128 | const,const,const,const | 1 | Using where; Using index |
|
| 1 | SIMPLE | obsloc | eq_ref | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | weather.xsite.type2,weather.xsite.code2 | 1 | Using where |
|
| 1 | SIMPLE | obs | range | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2 | 3 | NULL | 5989 | Range checked for each record (index map: 0x6) |
|
| 1 | SIMPLE | climate | eq_ref | idx_climatology_1,idx_climatology_2,idx_climatology_3 | idx_climatology_1 | 48 | weather.xsite.type2,weather.xsite.code2,func | 1 | Using where |
|
|
|
returned 2009 rows in set (11 min 10.44 sec)
|
changing the range part on obs.dtDate to obs.dtDate = '2015-03-14'
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
| 1 | SIMPLE | loc | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | Using temporary; Using filesort |
|
| 1 | SIMPLE | tz | const | idx_location_time_zones_1 | idx_location_time_zones_1 | 44 | const,const | 1 | Using index |
|
| 1 | SIMPLE | c | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | |
|
| 1 | SIMPLE | xsite | ref | idx_location_mappings_2008_1,idx_location_mappings_2008_2 | idx_location_mappings_2008_1 | 128 | const,const,const,const | 1 | Using where; Using index |
|
| 1 | SIMPLE | obsloc | eq_ref | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | weather.xsite.type2,weather.xsite.code2 | 1 | Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan |
|
| 1 | SIMPLE | obs | ref | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2 | 3 | const | 5989 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |
|
| 1 | SIMPLE | climate | eq_ref | idx_climatology_1,idx_climatology_2,idx_climatology_3 | idx_climatology_1 | 48 | weather.xsite.type2,weather.xsite.code2,func | 1 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |
|
|
|
resulting in 2009 rows in set (0.14 sec)
|
index stats which don't change much after running analyize tables a few times.
5.5.29
|
dba@localhost [weather] select * from information_schema.INNODB_INDEX_STATS where table_schema='weather' and table_name='obs_daily';
|
+--------------+------------+-----------------+--------+------------------+-------------------+------------------+
|
| table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages |
|
+--------------+------------+-----------------+--------+------------------+-------------------+------------------+
|
| weather | obs_daily | PRIMARY | 1 | 1 | 290176 | 253471 |
|
| weather | obs_daily | idx_obs_daily_1 | 3 | 3034775, 3513, 1 | 71232 | 62121 |
|
| weather | obs_daily | idx_obs_daily_2 | 2 | 8479, 1 | 29504 | 25696 |
|
5.5.42
|
MariaDB [weather]> select * from information_schema.INNODB_INDEX_STATS where table_schema='weather' and table_name='obs_daily';
|
+--------------+------------+-----------------+--------+-----------------+-------------------+------------------+
|
| table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages |
|
+--------------+------------+-----------------+--------+-----------------+-------------------+------------------+
|
| weather | obs_daily | PRIMARY | 1 | 1 | 315136 | 275181 |
|
| weather | obs_daily | idx_obs_daily_1 | 3 | 752141, 1374, 1 | 71232 | 62106 |
|
| weather | obs_daily | idx_obs_daily_2 | 2 | 4641, 0 | 33344 | 29082 |
|
| obs_daily | CREATE TABLE `obs_daily` (
|
`iId` int(11) NOT NULL AUTO_INCREMENT,
|
`sLocType` varchar(10) NOT NULL DEFAULT '',
|
`sLocCode` varchar(30) NOT NULL DEFAULT '',
|
`dtDate` date NOT NULL DEFAULT '0000-00-00',
|
...
|
PRIMARY KEY (`iId`),
|
UNIQUE KEY `idx_obs_daily_1` (`sLocType`,`sLocCode`,`dtDate`),
|
KEY `idx_obs_daily_2` (`dtDate`)
|
) ENGINE=InnoDB AUTO_INCREMENT=24862119 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC |
|
Attachments
Issue Links
Activity
The problem apparently came from this push into 5.1 tree:
revno: 3204
|
revision-id: igor@askmonty.org-20130813222111-vf6dg00gzb0gwipl
|
parent: holyfoot@askmonty.org-20130721195506-iwr7ydux2eksbroy
|
committer: Igor Babaev <igor@askmonty.org>
|
branch nick: maria-5.1
|
timestamp: Tue 2013-08-13 15:21:11 -0700
|
message:
|
Fixed bug mdev-4894.
|
This a an old legacy performance bug.
|
When a very selective range scan existed for the second table in a join,
|
and, at the same time, there was another range condition depending on the
|
fields of the first table, the optimizer chose a plan with
|
'Range checked for each record'. This plan was extremely inefficient in
|
comparison with the regular selective range scan.
|
As a matter of fact the range scan chosen for each record was the same as
|
that selective range scan.
|
|
Changed the test case for bug 24776 to preserve the old output for explain.
|
Here are results on 5.1:
|
5.1 revno 3203 |
+----+-------------+-------+-------+---------------+------+---------+------+--------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------+---------+------+--------+---------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 110040 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | range | idx1,idx2 | idx2 | 3 | NULL | 998 | Using where |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 785 | |
|
+----+-------------+-------+-------+---------------+------+---------+------+--------+---------------------------------+
|
|
|
<query execution>
|
300 rows in set (8 min 1.68 sec)
|
|
5.1 revno 3204 |
+----+-------------+-------+-------+---------------+------+---------+------+--------+------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------+---------+------+--------+------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 110563 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | range | idx1,idx2 | idx2 | 3 | NULL | 998 | Range checked for each record (index map: 0x6) |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 927 | |
|
+----+-------------+-------+-------+---------------+------+---------+------+--------+------------------------------------------------+
|
|
|
<query execution>
|
interrupted after 17 min of running
|
This push happened after the last 5.1/5.2/5.3 releases, so 5.1.67, 5.2.14 and 5.3.12 are unaffected.
In 5.5, it appeared in 5.5.33.
The r3204 commit corresponds to https://github.com/MariaDB/server/commit/a8880257e658393b6c20b1e0e96a87a0a4874c19
This falls in the same 5.5.32-5.5.33 range that mg identified in MDEV-6735.
elenst it is possible for you to move your test case to ftp://ftp.askmonty.org/public ?
Elena Stepanova it is possible for you to move your test case to ftp://ftp.askmonty.org/public
Done, ftp://ftp.askmonty.org/public/mdev7786.dump.gz
And yes, as I mentioned at the end of my previous comment, 5.5-release-wise the problem appeared in 5.5.33.
using mdev7786.dump.gz and patch on MDEV-6735
MariaDB [test]> explain SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 109143 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | range | idx1,idx2 | idx2 | 3 | NULL | 998 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 674 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
|
|
300 rows in set (1 min 50.42 sec)
|
with patch on 10.0 branch head
MariaDB [test]> explain SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 110563 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | range | idx1,idx2 | idx2 | 3 | NULL | 998 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 553 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
300 rows in set (1 min 15.81 sec)
|
so solved, pending a full mtr of course.
Since the problem was introduced by the fix for mdev-4894, I was wondering whether that fix has a problem. Looking at https://github.com/MariaDB/server/commit/a8880257e658393b6c20b1e0e96a87a0a4874c19 and dont see any problems.
MySQL 5.6.23 fails the testcase for MDEV-4894:
MySQL [j5]> explain select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
|
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 64 | NULL |
|
| 1 | SIMPLE | t1 | range | i1,i2 | i1 | 4 | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
|
MySQL [j5]> explain select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
|
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------+
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 64 | NULL |
|
| 1 | SIMPLE | t1 | range | i2 | i1 | 4 | NULL | 6 | Range checked for each record (index map: 0x2) |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------+
|
Just trying to understand why this works in mysql-5.6. (maybe, they have some bugfix that we are not aware of?)
mysql-5.6 doesn't pick "Records in range", because the if-condition is not satisfied:
if (!sel->quick_keys.is_subset(tab->checked_keys) ||
|
!sel->needed_reg.is_subset(tab->checked_keys))
|
{
|
The values are
- sel->quick_keys= {map = 0}
- sel->needed_reg =
{map = 2}
- tab->checked_keys = {map = 2} - tab->select->quick->index = 2
Note that sel->quick_keys=0 and at the same time there is a quick select on index=2. This is a product of a bug: we check SQL_SELECT::quick_keys, while the range optimizer updates TABLE::quick_keys:
(gdb) p tab->table->quick_keys
|
$129 = {map = 4}
|
So, it is pure chance that 5.6 works.
Test data dump is uploaded to ftp.askmonty.org/public/mdev7786.dump.gz .
It is completely artificial, nothing confidential about it, it's just too big to be attached to a JIRA issue.
After loading the dump, run
On my machine, results are as follows:
5.5.29
...5.5.42
...So, I haven't got an instant execution on 5.5.29 (I wonder if query cache had something to do with it in the initial report); nevertheless, the difference between 5.5.29 and 5.5.42 is glaring obvious.
10.0.17 also has the problem. The plan looks slightly different there, but the query is still slow:
10.0.17
Command line options to start the server (most options must be unnecessary, this set contains all non-default options from the attached vars, apart from InnoDB ones):
--join_cache_level=8 --key_buffer_size=262144 --max_heap_table_size=67108864 --max_long_data_size=67108864 --mrr_buffer_size=1048576 --myisam_sort_buffer_size=536870912 --optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off --query_cache_limit=32768 --query_cache_size=67108864 --query_cache_strip_comments=ON --read_buffer_size=8388608 --read_rnd_buffer_size=1048576 --sort_buffer_size=524288 --tmp_table_size=67108864 --character-set-server=utf8