diff options
Diffstat (limited to 'mysql-test/main/key_cache.test')
| -rw-r--r-- | mysql-test/main/key_cache.test | 540 |
1 files changed, 540 insertions, 0 deletions
diff --git a/mysql-test/main/key_cache.test b/mysql-test/main/key_cache.test new file mode 100644 index 00000000000..d28e7c23a4c --- /dev/null +++ b/mysql-test/main/key_cache.test @@ -0,0 +1,540 @@ +# +# Test of multiple key caches, simple and segmented +# +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings + +SET @save_key_buffer_size=@@key_buffer_size; +SET @save_key_cache_block_size=@@key_cache_block_size; +SET @save_key_cache_segments=@@key_cache_segments; +SET @save_key_cache_file_hash_size=@@key_cache_file_hash_size; + +SELECT @@key_buffer_size, @@small.key_buffer_size; + +# Change default key cache size +SET @@global.key_buffer_size=16*1024*1024; +SET @@global.default.key_buffer_size=16*1024*1024; +SET @@global.default.key_buffer_size=16*1024*1024; + +SET @@global.small.key_buffer_size=1*1024*1024; +SET @@global.medium.key_buffer_size=4*1024*1024; +# Drop buffer +SET @@global.medium.key_buffer_size=0; +# Test double drop +SET @@global.medium.key_buffer_size=0; + +# Print key buffer with different syntaxes +SHOW VARIABLES like "key_buffer_size"; +SELECT @@key_buffer_size; +SELECT @@global.key_buffer_size; +SELECT @@global.default.key_buffer_size; +SELECT @@global.default.`key_buffer_size`; +SELECT @@global.`default`.`key_buffer_size`; +SELECT @@`default`.key_buffer_size; + +SELECT @@small.key_buffer_size; +SELECT @@medium.key_buffer_size; + +SET @@global.key_buffer_size=@save_key_buffer_size; + +# +# Errors +# + +--error 1064 +SELECT @@default.key_buffer_size; +--error 1272 +SELECT @@skr.storage_engine="test"; + +select @@keycache1.key_cache_block_size; +select @@keycache1.key_buffer_size; +set global keycache1.key_cache_block_size=2048; +select @@keycache1.key_buffer_size; +select @@keycache1.key_cache_block_size; +set global keycache1.key_buffer_size=1*1024*1024; +select @@keycache1.key_buffer_size; +select @@keycache1.key_cache_block_size; +set global keycache2.key_buffer_size=4*1024*1024; +select @@keycache2.key_buffer_size; +select @@keycache2.key_cache_block_size; +set global keycache1.key_buffer_size=0; +select @@keycache1.key_buffer_size; +select @@keycache1.key_cache_block_size; +select @@key_buffer_size; +select @@key_cache_block_size; +select @@key_cache_file_hash_size; + +set global keycache1.key_buffer_size=1024*1024; + +let org_key_blocks_unused=`select unused_blocks as unused from information_schema.key_caches where key_cache_name="default"`; +--disable_query_log +eval set @org_key_blocks_unused=$org_key_blocks_unused; +--enable_query_log + +create table t1 (p int primary key, a char(10)) delay_key_write=1; +create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a)); + +select @org_key_blocks_unused-unused_blocks as key_blocks_unused, used_blocks as key_blocks_used from information_schema.key_caches where key_cache_name="default"; + +insert into t1 values (1, 'qqqq'), (11, 'yyyy'); +insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), + (3, 1, 'yyyy'), (4, 3, 'zzzz'); +select * from t1; +select * from t2; + +update t1 set p=2 where p=1; +update t2 set i=2 where i=1; + +select @org_key_blocks_unused-unused_blocks as key_blocks_unused, used_blocks as key_blocks_used from information_schema.key_caches where key_cache_name="default"; + +cache index t1 key (`primary`) in keycache1; + +explain select p from t1; +select p from t1; +explain select i from t2; +select i from t2; +explain select count(*) from t1, t2 where t1.p = t2.i; +select count(*) from t1, t2 where t1.p = t2.i; + +cache index t2 in keycache1; +update t2 set p=p+1000, i=2 where a='qqqq'; +cache index t2 in keycache2; +insert into t2 values (2000, 3, 'yyyy'); +cache index t2 in keycache1; +update t2 set p=3000 where a='zzzz'; +select * from t2; +explain select p from t2; +select p from t2; +explain select i from t2; +select i from t2; +explain select a from t2; +select a from t2; + +# Test some error conditions +--error 1284 +cache index t1 in unknown_key_cache; +cache index t1 key (unknown_key) in keycache1; + +select @@keycache2.key_buffer_size; +select @@keycache2.key_cache_block_size; +set global keycache2.key_buffer_size=0; +select @@keycache2.key_buffer_size; +select @@keycache2.key_cache_block_size; +set global keycache2.key_buffer_size=1024*1024; +select @@keycache2.key_buffer_size; + +update t2 set p=4000 where a='zzzz'; +update t1 set p=p+1; + +set global keycache1.key_buffer_size=0; +select * from t2; +select p from t2; +explain select i from t2; +select i from t2; +explain select a from t2; +select a from t2; + +select * from t1; +select p from t1; + +# Use the 'small' key cache +create table t3 (like t1); +cache index t3 in small; +insert into t3 select * from t1; +cache index t3 in keycache2; +cache index t1,t2 in default; +drop table t1,t2,t3; + +select @org_key_blocks_unused-unused_blocks as key_blocks_unused, used_blocks as key_blocks_used from information_schema.key_caches where key_cache_name="default"; + +create table t1 (a int primary key); +cache index t1 in keycache2; +insert t1 values (1),(2),(3),(4),(5),(6),(7),(8); +# delete keycache2, t1 is reassigned to default +set global keycache2.key_buffer_size=0; +select * from t1; +drop table t1; + +# Test to set up a too small size for a key cache (bug #2064) +set global keycache3.key_buffer_size=100; +set global keycache3.key_buffer_size=0; + +# Test case for bug 6447 + +create table t1 (mytext text, FULLTEXT (mytext)); +insert t1 values ('aaabbb'); + +check table t1; +set @my_key_cache_block_size= @@global.key_cache_block_size; +set GLOBAL key_cache_block_size=2048; +check table t1; +drop table t1; +# Restore the changed variable value +set global key_cache_block_size= @my_key_cache_block_size; + +# +# Bug #19079: corrupted index when key_cache_block_size is not multiple of +# myisam_block_size + +CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY); +SET @my_key_cache_block_size= @@global.key_cache_block_size; +SET GLOBAL key_cache_block_size=1536; +INSERT INTO t1 VALUES (1); +SELECT @@key_cache_block_size; +CHECK TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int); +CREATE TABLE t2(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int); +SET GLOBAL key_cache_block_size=1536; +INSERT INTO t1 VALUES (1,0); +INSERT INTO t2(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t2; +INSERT INTO t2(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t2; +INSERT INTO t2(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t2; +INSERT INTO t2(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t2; +INSERT INTO t2(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t2; +INSERT INTO t2(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t2; +INSERT INTO t2(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t2; +INSERT INTO t2(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t2; +INSERT INTO t2(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t2; +SELECT COUNT(*) FROM t1; +SELECT @@key_cache_block_size; +CHECK TABLE t1; +DROP TABLE t1,t2; +# Restore changed variables +set global key_cache_block_size= @my_key_cache_block_size; + +# +# Bug#10473 - Can't set 'key_buffer_size' system variable to ZERO +# (One cannot drop the default key cache.) +# +--error ER_WARN_CANT_DROP_DEFAULT_KEYCACHE +set @@global.key_buffer_size=0; +select @@global.key_buffer_size; + +# +# Bug#28478 - Improper key_cache_block_size corrupts MyISAM tables +# +SET @bug28478_key_cache_block_size= @@global.key_cache_block_size; +SET GLOBAL key_cache_block_size= 1536; +CREATE TABLE t1 ( + id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c1 CHAR(150), + c2 CHAR(150), + c3 CHAR(150), + KEY(c1, c2, c3) + ) ENGINE= MyISAM; +INSERT INTO t1 (c1, c2, c3) VALUES + ('a', 'b', 'c'), ('b', 'c', 'd'), ('c', 'd', 'e'), ('d', 'e', 'f'), + ('e', 'f', 'g'), ('f', 'g', 'h'), ('g', 'h', 'i'), ('h', 'i', 'j'), + ('i', 'j', 'k'), ('j', 'k', 'l'), ('k', 'l', 'm'), ('l', 'm', 'n'), + ('m', 'n', 'o'), ('n', 'o', 'p'), ('o', 'p', 'q'), ('p', 'q', 'r'), + ('q', 'r', 's'), ('r', 's', 't'), ('s', 't', 'u'), ('t', 'u', 'v'), + ('u', 'v', 'w'), ('v', 'w', 'x'), ('w', 'x', 'y'), ('x', 'y', 'z'); +INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; +INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; +INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; +CHECK TABLE t1; +SHOW VARIABLES LIKE 'key_cache_block_size'; +SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size; +DROP TABLE t1; + +# End of 4.1 tests + +--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; +INSERT INTO t1 VALUES (1, 1); +--error ER_UNKNOWN_KEY_CACHE +CACHE INDEX t1 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; + +# End of 5.1 tests + +# +# Test cases for segmented key caches +# + +# Test usage of the KEY_CACHE table from information schema +# for a simple key cache + +set global key_buffer_size=@save_key_buffer_size; +set global key_cache_block_size=@save_key_cache_block_size; +select @@key_buffer_size; +select @@key_cache_block_size; +select @@key_cache_segments; + +create table t1 ( + p int not null auto_increment primary key, + a char(10)); +create table t2 ( + p int not null auto_increment primary key, + i int, a char(10), key k1(i), key k2(a)); + +select @@key_cache_segments; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +insert into t1 values (1, 'qqqq'), (2, 'yyyy'); +insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), + (3, 1, 'yyyy'), (4, 3, 'zzzz'); +select * from t1; +select * from t2; +update t1 set p=3 where p=1; +update t2 set i=2 where i=1; + +select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused'; +select variable_value into @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +delete from t2 where a='zzzz'; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +delete from t1; +delete from t2; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +# For the key cache with 2 segments execute the same sequence of +# statements as for the simple cache above. +# The statistical information on the number of i/o requests and +# the number of is expected to be the same. + +set global key_cache_segments=2; +select @@key_cache_segments; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +insert into t1 values (1, 'qqqq'), (2, 'yyyy'); +insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), + (3, 1, 'yyyy'), (4, 3, 'zzzz'); +select * from t1; +select * from t2; +update t1 set p=3 where p=1; +update t2 set i=2 where i=1; + + +select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused'; +select variable_value < @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +delete from t1; +delete from t2; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +# Check that we can work with one segment with the same results + +set global key_cache_segments=1; +select @@key_cache_segments; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +insert into t1 values (1, 'qqqq'), (2, 'yyyy'); +insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), + (3, 1, 'yyyy'), (4, 3, 'zzzz'); +select * from t1; +select * from t2; +update t1 set p=3 where p=1; +update t2 set i=2 where i=1; + +select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused'; +select variable_value = @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +delete from t1; +delete from t2; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +flush tables; flush status; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +# Switch back to 2 segments + +set global key_buffer_size=32*1024; +set global key_cache_file_hash_size=128; +select @@key_buffer_size; +set global key_cache_segments=2; +select @@key_cache_segments; +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +insert into t1 values (1, 'qqqq'), (2, 'yyyy'); +insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), + (3, 1, 'yyyy'), (4, 3, 'zzzz'); +select * from t1; +select * from t2; +update t1 set p=3 where p=1; +update t2 set i=2 where i=1; + +--replace_column 7 # +select * from information_schema.key_caches where segment_number is null; + +# Add more rows to tables t1 and t2 + +insert into t1(a) select a from t1; +insert into t1(a) select a from t1; +insert into t1(a) select a from t1; +insert into t1(a) select a from t1; +insert into t1(a) select a from t1; +insert into t1(a) select a from t1; +insert into t1(a) select a from t1; +insert into t1(a) select a from t1; + +insert into t2(i,a) select i,a from t2; +insert into t2(i,a) select i,a from t2; +insert into t2(i,a) select i,a from t2; +insert into t2(i,a) select i,a from t2; +insert into t2(i,a) select i,a from t2; +insert into t2(i,a) select i,a from t2; +insert into t2(i,a) select i,a from t2; +insert into t2(i,a) select i,a from t2; + +--replace_column 6 # 7 # 10 # +select * from information_schema.key_caches where segment_number is null; + +select * from t1 where p between 1010 and 1020 ; +select * from t2 where p between 1010 and 1020 ; +--replace_column 6 # 7 # 10 # +select * from information_schema.key_caches where segment_number is null; + +flush tables; flush status; +update t1 set a='zzzz' where a='qqqq'; +update t2 set i=1 where i=2; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +# Now test how we can work with 7 segments + +set global keycache1.key_buffer_size=256*1024; +select @@keycache1.key_buffer_size; +set global keycache1.key_cache_segments=7; +select @@keycache1.key_cache_segments; + +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; +--replace_column 7 # +select * from information_schema.key_caches where key_cache_name like "key%" + and segment_number is null; + +cache index t1 key (`primary`) in keycache1; + +explain select p from t1 where p between 1010 and 1020; +select p from t1 where p between 1010 and 1020; +explain select i from t2 where p between 1010 and 1020; +select i from t2 where p between 1010 and 1020; +explain select count(*) from t1, t2 where t1.p = t2.i; +select count(*) from t1, t2 where t1.p = t2.i; + +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; +--replace_column 7 # +select * from information_schema.key_caches where key_cache_name like "key%" + and segment_number is null; + +cache index t2 in keycache1; +update t2 set p=p+3000, i=2 where a='qqqq'; +--replace_column 7 # +select * from information_schema.key_caches where key_cache_name like "key%" + and segment_number is null; + +set global keycache2.key_buffer_size=1024*1024; +cache index t2 in keycache2; +insert into t2 values (2000, 3, 'yyyy'); +--replace_column 7 # +select * from information_schema.key_caches where key_cache_name like "keycache2" + and segment_number is null; +--replace_column 7 # +select * from information_schema.key_caches where key_cache_name like "key%" + and segment_number is null; + +cache index t2 in keycache1; +update t2 set p=p+5000 where a='zzzz'; +select * from t2 where p between 1010 and 1020; +explain select p from t2 where p between 1010 and 1020; +select p from t2 where p between 1010 and 1020; +explain select i from t2 where a='yyyy' and i=3; +select i from t2 where a='yyyy' and i=3; +explain select a from t2 where a='yyyy' and i=3; +select a from t2 where a='yyyy' and i=3 ; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +set global keycache1.key_cache_block_size=2*1024; +insert into t2 values (7000, 3, 'yyyy'); +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +set global keycache1.key_cache_block_size=8*1024; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; +insert into t2 values (8000, 3, 'yyyy'); +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +set global keycache1.key_buffer_size=64*1024; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +set global keycache1.key_cache_block_size=2*1024; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +set global keycache1.key_cache_block_size=8*1024; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +set global keycache1.key_buffer_size=0; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +set global keycache1.key_cache_block_size=8*1024; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +set global keycache1.key_buffer_size=0; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +set global keycache1.key_buffer_size=128*1024; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +set global keycache1.key_cache_block_size=1024; +--replace_column 6 # 7 # +select * from information_schema.key_caches where segment_number is null; + +drop table t1,t2; + +set global keycache1.key_buffer_size=0; +set global keycache2.key_buffer_size=0; + +set global key_buffer_size=@save_key_buffer_size; +set global key_cache_segments=@save_key_cache_segments; +set global key_cache_file_hash_size=@save_key_cache_file_hash_size; + +# End of 5.2 tests |