From: Mattias Jonsson
Date: June 5 2012 10:13am
Subject: bzr push into mysql-trunk branch (mattias.jonsson:3947 to 3948) WL#4443
List-Archive: http://lists.mysql.com/commits/144093
Message-Id: <201206051013.q55ADjp6021197@acsmt357.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3948 Mattias Jonsson	2012-06-05
      WL#4443: Remove scalability problem with many lock's
      and external lock's on partitioned tables with many partitions.
      
      Minor test cleanup.
      Removed is_pruning_completed= true when tree type was
      not KEY/KEY_SMALLER, since it was not covered and possibly
      not true.

    modified:
      mysql-test/r/partition_locking.result
      mysql-test/t/partition_locking.test
      sql/opt_range.cc
 3947 Mattias Jonsson	2012-06-01
      WL#4443: Remove scalability problem with many lock's
      and external lock's on partitioned tables with many partitions.
      
      Updated test.
      
      - Turned off innodb persistent stats.
      - Removed some duplicate tests.
      - Filtered estimated rows in EXPLAIN to get more stable results.

    modified:
      mysql-test/r/partition_locking.result
      mysql-test/t/partition_locking.test
=== modified file 'mysql-test/r/partition_locking.result'
--- a/mysql-test/r/partition_locking.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/r/partition_locking.result	revid:mattias.jonsson@stripped
@@ -3842,6 +3842,36 @@ SELECT * FROM t2 ORDER BY a;
 a	b
 4	First row, p4, t1.b:First row, p4
 #
+# Test subquery IN expression
+#
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT count(*) FROM t1 p
+WHERE a IN (1, 2, 9);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	p	p1,p2,p9	index	PRIMARY	b	131	NULL	4	Using where; Using index
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	8
+HANDLER_WRITE	17
+# 8 locks (1 table, 3 partitions, lock/unlock)
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT count(*) FROM t1 p
+WHERE a IN
+(SELECT a + 1 FROM t2 WHERE a = 4);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	p	p5	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	SIMPLE	t2	p4	const	PRIMARY	PRIMARY	4	const	1	Using index
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	32
+HANDLER_READ_KEY	2
+HANDLER_WRITE	17
+# 32 locks (2 table, 13 + 1 partitions, lock/unlock)
+#
 # Test triggers
 # Tables used in triggers cannot be pruned for locks.
 # Tables with triggers cannot be pruned for locks if
@@ -4011,16 +4041,9 @@ HANDLER_WRITE	19
 SELECT * FROM t1 WHERE a = 3;
 a	b
 3	First row, p3, same as min(a) + 2 in t2, UPDATED2
-FLUSH STATUS;
 EXPLAIN PARTITIONS INSERT INTO t1 VALUES (12, "First row, p12");
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
-WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
-VARIABLE_NAME	VARIABLE_VALUE
-HANDLER_WRITE	17
-# 16 locks (2 table, 1 + 5 partition lock/unlock)
-# t1, t3 after insert trigger
 FLUSH STATUS;
 INSERT INTO t1 VALUES (12, "First row, p12");
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
@@ -5202,7 +5225,7 @@ HANDLER_READ_FIRST	6
 HANDLER_READ_KEY	6
 HANDLER_READ_RND_NEXT	22
 HANDLER_WRITE	18
-# 12 locks (3 tables + 3 partitions) x lock/unlock
+# 24 locks (3 tables + 3 x 3 partitions) x lock/unlock
 # I.e. No lock pruning possible
 FLUSH STATUS;
 EXPLAIN PARTITIONS INSERT INTO t2 VALUES ((SELECT max(a) FROM t1),

=== modified file 'mysql-test/t/partition_locking.test'
--- a/mysql-test/t/partition_locking.test	revid:mattias.jonsson@stripped
+++ b/mysql-test/t/partition_locking.test	revid:mattias.jonsson@stripped
@@ -1350,6 +1350,22 @@ SELECT * FROM t1 ORDER BY a;
 SELECT * FROM t2 ORDER BY a;
 
 --echo #
+--echo # Test subquery IN expression
+--echo #
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT count(*) FROM t1 p
+WHERE a IN (1, 2, 9);
+eval $get_handler_status_counts;
+--echo # 8 locks (1 table, 3 partitions, lock/unlock)
+
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT count(*) FROM t1 p
+WHERE a IN
+(SELECT a + 1 FROM t2 WHERE a = 4);
+eval $get_handler_status_counts;
+--echo # 32 locks (2 table, 13 + 1 partitions, lock/unlock)
+
+--echo #
 --echo # Test triggers
 --echo # Tables used in triggers cannot be pruned for locks.
 --echo # Tables with triggers cannot be pruned for locks if
@@ -1456,11 +1472,7 @@ eval $get_handler_status_counts;
 --echo # t1, before update, after update
 SELECT * FROM t1 WHERE a = 3;
 
-FLUSH STATUS;
 EXPLAIN PARTITIONS INSERT INTO t1 VALUES (12, "First row, p12");
-eval $get_handler_status_counts;
---echo # 16 locks (2 table, 1 + 5 partition lock/unlock)
---echo # t1, t3 after insert trigger
 
 FLUSH STATUS;
 INSERT INTO t1 VALUES (12, "First row, p12");
@@ -1662,6 +1674,7 @@ eval $get_handler_status_counts;
 --echo # 28 locks (1 tables, 13 partitions lock/unlock)
 
 SELECT * FROM t1 ORDER BY a;
+
 DROP TABLE t1, t2, t3;
 
 --echo #
@@ -2084,7 +2097,7 @@ INSERT INTO t1 VALUES (1, 1), (2, 0), (4
 FLUSH STATUS;
 INSERT INTO t2 VALUES ((SELECT max(a) FROM t1), (SELECT min(a) FROM t1));
 eval $get_handler_status_counts;
---echo # 12 locks (3 tables + 3 partitions) x lock/unlock
+--echo # 24 locks (3 tables + 3 x 3 partitions) x lock/unlock
 --echo # I.e. No lock pruning possible
 FLUSH STATUS;
 --replace_column 10 #

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	revid:mattias.jonsson@stripped
+++ b/sql/opt_range.cc	revid:mattias.jonsson@stripped
@@ -3171,19 +3171,7 @@ bool prune_partitions(THD *thd, TABLE *t
   }
 
   if (tree->type != SEL_TREE::KEY && tree->type != SEL_TREE::KEY_SMALLER)
-  {
-    /*
-      If the condition can be evaluated now, we are done with pruning.
-
-      During the prepare phase, before locking, subqueries and stored programs
-      are not evaluated. So we need to run prune_partitions() a second time in
-      the optimize phase to prune partitions for reading, when subqueries and
-      stored programs may be evaluated.
-    */
-    if (pprune_cond->can_be_evaluated_now())
-      part_info->is_pruning_completed= true;
     goto all_used;
-  }
 
   if (tree->merges.is_empty())
   {
@@ -3233,7 +3221,14 @@ bool prune_partitions(THD *thd, TABLE *t
     }
   }
   
-  /* Same here regarding avoid the second run in the optimize phase. */
+  /*
+    If the condition can be evaluated now, we are done with pruning.
+
+    During the prepare phase, before locking, subqueries and stored programs
+    are not evaluated. So we need to run prune_partitions() a second time in
+    the optimize phase to prune partitions for reading, when subqueries and
+    stored programs may be evaluated.
+  */
   if (pprune_cond->can_be_evaluated_now())
     part_info->is_pruning_completed= true;
   goto end;

No bundle (reason: useless for push emails).