[Commits] Rev 3395: Merge 5.3->main -> 5.3-optimizer_debugging in file:///home/psergey/dev2/5.3-optimizer-debug-r4/

Sergey Petrunya psergey at askmonty.org
Wed Jan 25 18:48:33 EET 2012


At file:///home/psergey/dev2/5.3-optimizer-debug-r4/

------------------------------------------------------------
revno: 3395 [merge]
revision-id: psergey at askmonty.org-20120125164829-lqviyv2ze3c0atkl
parent: psergey at askmonty.org-20120124161330-ele7oc6hipwhnhpd
parent: psergey at askmonty.org-20120125143657-eavawi511n3mxf7d
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-optimizer-debug-r4
timestamp: Wed 2012-01-25 20:48:29 +0400
message:
  Merge 5.3->main -> 5.3-optimizer_debugging
modified:
  mysql-test/r/myisam_mrr.result myisam_mrr.result-20091215071345-6wadxunod6vi8m48-1
  mysql-test/r/status.result     sp1f-status.result-20010127030042-xmcayiu3ythpazhcl57hxwab2xefawxh
  mysql-test/r/status_user.result status_user.result-20091019051724-x1vmdm6cvmjlps1n-1
  mysql-test/r/subselect_sj.result subselect_sj.result-20100117143926-nrop4ku355g3kv8b-1
  mysql-test/r/subselect_sj_jcl6.result subselect_sj_jcl6.re-20100117143928-7vzk51yaf29cdavp-1
  mysql-test/t/myisam_mrr.test   myisam_mrr.test-20091215071351-e8gzeg5bminmkaw3-1
  mysql-test/t/subselect_sj.test subselect_sj.test-20100117143931-qp396ufpe3k0scre-1
  sql/multi_range_read.cc        multi_range_read.cc-20091222114932-4426uld8n8lgl6pq-1
  sql/mysqld.cc                  sp1f-mysqld.cc-19700101030959-zpswdvekpvixxzxf7gdtofzel7nywtfj
  sql/opt_subselect.cc           opt_subselect.cc-20100215190428-nekkl8wisp0k6nlk-1
  sql/sql_class.h                sp1f-sql_class.h-19700101030959-jnqnbrjyqsvgncsibnumsmg3lyi7pa5s
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
  sql/sql_select.h               sp1f-sql_select.h-19700101030959-oqegfxr76xlgmrzd6qlevonoibfnwzoz
=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result	2011-12-19 21:05:44 +0000
+++ b/mysql-test/r/myisam_mrr.result	2012-01-25 14:27:34 +0000
@@ -557,4 +557,81 @@
 set @@join_cache_level= @tmp_730133_jcl;
 set @@optimizer_switch= @tmp_730133_os;
 drop table t1;
+#
+# Test of MRR handler counters
+#
+flush status;
+show status like 'Handler_mrr%';
+Variable_name	Value
+Handler_mrr_extra_key_sorts	0
+Handler_mrr_extra_rowid_sorts	0
+Handler_mrr_init	0
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, filler char(200), key(a));
+insert into t1 
+select A.a+10*B.a+100*C.a+1000*D.a, 123,'filler' from t0 A, t0 B, t0 C, t0 D;
+explain select sum(b) from t1 where a < 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	8	Using index condition; Rowid-ordered scan
+# This should show one MRR scan and no re-fills:
+flush status;
+select sum(b) from t1 where a < 10;
+sum(b)
+1230
+show status like 'handler_mrr%';
+Variable_name	Value
+Handler_mrr_extra_key_sorts	0
+Handler_mrr_extra_rowid_sorts	0
+Handler_mrr_init	1
+set @mrr_buffer_size_save= @@mrr_buffer_size;
+set mrr_buffer_size=128;
+explain select sum(b) from t1 where a < 1600;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	1380	Using index condition; Rowid-ordered scan
+# This should show one MRR scan and one extra rowid sort:
+flush status;
+select sum(b) from t1 where a < 1600;
+sum(b)
+196800
+show status like 'handler_mrr%';
+Variable_name	Value
+Handler_mrr_extra_key_sorts	0
+Handler_mrr_extra_rowid_sorts	1
+Handler_mrr_init	1
+set @@mrr_buffer_size= @mrr_buffer_size_save;
+#Now, let's check BKA: 
+set @join_cache_level_save= @@join_cache_level;
+set @join_buffer_size_save= @@join_buffer_size;
+set join_cache_level=6;
+explain select sum(t1.b) from t0,t1 where t0.a=t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+flush status;
+select sum(t1.b) from t0,t1 where t0.a=t1.a;
+sum(t1.b)
+1230
+show status like 'handler_mrr%';
+Variable_name	Value
+Handler_mrr_extra_key_sorts	0
+Handler_mrr_extra_rowid_sorts	0
+Handler_mrr_init	1
+set join_buffer_size=10;
+explain select sum(t1.b) from t0,t1 where t0.a=t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+flush status;
+select sum(t1.b) from t0,t1 where t0.a=t1.a;
+sum(t1.b)
+1230
+show status like 'handler_mrr%';
+Variable_name	Value
+Handler_mrr_extra_key_sorts	1or2
+Handler_mrr_extra_rowid_sorts	1or2
+Handler_mrr_init	1or2
+set join_cache_level= @join_cache_level_save;
+set join_buffer_size= @join_buffer_size_save;
+drop table t0, t1;
 set optimizer_switch= @myisam_mrr_tmp;

=== modified file 'mysql-test/r/status.result'
--- a/mysql-test/r/status.result	2012-01-13 12:35:49 +0000
+++ b/mysql-test/r/status.result	2012-01-25 14:27:34 +0000
@@ -275,6 +275,9 @@
 Handler_commit	0
 Handler_delete	0
 Handler_discover	0
+Handler_mrr_extra_key_sorts	0
+Handler_mrr_extra_rowid_sorts	0
+Handler_mrr_init	0
 Handler_prepare	0
 Handler_read_first	0
 Handler_read_key	4
@@ -297,7 +300,7 @@
 Created_tmp_tables	2
 Handler_tmp_update	2
 Handler_tmp_write	7
-Rows_tmp_read	35
+Rows_tmp_read	38
 drop table t1;
 CREATE TABLE t1 (i int(11) DEFAULT NULL, KEY i (i) ) ENGINE=MyISAM;
 insert into t1 values (1),(2),(3),(4),(5);
@@ -310,6 +313,9 @@
 Handler_commit	0
 Handler_delete	0
 Handler_discover	0
+Handler_mrr_extra_key_sorts	0
+Handler_mrr_extra_rowid_sorts	0
+Handler_mrr_init	0
 Handler_prepare	0
 Handler_read_first	0
 Handler_read_key	2

=== modified file 'mysql-test/r/status_user.result'
--- a/mysql-test/r/status_user.result	2012-01-13 12:35:49 +0000
+++ b/mysql-test/r/status_user.result	2012-01-25 14:27:34 +0000
@@ -100,6 +100,9 @@
 Handler_commit	19
 Handler_delete	1
 Handler_discover	0
+Handler_mrr_extra_key_sorts	0
+Handler_mrr_extra_rowid_sorts	0
+Handler_mrr_init	0
 Handler_prepare	18
 Handler_read_first	0
 Handler_read_key	3

=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2012-01-22 20:54:30 +0000
+++ b/mysql-test/r/subselect_sj.result	2012-01-25 14:36:57 +0000
@@ -2475,4 +2475,38 @@
 a	COUNT(*)
 NULL	0
 DROP TABLE t1, t2, t3;
+#
+# BUG#920255: Wrong result (extra rows) with loosescan and IN subquery
+#
+CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
+INSERT INTO t1 VALUES
+(1,2),(2,1),(3,3),(4,2),(5,5),
+(6,3),(7,1),(8,4),(9,3),(10,2);
+CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+INSERT INTO t2 VALUES
+(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+a	b	d
+2	1	2
+7	1	2
+2	1	2
+7	1	2
+1	2	1
+4	2	1
+10	2	1
+1	2	1
+4	2	1
+10	2	1
+3	3	3
+6	3	3
+9	3	3
+3	3	3
+6	3	3
+9	3	3
+8	4	2
+8	4	2
+5	5	5
+DROP TABLE t1, t2;
 set optimizer_switch=@subselect_sj_tmp;

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2012-01-22 20:54:30 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2012-01-25 14:36:57 +0000
@@ -2489,6 +2489,40 @@
 a	COUNT(*)
 NULL	0
 DROP TABLE t1, t2, t3;
+#
+# BUG#920255: Wrong result (extra rows) with loosescan and IN subquery
+#
+CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
+INSERT INTO t1 VALUES
+(1,2),(2,1),(3,3),(4,2),(5,5),
+(6,3),(7,1),(8,4),(9,3),(10,2);
+CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+INSERT INTO t2 VALUES
+(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+a	b	d
+1	2	1
+1	2	1
+2	1	2
+2	1	2
+3	3	3
+3	3	3
+4	2	1
+4	2	1
+5	5	5
+6	3	3
+6	3	3
+7	1	2
+7	1	2
+8	4	2
+8	4	2
+9	3	3
+9	3	3
+10	2	1
+10	2	1
+DROP TABLE t1, t2;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off

=== modified file 'mysql-test/t/myisam_mrr.test'
--- a/mysql-test/t/myisam_mrr.test	2011-10-01 01:55:02 +0000
+++ b/mysql-test/t/myisam_mrr.test	2012-01-25 14:27:34 +0000
@@ -268,5 +268,58 @@
 set @@optimizer_switch= @tmp_730133_os;
 drop table t1;
 
+--echo #
+--echo # Test of MRR handler counters
+--echo #
+flush status;
+show status like 'Handler_mrr%';
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, filler char(200), key(a));
+insert into t1 
+select A.a+10*B.a+100*C.a+1000*D.a, 123,'filler' from t0 A, t0 B, t0 C, t0 D;
+
+explain select sum(b) from t1 where a < 10;
+--echo # This should show one MRR scan and no re-fills:
+flush status;
+select sum(b) from t1 where a < 10;
+show status like 'handler_mrr%';
+
+set @mrr_buffer_size_save= @@mrr_buffer_size;
+--disable_warnings
+set mrr_buffer_size=128;
+--enable_warnings
+
+explain select sum(b) from t1 where a < 1600;
+--echo # This should show one MRR scan and one extra rowid sort:
+flush status;
+select sum(b) from t1 where a < 1600;
+show status like 'handler_mrr%';
+set @@mrr_buffer_size= @mrr_buffer_size_save;
+
+--echo #Now, let's check BKA: 
+set @join_cache_level_save= @@join_cache_level;
+set @join_buffer_size_save= @@join_buffer_size;
+set join_cache_level=6;
+
+explain select sum(t1.b) from t0,t1 where t0.a=t1.a;
+flush status;
+select sum(t1.b) from t0,t1 where t0.a=t1.a;
+show status like 'handler_mrr%';
+
+--disable_warnings
+set join_buffer_size=10;
+--enable_warnings
+explain select sum(t1.b) from t0,t1 where t0.a=t1.a;
+flush status;
+select sum(t1.b) from t0,t1 where t0.a=t1.a;
+--replace_result 1 1or2 2 1or2
+show status like 'handler_mrr%';
+
+set join_cache_level= @join_cache_level_save;
+set join_buffer_size= @join_buffer_size_save;
+
+drop table t0, t1;
+
 ## This must be last line in the file:
 set optimizer_switch= @myisam_mrr_tmp;

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2012-01-22 20:54:30 +0000
+++ b/mysql-test/t/subselect_sj.test	2012-01-25 14:36:57 +0000
@@ -2234,5 +2234,24 @@
 
 DROP TABLE t1, t2, t3;
 
+--echo #
+--echo # BUG#920255: Wrong result (extra rows) with loosescan and IN subquery
+--echo #
+CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
+INSERT INTO t1 VALUES
+  (1,2),(2,1),(3,3),(4,2),(5,5),
+  (6,3),(7,1),(8,4),(9,3),(10,2);
+
+CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+INSERT INTO t2 VALUES
+  (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+  ( SELECT b, d FROM t1, t2 WHERE b = c );
+
+DROP TABLE t1, t2;
+
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;

=== modified file 'sql/multi_range_read.cc'
--- a/sql/multi_range_read.cc	2011-10-22 14:19:43 +0000
+++ b/sql/multi_range_read.cc	2012-01-23 19:35:52 +0000
@@ -466,6 +466,10 @@
 
 /**
   Fill the buffer with (lookup_tuple, range_id) pairs and sort
+
+  @return 
+    0                   OK, the buffer is non-empty and sorted
+    HA_ERR_END_OF_FILE  Source exhausted, the buffer is empty.
 */
 
 int Mrr_ordered_index_reader::refill_buffer(bool initial)
@@ -502,6 +506,13 @@
   if (source_exhausted && key_buffer->is_empty())
     DBUG_RETURN(HA_ERR_END_OF_FILE);
 
+  if (!initial)
+  {
+    /* This is a non-initial buffer fill and we've got a non-empty buffer */
+    THD *thd= current_thd;
+    status_var_increment(thd->status_var.ha_mrr_extra_key_sorts);
+  }
+
   key_buffer->sort((key_buffer->type() == Lifo_buffer::FORWARD)? 
                      (qsort2_cmp)Mrr_ordered_index_reader::compare_keys_reverse : 
                      (qsort2_cmp)Mrr_ordered_index_reader::compare_keys, 
@@ -576,6 +587,7 @@
 int Mrr_ordered_rndpos_reader::refill_buffer(bool initial)
 {
   int res;
+  bool first_call= initial;
   DBUG_ENTER("Mrr_ordered_rndpos_reader::refill_buffer");
 
   if (index_reader_exhausted)
@@ -593,6 +605,14 @@
     initial= FALSE;
     index_reader_needs_refill= FALSE;
   }
+
+  if (!first_call && !index_reader_exhausted)
+  {
+    /* Ok, this was a successful buffer refill operation */
+    THD *thd= current_thd;
+    status_var_increment(thd->status_var.ha_mrr_extra_rowid_sorts);
+  }
+
   DBUG_RETURN(res);
 }
 
@@ -825,8 +845,7 @@
     strategy= disk_strategy= &reader_factory.ordered_rndpos_reader;
   }
 
-  if (is_mrr_assoc)
-    status_var_increment(thd->status_var.ha_multi_range_read_init_count);
+  status_var_increment(thd->status_var.ha_multi_range_read_init_count);
 
   full_buf= buf->buffer;
   full_buf_end= buf->buffer_end;

=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc	2012-01-24 16:13:30 +0000
+++ b/sql/mysqld.cc	2012-01-25 16:48:29 +0000
@@ -8323,6 +8323,9 @@
   {"Handler_write",            (char*) offsetof(STATUS_VAR, ha_write_count), SHOW_LONG_STATUS},
   {"Handler_tmp_update",       (char*) offsetof(STATUS_VAR, ha_tmp_update_count), SHOW_LONG_STATUS},
   {"Handler_tmp_write",        (char*) offsetof(STATUS_VAR, ha_tmp_write_count), SHOW_LONG_STATUS},
+  {"Handler_mrr_init",         (char*) offsetof(STATUS_VAR, ha_multi_range_read_init_count),  SHOW_LONG_STATUS},
+  {"Handler_mrr_extra_rowid_sorts", (char*) offsetof(STATUS_VAR, ha_mrr_extra_rowid_sorts), SHOW_LONG_STATUS},
+  {"Handler_mrr_extra_key_sorts",   (char*) offsetof(STATUS_VAR, ha_mrr_extra_key_sorts), SHOW_LONG_STATUS},
   {"Key",                      (char*) &show_default_keycache, SHOW_FUNC},
   {"Last_query_cost",          (char*) offsetof(STATUS_VAR, last_query_cost), SHOW_DOUBLE_STATUS},
   {"Max_used_connections",     (char*) &max_used_connections,  SHOW_LONG},

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2012-01-24 16:13:30 +0000
+++ b/sql/opt_subselect.cc	2012-01-25 16:48:29 +0000
@@ -4111,6 +4111,7 @@
         for (uint kp=0; kp < pos->loosescan_picker.loosescan_parts; kp++)
           keylen += tab->table->key_info[keyno].key_part[kp].store_length;
 
+        tab->loosescan_key= keyno;
         tab->loosescan_key_len= keylen;
         if (pos->n_sj_tables > 1) 
           tab[pos->n_sj_tables - 1].do_firstmatch= tab;

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2012-01-24 16:13:30 +0000
+++ b/sql/sql_class.h	2012-01-25 16:48:29 +0000
@@ -590,6 +590,8 @@
     BatchedKeyAccess.
   */
   ulong ha_multi_range_read_init_count;
+  ulong ha_mrr_extra_key_sorts;
+  ulong ha_mrr_extra_rowid_sorts;
 
   ulong ha_rollback_count;
   ulong ha_update_count;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-01-24 16:13:30 +0000
+++ b/sql/sql_select.cc	2012-01-25 16:48:29 +0000
@@ -15404,7 +15404,7 @@
     if (join_tab->loosescan_match_tab && 
         join_tab->loosescan_match_tab->found_match)
     {
-      KEY *key= join_tab->table->key_info + join_tab->index;
+      KEY *key= join_tab->table->key_info + join_tab->loosescan_key;
       key_copy(join_tab->loosescan_buf, join_tab->table->record[0], key, 
                join_tab->loosescan_key_len);
       skip_over= TRUE;
@@ -15414,7 +15414,7 @@
 
     if (skip_over && !error) 
     {
-      if(!key_cmp(join_tab->table->key_info[join_tab->index].key_part,
+      if(!key_cmp(join_tab->table->key_info[join_tab->loosescan_key].key_part,
                   join_tab->loosescan_buf, join_tab->loosescan_key_len))
       {
         /* 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-01-10 21:26:00 +0000
+++ b/sql/sql_select.h	2012-01-25 14:33:57 +0000
@@ -379,6 +379,12 @@
   /* Buffer to save index tuple to be able to skip duplicates */
   uchar *loosescan_buf;
   
+  /* 
+    Index used by LooseScan (we store it here separately because ref access
+    stores it in tab->ref.key, while range scan stores it in tab->index, etc)
+  */
+  uint loosescan_key;
+
   /* Length of key tuple (depends on #keyparts used) to store in the above */
   uint loosescan_key_len;
 



More information about the commits mailing list