3919 Olav Sandstaa 2012-06-07
WL#6082 Improve the Disk-Sweep Multi-Range Read cost model
Fix to make the innodb_mysql test stable when run with 4K InnoDB
block size.
The following explain in the innodb_mysql test:
EXPLAIN SELECT * FROM t1 WHERE f1 IN
(3305028,3353871,3772880,3346860,4228206,3336022,
3470988,3305175,3329875,3817277,3856380,3796193,
3784744,4180925,4559596,3963734,3856391,4494153)
AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
started to fail due to changes in the query plan when running
the test with 4K InnoDB block size. The change was from using index
merge on (idx2,idx1,PRIMARY) to use index merge on (idx2,PRIMARY).
This cause for this change is that the new DS-MRR cost model
produces lower cost estimates for reading the table as a "disk sweep".
The reason for producing different plans when running with 16K versus
4K InnoDB block size is that for the 4K InnoDB blocksize case the size
of the table is smaller (32KB vs 48KB) and this influences on the
cost estimate for reading the data.
In order to get the same query plan when running with 4K and 16K
block size, the fix for this problem is to:
-add some extra records to the table
-enable persistent statistics on the table to get more exact statistical data
Approved by Vasil on IM.
@ mysql-test/std_data/intersect-bug50389.tsv
WL#6082: Add more data to the table used by one of the tests
in innodb_mysql.
@ mysql-test/suite/innodb/r/innodb_mysql.result
WL#6082: Enable persistent statistics for one table to get
more exact statistical data. This is done to avoid variations
in cost data that caused plan changes for index merge.
@ mysql-test/suite/innodb/t/innodb_mysql.test
WL#6082: Enable persistent statistics for one table to get
more exact statistical data. This is done to avoid variations
in cost data that caused plan changes for index merge when
running with 4K InnoDB buffer blocks.
modified:
mysql-test/std_data/intersect-bug50389.tsv
mysql-test/suite/innodb/r/innodb_mysql.result
mysql-test/suite/innodb/t/innodb_mysql.test
3918 Olav Sandstaa 2012-06-06 [merge]
Merge from mysql-trunk to mysql-trunk-wl6082
modified:
.bzrignore
mysql-test/suite/engines/iuds/r/update_year.result
mysql-test/suite/engines/iuds/t/update_year.test
sql/handler.cc
sql/sql_insert.cc
sql/sql_tmp_table.cc
storage/innobase/row/row0import.cc
storage/innobase/row/row0mysql.cc
=== modified file 'mysql-test/std_data/intersect-bug50389.tsv'
=== modified file 'mysql-test/std_data/intersect-bug50389.tsv'
--- a/mysql-test/std_data/intersect-bug50389.tsv 2010-06-21 11:09:58 +0000
+++ b/mysql-test/std_data/intersect-bug50389.tsv 2012-06-07 09:57:30 +0000
@@ -439,3 +439,107 @@
4535721 1221287 0 0 asdfghjklzxcvbnm
4559596 1221457 0 0 abcdefghijklmnopwrst
4617751 1221393 0 0 abcdefghijklmnopwrst
+4535722 1221287 0 0 asdfghjklzxcvbnm
+4535723 1221287 0 0 asdfghjklzxcvbnm
+4535724 1221287 0 0 asdfghjklzxcvbnm
+4535725 1221287 0 0 asdfghjklzxcvbnm
+4535726 1221287 0 0 asdfghjklzxcvbnm
+4535727 1221287 0 0 asdfghjklzxcvbnm
+4535728 1221287 0 0 asdfghjklzxcvbnm
+4535729 1221287 0 0 asdfghjklzxcvbnm
+4535732 1221287 0 0 asdfghjklzxcvbnm
+4535733 1221287 0 0 asdfghjklzxcvbnm
+4535734 1221287 0 0 asdfghjklzxcvbnm
+4535735 1221287 0 0 asdfghjklzxcvbnm
+4535736 1221287 0 0 asdfghjklzxcvbnm
+4535737 1221287 0 0 asdfghjklzxcvbnm
+4535738 1221287 0 0 asdfghjklzxcvbnm
+4535739 1221287 0 0 asdfghjklzxcvbnm
+4535742 1221287 0 0 asdfghjklzxcvbnm
+4535743 1221287 0 0 asdfghjklzxcvbnm
+4535744 1221287 0 0 asdfghjklzxcvbnm
+4535745 1221287 0 0 asdfghjklzxcvbnm
+4535746 1221287 0 0 asdfghjklzxcvbnm
+4535747 1221287 0 0 asdfghjklzxcvbnm
+4535748 1221287 0 0 asdfghjklzxcvbnm
+4535749 1221287 0 0 asdfghjklzxcvbnm
+4535752 1221287 0 0 asdfghjklzxcvbnm
+4535753 1221287 0 0 asdfghjklzxcvbnm
+4535754 1221287 0 0 asdfghjklzxcvbnm
+4535755 1221287 0 0 asdfghjklzxcvbnm
+4535756 1221287 0 0 asdfghjklzxcvbnm
+4535757 1221287 0 0 asdfghjklzxcvbnm
+4535758 1221287 0 0 asdfghjklzxcvbnm
+4535759 1221287 0 0 asdfghjklzxcvbnm
+4535762 1221287 0 0 asdfghjklzxcvbnm
+4535763 1221287 0 0 asdfghjklzxcvbnm
+4535764 1221287 0 0 asdfghjklzxcvbnm
+4535765 1221287 0 0 asdfghjklzxcvbnm
+4535766 1221287 0 0 asdfghjklzxcvbnm
+4535767 1221287 0 0 asdfghjklzxcvbnm
+4535768 1221287 0 0 asdfghjklzxcvbnm
+4535769 1221287 0 0 asdfghjklzxcvbnm
+4535772 1221287 0 0 asdfghjklzxcvbnm
+4535773 1221287 0 0 asdfghjklzxcvbnm
+4535774 1221287 0 0 asdfghjklzxcvbnm
+4535775 1221287 0 0 asdfghjklzxcvbnm
+4535776 1221287 0 0 asdfghjklzxcvbnm
+4535777 1221287 0 0 asdfghjklzxcvbnm
+4535778 1221287 0 0 asdfghjklzxcvbnm
+4535779 1221287 0 0 asdfghjklzxcvbnm
+4535782 1221287 0 0 asdfghjklzxcvbnm
+4535783 1221287 0 0 asdfghjklzxcvbnm
+4535784 1221287 0 0 asdfghjklzxcvbnm
+4535785 1221287 0 0 asdfghjklzxcvbnm
+4535786 1221287 0 0 asdfghjklzxcvbnm
+4535787 1221287 0 0 asdfghjklzxcvbnm
+4535788 1221287 0 0 asdfghjklzxcvbnm
+4535789 1221287 0 0 asdfghjklzxcvbnm
+4535792 1221287 0 0 asdfghjklzxcvbnm
+4535793 1221287 0 0 asdfghjklzxcvbnm
+4535794 1221287 0 0 asdfghjklzxcvbnm
+4535795 1221287 0 0 asdfghjklzxcvbnm
+4535796 1221287 0 0 asdfghjklzxcvbnm
+4535797 1221287 0 0 asdfghjklzxcvbnm
+4535798 1221287 0 0 asdfghjklzxcvbnm
+4535799 1221287 0 0 asdfghjklzxcvbnm
+4535892 1221287 0 0 asdfghjklzxcvbnm
+4535893 1221287 0 0 asdfghjklzxcvbnm
+4535894 1221287 0 0 asdfghjklzxcvbnm
+4535895 1221287 0 0 asdfghjklzxcvbnm
+4535896 1221287 0 0 asdfghjklzxcvbnm
+4535897 1221287 0 0 asdfghjklzxcvbnm
+4535898 1221287 0 0 asdfghjklzxcvbnm
+4535899 1221287 0 0 asdfghjklzxcvbnm
+4535992 1221287 0 0 asdfghjklzxcvbnm
+4535993 1221287 0 0 asdfghjklzxcvbnm
+4535994 1221287 0 0 asdfghjklzxcvbnm
+4535995 1221287 0 0 asdfghjklzxcvbnm
+4535996 1221287 0 0 asdfghjklzxcvbnm
+4535997 1221287 0 0 asdfghjklzxcvbnm
+4535998 1221287 0 0 asdfghjklzxcvbnm
+4535999 1221287 0 0 asdfghjklzxcvbnm
+4536792 1221287 0 0 asdfghjklzxcvbnm
+4536793 1221287 0 0 asdfghjklzxcvbnm
+4536794 1221287 0 0 asdfghjklzxcvbnm
+4536795 1221287 0 0 asdfghjklzxcvbnm
+4536796 1221287 0 0 asdfghjklzxcvbnm
+4536797 1221287 0 0 asdfghjklzxcvbnm
+4536798 1221287 0 0 asdfghjklzxcvbnm
+4536799 1221287 0 0 asdfghjklzxcvbnm
+4537792 1221287 0 0 asdfghjklzxcvbnm
+4537793 1221287 0 0 asdfghjklzxcvbnm
+4537794 1221287 0 0 asdfghjklzxcvbnm
+4537795 1221287 0 0 asdfghjklzxcvbnm
+4537796 1221287 0 0 asdfghjklzxcvbnm
+4537797 1221287 0 0 asdfghjklzxcvbnm
+4537798 1221287 0 0 asdfghjklzxcvbnm
+4537799 1221287 0 0 asdfghjklzxcvbnm
+4538792 1221287 0 0 asdfghjklzxcvbnm
+4538793 1221287 0 0 asdfghjklzxcvbnm
+4538794 1221287 0 0 asdfghjklzxcvbnm
+4538795 1221287 0 0 asdfghjklzxcvbnm
+4538796 1221287 0 0 asdfghjklzxcvbnm
+4538797 1221287 0 0 asdfghjklzxcvbnm
+4538798 1221287 0 0 asdfghjklzxcvbnm
+4538799 1221287 0 0 asdfghjklzxcvbnm
=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result 2012-05-31 06:46:35 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2012-06-07 09:57:30 +0000
@@ -2544,8 +2544,9 @@
PRIMARY KEY (f1),
KEY idx1 (f2,f5,f4),
KEY idx2 (f2,f4)
-) ENGINE=InnoDB STATS_PERSISTENT=0;
+) ENGINE=InnoDB;
LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1;
+ANALYZE TABLE t1;
SELECT * FROM t1 WHERE f1 IN
(3305028,3353871,3772880,3346860,4228206,3336022,
3470988,3305175,3329875,3817277,3856380,3796193,
=== modified file 'mysql-test/suite/innodb/t/innodb_mysql.test'
--- a/mysql-test/suite/innodb/t/innodb_mysql.test 2012-05-31 06:46:35 +0000
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test 2012-06-07 09:57:30 +0000
@@ -707,10 +707,14 @@
PRIMARY KEY (f1),
KEY idx1 (f2,f5,f4),
KEY idx2 (f2,f4)
-) ENGINE=InnoDB STATS_PERSISTENT=0;
+) ENGINE=InnoDB;
LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1;
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+
SELECT * FROM t1 WHERE f1 IN
(3305028,3353871,3772880,3346860,4228206,3336022,
3470988,3305175,3329875,3817277,3856380,3796193,
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (olav.sandstaa:3918 to 3919) WL#6082 | Olav Sandstaa | 7 Jun |
