List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:June 7 2012 9:58am
Subject:bzr push into mysql-trunk branch (olav.sandstaa:3918 to 3919) WL#6082
View as plain text  
 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#6082Olav Sandstaa7 Jun