Edit File: partition_key_cache.result
DROP TABLE IF EXISTS t1, t2, v, x; # Actual test of key caches # Verifing that reads/writes use the key cache correctly SELECT @org_key_cache_buffer_size:= @@global.default.key_buffer_size; @org_key_cache_buffer_size:= @@global.default.key_buffer_size 1048576 # 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; 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; FLUSH STATUS; INSERT t1 SELECT @a, @a * (1 - ((@a % 2) * 2)) , 1167612400 - (@a:=@a+1) FROM x, x y; reads vs requests reads == requests writes vs requests writes == requests # row distribution: SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' and TABLE_NAME='t1'; PARTITION_NAME SUBPARTITION_NAME TABLE_ROWS p0 sp0 5 p0 sp1 5 p1 sp2 2043 p1 sp3 2043 DROP VIEW x; DROP VIEW v; FLUSH TABLES; FLUSH STATUS; SELECT COUNT(b) FROM t1 WHERE b >= 0; COUNT(b) 2048 Zero key reads? No! INSERT t2 SELECT a,b,c FROM t1; reads vs requests reads == requests writes vs requests writes == requests FLUSH STATUS; SELECT COUNT(b) FROM t2 WHERE b >= 0; COUNT(b) 2048 Zero key reads? No! FLUSH TABLES; # Setting the default key cache to 1M SET GLOBAL key_buffer_size = 1024*1024; FLUSH STATUS; # All these have to read the indexes LOAD INDEX INTO CACHE t1 PARTITION (p1); Table Op Msg_type Msg_text test.t1 preload_keys status OK Zero key reads? No! SELECT COUNT(b) FROM t1 WHERE b >= 0; COUNT(b) 2048 Zero key reads? No! SELECT COUNT(b) FROM t2 WHERE b >= 0; COUNT(b) 2048 Zero key reads? No! # All these should be able to use the key cache SELECT COUNT(b) FROM t1 WHERE b >= 0; COUNT(b) 2048 Zero key reads? Yes! SELECT COUNT(b) FROM t2 WHERE b >= 0; COUNT(b) 2048 Zero key reads? Yes! FLUSH TABLES; LOAD INDEX INTO CACHE t1 PARTITION (p1,p0); Table Op Msg_type Msg_text test.t1 preload_keys status OK Zero key reads? No! # should not be zero SELECT COUNT(b) FROM t1 WHERE b >= 0; COUNT(b) 2048 Zero key reads? Yes! LOAD INDEX INTO CACHE t2; Table Op Msg_type Msg_text test.t2 preload_keys status OK Zero key reads? No! # should not be zero SELECT COUNT(b) FROM t2 WHERE b >= 0; COUNT(b) 2048 Zero key reads? Yes! FLUSH TABLES; LOAD INDEX INTO CACHE t1 PARTITION (p1,p0) IGNORE LEAVES; Table Op Msg_type Msg_text test.t1 preload_keys status OK Zero key reads? No! # should not be zero SELECT COUNT(b) FROM t1 WHERE b >= 0; COUNT(b) 2048 Zero key reads? No! LOAD INDEX INTO CACHE t2 IGNORE LEAVES; Table Op Msg_type Msg_text test.t2 preload_keys status OK Zero key reads? No! # should not be zero SELECT COUNT(b) FROM t2 WHERE b >= 0; COUNT(b) 2048 Zero key reads? No! TRUNCATE TABLE t2; INSERT t2 SELECT a,b,c FROM t1; reads vs requests reads != requests writes vs requests writes != requests 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; a b c d 1048576 1024 300 100 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; a b c d 1048576 1024 300 100 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; a b c d 1048576 1024 300 100 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; a b c d 1048576 1024 300 100 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; 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; COUNT(*) 4096 SELECT COUNT(*) FROM t2; COUNT(*) 4096 FLUSH TABLES; # Restrict partitioned commands to partitioned tables only CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache; ERROR HY000: Partition management on a not partitioned table is not possible CACHE INDEX t2 PARTITION (p0,`p1`) INDEX (`PRIMARY`) IN hot_cache; ERROR HY000: Partition management on a not partitioned table is not possible CACHE INDEX t2 PARTITION (`p1`) INDEX (`PRIMARY`,`inx_b`) IN hot_cache; ERROR HY000: Partition management on a not partitioned table is not possible CACHE INDEX t2 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN hot_cache; ERROR HY000: Partition management on a not partitioned table is not possible # Basic key cache testing # The manual correctly says: "The syntax of CACHE INDEX enables you to # specify that only particular indexes from a table should be assigned # to the cache. The current implementation assigns all the table's # indexes to the cache, so there is no reason to specify anything # other than the table name." # So the most of the test only tests the syntax CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK CACHE INDEX t2 KEY (`PRIMARY`) IN warm_cache; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK CACHE INDEX t2 KEY (`PRIMARY`,`inx_b`) IN cold_cache; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK CACHE INDEX t2 INDEX (inx_b,`PRIMARY`) IN default; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN cold_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK CACHE INDEX t1 PARTITIONS (p0) KEY (`inx_b`) IN cold_cache; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITIONS (p0) KEY (`inx_b`) IN cold_cache' at line 1 # only one table at a time if specifying partitions CACHE INDEX t1,t2 PARTITION (p0) KEY (`inx_b`) IN cold_cache; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION (p0) KEY (`inx_b`) IN cold_cache' at line 1 CACHE INDEX t1 PARTITION (`p0`,p1) INDEX (`PRIMARY`) IN warm_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK CACHE INDEX t1 PARTITION (`p1`) INDEX (`PRIMARY`,inx_b) IN hot_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK CACHE INDEX t1 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN default; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK CACHE INDEX t1 PARTITION (ALL) IN hot_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK CACHE INDEX t1 INDEX (`inx_b`) IN default; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK CACHE INDEX t1 KEY (`PRIMARY`) IN hot_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK CACHE INDEX t1 KEY (`PRIMARY`,`inx_b`) IN warm_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK CACHE INDEX t1 INDEX (`inx_b`,`PRIMARY`) IN cold_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK CACHE INDEX t1 IN hot_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK # Test of non existent key cache: CACHE INDEX t1 IN non_existent_key_cache; ERROR HY000: Unknown key cache 'non_existent_key_cache' # Basic testing of LOAD INDEX LOAD INDEX INTO CACHE t2; Table Op Msg_type Msg_text test.t2 preload_keys status OK # PRIMARY and secondary keys have different block sizes LOAD INDEX INTO CACHE t2 ignore leaves; Table Op Msg_type Msg_text test.t2 preload_keys error Indexes use different block sizes test.t2 preload_keys status Operation failed # Must have INDEX or KEY before the index list LOAD INDEX INTO CACHE t2 (`PRIMARY`); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(`PRIMARY`)' at line 1 # Test of IGNORE LEAVES LOAD INDEX INTO CACHE t2 INDEX (`PRIMARY`); Table Op Msg_type Msg_text test.t2 preload_keys status OK LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`) IGNORE LEAVES; Table Op Msg_type Msg_text test.t2 preload_keys error Indexes use different block sizes test.t2 preload_keys status Operation failed CACHE INDEX t2 IN warm_cache; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK CACHE INDEX t1 IN cold_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`) IGNORE LEAVES; Table Op Msg_type Msg_text test.t2 preload_keys error Indexes use different block sizes test.t2 preload_keys status Operation failed CACHE INDEX t2 INDEX (`inx_b`, `inx_c`) IN hot_cache; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK LOAD INDEX INTO CACHE t2 KEY (`inx_b`, `inx_c`) IGNORE LEAVES; Table Op Msg_type Msg_text test.t2 preload_keys error Indexes use different block sizes test.t2 preload_keys status Operation failed CACHE INDEX t2 IN warm_cache; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK CACHE INDEX t2 INDEX (`PRIMARY`, `inx_c`) IN hot_cache; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_c`) IGNORE LEAVES; Table Op Msg_type Msg_text test.t2 preload_keys error Indexes use different block sizes test.t2 preload_keys status Operation failed CACHE INDEX t2 INDEX (`inx_b`,`PRIMARY`) IN default; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`); Table Op Msg_type Msg_text test.t2 preload_keys status OK CACHE INDEX t2 IN default; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK LOAD INDEX INTO CACHE t2 IGNORE LEAVES; Table Op Msg_type Msg_text test.t2 preload_keys error Indexes use different block sizes test.t2 preload_keys status Operation failed LOAD INDEX INTO CACHE t2 PARTITION (p1) INDEX (`PRIMARY`); ERROR HY000: Partition management on a not partitioned table is not possible LOAD INDEX INTO CACHE t1, t2; Table Op Msg_type Msg_text test.t1 preload_keys status OK test.t2 preload_keys status OK # only one table at a time if specifying partitions LOAD INDEX INTO CACHE t1 PARTITION (p0), t2; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' t2' at line 1 LOAD INDEX INTO CACHE t1 IGNORE LEAVES; Table Op Msg_type Msg_text test.t1 preload_keys error Indexes use different block sizes test.t1 preload_keys error Subpartition sp2 returned error test.t1 preload_keys status Operation failed LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`); Table Op Msg_type Msg_text test.t1 preload_keys status OK LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`) IGNORE LEAVES; Table Op Msg_type Msg_text test.t1 preload_keys error Indexes use different block sizes test.t1 preload_keys error Subpartition sp2 returned error test.t1 preload_keys status Operation failed LOAD INDEX INTO CACHE t1 INDEX (`inx_b`) IGNORE LEAVES; Table Op Msg_type Msg_text test.t1 preload_keys error Indexes use different block sizes test.t1 preload_keys error Subpartition sp2 returned error test.t1 preload_keys status Operation failed LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`) IGNORE LEAVES; Table Op Msg_type Msg_text test.t1 preload_keys error Indexes use different block sizes test.t1 preload_keys error Subpartition sp2 returned error test.t1 preload_keys status Operation failed LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`); Table Op Msg_type Msg_text test.t1 preload_keys status OK LOAD INDEX INTO CACHE t1 PARTITION (p1) INDEX (`PRIMARY`); Table Op Msg_type Msg_text test.t1 preload_keys status OK LOAD INDEX INTO CACHE t1 PARTITION (`p1`,p0) KEY (`PRIMARY`) IGNORE LEAVES; Table Op Msg_type Msg_text test.t1 preload_keys error Indexes use different block sizes test.t1 preload_keys error Subpartition sp2 returned error test.t1 preload_keys status Operation failed LOAD INDEX INTO CACHE t1 PARTITION (ALL); Table Op Msg_type Msg_text test.t1 preload_keys status OK LOAD INDEX INTO CACHE t1 PARTITIONS ALL; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITIONS ALL' at line 1 LOAD INDEX INTO CACHE t1 PARTITION (p1,`p0`) IGNORE LEAVES; Table Op Msg_type Msg_text test.t1 preload_keys error Indexes use different block sizes test.t1 preload_keys error Subpartition sp2 returned error test.t1 preload_keys status Operation failed DROP INDEX `inx_b` on t1; DROP INDEX `inx_b` on t2; CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache; ERROR HY000: Partition management on a not partitioned table is not possible CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache; Table Op Msg_type Msg_text test.t2 assign_to_keycache Error Key 'inx_b' doesn't exist in table 't2' test.t2 assign_to_keycache status Operation failed CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN hot_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache error Subpartition sp0 returned error test.t1 assign_to_keycache Error Key 'inx_b' doesn't exist in table 't1' test.t1 assign_to_keycache status Operation failed CACHE INDEX t1 INDEX (`inx_b`) IN hot_cache; Table Op Msg_type Msg_text test.t1 assign_to_keycache error Subpartition sp0 returned error test.t1 assign_to_keycache Error Key 'inx_b' doesn't exist in table 't1' test.t1 assign_to_keycache status Operation failed DROP TABLE t1,t2; # # Bug#12361113: crash when load index into cache # # 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); CACHE INDEX t1 IN key_cache_none; ERROR HY000: Unknown key cache 'key_cache_none' CACHE INDEX t1 PARTITION (p0) IN key_cache_none; ERROR HY000: Unknown key cache 'key_cache_none' CACHE INDEX t1 PARTITION (p1) IN key_cache_none; ERROR HY000: Unknown key cache 'key_cache_none' CACHE INDEX t1 PARTITION (p0) KEY (`b`) IN key_cache_none; ERROR HY000: Unknown key cache 'key_cache_none' CACHE INDEX t1 PARTITION (p1) KEY (`b`) IN key_cache_none; ERROR HY000: Unknown key cache 'key_cache_none' # The bug crashed the server at LOAD INDEX below. Now it will succeed # since the default cache is used due to CACHE INDEX failed for # key_cache_none. LOAD INDEX INTO CACHE t1; Table Op Msg_type Msg_text test.t1 preload_keys status OK DROP TABLE t1; # 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; a b c d 1048576 1024 300 100 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; a b c d 0 1024 300 100 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; a b c d 0 1024 300 100 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; a b c d 0 1024 300 100 SET @@global.default.key_buffer_size = @org_key_cache_buffer_size;
Back to File Manager