diff options
Diffstat (limited to 'mysql-test/main/partition_key_cache.test')
| -rw-r--r-- | mysql-test/main/partition_key_cache.test | 279 |
1 files changed, 279 insertions, 0 deletions
diff --git a/mysql-test/main/partition_key_cache.test b/mysql-test/main/partition_key_cache.test new file mode 100644 index 00000000..365a571a --- /dev/null +++ b/mysql-test/main/partition_key_cache.test @@ -0,0 +1,279 @@ +# Test of key cache with partitions +--source include/have_partition.inc + +--disable_warnings +DROP TABLE IF EXISTS t1, t2, v, x; +--enable_warnings + +--echo # Actual test of key caches +--echo # Verifing that reads/writes use the key cache correctly +SET @org_key_cache_buffer_size= @@global.default.key_buffer_size; +--echo # Minimize default key cache (almost disabled). +SET @@global.default.key_buffer_size = 4096; +CREATE TABLE t1 ( + a INT, + b INT, + c INT NOT NULL, + PRIMARY KEY (a), + KEY `inx_b` (b)) +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION p0 VALUES LESS THAN (1167602410) + (SUBPARTITION sp0, + SUBPARTITION sp1), + PARTITION p1 VALUES LESS THAN MAXVALUE + (SUBPARTITION sp2, + SUBPARTITION sp3)); +CREATE TABLE t2 ( + a INT, + b INT, + c INT NOT NULL, + PRIMARY KEY (a), + KEY `inx_b` (b)); +FLUSH TABLES; +FLUSH STATUS; + +# Genereate 4096 rows. Idea from: +# http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html +SET @a:=1167602400; +CREATE VIEW v AS SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4; +CREATE VIEW x AS SELECT 1 FROM v,v a,v b; +# due to I_S performance, this was substituted with include files which +# uses SHOW STATUS +#DELIMITER |; +#CREATE PROCEDURE was_zero_reads() +#BEGIN +# SELECT IF(VARIABLE_VALUE = 0,"Yes!","No!") as 'Was zero reads?' +# FROM INFORMATION_SCHEMA.SESSION_STATUS +# WHERE VARIABLE_NAME = 'KEY_READS'; +# FLUSH STATUS; +#END| +#DELIMITER ;| + +FLUSH STATUS; +INSERT t1 SELECT @a, @a * (1 - ((@a % 2) * 2)) , 1167612400 - (@a:=@a+1) FROM x, x y; +--source include/check_key_req.inc +--echo # row distribution: +SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' and TABLE_NAME='t1'; +DROP VIEW x; +DROP VIEW v; +FLUSH TABLES; +FLUSH STATUS; +SELECT COUNT(b) FROM t1 WHERE b >= 0; +--source include/check_key_reads.inc +INSERT t2 SELECT a,b,c FROM t1; +--source include/check_key_req.inc +FLUSH STATUS; +SELECT COUNT(b) FROM t2 WHERE b >= 0; +--source include/check_key_reads.inc +FLUSH TABLES; +--echo # Setting the default key cache to 1M +SET GLOBAL key_buffer_size = 1024*1024; +FLUSH STATUS; +--echo # All these have to read the indexes +LOAD INDEX INTO CACHE t1 PARTITION (p1); +--source include/check_key_reads.inc +SELECT COUNT(b) FROM t1 WHERE b >= 0; +--source include/check_key_reads.inc +SELECT COUNT(b) FROM t2 WHERE b >= 0; +--source include/check_key_reads.inc +--echo # All these should be able to use the key cache +SELECT COUNT(b) FROM t1 WHERE b >= 0; +--source include/check_key_reads.inc +SELECT COUNT(b) FROM t2 WHERE b >= 0; +--source include/check_key_reads.inc +FLUSH TABLES; +LOAD INDEX INTO CACHE t1 PARTITION (p1,p0); +--source include/check_key_reads.inc +--echo # should not be zero +SELECT COUNT(b) FROM t1 WHERE b >= 0; +--source include/check_key_reads.inc +LOAD INDEX INTO CACHE t2; +--source include/check_key_reads.inc +--echo # should not be zero +SELECT COUNT(b) FROM t2 WHERE b >= 0; +--source include/check_key_reads.inc +FLUSH TABLES; +LOAD INDEX INTO CACHE t1 PARTITION (p1,p0) IGNORE LEAVES; +--source include/check_key_reads.inc +--echo # should not be zero +SELECT COUNT(b) FROM t1 WHERE b >= 0; +--source include/check_key_reads.inc +LOAD INDEX INTO CACHE t2 IGNORE LEAVES; +--source include/check_key_reads.inc +--echo # should not be zero +SELECT COUNT(b) FROM t2 WHERE b >= 0; +--source include/check_key_reads.inc +TRUNCATE TABLE t2; +INSERT t2 SELECT a,b,c FROM t1; +--source include/check_key_req.inc +DROP TABLE t1,t2; + +SET GLOBAL hot_cache.key_buffer_size = 1024*1024; +SET GLOBAL warm_cache.key_buffer_size = 1024*1024; +SET @@global.cold_cache.key_buffer_size = 1024*1024; +SELECT @@global.default.key_buffer_size a, @@global.default.key_cache_block_size b, @@global.default.key_cache_age_threshold c, @@global.default.key_cache_division_limit d; +SELECT @@global.hot_cache.key_buffer_size a, @@global.hot_cache.key_cache_block_size b, @@global.hot_cache.key_cache_age_threshold c, @@global.hot_cache.key_cache_division_limit d; +SELECT @@global.warm_cache.key_buffer_size a, @@global.warm_cache.key_cache_block_size b, @@global.warm_cache.key_cache_age_threshold c, @@global.warm_cache.key_cache_division_limit d; +SELECT @@global.cold_cache.key_buffer_size a, @@global.cold_cache.key_cache_block_size b, @@global.cold_cache.key_cache_age_threshold c, @@global.cold_cache.key_cache_division_limit d; +CREATE TABLE t1 ( + a INT, + b VARCHAR(257), + c INT NOT NULL, + PRIMARY KEY (a), + KEY `inx_b` (b), + KEY `inx_c`(c)) +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION p0 VALUES LESS THAN (10) + (SUBPARTITION sp0, + SUBPARTITION sp1), + PARTITION p1 VALUES LESS THAN MAXVALUE + (SUBPARTITION sp2, + SUBPARTITION sp3)); +CREATE TABLE t2 ( + a INT, + b VARCHAR(257), + c INT NOT NULL, + PRIMARY KEY (a), + KEY `inx_b` (b), + KEY `inx_c`(c)); +SET @a:=1167602400; +# Genereate 4096 rows. Idea from: +# http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html +CREATE VIEW v AS SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4; +CREATE VIEW x AS SELECT 1 FROM v,v a,v b; +INSERT t1 SELECT @a, CONCAT('X_', @a, ' MySQL'), 1167612400 - (@a:=@a+1) FROM x, x a; +DROP VIEW x; +DROP VIEW v; +INSERT t2 SELECT a, b, c FROM t1; +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t2; +FLUSH TABLES; + +--echo # Restrict partitioned commands to partitioned tables only +--error ER_PARTITION_MGMT_ON_NONPARTITIONED +CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache; +--error ER_PARTITION_MGMT_ON_NONPARTITIONED +CACHE INDEX t2 PARTITION (p0,`p1`) INDEX (`PRIMARY`) IN hot_cache; +--error ER_PARTITION_MGMT_ON_NONPARTITIONED +CACHE INDEX t2 PARTITION (`p1`) INDEX (`PRIMARY`,`inx_b`) IN hot_cache; +--error ER_PARTITION_MGMT_ON_NONPARTITIONED +CACHE INDEX t2 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN hot_cache; +--echo # Basic key cache testing +--echo # The manual correctly says: "The syntax of CACHE INDEX enables you to +--echo # specify that only particular indexes from a table should be assigned +--echo # to the cache. The current implementation assigns all the table's +--echo # indexes to the cache, so there is no reason to specify anything +--echo # other than the table name." +--echo # So the most of the test only tests the syntax +CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache; +CACHE INDEX t2 KEY (`PRIMARY`) IN warm_cache; +CACHE INDEX t2 KEY (`PRIMARY`,`inx_b`) IN cold_cache; +CACHE INDEX t2 INDEX (inx_b,`PRIMARY`) IN default; +CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN cold_cache; +--error ER_PARSE_ERROR +CACHE INDEX t1 PARTITIONS (p0) KEY (`inx_b`) IN cold_cache; +--echo # only one table at a time if specifying partitions +--error ER_PARSE_ERROR +CACHE INDEX t1,t2 PARTITION (p0) KEY (`inx_b`) IN cold_cache; +CACHE INDEX t1 PARTITION (`p0`,p1) INDEX (`PRIMARY`) IN warm_cache; +CACHE INDEX t1 PARTITION (`p1`) INDEX (`PRIMARY`,inx_b) IN hot_cache; +CACHE INDEX t1 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN default; +CACHE INDEX t1 PARTITION (ALL) IN hot_cache; +CACHE INDEX t1 INDEX (`inx_b`) IN default; +CACHE INDEX t1 KEY (`PRIMARY`) IN hot_cache; +CACHE INDEX t1 KEY (`PRIMARY`,`inx_b`) IN warm_cache; +CACHE INDEX t1 INDEX (`inx_b`,`PRIMARY`) IN cold_cache; +CACHE INDEX t1 IN hot_cache; +--echo # Test of non existent key cache: +--error ER_UNKNOWN_KEY_CACHE +CACHE INDEX t1 IN non_existent_key_cache; +--echo # Basic testing of LOAD INDEX +LOAD INDEX INTO CACHE t2; +--echo # PRIMARY and secondary keys have different block sizes +LOAD INDEX INTO CACHE t2 ignore leaves; +--echo # Must have INDEX or KEY before the index list +--error ER_PARSE_ERROR +LOAD INDEX INTO CACHE t2 (`PRIMARY`); + +--echo # Test of IGNORE LEAVES +LOAD INDEX INTO CACHE t2 INDEX (`PRIMARY`); +LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`) IGNORE LEAVES; +CACHE INDEX t2 IN warm_cache; +CACHE INDEX t1 IN cold_cache; +LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`) IGNORE LEAVES; +CACHE INDEX t2 INDEX (`inx_b`, `inx_c`) IN hot_cache; +LOAD INDEX INTO CACHE t2 KEY (`inx_b`, `inx_c`) IGNORE LEAVES; +CACHE INDEX t2 IN warm_cache; +CACHE INDEX t2 INDEX (`PRIMARY`, `inx_c`) IN hot_cache; +LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_c`) IGNORE LEAVES; +CACHE INDEX t2 INDEX (`inx_b`,`PRIMARY`) IN default; +LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`); +CACHE INDEX t2 IN default; +LOAD INDEX INTO CACHE t2 IGNORE LEAVES; + +--error ER_PARTITION_MGMT_ON_NONPARTITIONED +LOAD INDEX INTO CACHE t2 PARTITION (p1) INDEX (`PRIMARY`); +LOAD INDEX INTO CACHE t1, t2; +--echo # only one table at a time if specifying partitions +--error ER_PARSE_ERROR +LOAD INDEX INTO CACHE t1 PARTITION (p0), t2; +LOAD INDEX INTO CACHE t1 IGNORE LEAVES; +LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`); +LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`) IGNORE LEAVES; +LOAD INDEX INTO CACHE t1 INDEX (`inx_b`) IGNORE LEAVES; +LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`) IGNORE LEAVES; +LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`); +LOAD INDEX INTO CACHE t1 PARTITION (p1) INDEX (`PRIMARY`); +LOAD INDEX INTO CACHE t1 PARTITION (`p1`,p0) KEY (`PRIMARY`) IGNORE LEAVES; +LOAD INDEX INTO CACHE t1 PARTITION (ALL); +--error ER_PARSE_ERROR +LOAD INDEX INTO CACHE t1 PARTITIONS ALL; +LOAD INDEX INTO CACHE t1 PARTITION (p1,`p0`) IGNORE LEAVES; +DROP INDEX `inx_b` on t1; +DROP INDEX `inx_b` on t2; +--error ER_PARTITION_MGMT_ON_NONPARTITIONED +CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache; +CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache; +CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN hot_cache; +CACHE INDEX t1 INDEX (`inx_b`) IN hot_cache; +DROP TABLE t1,t2; + +--echo # +--echo # Bug#12361113: crash when load index into cache +--echo # +--echo # Note that this creates an empty disabled key cache! +SET GLOBAL key_cache_none.key_cache_block_size = 1024; +CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) ) +ENGINE = MYISAM +PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 VALUES (1, 1); +--error ER_UNKNOWN_KEY_CACHE +CACHE INDEX t1 IN key_cache_none; +--error ER_UNKNOWN_KEY_CACHE +CACHE INDEX t1 PARTITION (p0) IN key_cache_none; +--error ER_UNKNOWN_KEY_CACHE +CACHE INDEX t1 PARTITION (p1) IN key_cache_none; +--error ER_UNKNOWN_KEY_CACHE +CACHE INDEX t1 PARTITION (p0) KEY (`b`) IN key_cache_none; +--error ER_UNKNOWN_KEY_CACHE +CACHE INDEX t1 PARTITION (p1) KEY (`b`) IN key_cache_none; +--echo # The bug crashed the server at LOAD INDEX below. Now it will succeed +--echo # since the default cache is used due to CACHE INDEX failed for +--echo # key_cache_none. +LOAD INDEX INTO CACHE t1; +DROP TABLE t1; + + +--echo # Clean up +SET GLOBAL hot_cache.key_buffer_size = 0; +SET GLOBAL warm_cache.key_buffer_size = 0; +SET @@global.cold_cache.key_buffer_size = 0; +SELECT @@global.default.key_buffer_size a, @@global.default.key_cache_block_size b, @@global.default.key_cache_age_threshold c, @@global.default.key_cache_division_limit d; +SELECT @@global.hot_cache.key_buffer_size a, @@global.hot_cache.key_cache_block_size b, @@global.hot_cache.key_cache_age_threshold c, @@global.hot_cache.key_cache_division_limit d; +SELECT @@global.warm_cache.key_buffer_size a, @@global.warm_cache.key_cache_block_size b, @@global.warm_cache.key_cache_age_threshold c, @@global.warm_cache.key_cache_division_limit d; +SELECT @@global.cold_cache.key_buffer_size a, @@global.cold_cache.key_cache_block_size b, @@global.cold_cache.key_cache_age_threshold c, @@global.cold_cache.key_cache_division_limit d; +--disable_warnings +SET @@global.default.key_buffer_size = @org_key_cache_buffer_size; +--enable_warnings |