Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7786

regression in query plan - join buffer (flat, BNL join) -> Range checked for each record (index map: 0x6); Rowid-ordered scan

        Details

        • Sprint:
          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 |

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-4894 Unnecessary "Range checked for each record" leads to a poor performance

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-7923 forcing index changes query plan (even if default chose that index)

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-6735 Range checked for each record used with key

                • Major - Major loss of function.
                • Closed

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment - - edited

                  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

                  analyze table t1, t2, t3;
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;
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;

                  On my machine, results are as follows:

                  5.5.29
                  +------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                                                                                   |
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------------------------------------+
|    1 | SIMPLE      | t1    | ALL   | NULL          | NULL | NULL    | NULL | 109591 | Using temporary; Using filesort                                                         |
|    1 | SIMPLE      | t2    | range | idx1,idx2     | idx2 | 3       | NULL |    998 | Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)                     |
|    1 | SIMPLE      | t3    | ref   | idx3          | idx3 | 4       | func |    669 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [test]>  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;
...
300 rows in set (54.61 sec)
                  5.5.42
                  +------+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------------------------------------------------------+
| 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); Rowid-ordered scan               |
|    1 | SIMPLE      | t3    | ref   | idx3          | idx3 | 4       | func |    936 | Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan |
+------+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [test]>  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;
...
interrupted after 15 min of execution

                  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
                  +------+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                                                                            |
+------+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------------------------------------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |  109733 | Using temporary; Using filesort                                                  |
|    1 | SIMPLE      | t2    | ALL  | idx1,idx2     | idx2 | 3       | NULL | 3305947 | Range checked for each record (index map: 0x6)                                   |
|    1 | SIMPLE      | t3    | ref  | idx3          | idx3 | 4       | func |     719 | Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan |
+------+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------------------------------------------+

                  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
                  Show
                  elenst Elena Stepanova added a comment - - edited 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 analyze table t1, t2, t3; 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; 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; On my machine, results are as follows: 5.5.29 +------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 109591 | Using temporary; Using filesort | | 1 | SIMPLE | t2 | range | idx1,idx2 | idx2 | 3 | NULL | 998 | Using where ; Rowid-ordered scan; Using join buffer (flat, BNL join) | | 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 669 | Using where ; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan | +------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) MariaDB [test]> 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; ... 300 rows in set (54.61 sec) 5.5.42 +------+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------------------------------------------------------+ | 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); Rowid-ordered scan | | 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 936 | Using where ; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan | +------+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) MariaDB [test]> 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; ... interrupted after 15 min of execution 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 +------+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 109733 | Using temporary; Using filesort | | 1 | SIMPLE | t2 | ALL | idx1,idx2 | idx2 | 3 | NULL | 3305947 | Range checked for each record (index map: 0x6) | | 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 719 | Using where ; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan | +------+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------------------------------------------+ 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
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  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.

                  Show
                  elenst Elena Stepanova added a comment - 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.
                  Hide
                  Permalink
                  danblack Daniel Black added a comment - - edited

                  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.

                  Elena Stepanova it is possible for you to move your test case to ftp://ftp.askmonty.org/public ?

                  Show
                  danblack Daniel Black added a comment - - edited 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 . Elena Stepanova it is possible for you to move your test case to ftp://ftp.askmonty.org/public ?
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  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.

                  Show
                  elenst Elena Stepanova added a comment - 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.
                  Hide
                  Permalink
                  danblack Daniel Black added a comment -

                  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.

                  Show
                  danblack Daniel Black added a comment - 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.
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  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) |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------+
                  Show
                  psergey Sergei Petrunia added a comment - 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) | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------+
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  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.

                  Show
                  psergey Sergei Petrunia added a comment - 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.
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  The fix was pushed into 5.5 tree.

                  Show
                  psergey Sergei Petrunia added a comment - The fix was pushed into 5.5 tree.

                    People

                    • Assignee:
                      psergey Sergei Petrunia
                      Reporter:
                      danblack Daniel Black
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      6 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved:

                        Agile