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).
| Thread |
|---|
| • bzr push into mysql-trunk branch (mattias.jonsson:3947 to 3948) WL#4443 | Mattias Jonsson | 5 Jun |
