1drop table if exists t1, t2, t3; 2SET @save_key_buffer_size=@@key_buffer_size; 3SET @save_key_cache_block_size=@@key_cache_block_size; 4SET @save_key_cache_segments=@@key_cache_segments; 5SET @save_key_cache_file_hash_size=@@key_cache_file_hash_size; 6SELECT @@key_buffer_size, @@small.key_buffer_size; 7@@key_buffer_size @@small.key_buffer_size 82097152 131072 9SET @@global.key_buffer_size=16*1024*1024; 10SET @@global.default.key_buffer_size=16*1024*1024; 11SET @@global.default.key_buffer_size=16*1024*1024; 12SET @@global.small.key_buffer_size=1*1024*1024; 13SET @@global.medium.key_buffer_size=4*1024*1024; 14SET @@global.medium.key_buffer_size=0; 15SET @@global.medium.key_buffer_size=0; 16SHOW VARIABLES like "key_buffer_size"; 17Variable_name Value 18key_buffer_size 16777216 19SELECT @@key_buffer_size; 20@@key_buffer_size 2116777216 22SELECT @@global.key_buffer_size; 23@@global.key_buffer_size 2416777216 25SELECT @@global.default.key_buffer_size; 26@@global.default.key_buffer_size 2716777216 28SELECT @@global.default.`key_buffer_size`; 29@@global.default.`key_buffer_size` 3016777216 31SELECT @@global.`default`.`key_buffer_size`; 32@@global.`default`.`key_buffer_size` 3316777216 34SELECT @@`default`.key_buffer_size; 35@@`default`.key_buffer_size 3616777216 37SELECT @@small.key_buffer_size; 38@@small.key_buffer_size 391048576 40SELECT @@medium.key_buffer_size; 41@@medium.key_buffer_size 420 43SET @@global.key_buffer_size=@save_key_buffer_size; 44SELECT @@default.key_buffer_size; 45ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'default.key_buffer_size' at line 1 46SELECT @@skr.storage_engine="test"; 47ERROR HY000: Variable 'storage_engine' is not a variable component (can't be used as XXXX.variable_name) 48select @@keycache1.key_cache_block_size; 49@@keycache1.key_cache_block_size 500 51select @@keycache1.key_buffer_size; 52@@keycache1.key_buffer_size 530 54set global keycache1.key_cache_block_size=2048; 55select @@keycache1.key_buffer_size; 56@@keycache1.key_buffer_size 570 58select @@keycache1.key_cache_block_size; 59@@keycache1.key_cache_block_size 602048 61set global keycache1.key_buffer_size=1*1024*1024; 62select @@keycache1.key_buffer_size; 63@@keycache1.key_buffer_size 641048576 65select @@keycache1.key_cache_block_size; 66@@keycache1.key_cache_block_size 672048 68set global keycache2.key_buffer_size=4*1024*1024; 69select @@keycache2.key_buffer_size; 70@@keycache2.key_buffer_size 714194304 72select @@keycache2.key_cache_block_size; 73@@keycache2.key_cache_block_size 741024 75set global keycache1.key_buffer_size=0; 76select @@keycache1.key_buffer_size; 77@@keycache1.key_buffer_size 780 79select @@keycache1.key_cache_block_size; 80@@keycache1.key_cache_block_size 812048 82select @@key_buffer_size; 83@@key_buffer_size 842097152 85select @@key_cache_block_size; 86@@key_cache_block_size 871024 88select @@key_cache_file_hash_size; 89@@key_cache_file_hash_size 90512 91set global keycache1.key_buffer_size=1024*1024; 92create table t1 (p int primary key, a char(10)) delay_key_write=1; 93create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a)); 94select @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"; 95key_blocks_unused key_blocks_used 960 0 97insert into t1 values (1, 'qqqq'), (11, 'yyyy'); 98insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 99(3, 1, 'yyyy'), (4, 3, 'zzzz'); 100select * from t1; 101p a 1021 qqqq 10311 yyyy 104select * from t2; 105p i a 1061 1 qqqq 1072 1 pppp 1083 1 yyyy 1094 3 zzzz 110update t1 set p=2 where p=1; 111update t2 set i=2 where i=1; 112select @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"; 113key_blocks_unused key_blocks_used 1144 4 115cache index t1 key (`primary`) in keycache1; 116Table Op Msg_type Msg_text 117test.t1 assign_to_keycache status OK 118explain select p from t1; 119id select_type table type possible_keys key key_len ref rows Extra 1201 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index 121select p from t1; 122p 1232 12411 125explain select i from t2; 126id select_type table type possible_keys key key_len ref rows Extra 1271 SIMPLE t2 index NULL k1 5 NULL 4 Using index 128select i from t2; 129i 1302 1312 1322 1333 134explain select count(*) from t1, t2 where t1.p = t2.i; 135id select_type table type possible_keys key key_len ref rows Extra 1361 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index 1371 SIMPLE t2 ref k1 k1 5 test.t1.p 2 Using index 138select count(*) from t1, t2 where t1.p = t2.i; 139count(*) 1403 141cache index t2 in keycache1; 142Table Op Msg_type Msg_text 143test.t2 assign_to_keycache status OK 144update t2 set p=p+1000, i=2 where a='qqqq'; 145cache index t2 in keycache2; 146Table Op Msg_type Msg_text 147test.t2 assign_to_keycache status OK 148insert into t2 values (2000, 3, 'yyyy'); 149cache index t2 in keycache1; 150Table Op Msg_type Msg_text 151test.t2 assign_to_keycache status OK 152update t2 set p=3000 where a='zzzz'; 153select * from t2; 154p i a 1551001 2 qqqq 1562 2 pppp 1573 2 yyyy 1583000 3 zzzz 1592000 3 yyyy 160explain select p from t2; 161id select_type table type possible_keys key key_len ref rows Extra 1621 SIMPLE t2 index NULL PRIMARY 4 NULL 5 Using index 163select p from t2; 164p 1652 1663 1671001 1682000 1693000 170explain select i from t2; 171id select_type table type possible_keys key key_len ref rows Extra 1721 SIMPLE t2 index NULL k1 5 NULL 5 Using index 173select i from t2; 174i 1752 1762 1772 1783 1793 180explain select a from t2; 181id select_type table type possible_keys key key_len ref rows Extra 1821 SIMPLE t2 index NULL k2 11 NULL 5 Using index 183select a from t2; 184a 185pppp 186qqqq 187yyyy 188yyyy 189zzzz 190cache index t1 in unknown_key_cache; 191ERROR HY000: Unknown key cache 'unknown_key_cache' 192cache index t1 key (unknown_key) in keycache1; 193Table Op Msg_type Msg_text 194test.t1 assign_to_keycache Error Key 'unknown_key' doesn't exist in table 't1' 195test.t1 assign_to_keycache status Operation failed 196select @@keycache2.key_buffer_size; 197@@keycache2.key_buffer_size 1984194304 199select @@keycache2.key_cache_block_size; 200@@keycache2.key_cache_block_size 2011024 202set global keycache2.key_buffer_size=0; 203select @@keycache2.key_buffer_size; 204@@keycache2.key_buffer_size 2050 206select @@keycache2.key_cache_block_size; 207@@keycache2.key_cache_block_size 2081024 209set global keycache2.key_buffer_size=1024*1024; 210select @@keycache2.key_buffer_size; 211@@keycache2.key_buffer_size 2121048576 213update t2 set p=4000 where a='zzzz'; 214update t1 set p=p+1; 215set global keycache1.key_buffer_size=0; 216select * from t2; 217p i a 2181001 2 qqqq 2192 2 pppp 2203 2 yyyy 2214000 3 zzzz 2222000 3 yyyy 223select p from t2; 224p 2252 2263 2271001 2282000 2294000 230explain select i from t2; 231id select_type table type possible_keys key key_len ref rows Extra 2321 SIMPLE t2 index NULL k1 5 NULL 5 Using index 233select i from t2; 234i 2352 2362 2372 2383 2393 240explain select a from t2; 241id select_type table type possible_keys key key_len ref rows Extra 2421 SIMPLE t2 index NULL k2 11 NULL 5 Using index 243select a from t2; 244a 245pppp 246qqqq 247yyyy 248yyyy 249zzzz 250select * from t1; 251p a 2523 qqqq 25312 yyyy 254select p from t1; 255p 2563 25712 258create table t3 (like t1); 259cache index t3 in small; 260Table Op Msg_type Msg_text 261test.t3 assign_to_keycache status OK 262insert into t3 select * from t1; 263cache index t3 in keycache2; 264Table Op Msg_type Msg_text 265test.t3 assign_to_keycache status OK 266cache index t1,t2 in default; 267Table Op Msg_type Msg_text 268test.t1 assign_to_keycache status OK 269test.t2 assign_to_keycache status OK 270drop table t1,t2,t3; 271select @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"; 272key_blocks_unused key_blocks_used 2730 4 274create table t1 (a int primary key); 275cache index t1 in keycache2; 276Table Op Msg_type Msg_text 277test.t1 assign_to_keycache status OK 278insert t1 values (1),(2),(3),(4),(5),(6),(7),(8); 279set global keycache2.key_buffer_size=0; 280select * from t1; 281a 2821 2832 2843 2854 2865 2876 2887 2898 290drop table t1; 291set global keycache3.key_buffer_size=100; 292Warnings: 293Warning 1292 Truncated incorrect key_buffer_size value: '100' 294set global keycache3.key_buffer_size=0; 295create table t1 (mytext text, FULLTEXT (mytext)); 296insert t1 values ('aaabbb'); 297check table t1; 298Table Op Msg_type Msg_text 299test.t1 check status OK 300set @my_key_cache_block_size= @@global.key_cache_block_size; 301set GLOBAL key_cache_block_size=2048; 302check table t1; 303Table Op Msg_type Msg_text 304test.t1 check status OK 305drop table t1; 306set global key_cache_block_size= @my_key_cache_block_size; 307CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY); 308SET @my_key_cache_block_size= @@global.key_cache_block_size; 309SET GLOBAL key_cache_block_size=1536; 310INSERT INTO t1 VALUES (1); 311SELECT @@key_cache_block_size; 312@@key_cache_block_size 3131536 314CHECK TABLE t1; 315Table Op Msg_type Msg_text 316test.t1 check status OK 317DROP TABLE t1; 318CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int); 319CREATE TABLE t2(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int); 320SET GLOBAL key_cache_block_size=1536; 321INSERT INTO t1 VALUES (1,0); 322INSERT INTO t2(b) SELECT b FROM t1; 323INSERT INTO t1(b) SELECT b FROM t2; 324INSERT INTO t2(b) SELECT b FROM t1; 325INSERT INTO t1(b) SELECT b FROM t2; 326INSERT INTO t2(b) SELECT b FROM t1; 327INSERT INTO t1(b) SELECT b FROM t2; 328INSERT INTO t2(b) SELECT b FROM t1; 329INSERT INTO t1(b) SELECT b FROM t2; 330INSERT INTO t2(b) SELECT b FROM t1; 331INSERT INTO t1(b) SELECT b FROM t2; 332INSERT INTO t2(b) SELECT b FROM t1; 333INSERT INTO t1(b) SELECT b FROM t2; 334INSERT INTO t2(b) SELECT b FROM t1; 335INSERT INTO t1(b) SELECT b FROM t2; 336INSERT INTO t2(b) SELECT b FROM t1; 337INSERT INTO t1(b) SELECT b FROM t2; 338INSERT INTO t2(b) SELECT b FROM t1; 339INSERT INTO t1(b) SELECT b FROM t2; 340SELECT COUNT(*) FROM t1; 341COUNT(*) 3424181 343SELECT @@key_cache_block_size; 344@@key_cache_block_size 3451536 346CHECK TABLE t1; 347Table Op Msg_type Msg_text 348test.t1 check status OK 349DROP TABLE t1,t2; 350set global key_cache_block_size= @my_key_cache_block_size; 351set @@global.key_buffer_size=0; 352ERROR HY000: Cannot drop default keycache 353select @@global.key_buffer_size; 354@@global.key_buffer_size 3552097152 356SET @bug28478_key_cache_block_size= @@global.key_cache_block_size; 357SET GLOBAL key_cache_block_size= 1536; 358CREATE TABLE t1 ( 359id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 360c1 CHAR(150), 361c2 CHAR(150), 362c3 CHAR(150), 363KEY(c1, c2, c3) 364) ENGINE= MyISAM; 365INSERT INTO t1 (c1, c2, c3) VALUES 366('a', 'b', 'c'), ('b', 'c', 'd'), ('c', 'd', 'e'), ('d', 'e', 'f'), 367('e', 'f', 'g'), ('f', 'g', 'h'), ('g', 'h', 'i'), ('h', 'i', 'j'), 368('i', 'j', 'k'), ('j', 'k', 'l'), ('k', 'l', 'm'), ('l', 'm', 'n'), 369('m', 'n', 'o'), ('n', 'o', 'p'), ('o', 'p', 'q'), ('p', 'q', 'r'), 370('q', 'r', 's'), ('r', 's', 't'), ('s', 't', 'u'), ('t', 'u', 'v'), 371('u', 'v', 'w'), ('v', 'w', 'x'), ('w', 'x', 'y'), ('x', 'y', 'z'); 372INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; 373INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; 374INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; 375CHECK TABLE t1; 376Table Op Msg_type Msg_text 377test.t1 check status OK 378SHOW VARIABLES LIKE 'key_cache_block_size'; 379Variable_name Value 380key_cache_block_size 1536 381SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size; 382DROP TABLE t1; 383# 384# Bug#12361113: crash when load index into cache 385# 386# Note that this creates an empty disabled key cache! 387SET GLOBAL key_cache_none.key_cache_block_size = 1024; 388CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) ) ENGINE = MYISAM; 389INSERT INTO t1 VALUES (1, 1); 390CACHE INDEX t1 in key_cache_none; 391ERROR HY000: Unknown key cache 'key_cache_none' 392# The bug crashed the server at LOAD INDEX below. Now it will succeed 393# since the default cache is used due to CACHE INDEX failed for 394# key_cache_none. 395LOAD INDEX INTO CACHE t1; 396Table Op Msg_type Msg_text 397test.t1 preload_keys status OK 398DROP TABLE t1; 399set global key_buffer_size=@save_key_buffer_size; 400set global key_cache_block_size=@save_key_cache_block_size; 401select @@key_buffer_size; 402@@key_buffer_size 4032097152 404select @@key_cache_block_size; 405@@key_cache_block_size 4061024 407select @@key_cache_segments; 408@@key_cache_segments 4090 410create table t1 ( 411p int not null auto_increment primary key, 412a char(10)); 413create table t2 ( 414p int not null auto_increment primary key, 415i int, a char(10), key k1(i), key k2(a)); 416select @@key_cache_segments; 417@@key_cache_segments 4180 419select * from information_schema.key_caches where segment_number is null; 420KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 421default NULL NULL 2097152 1024 0 # 0 0 0 0 0 422small NULL NULL 1048576 1024 1 # 0 1 0 2 1 423insert into t1 values (1, 'qqqq'), (2, 'yyyy'); 424insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 425(3, 1, 'yyyy'), (4, 3, 'zzzz'); 426select * from t1; 427p a 4281 qqqq 4292 yyyy 430select * from t2; 431p i a 4321 1 qqqq 4332 1 pppp 4343 1 yyyy 4354 3 zzzz 436update t1 set p=3 where p=1; 437update t2 set i=2 where i=1; 438select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused'; 439VARIABLE_NAME VARIABLE_VALUE 440KEY_BLOCKS_NOT_FLUSHED 0 441KEY_BLOCKS_USED 4 442KEY_BLOCKS_WARM 0 443KEY_READ_REQUESTS 22 444KEY_READS 0 445KEY_WRITE_REQUESTS 26 446KEY_WRITES 6 447select variable_value into @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; 448select * from information_schema.key_caches where segment_number is null; 449KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 450default NULL NULL 2097152 1024 4 # 0 22 0 26 6 451small NULL NULL 1048576 1024 1 # 0 1 0 2 1 452delete from t2 where a='zzzz'; 453select * from information_schema.key_caches where segment_number is null; 454KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 455default NULL NULL 2097152 1024 4 # 0 29 0 32 9 456small NULL NULL 1048576 1024 1 # 0 1 0 2 1 457delete from t1; 458delete from t2; 459select * from information_schema.key_caches where segment_number is null; 460KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 461default NULL NULL 2097152 1024 4 # 0 29 0 32 9 462small NULL NULL 1048576 1024 1 # 0 1 0 2 1 463set global key_cache_segments=2; 464select @@key_cache_segments; 465@@key_cache_segments 4662 467select * from information_schema.key_caches where segment_number is null; 468KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 469default 2 NULL 2097152 1024 0 # 0 0 0 0 0 470small NULL NULL 1048576 1024 1 # 0 1 0 2 1 471insert into t1 values (1, 'qqqq'), (2, 'yyyy'); 472insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 473(3, 1, 'yyyy'), (4, 3, 'zzzz'); 474select * from t1; 475p a 4761 qqqq 4772 yyyy 478select * from t2; 479p i a 4801 1 qqqq 4812 1 pppp 4823 1 yyyy 4834 3 zzzz 484update t1 set p=3 where p=1; 485update t2 set i=2 where i=1; 486select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused'; 487VARIABLE_NAME VARIABLE_VALUE 488KEY_BLOCKS_NOT_FLUSHED 0 489KEY_BLOCKS_USED 4 490KEY_BLOCKS_WARM 0 491KEY_READ_REQUESTS 22 492KEY_READS 0 493KEY_WRITE_REQUESTS 26 494KEY_WRITES 6 495select variable_value < @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; 496variable_value < @key_blocks_unused 4971 498select * from information_schema.key_caches where segment_number is null; 499KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 500default 2 NULL 2097152 1024 4 # 0 22 0 26 6 501small NULL NULL 1048576 1024 1 # 0 1 0 2 1 502delete from t1; 503delete from t2; 504select * from information_schema.key_caches where segment_number is null; 505KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 506default 2 NULL 2097152 1024 4 # 0 22 0 26 6 507small NULL NULL 1048576 1024 1 # 0 1 0 2 1 508set global key_cache_segments=1; 509select @@key_cache_segments; 510@@key_cache_segments 5111 512select * from information_schema.key_caches where segment_number is null; 513KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 514default 1 NULL 2097152 1024 0 # 0 0 0 0 0 515small NULL NULL 1048576 1024 1 # 0 1 0 2 1 516insert into t1 values (1, 'qqqq'), (2, 'yyyy'); 517insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 518(3, 1, 'yyyy'), (4, 3, 'zzzz'); 519select * from t1; 520p a 5211 qqqq 5222 yyyy 523select * from t2; 524p i a 5251 1 qqqq 5262 1 pppp 5273 1 yyyy 5284 3 zzzz 529update t1 set p=3 where p=1; 530update t2 set i=2 where i=1; 531select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused'; 532VARIABLE_NAME VARIABLE_VALUE 533KEY_BLOCKS_NOT_FLUSHED 0 534KEY_BLOCKS_USED 4 535KEY_BLOCKS_WARM 0 536KEY_READ_REQUESTS 22 537KEY_READS 0 538KEY_WRITE_REQUESTS 26 539KEY_WRITES 6 540select variable_value = @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; 541variable_value = @key_blocks_unused 5421 543select * from information_schema.key_caches where segment_number is null; 544KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 545default 1 NULL 2097152 1024 4 # 0 22 0 26 6 546small NULL NULL 1048576 1024 1 # 0 1 0 2 1 547delete from t1; 548delete from t2; 549select * from information_schema.key_caches where segment_number is null; 550KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 551default 1 NULL 2097152 1024 4 # 0 22 0 26 6 552small NULL NULL 1048576 1024 1 # 0 1 0 2 1 553flush tables; 554flush status; 555select * from information_schema.key_caches where segment_number is null; 556KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 557default 1 NULL 2097152 1024 4 # 0 0 0 0 0 558small NULL NULL 1048576 1024 1 # 0 0 0 0 0 559set global key_buffer_size=32*1024; 560set global key_cache_file_hash_size=128; 561select @@key_buffer_size; 562@@key_buffer_size 56332768 564set global key_cache_segments=2; 565select @@key_cache_segments; 566@@key_cache_segments 5672 568select * from information_schema.key_caches where segment_number is null; 569KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 570default 2 NULL 32768 1024 0 # 0 0 0 0 0 571small NULL NULL 1048576 1024 1 # 0 0 0 0 0 572insert into t1 values (1, 'qqqq'), (2, 'yyyy'); 573insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 574(3, 1, 'yyyy'), (4, 3, 'zzzz'); 575select * from t1; 576p a 5771 qqqq 5782 yyyy 579select * from t2; 580p i a 5811 1 qqqq 5822 1 pppp 5833 1 yyyy 5844 3 zzzz 585update t1 set p=3 where p=1; 586update t2 set i=2 where i=1; 587select * from information_schema.key_caches where segment_number is null; 588KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 589default 2 NULL 32768 1024 4 # 0 22 0 26 6 590small NULL NULL 1048576 1024 1 # 0 0 0 0 0 591insert into t1(a) select a from t1; 592insert into t1(a) select a from t1; 593insert into t1(a) select a from t1; 594insert into t1(a) select a from t1; 595insert into t1(a) select a from t1; 596insert into t1(a) select a from t1; 597insert into t1(a) select a from t1; 598insert into t1(a) select a from t1; 599insert into t2(i,a) select i,a from t2; 600insert into t2(i,a) select i,a from t2; 601insert into t2(i,a) select i,a from t2; 602insert into t2(i,a) select i,a from t2; 603insert into t2(i,a) select i,a from t2; 604insert into t2(i,a) select i,a from t2; 605insert into t2(i,a) select i,a from t2; 606insert into t2(i,a) select i,a from t2; 607select * from information_schema.key_caches where segment_number is null; 608KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 609default 2 NULL 32768 1024 # # 0 6733 # 3684 103 610small NULL NULL 1048576 1024 # # 0 0 # 0 0 611select * from t1 where p between 1010 and 1020 ; 612p a 613select * from t2 where p between 1010 and 1020 ; 614p i a 6151010 2 pppp 6161011 2 yyyy 6171012 3 zzzz 6181013 2 qqqq 6191014 2 pppp 6201015 2 yyyy 6211016 3 zzzz 6221017 2 qqqq 6231018 2 pppp 6241019 2 yyyy 6251020 3 zzzz 626select * from information_schema.key_caches where segment_number is null; 627KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 628default 2 NULL 32768 1024 # # 0 6750 # 3684 103 629small NULL NULL 1048576 1024 # # 0 0 # 0 0 630flush tables; 631flush status; 632update t1 set a='zzzz' where a='qqqq'; 633update t2 set i=1 where i=2; 634select * from information_schema.key_caches where segment_number is null; 635KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 636default 2 NULL 32768 1024 # # 0 3076 18 1552 18 637small NULL NULL 1048576 1024 # # 0 0 0 0 0 638set global keycache1.key_buffer_size=256*1024; 639select @@keycache1.key_buffer_size; 640@@keycache1.key_buffer_size 641262144 642set global keycache1.key_cache_segments=7; 643select @@keycache1.key_cache_segments; 644@@keycache1.key_cache_segments 6457 646select * from information_schema.key_caches where segment_number is null; 647KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 648default 2 NULL 32768 1024 # # 0 3076 18 1552 18 649small NULL NULL 1048576 1024 # # 0 0 0 0 0 650keycache1 7 NULL 262143 2048 # # 0 0 0 0 0 651select * from information_schema.key_caches where key_cache_name like "key%" 652 and segment_number is null; 653KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 654keycache1 7 NULL 262143 2048 0 # 0 0 0 0 0 655cache index t1 key (`primary`) in keycache1; 656Table Op Msg_type Msg_text 657test.t1 assign_to_keycache status OK 658explain select p from t1 where p between 1010 and 1020; 659id select_type table type possible_keys key key_len ref rows Extra 6601 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index 661select p from t1 where p between 1010 and 1020; 662p 663explain select i from t2 where p between 1010 and 1020; 664id select_type table type possible_keys key key_len ref rows Extra 6651 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 9 Using index condition 666select i from t2 where p between 1010 and 1020; 667i 6681 6691 6703 6711 6721 6731 6743 6751 6761 6771 6783 679explain select count(*) from t1, t2 where t1.p = t2.i; 680id select_type table type possible_keys key key_len ref rows Extra 6811 SIMPLE t2 index k1 k1 5 NULL 1024 Using where; Using index 6821 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.i 1 Using index 683select count(*) from t1, t2 where t1.p = t2.i; 684count(*) 685256 686select * from information_schema.key_caches where segment_number is null; 687KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 688default 2 NULL 32768 1024 # # 0 3172 24 1552 18 689small NULL NULL 1048576 1024 # # 0 0 0 0 0 690keycache1 7 NULL 262143 2048 # # 0 14 3 0 0 691select * from information_schema.key_caches where key_cache_name like "key%" 692 and segment_number is null; 693KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 694keycache1 7 NULL 262143 2048 3 # 0 14 3 0 0 695cache index t2 in keycache1; 696Table Op Msg_type Msg_text 697test.t2 assign_to_keycache status OK 698update t2 set p=p+3000, i=2 where a='qqqq'; 699select * from information_schema.key_caches where key_cache_name like "key%" 700 and segment_number is null; 701KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 702keycache1 7 NULL 262143 2048 25 # 0 2082 25 1071 19 703set global keycache2.key_buffer_size=1024*1024; 704cache index t2 in keycache2; 705Table Op Msg_type Msg_text 706test.t2 assign_to_keycache status OK 707insert into t2 values (2000, 3, 'yyyy'); 708select * from information_schema.key_caches where key_cache_name like "keycache2" 709 and segment_number is null; 710KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 711keycache2 NULL NULL 1048576 1024 6 # 0 6 6 3 3 712select * from information_schema.key_caches where key_cache_name like "key%" 713and segment_number is null; 714KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 715keycache1 7 NULL 262143 2048 25 # 0 2082 25 1071 19 716keycache2 NULL NULL 1048576 1024 6 # 0 6 6 3 3 717cache index t2 in keycache1; 718Table Op Msg_type Msg_text 719test.t2 assign_to_keycache status OK 720update t2 set p=p+5000 where a='zzzz'; 721select * from t2 where p between 1010 and 1020; 722p i a 7231010 1 pppp 7241011 1 yyyy 7251014 1 pppp 7261015 1 yyyy 7271018 1 pppp 7281019 1 yyyy 729explain select p from t2 where p between 1010 and 1020; 730id select_type table type possible_keys key key_len ref rows Extra 7311 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 Using where; Using index 732select p from t2 where p between 1010 and 1020; 733p 7341010 7351011 7361014 7371015 7381018 7391019 740explain select i from t2 where a='yyyy' and i=3; 741id select_type table type possible_keys key key_len ref rows Extra 7421 SIMPLE t2 ref k1,k2 k1 5 const 189 Using where 743select i from t2 where a='yyyy' and i=3; 744i 7453 746explain select a from t2 where a='yyyy' and i=3; 747id select_type table type possible_keys key key_len ref rows Extra 7481 SIMPLE t2 ref k1,k2 k1 5 const 189 Using where 749select a from t2 where a='yyyy' and i=3 ; 750a 751yyyy 752select * from information_schema.key_caches where segment_number is null; 753KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 754default 2 NULL 32768 1024 # # 0 3172 24 1552 18 755small NULL NULL 1048576 1024 # # 0 0 0 0 0 756keycache1 7 NULL 262143 2048 # # 0 3201 43 1594 30 757keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 758set global keycache1.key_cache_block_size=2*1024; 759insert into t2 values (7000, 3, 'yyyy'); 760select * from information_schema.key_caches where segment_number is null; 761KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 762default 2 NULL 32768 1024 # # 0 3172 24 1552 18 763small NULL NULL 1048576 1024 # # 0 0 0 0 0 764keycache1 7 NULL 262143 2048 # # 0 6 6 3 3 765keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 766set global keycache1.key_cache_block_size=8*1024; 767select * from information_schema.key_caches where segment_number is null; 768KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 769default 2 NULL 32768 1024 # # 0 3172 24 1552 18 770small NULL NULL 1048576 1024 # # 0 0 0 0 0 771keycache1 3 NULL 262143 8192 # # 0 0 0 0 0 772keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 773insert into t2 values (8000, 3, 'yyyy'); 774select * from information_schema.key_caches where segment_number is null; 775KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 776default 2 NULL 32768 1024 # # 0 3172 24 1552 18 777small NULL NULL 1048576 1024 # # 0 0 0 0 0 778keycache1 3 NULL 262143 8192 # # 0 6 5 3 3 779keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 780set global keycache1.key_buffer_size=64*1024; 781select * from information_schema.key_caches where segment_number is null; 782KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 783default 2 NULL 32768 1024 # # 0 3172 24 1552 18 784small NULL NULL 1048576 1024 # # 0 0 0 0 0 785keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 786set global keycache1.key_cache_block_size=2*1024; 787select * from information_schema.key_caches where segment_number is null; 788KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 789default 2 NULL 32768 1024 # # 0 3172 24 1552 18 790small NULL NULL 1048576 1024 # # 0 0 0 0 0 791keycache1 3 NULL 65535 2048 # # 0 0 0 0 0 792keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 793set global keycache1.key_cache_block_size=8*1024; 794select * from information_schema.key_caches where segment_number is null; 795KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 796default 2 NULL 32768 1024 # # 0 3172 24 1552 18 797small NULL NULL 1048576 1024 # # 0 0 0 0 0 798keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 799set global keycache1.key_buffer_size=0; 800select * from information_schema.key_caches where segment_number is null; 801KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 802default 2 NULL 32768 1024 # # 0 3172 24 1552 18 803small NULL NULL 1048576 1024 # # 0 0 0 0 0 804keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 805set global keycache1.key_cache_block_size=8*1024; 806select * from information_schema.key_caches where segment_number is null; 807KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 808default 2 NULL 32768 1024 # # 0 3172 24 1552 18 809small NULL NULL 1048576 1024 # # 0 0 0 0 0 810keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 811set global keycache1.key_buffer_size=0; 812select * from information_schema.key_caches where segment_number is null; 813KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 814default 2 NULL 32768 1024 # # 0 3172 24 1552 18 815small NULL NULL 1048576 1024 # # 0 0 0 0 0 816keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 817set global keycache1.key_buffer_size=128*1024; 818select * from information_schema.key_caches where segment_number is null; 819KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 820default 2 NULL 32768 1024 # # 0 3172 24 1552 18 821small NULL NULL 1048576 1024 # # 0 0 0 0 0 822keycache1 1 NULL 131072 8192 # # 0 0 0 0 0 823keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 824set global keycache1.key_cache_block_size=1024; 825select * from information_schema.key_caches where segment_number is null; 826KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES 827default 2 NULL 32768 1024 # # 0 3172 24 1552 18 828small NULL NULL 1048576 1024 # # 0 0 0 0 0 829keycache1 7 NULL 131068 1024 # # 0 0 0 0 0 830keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 831drop table t1,t2; 832set global keycache1.key_buffer_size=0; 833set global keycache2.key_buffer_size=0; 834set global key_buffer_size=@save_key_buffer_size; 835set global key_cache_segments=@save_key_cache_segments; 836set global key_cache_file_hash_size=@save_key_cache_file_hash_size; 837