List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:May 10 2011 12:16pm
Subject:bzr commit into mysql-5.1 branch (mattias.jonsson:3683) Bug#11761296
View as plain text  
#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#11761296Mattias Jonsson10 May