#At file:///C:/ade/mysql-bzr/b11761296-5.1/ based on revid:mattias.jonsson@stripped
3683 Mattias Jonsson 2011-05-10
Minor update to bug#11761296 after the first review.
TODO: extend the test to also test cached pruned queries.
added:
mysql-test/r/partition_cache_myisam.result
mysql-test/t/partition_cache_myisam.test
modified:
sql/ha_partition.cc
=== added file 'mysql-test/r/partition_cache_myisam.result'
--- a/mysql-test/r/partition_cache_myisam.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/partition_cache_myisam.result 2011-05-10 12:16:35 +0000
@@ -0,0 +1,335 @@
+SET GLOBAL query_cache_size = 1024 * 1024;
+SET SESSION STORAGE_ENGINE = MyISAM;
+drop table if exists t1,t2,t3;
+flush status;
+set autocommit=0;
+create table t1 (a int not null) PARTITION BY HASH (a) PARTITIONS 7;
+insert into t1 values (1),(2),(3);
+select * from t1;
+a
+1
+2
+3
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 1
+drop table t1;
+commit;
+set autocommit=1;
+begin;
+create table t1 (a int not null) PARTITION BY HASH (a) PARTITIONS 7;
+insert into t1 values (1),(2),(3);
+select * from t1;
+a
+1
+2
+3
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 1
+drop table t1;
+commit;
+create table t1 (a int not null) PARTITION BY HASH (a) PARTITIONS 7;
+create table t2 (a int not null) PARTITION BY HASH (a) PARTITIONS 7;
+create table t3 (a int not null) PARTITION BY HASH (a) PARTITIONS 7;
+insert into t1 values (1),(2);
+insert into t2 values (1),(2);
+insert into t3 values (1),(2);
+select * from t1;
+a
+1
+2
+select * from t2;
+a
+1
+2
+select * from t3;
+a
+1
+2
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 3
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 0
+begin;
+select * from t1;
+a
+1
+2
+select * from t2;
+a
+1
+2
+select * from t3;
+a
+1
+2
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 6
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 0
+insert into t1 values (3);
+insert into t2 values (3);
+insert into t1 values (4);
+select * from t1;
+a
+1
+2
+3
+4
+select * from t2;
+a
+1
+2
+3
+select * from t3;
+a
+1
+2
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 4
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 1
+commit;
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 4
+drop table t3,t2,t1;
+CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) PARTITION BY HASH (id) PARTITIONS 7;
+select count(*) from t1;
+count(*)
+0
+insert into t1 (id) values (0);
+select count(*) from t1;
+count(*)
+1
+drop table t1;
+SET SESSION STORAGE_ENGINE = MyISAM;
+SET @@autocommit=1;
+connection default
+SHOW VARIABLES LIKE 'have_query_cache';
+Variable_name Value
+have_query_cache YES
+SET GLOBAL query_cache_size = 200000;
+flush status;
+SET @@autocommit=1;
+SET SESSION STORAGE_ENGINE = MyISAM;
+CREATE TABLE t2 (s1 int, s2 varchar(1000), key(s1)) PARTITION BY HASH (s1) PARTITIONS 7;
+INSERT INTO t2 VALUES (1,repeat('a',10)),(2,repeat('a',10)),(3,repeat('a',10)),(4,repeat('a',10));
+COMMIT;
+START TRANSACTION;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+0
+UPDATE t2 SET s2 = 'w' WHERE s1 = 3;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+1
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 1
+connection connection1
+START TRANSACTION;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+1
+INSERT INTO t2 VALUES (5,'w');
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+COMMIT;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 2
+connection default
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+COMMIT;
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 2
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 2
+connection connection1
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+START TRANSACTION;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+INSERT INTO t2 VALUES (6,'w');
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+3
+connection default
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+3
+START TRANSACTION;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+3
+DELETE from t2 WHERE s1=3;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+COMMIT;
+connection connection1
+COMMIT;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 2
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 6
+set GLOBAL query_cache_size=0;
+drop table t2;
+SET GLOBAL query_cache_size = 1024 * 1024;
+create table t1
+(name varchar(128),
+dept int,
+primary key(dept,name))
+ENGINE=MyISAM
+PARTITION BY RANGE (`dept`)
+(
+PARTITION d0 VALUES LESS THAN (1),
+PARTITION d1 VALUES LESS THAN (2),
+PARTITION d2 VALUES LESS THAN (3),
+PARTITION d_other VALUES LESS THAN MAXVALUE);
+set autocommit=0;
+start transaction;
+insert into t1(name,dept) values('a',1);
+insert into t1(name,dept) values('b',1);
+insert into t1(name,dept) values('c',1);
+select count(*) from t1;
+count(*)
+3
+# expecting 3
+rollback;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+select count(*) from t1;
+count(*)
+3
+# expecting 0, and seeing 0
+select count(*) from t1;
+count(*)
+3
+# expecting 0, and seeing 3 before fix!
+drop table t1;
+CREATE TABLE `t1`
+(`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+) ENGINE=MyISAM;
+ALTER TABLE t1
+PARTITION BY RANGE (TO_DAYS(created_at))
+(PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (1, now(), 0);
+BEGIN;
+UPDATE `t1` SET `cool` = 1 WHERE `id` = 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+BEGIN;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+DROP TABLE `t1`;
+CREATE TABLE t1 (a INT, b VARCHAR(64))
+ENGINE = MyISAM
+PARTITION BY HASH (a)
+PARTITIONS 5;
+INSERT INTO t1 VALUES (11, 'Eleven'), (1, 'One'), (2, 'Two'), (12, 'aslasdrfa'), (7, 'sdfae'), (4, 'asdfees'), (14, '3asdf3'), (9, 'asdfeea');
+set autocommit=1;
+FLUSH STATUS;
+SELECT * FROM t1;
+a b
+11 Eleven
+1 One
+2 Two
+12 aslasdrfa
+7 sdfae
+4 asdfees
+14 3asdf3
+9 asdfeea
+SHOW STATUS LIKE "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 1
+SHOW STATUS LIKE "Qcache_inserts";
+Variable_name Value
+Qcache_inserts 1
+SHOW STATUS LIKE "Qcache_hits";
+Variable_name Value
+Qcache_hits 0
+SELECT * FROM t1;
+a b
+11 Eleven
+1 One
+2 Two
+12 aslasdrfa
+7 sdfae
+4 asdfees
+14 3asdf3
+9 asdfeea
+SHOW STATUS LIKE "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 1
+SHOW STATUS LIKE "Qcache_inserts";
+Variable_name Value
+Qcache_inserts 1
+SHOW STATUS LIKE "Qcache_hits";
+Variable_name Value
+Qcache_hits 1
+FLUSH TABLES;
+SELECT * FROM t1;
+a b
+11 Eleven
+1 One
+2 Two
+12 aslasdrfa
+7 sdfae
+4 asdfees
+14 3asdf3
+9 asdfeea
+SHOW STATUS LIKE "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 1
+SHOW STATUS LIKE "Qcache_inserts";
+Variable_name Value
+Qcache_inserts 2
+SHOW STATUS LIKE "Qcache_hits";
+Variable_name Value
+Qcache_hits 1
+DROP TABLE t1;
+set GLOBAL query_cache_size=0;
=== added file 'mysql-test/t/partition_cache_myisam.test'
--- a/mysql-test/t/partition_cache_myisam.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/partition_cache_myisam.test 2011-05-10 12:16:35 +0000
@@ -0,0 +1,105 @@
+--source include/have_query_cache.inc
+
+--source include/have_partition.inc
+let $engine_type= MyISAM;
+# partitioned MyISAM does NOT supports FOREIGN KEYs
+let $test_foreign_keys= 0;
+
+# Using SELECT to get a space as first character.
+let $partitions_a= `SELECT ' PARTITION BY HASH (a) PARTITIONS 7'`;
+let $partitions_id= `SELECT ' PARTITION BY HASH (id) PARTITIONS 7'`;
+let $partitions_s1= `SELECT ' PARTITION BY HASH (s1) PARTITIONS 7'`;
+
+--source include/query_cache.inc
+
+let $save_query_cache_size=`select @@global.query_cache_size`;
+SET GLOBAL query_cache_size = 1024 * 1024;
+create table t1
+(name varchar(128),
+ dept int,
+ primary key(dept,name))
+ENGINE=MyISAM
+PARTITION BY RANGE (`dept`)
+(
+ PARTITION d0 VALUES LESS THAN (1),
+ PARTITION d1 VALUES LESS THAN (2),
+ PARTITION d2 VALUES LESS THAN (3),
+ PARTITION d_other VALUES LESS THAN MAXVALUE);
+
+set autocommit=0;
+start transaction;
+
+insert into t1(name,dept) values('a',1);
+insert into t1(name,dept) values('b',1);
+insert into t1(name,dept) values('c',1);
+
+select count(*) from t1;
+--echo # expecting 3
+rollback;
+
+select count(*) from t1;
+--echo # expecting 0, and seeing 0
+
+select count(*) from t1;
+--echo # expecting 0, and seeing 3 before fix!
+
+drop table t1;
+
+
+
+CREATE TABLE `t1`
+(`id` int(11) NOT NULL ,
+ `created_at` datetime NOT NULL,
+ `cool` tinyint default 0
+) ENGINE=MyISAM;
+
+ALTER TABLE t1
+PARTITION BY RANGE (TO_DAYS(created_at))
+(PARTITION month_2010_4 VALUES LESS THAN (734258),
+ PARTITION month_2010_5 VALUES LESS THAN (734289),
+ PARTITION month_max VALUES LESS THAN MAXVALUE);
+
+INSERT INTO t1 VALUES (1, now(), 0);
+
+BEGIN;
+
+UPDATE `t1` SET `cool` = 1 WHERE `id` = 1;
+
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+
+ROLLBACK;
+
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+
+BEGIN;
+
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+
+ROLLBACK;
+
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+
+DROP TABLE `t1`;
+
+CREATE TABLE t1 (a INT, b VARCHAR(64))
+ENGINE = MyISAM
+PARTITION BY HASH (a)
+PARTITIONS 5;
+INSERT INTO t1 VALUES (11, 'Eleven'), (1, 'One'), (2, 'Two'), (12, 'aslasdrfa'), (7, 'sdfae'), (4, 'asdfees'), (14, '3asdf3'), (9, 'asdfeea');
+set autocommit=1;
+FLUSH STATUS;
+SELECT * FROM t1;
+SHOW STATUS LIKE "Qcache_queries_in_cache";
+SHOW STATUS LIKE "Qcache_inserts";
+SHOW STATUS LIKE "Qcache_hits";
+SELECT * FROM t1;
+SHOW STATUS LIKE "Qcache_queries_in_cache";
+SHOW STATUS LIKE "Qcache_inserts";
+SHOW STATUS LIKE "Qcache_hits";
+FLUSH TABLES;
+SELECT * FROM t1;
+SHOW STATUS LIKE "Qcache_queries_in_cache";
+SHOW STATUS LIKE "Qcache_inserts";
+SHOW STATUS LIKE "Qcache_hits";
+DROP TABLE t1;
+eval set GLOBAL query_cache_size=$save_query_cache_size;
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2011-05-08 21:23:01 +0000
+++ b/sql/ha_partition.cc 2011-05-10 12:16:35 +0000
@@ -6465,7 +6465,8 @@ static my_bool partition_qc_check_callba
else if (tot_parts <= 255)
{
uint8 *pruned_engine_data= (uint8*) engine_data;
- DBUG_ASSERT(part_count < 8);
+ if (part_count >= 8)
+ break;
if (!pruned_engine_data[part_count])
break;
if ((part_id + 1) != pruned_engine_data[part_count])
@@ -6474,7 +6475,8 @@ static my_bool partition_qc_check_callba
else
{
uint16 *pruned_engine_data= (uint16*) engine_data;
- DBUG_ASSERT(part_count < 4);
+ if (part_count >= 4)
+ break;
if (!pruned_engine_data[part_count])
break;
if ((part_id + 1) != pruned_engine_data[part_count])
@@ -6545,15 +6547,17 @@ my_bool ha_partition::register_query_cac
/*
Check if it is possible to use pruning by using
the unused engine_data.
- Like:
- TODO: Fix this scheme, fails if only first partition is used!
- Solve by moving part_id range with one -> 1..m_tot_parts instead
- of 0..(m_tot_parts - 1)
+ Use part_id range -> 1..m_tot_parts instead of 0..(m_tot_parts - 1).
+ This way if engine_data == 0 it means that no pruning was used.
If <= 64 parts use as bitmap.
If (> 64 parts && <= 255 parts && max 8 partitions used) use
each byte as (partition id + 1) (uint8).
If (> 255 parts && max 4 partitions used) use
each pair of byte as (partition id + 1) (uint16).
+ TODO: Change the query cache api to store a 'blob' of arbitrary length
+ instead of a fixed length engine_data. Preferable a pointer + length which
+ the query cache will copy and store. Copying since the query cache manages
+ its own memory and its own limits.
*/
if (m_tot_parts <= 64)
{
@@ -6651,7 +6655,8 @@ my_bool ha_partition::register_query_cac
if (!ha_data->static_qc_callback)
{
pthread_mutex_lock(&table_share->mutex);
- ha_data->static_qc_callback= first_callback;
+ if (!ha_data->static_qc_callback)
+ ha_data->static_qc_callback= first_callback;
pthread_mutex_unlock(&table_share->mutex);
}
Attachment: [text/bzr-bundle] bzr/mattias.jonsson@oracle.com-20110510121635-qdxsuf7q8fnwfil9.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1 branch (mattias.jonsson:3683) Bug#11761296 | Mattias Jonsson | 10 May |
