1# Test of key cache with partitions 2--source include/have_partition.inc 3 4--disable_warnings 5DROP TABLE IF EXISTS t1, t2, v, x; 6--enable_warnings 7 8--echo # Actual test of key caches 9--echo # Verifing that reads/writes use the key cache correctly 10SET @org_key_cache_buffer_size= @@global.default.key_buffer_size; 11--echo # Minimize default key cache (almost disabled). 12SET @@global.default.key_buffer_size = 4096; 13CREATE TABLE t1 ( 14 a INT, 15 b INT, 16 c INT NOT NULL, 17 PRIMARY KEY (a), 18 KEY `inx_b` (b)) 19PARTITION BY RANGE (a) 20SUBPARTITION BY HASH (a) 21(PARTITION p0 VALUES LESS THAN (1167602410) 22 (SUBPARTITION sp0, 23 SUBPARTITION sp1), 24 PARTITION p1 VALUES LESS THAN MAXVALUE 25 (SUBPARTITION sp2, 26 SUBPARTITION sp3)); 27CREATE TABLE t2 ( 28 a INT, 29 b INT, 30 c INT NOT NULL, 31 PRIMARY KEY (a), 32 KEY `inx_b` (b)); 33FLUSH TABLES; 34FLUSH STATUS; 35 36# Genereate 4096 rows. Idea from: 37# http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html 38SET @a:=1167602400; 39CREATE VIEW v AS SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4; 40CREATE VIEW x AS SELECT 1 FROM v,v a,v b; 41# due to I_S performance, this was substituted with include files which 42# uses SHOW STATUS 43#DELIMITER |; 44#CREATE PROCEDURE was_zero_reads() 45#BEGIN 46# SELECT IF(VARIABLE_VALUE = 0,"Yes!","No!") as 'Was zero reads?' 47# FROM INFORMATION_SCHEMA.SESSION_STATUS 48# WHERE VARIABLE_NAME = 'KEY_READS'; 49# FLUSH STATUS; 50#END| 51#DELIMITER ;| 52 53FLUSH STATUS; 54INSERT t1 SELECT @a, @a * (1 - ((@a % 2) * 2)) , 1167612400 - (@a:=@a+1) FROM x, x y; 55--source include/check_key_req.inc 56--echo # row distribution: 57SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' and TABLE_NAME='t1'; 58DROP VIEW x; 59DROP VIEW v; 60FLUSH TABLES; 61FLUSH STATUS; 62SELECT COUNT(b) FROM t1 WHERE b >= 0; 63--source include/check_key_reads.inc 64INSERT t2 SELECT a,b,c FROM t1; 65--source include/check_key_req.inc 66FLUSH STATUS; 67SELECT COUNT(b) FROM t2 WHERE b >= 0; 68--source include/check_key_reads.inc 69FLUSH TABLES; 70--echo # Setting the default key cache to 1M 71SET GLOBAL key_buffer_size = 1024*1024; 72FLUSH STATUS; 73--echo # All these have to read the indexes 74LOAD INDEX INTO CACHE t1 PARTITION (p1); 75--source include/check_key_reads.inc 76SELECT COUNT(b) FROM t1 WHERE b >= 0; 77--source include/check_key_reads.inc 78SELECT COUNT(b) FROM t2 WHERE b >= 0; 79--source include/check_key_reads.inc 80--echo # All these should be able to use the key cache 81SELECT COUNT(b) FROM t1 WHERE b >= 0; 82--source include/check_key_reads.inc 83SELECT COUNT(b) FROM t2 WHERE b >= 0; 84--source include/check_key_reads.inc 85FLUSH TABLES; 86LOAD INDEX INTO CACHE t1 PARTITION (p1,p0); 87--source include/check_key_reads.inc 88--echo # should not be zero 89SELECT COUNT(b) FROM t1 WHERE b >= 0; 90--source include/check_key_reads.inc 91LOAD INDEX INTO CACHE t2; 92--source include/check_key_reads.inc 93--echo # should not be zero 94SELECT COUNT(b) FROM t2 WHERE b >= 0; 95--source include/check_key_reads.inc 96FLUSH TABLES; 97LOAD INDEX INTO CACHE t1 PARTITION (p1,p0) IGNORE LEAVES; 98--source include/check_key_reads.inc 99--echo # should not be zero 100SELECT COUNT(b) FROM t1 WHERE b >= 0; 101--source include/check_key_reads.inc 102LOAD INDEX INTO CACHE t2 IGNORE LEAVES; 103--source include/check_key_reads.inc 104--echo # should not be zero 105SELECT COUNT(b) FROM t2 WHERE b >= 0; 106--source include/check_key_reads.inc 107TRUNCATE TABLE t2; 108INSERT t2 SELECT a,b,c FROM t1; 109--source include/check_key_req.inc 110DROP TABLE t1,t2; 111 112SET GLOBAL hot_cache.key_buffer_size = 1024*1024; 113SET GLOBAL warm_cache.key_buffer_size = 1024*1024; 114SET @@global.cold_cache.key_buffer_size = 1024*1024; 115SELECT @@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; 116SELECT @@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; 117SELECT @@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; 118SELECT @@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; 119CREATE TABLE t1 ( 120 a INT, 121 b VARCHAR(257), 122 c INT NOT NULL, 123 PRIMARY KEY (a), 124 KEY `inx_b` (b), 125 KEY `inx_c`(c)) 126PARTITION BY RANGE (a) 127SUBPARTITION BY HASH (a) 128(PARTITION p0 VALUES LESS THAN (10) 129 (SUBPARTITION sp0, 130 SUBPARTITION sp1), 131 PARTITION p1 VALUES LESS THAN MAXVALUE 132 (SUBPARTITION sp2, 133 SUBPARTITION sp3)); 134CREATE TABLE t2 ( 135 a INT, 136 b VARCHAR(257), 137 c INT NOT NULL, 138 PRIMARY KEY (a), 139 KEY `inx_b` (b), 140 KEY `inx_c`(c)); 141SET @a:=1167602400; 142# Genereate 4096 rows. Idea from: 143# http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html 144CREATE VIEW v AS SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4; 145CREATE VIEW x AS SELECT 1 FROM v,v a,v b; 146INSERT t1 SELECT @a, CONCAT('X_', @a, ' MySQL'), 1167612400 - (@a:=@a+1) FROM x, x a; 147DROP VIEW x; 148DROP VIEW v; 149INSERT t2 SELECT a, b, c FROM t1; 150SELECT COUNT(*) FROM t1; 151SELECT COUNT(*) FROM t2; 152FLUSH TABLES; 153 154--echo # Restrict partitioned commands to partitioned tables only 155--error ER_PARTITION_MGMT_ON_NONPARTITIONED 156CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache; 157--error ER_PARTITION_MGMT_ON_NONPARTITIONED 158CACHE INDEX t2 PARTITION (p0,`p1`) INDEX (`PRIMARY`) IN hot_cache; 159--error ER_PARTITION_MGMT_ON_NONPARTITIONED 160CACHE INDEX t2 PARTITION (`p1`) INDEX (`PRIMARY`,`inx_b`) IN hot_cache; 161--error ER_PARTITION_MGMT_ON_NONPARTITIONED 162CACHE INDEX t2 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN hot_cache; 163--echo # Basic key cache testing 164--echo # The manual correctly says: "The syntax of CACHE INDEX enables you to 165--echo # specify that only particular indexes from a table should be assigned 166--echo # to the cache. The current implementation assigns all the table's 167--echo # indexes to the cache, so there is no reason to specify anything 168--echo # other than the table name." 169--echo # So the most of the test only tests the syntax 170CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache; 171CACHE INDEX t2 KEY (`PRIMARY`) IN warm_cache; 172CACHE INDEX t2 KEY (`PRIMARY`,`inx_b`) IN cold_cache; 173CACHE INDEX t2 INDEX (inx_b,`PRIMARY`) IN default; 174CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN cold_cache; 175--error ER_PARSE_ERROR 176CACHE INDEX t1 PARTITIONS (p0) KEY (`inx_b`) IN cold_cache; 177--echo # only one table at a time if specifying partitions 178--error ER_PARSE_ERROR 179CACHE INDEX t1,t2 PARTITION (p0) KEY (`inx_b`) IN cold_cache; 180CACHE INDEX t1 PARTITION (`p0`,p1) INDEX (`PRIMARY`) IN warm_cache; 181CACHE INDEX t1 PARTITION (`p1`) INDEX (`PRIMARY`,inx_b) IN hot_cache; 182CACHE INDEX t1 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN default; 183CACHE INDEX t1 PARTITION (ALL) IN hot_cache; 184CACHE INDEX t1 INDEX (`inx_b`) IN default; 185CACHE INDEX t1 KEY (`PRIMARY`) IN hot_cache; 186CACHE INDEX t1 KEY (`PRIMARY`,`inx_b`) IN warm_cache; 187CACHE INDEX t1 INDEX (`inx_b`,`PRIMARY`) IN cold_cache; 188CACHE INDEX t1 IN hot_cache; 189--echo # Test of non existent key cache: 190--error ER_UNKNOWN_KEY_CACHE 191CACHE INDEX t1 IN non_existent_key_cache; 192--echo # Basic testing of LOAD INDEX 193LOAD INDEX INTO CACHE t2; 194--echo # PRIMARY and secondary keys have different block sizes 195LOAD INDEX INTO CACHE t2 ignore leaves; 196--echo # Must have INDEX or KEY before the index list 197--error ER_PARSE_ERROR 198LOAD INDEX INTO CACHE t2 (`PRIMARY`); 199 200--echo # Test of IGNORE LEAVES 201LOAD INDEX INTO CACHE t2 INDEX (`PRIMARY`); 202LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`) IGNORE LEAVES; 203CACHE INDEX t2 IN warm_cache; 204CACHE INDEX t1 IN cold_cache; 205LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`) IGNORE LEAVES; 206CACHE INDEX t2 INDEX (`inx_b`, `inx_c`) IN hot_cache; 207LOAD INDEX INTO CACHE t2 KEY (`inx_b`, `inx_c`) IGNORE LEAVES; 208CACHE INDEX t2 IN warm_cache; 209CACHE INDEX t2 INDEX (`PRIMARY`, `inx_c`) IN hot_cache; 210LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_c`) IGNORE LEAVES; 211CACHE INDEX t2 INDEX (`inx_b`,`PRIMARY`) IN default; 212LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`); 213CACHE INDEX t2 IN default; 214LOAD INDEX INTO CACHE t2 IGNORE LEAVES; 215 216--error ER_PARTITION_MGMT_ON_NONPARTITIONED 217LOAD INDEX INTO CACHE t2 PARTITION (p1) INDEX (`PRIMARY`); 218LOAD INDEX INTO CACHE t1, t2; 219--echo # only one table at a time if specifying partitions 220--error ER_PARSE_ERROR 221LOAD INDEX INTO CACHE t1 PARTITION (p0), t2; 222LOAD INDEX INTO CACHE t1 IGNORE LEAVES; 223LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`); 224LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`) IGNORE LEAVES; 225LOAD INDEX INTO CACHE t1 INDEX (`inx_b`) IGNORE LEAVES; 226LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`) IGNORE LEAVES; 227LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`); 228LOAD INDEX INTO CACHE t1 PARTITION (p1) INDEX (`PRIMARY`); 229LOAD INDEX INTO CACHE t1 PARTITION (`p1`,p0) KEY (`PRIMARY`) IGNORE LEAVES; 230LOAD INDEX INTO CACHE t1 PARTITION (ALL); 231--error ER_PARSE_ERROR 232LOAD INDEX INTO CACHE t1 PARTITIONS ALL; 233LOAD INDEX INTO CACHE t1 PARTITION (p1,`p0`) IGNORE LEAVES; 234DROP INDEX `inx_b` on t1; 235DROP INDEX `inx_b` on t2; 236--error ER_PARTITION_MGMT_ON_NONPARTITIONED 237CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache; 238CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache; 239CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN hot_cache; 240CACHE INDEX t1 INDEX (`inx_b`) IN hot_cache; 241DROP TABLE t1,t2; 242 243--echo # 244--echo # Bug#12361113: crash when load index into cache 245--echo # 246--echo # Note that this creates an empty disabled key cache! 247SET GLOBAL key_cache_none.key_cache_block_size = 1024; 248CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) ) 249ENGINE = MYISAM 250PARTITION BY HASH(a) PARTITIONS 2; 251INSERT INTO t1 VALUES (1, 1); 252--error ER_UNKNOWN_KEY_CACHE 253CACHE INDEX t1 IN key_cache_none; 254--error ER_UNKNOWN_KEY_CACHE 255CACHE INDEX t1 PARTITION (p0) IN key_cache_none; 256--error ER_UNKNOWN_KEY_CACHE 257CACHE INDEX t1 PARTITION (p1) IN key_cache_none; 258--error ER_UNKNOWN_KEY_CACHE 259CACHE INDEX t1 PARTITION (p0) KEY (`b`) IN key_cache_none; 260--error ER_UNKNOWN_KEY_CACHE 261CACHE INDEX t1 PARTITION (p1) KEY (`b`) IN key_cache_none; 262--echo # The bug crashed the server at LOAD INDEX below. Now it will succeed 263--echo # since the default cache is used due to CACHE INDEX failed for 264--echo # key_cache_none. 265LOAD INDEX INTO CACHE t1; 266DROP TABLE t1; 267 268 269--echo # Clean up 270SET GLOBAL hot_cache.key_buffer_size = 0; 271SET GLOBAL warm_cache.key_buffer_size = 0; 272SET @@global.cold_cache.key_buffer_size = 0; 273SELECT @@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; 274SELECT @@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; 275SELECT @@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; 276SELECT @@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; 277--disable_warnings 278SET @@global.default.key_buffer_size = @org_key_cache_buffer_size; 279--enable_warnings 280