#At lp:maria/5.2 based on revid:igor@askmonty.org-20100331231003-9gvmv2y8384kbqfa 2746 Igor Babaev 2010-03-31 Made the results of the key_cache test to be platform independent. modified: mysql-test/r/key_cache.result mysql-test/t/key_cache.test === modified file 'mysql-test/r/key_cache.result' --- a/mysql-test/r/key_cache.result 2010-03-31 18:45:41 +0000 +++ b/mysql-test/r/key_cache.result 2010-03-31 23:37:28 +0000 @@ -388,7 +388,7 @@ i int, a char(10), key k1(i), key k2(a)) select @@key_cache_partitions; @@key_cache_partitions 0 -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES default NULL NULL 2097152 1024 0 # 0 0 0 0 0 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 @@ -416,18 +416,18 @@ Key_read_requests 22 Key_reads 0 Key_write_requests 26 Key_writes 6 -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES default NULL NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t2 where a='zzzz'; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES default NULL NULL 2097152 1024 4 # 0 29 0 32 9 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t1; delete from t2; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES default NULL NULL 2097152 1024 4 # 0 29 0 32 9 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 @@ -435,10 +435,8 @@ set global key_cache_partitions=2; select @@key_cache_partitions; @@key_cache_partitions 2 -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 1048576 1024 0 # 0 0 0 0 0 -default 2 2 1048576 1024 0 # 0 0 0 0 0 default 2 NULL 2097152 1024 0 # 0 0 0 0 0 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 insert into t1 values (1, 'qqqq'), (2, 'yyyy'); @@ -465,27 +463,22 @@ Key_read_requests 22 Key_reads 0 Key_write_requests 26 Key_writes 6 -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 1048576 1024 # # 0 10 0 13 4 -default 2 2 1048576 1024 # # 0 12 0 13 2 -default 2 NULL 2097152 1024 # # 0 22 0 26 6 -small NULL NULL 1048576 1024 # # 0 1 0 2 1 +default 2 NULL 2097152 1024 4 # 0 22 0 26 6 +small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t1; delete from t2; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 1048576 1024 # # 0 10 0 13 4 -default 2 2 1048576 1024 # # 0 12 0 13 2 -default 2 NULL 2097152 1024 # # 0 22 0 26 6 -small NULL NULL 1048576 1024 # # 0 1 0 2 1 +default 2 NULL 2097152 1024 4 # 0 22 0 26 6 +small NULL NULL 1048576 1024 1 # 0 1 0 2 1 set global key_cache_partitions=1; select @@key_cache_partitions; @@key_cache_partitions 1 -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 1 1 2097152 1024 0 # 0 0 0 0 0 default 1 NULL 2097152 1024 0 # 0 0 0 0 0 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 insert into t1 values (1, 'qqqq'), (2, 'yyyy'); @@ -512,23 +505,20 @@ Key_read_requests 22 Key_reads 0 Key_write_requests 26 Key_writes 6 -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 1 1 2097152 1024 4 # 0 22 0 26 6 default 1 NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t1; delete from t2; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 1 1 2097152 1024 4 # 0 22 0 26 6 default 1 NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 flush tables; flush status; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 1 1 2097152 1024 4 # 0 0 0 0 0 default 1 NULL 2097152 1024 4 # 0 0 0 0 0 small NULL NULL 1048576 1024 1 # 0 0 0 0 0 set global key_buffer_size=32*1024; @@ -539,10 +529,8 @@ set global key_cache_partitions=2; select @@key_cache_partitions; @@key_cache_partitions 2 -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 0 # 0 0 0 0 0 -default 2 2 16384 1024 0 # 0 0 0 0 0 default 2 NULL 32768 1024 0 # 0 0 0 0 0 small NULL NULL 1048576 1024 1 # 0 0 0 0 0 insert into t1 values (1, 'qqqq'), (2, 'yyyy'); @@ -560,10 +548,8 @@ p i a 4 3 zzzz update t1 set p=3 where p=1; update t2 set i=2 where i=1; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 1 # 0 12 0 13 2 -default 2 2 16384 1024 3 # 0 10 0 13 4 default 2 NULL 32768 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 0 0 0 0 insert into t1(a) select a from t1; @@ -582,10 +568,8 @@ 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; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 1951 # 1976 43 -default 2 2 16384 1024 # # 0 4782 # 1708 60 default 2 NULL 32768 1024 # # 0 6733 # 3684 103 small NULL NULL 1048576 1024 # # 0 0 # 0 0 select * from t1 where p between 1010 and 1020 ; @@ -603,20 +587,16 @@ p i a 1018 2 pppp 1019 2 yyyy 1020 3 zzzz -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 1954 # 1976 43 -default 2 2 16384 1024 # # 0 4796 # 1708 60 default 2 NULL 32768 1024 # # 0 6750 # 3684 103 small NULL NULL 1048576 1024 # # 0 0 # 0 0 flush tables; flush status; update t1 set a='zzzz' where a='qqqq'; update t2 set i=1 where i=2; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 940 10 939 10 -default 2 2 16384 1024 # # 0 2136 8 613 8 default 2 NULL 32768 1024 # # 0 3076 18 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_buffer_size=256*1024; @@ -627,29 +607,14 @@ set global keycache1.key_cache_partition select @@keycache1.key_cache_partitions; @@keycache1.key_cache_partitions 7 -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 940 10 939 10 -default 2 2 16384 1024 # # 0 2136 8 613 8 default 2 NULL 32768 1024 # # 0 3076 18 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 7 1 37449 2048 # # 0 0 0 0 0 -keycache1 7 2 37449 2048 # # 0 0 0 0 0 -keycache1 7 3 37449 2048 # # 0 0 0 0 0 -keycache1 7 4 37449 2048 # # 0 0 0 0 0 -keycache1 7 5 37449 2048 # # 0 0 0 0 0 -keycache1 7 6 37449 2048 # # 0 0 0 0 0 -keycache1 7 7 37449 2048 # # 0 0 0 0 0 keycache1 7 NULL 262143 2048 # # 0 0 0 0 0 -select * from information_schema.key_caches where key_cache_name like "key%"; +select * from information_schema.key_caches where key_cache_name like "key%" + and partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -keycache1 7 1 37449 2048 0 # 0 0 0 0 0 -keycache1 7 2 37449 2048 0 # 0 0 0 0 0 -keycache1 7 3 37449 2048 0 # 0 0 0 0 0 -keycache1 7 4 37449 2048 0 # 0 0 0 0 0 -keycache1 7 5 37449 2048 0 # 0 0 0 0 0 -keycache1 7 6 37449 2048 0 # 0 0 0 0 0 -keycache1 7 7 37449 2048 0 # 0 0 0 0 0 keycache1 7 NULL 262143 2048 0 # 0 0 0 0 0 cache index t1 key (`primary`) in keycache1; Table Op Msg_type Msg_text @@ -682,61 +647,35 @@ id select_type table type possible_keys select count(*) from t1, t2 where t1.p = t2.i; count(*) 256 -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 7 1 37449 2048 # # 0 2 1 0 0 -keycache1 7 2 37449 2048 # # 0 7 1 0 0 -keycache1 7 3 37449 2048 # # 0 0 0 0 0 -keycache1 7 4 37449 2048 # # 0 5 1 0 0 -keycache1 7 5 37449 2048 # # 0 0 0 0 0 -keycache1 7 6 37449 2048 # # 0 0 0 0 0 -keycache1 7 7 37449 2048 # # 0 0 0 0 0 keycache1 7 NULL 262143 2048 # # 0 14 3 0 0 -select * from information_schema.key_caches where key_cache_name like "key%"; +select * from information_schema.key_caches where key_cache_name like "key%" + and partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -keycache1 7 1 37449 2048 1 # 0 2 1 0 0 -keycache1 7 2 37449 2048 1 # 0 7 1 0 0 -keycache1 7 3 37449 2048 0 # 0 0 0 0 0 -keycache1 7 4 37449 2048 1 # 0 5 1 0 0 -keycache1 7 5 37449 2048 0 # 0 0 0 0 0 -keycache1 7 6 37449 2048 0 # 0 0 0 0 0 -keycache1 7 7 37449 2048 0 # 0 0 0 0 0 keycache1 7 NULL 262143 2048 3 # 0 14 3 0 0 cache index t2 in keycache1; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK update t2 set p=p+3000, i=2 where a='qqqq'; -select * from information_schema.key_caches where key_cache_name like "key%"; +select * from information_schema.key_caches where key_cache_name like "key%" + and partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -keycache1 7 1 37449 2048 3 # 0 44 3 43 2 -keycache1 7 2 37449 2048 4 # 0 61 4 51 1 -keycache1 7 3 37449 2048 4 # 0 177 4 176 3 -keycache1 7 4 37449 2048 4 # 0 122 4 119 3 -keycache1 7 5 37449 2048 4 # 0 840 4 335 4 -keycache1 7 6 37449 2048 3 # 0 627 3 133 3 -keycache1 7 7 37449 2048 3 # 0 211 3 214 3 keycache1 7 NULL 262143 2048 25 # 0 2082 25 1071 19 set global keycache2.key_buffer_size=1024*1024; cache index t2 in keycache2; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK insert into t2 values (2000, 3, 'yyyy'); -select * from information_schema.key_caches where key_cache_name like "keycache2"; +select * from information_schema.key_caches where key_cache_name like "keycache2" + and partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES keycache2 NULL NULL 1048576 1024 0 # 0 0 0 0 0 -select * from information_schema.key_caches where key_cache_name like "key%"; +select * from information_schema.key_caches where key_cache_name like "key%" +and partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -keycache1 7 1 37449 2048 3 # 0 44 3 43 2 -keycache1 7 2 37449 2048 4 # 0 61 4 51 1 -keycache1 7 3 37449 2048 4 # 0 177 4 176 3 -keycache1 7 4 37449 2048 4 # 0 122 4 119 3 -keycache1 7 5 37449 2048 4 # 0 840 4 335 4 -keycache1 7 6 37449 2048 3 # 0 627 3 133 3 -keycache1 7 7 37449 2048 3 # 0 211 3 214 3 keycache1 7 NULL 262143 2048 25 # 0 2082 25 1071 19 keycache2 NULL NULL 1048576 1024 0 # 0 0 0 0 0 cache index t2 in keycache1; @@ -774,127 +713,77 @@ id select_type table type possible_keys select a from t2 where a='yyyy' and i=3 ; a yyyy -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 7 1 37449 2048 # # 0 85 6 68 3 -keycache1 7 2 37449 2048 # # 0 122 6 102 2 -keycache1 7 3 37449 2048 # # 0 271 8 254 6 -keycache1 7 4 37449 2048 # # 0 179 6 170 4 -keycache1 7 5 37449 2048 # # 0 1445 7 416 6 -keycache1 7 6 37449 2048 # # 0 863 6 345 5 -keycache1 7 7 37449 2048 # # 0 236 4 239 4 keycache1 7 NULL 262143 2048 # # 0 3201 43 1594 30 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_cache_block_size=2*1024; insert into t2 values (7000, 3, 'yyyy'); -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 7 1 37449 2048 # # 0 1 1 1 1 -keycache1 7 2 37449 2048 # # 0 1 1 0 0 -keycache1 7 3 37449 2048 # # 0 0 0 0 0 -keycache1 7 4 37449 2048 # # 0 1 1 1 1 -keycache1 7 5 37449 2048 # # 0 1 1 0 0 -keycache1 7 6 37449 2048 # # 0 2 2 1 1 -keycache1 7 7 37449 2048 # # 0 0 0 0 0 keycache1 7 NULL 262143 2048 # # 0 6 6 3 3 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_cache_block_size=8*1024; insert into t2 values (8000, 3, 'yyyy'); -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 3 1 87381 8192 # # 0 1 1 1 1 -keycache1 3 2 87381 8192 # # 0 3 2 1 1 -keycache1 3 3 87381 8192 # # 0 2 2 1 1 keycache1 3 NULL 262143 8192 # # 0 6 5 3 3 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_buffer_size=64*1024; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_cache_block_size=2*1024; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 3 1 21845 2048 # # 0 0 0 0 0 -keycache1 3 2 21845 2048 # # 0 0 0 0 0 -keycache1 3 3 21845 2048 # # 0 0 0 0 0 keycache1 3 NULL 65535 2048 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_cache_block_size=8*1024; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_buffer_size=0; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_cache_block_size=8*1024; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_buffer_size=0; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_buffer_size=128*1024; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 1 1 131072 8192 # # 0 0 0 0 0 keycache1 1 NULL 131072 8192 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_cache_block_size=1024; -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; KEY_CACHE_NAME PARTITIONS PARTITION_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 1 16384 1024 # # 0 966 12 939 10 -default 2 2 16384 1024 # # 0 2206 12 613 8 default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 7 1 18724 1024 # # 0 0 0 0 0 -keycache1 7 2 18724 1024 # # 0 0 0 0 0 -keycache1 7 3 18724 1024 # # 0 0 0 0 0 -keycache1 7 4 18724 1024 # # 0 0 0 0 0 -keycache1 7 5 18724 1024 # # 0 0 0 0 0 -keycache1 7 6 18724 1024 # # 0 0 0 0 0 -keycache1 7 7 18724 1024 # # 0 0 0 0 0 keycache1 7 NULL 131068 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 0 0 0 0 drop table t1,t2; === modified file 'mysql-test/t/key_cache.test' --- a/mysql-test/t/key_cache.test 2010-03-31 18:45:41 +0000 +++ b/mysql-test/t/key_cache.test 2010-03-31 23:37:28 +0000 @@ -272,7 +272,7 @@ create table t2 ( select @@key_cache_partitions; --replace_column 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; insert into t1 values (1, 'qqqq'), (2, 'yyyy'); insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), @@ -285,16 +285,16 @@ update t2 set i=2 where i=1; --replace_result 1808 KEY_BLOCKS_UNUSED 1670 KEY_BLOCKS_UNUSED show status like 'key_%'; --replace_column 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; delete from t2 where a='zzzz'; --replace_column 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; delete from t1; delete from t2; --replace_column 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; # For the key cache with 2 partitions execute the same sequence of # statements as for the simple cache above. @@ -304,7 +304,7 @@ select * from information_schema.key_cac set global key_cache_partitions=2; select @@key_cache_partitions; --replace_column 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; insert into t1 values (1, 'qqqq'), (2, 'yyyy'); insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), @@ -316,20 +316,20 @@ update t2 set i=2 where i=1; --replace_result 1808 KEY_BLOCKS_UNUSED 1670 KEY_BLOCKS_UNUSED show status like 'key_%'; ---replace_column 6 # 7 # -select * from information_schema.key_caches; +--replace_column 7 # +select * from information_schema.key_caches where partition_number is null; delete from t1; delete from t2; ---replace_column 6 # 7 # -select * from information_schema.key_caches; +--replace_column 7 # +select * from information_schema.key_caches where partition_number is null; # Check that we can work with one partition with the same results set global key_cache_partitions=1; select @@key_cache_partitions; --replace_column 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; insert into t1 values (1, 'qqqq'), (2, 'yyyy'); insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), @@ -342,16 +342,16 @@ update t2 set i=2 where i=1; --replace_result 1808 KEY_BLOCKS_UNUSED 1670 KEY_BLOCKS_UNUSED show status like 'key_%'; --replace_column 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; delete from t1; delete from t2; --replace_column 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; flush tables; flush status; --replace_column 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; # Switch back to 2 partitions @@ -360,7 +360,7 @@ select @@key_buffer_size; set global key_cache_partitions=2; select @@key_cache_partitions; --replace_column 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; insert into t1 values (1, 'qqqq'), (2, 'yyyy'); insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), @@ -371,7 +371,7 @@ 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; +select * from information_schema.key_caches where partition_number is null; # Add more rows to tables t1 and t2 @@ -394,18 +394,18 @@ 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; +select * from information_schema.key_caches where partition_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; +select * from information_schema.key_caches where partition_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; +select * from information_schema.key_caches where partition_number is null; # Now test how we can work with 7 partitions @@ -415,9 +415,10 @@ set global keycache1.key_cache_partition select @@keycache1.key_cache_partitions; --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; --replace_column 7 # -select * from information_schema.key_caches where key_cache_name like "key%"; +select * from information_schema.key_caches where key_cache_name like "key%" + and partition_number is null; cache index t1 key (`primary`) in keycache1; @@ -429,22 +430,26 @@ explain select count(*) from t1, t2 wher select count(*) from t1, t2 where t1.p = t2.i; --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; --replace_column 7 # -select * from information_schema.key_caches where key_cache_name like "key%"; +select * from information_schema.key_caches where key_cache_name like "key%" + and partition_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%"; +select * from information_schema.key_caches where key_cache_name like "key%" + and partition_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"; +select * from information_schema.key_caches where key_cache_name like "keycache2" + and partition_number is null; --replace_column 7 # -select * from information_schema.key_caches where key_cache_name like "key%"; +select * from information_schema.key_caches where key_cache_name like "key%" + and partition_number is null; cache index t2 in keycache1; update t2 set p=p+5000 where a='zzzz'; @@ -456,49 +461,49 @@ 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; +select * from information_schema.key_caches where partition_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; +select * from information_schema.key_caches where partition_number is null; set global keycache1.key_cache_block_size=8*1024; insert into t2 values (8000, 3, 'yyyy'); --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; set global keycache1.key_buffer_size=64*1024; --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; set global keycache1.key_cache_block_size=2*1024; --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; set global keycache1.key_cache_block_size=8*1024; --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; set global keycache1.key_buffer_size=0; --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; set global keycache1.key_cache_block_size=8*1024; --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; set global keycache1.key_buffer_size=0; --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; set global keycache1.key_buffer_size=128*1024; --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; set global keycache1.key_cache_block_size=1024; --replace_column 6 # 7 # -select * from information_schema.key_caches; +select * from information_schema.key_caches where partition_number is null; drop table t1,t2;