2
# Test of multiple key caches, simple and segmented
5
drop table if exists t1, t2, t3;
8
SET @save_key_buffer_size=@@key_buffer_size;
9
SET @save_key_cache_block_size=@@key_cache_block_size;
10
SET @save_key_cache_segments=@@key_cache_segments;
12
SELECT @@key_buffer_size, @@small.key_buffer_size;
14
# Change default key cache size
15
SET @@global.key_buffer_size=16*1024*1024;
16
SET @@global.default.key_buffer_size=16*1024*1024;
17
SET @@global.default.key_buffer_size=16*1024*1024;
19
SET @@global.small.key_buffer_size=1*1024*1024;
20
SET @@global.medium.key_buffer_size=4*1024*1024;
22
SET @@global.medium.key_buffer_size=0;
24
SET @@global.medium.key_buffer_size=0;
26
# Print key buffer with different syntaxes
27
SHOW VARIABLES like "key_buffer_size";
28
SELECT @@key_buffer_size;
29
SELECT @@global.key_buffer_size;
30
SELECT @@global.default.key_buffer_size;
31
SELECT @@global.default.`key_buffer_size`;
32
SELECT @@global.`default`.`key_buffer_size`;
33
SELECT @@`default`.key_buffer_size;
35
SELECT @@small.key_buffer_size;
36
SELECT @@medium.key_buffer_size;
38
SET @@global.key_buffer_size=@save_key_buffer_size;
45
SELECT @@default.key_buffer_size;
47
SELECT @@skr.storage_engine="test";
49
select @@keycache1.key_cache_block_size;
50
select @@keycache1.key_buffer_size;
51
set global keycache1.key_cache_block_size=2048;
52
select @@keycache1.key_buffer_size;
53
select @@keycache1.key_cache_block_size;
54
set global keycache1.key_buffer_size=1*1024*1024;
55
select @@keycache1.key_buffer_size;
56
select @@keycache1.key_cache_block_size;
57
set global keycache2.key_buffer_size=4*1024*1024;
58
select @@keycache2.key_buffer_size;
59
select @@keycache2.key_cache_block_size;
60
set global keycache1.key_buffer_size=0;
61
select @@keycache1.key_buffer_size;
62
select @@keycache1.key_cache_block_size;
63
select @@key_buffer_size;
64
select @@key_cache_block_size;
66
set global keycache1.key_buffer_size=1024*1024;
68
create table t1 (p int primary key, a char(10)) delay_key_write=1;
69
create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a));
71
show status like 'key_blocks_used';
73
# Following results differs on 64 and 32 bit systems because of different
74
# pointer sizes, which takes up different amount of space in key cache
76
--replace_result 1812 KEY_BLOCKS_UNUSED 1793 KEY_BLOCKS_UNUSED 1674 KEY_BLOCKS_UNUSED 1818 KEY_BLOCKS_UNUSED 1824 KEY_BLOCKS_UNUSED
77
show status like 'key_blocks_unused';
79
insert into t1 values (1, 'qqqq'), (11, 'yyyy');
80
insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
81
(3, 1, 'yyyy'), (4, 3, 'zzzz');
85
update t1 set p=2 where p=1;
86
update t2 set i=2 where i=1;
88
show status like 'key_blocks_used';
89
--replace_result 1808 KEY_BLOCKS_UNUSED 1789 KEY_BLOCKS_UNUSED 1670 KEY_BLOCKS_UNUSED 1814 KEY_BLOCKS_UNUSED 1820 KEY_BLOCKS_UNUSED
90
show status like 'key_blocks_unused';
92
cache index t1 key (`primary`) in keycache1;
94
explain select p from t1;
96
explain select i from t2;
98
explain select count(*) from t1, t2 where t1.p = t2.i;
99
select count(*) from t1, t2 where t1.p = t2.i;
101
cache index t2 in keycache1;
102
update t2 set p=p+1000, i=2 where a='qqqq';
103
cache index t2 in keycache2;
104
insert into t2 values (2000, 3, 'yyyy');
105
cache index t2 in keycache1;
106
update t2 set p=3000 where a='zzzz';
108
explain select p from t2;
110
explain select i from t2;
112
explain select a from t2;
115
# Test some error conditions
117
cache index t1 in unknown_key_cache;
118
cache index t1 key (unknown_key) in keycache1;
120
select @@keycache2.key_buffer_size;
121
select @@keycache2.key_cache_block_size;
122
set global keycache2.key_buffer_size=0;
123
select @@keycache2.key_buffer_size;
124
select @@keycache2.key_cache_block_size;
125
set global keycache2.key_buffer_size=1024*1024;
126
select @@keycache2.key_buffer_size;
128
update t2 set p=4000 where a='zzzz';
131
set global keycache1.key_buffer_size=0;
134
explain select i from t2;
136
explain select a from t2;
142
# Use the 'small' key cache
143
create table t3 (like t1);
144
cache index t3 in small;
145
insert into t3 select * from t1;
146
cache index t3 in keycache2;
147
cache index t1,t2 in default;
150
show status like 'key_blocks_used';
151
--replace_result 1812 KEY_BLOCKS_UNUSED 1793 KEY_BLOCKS_UNUSED 1674 KEY_BLOCKS_UNUSED 1818 KEY_BLOCKS_UNUSED 1824 KEY_BLOCKS_UNUSED
152
show status like 'key_blocks_unused';
154
create table t1 (a int primary key);
155
cache index t1 in keycache2;
156
insert t1 values (1),(2),(3),(4),(5),(6),(7),(8);
157
# delete keycache2, t1 is reassigned to default
158
set global keycache2.key_buffer_size=0;
162
# Test to set up a too small size for a key cache (bug #2064)
163
set global keycache3.key_buffer_size=100;
164
set global keycache3.key_buffer_size=0;
166
# Test case for bug 6447
168
create table t1 (mytext text, FULLTEXT (mytext));
169
insert t1 values ('aaabbb');
172
set @my_key_cache_block_size= @@global.key_cache_block_size;
173
set GLOBAL key_cache_block_size=2048;
176
# Restore the changed variable value
177
set global key_cache_block_size= @my_key_cache_block_size;
180
# Bug #19079: corrupted index when key_cache_block_size is not multiple of
183
CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY);
184
SET @my_key_cache_block_size= @@global.key_cache_block_size;
185
SET GLOBAL key_cache_block_size=1536;
186
INSERT INTO t1 VALUES (1);
187
SELECT @@key_cache_block_size;
191
CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int);
192
CREATE TABLE t2(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int);
193
SET GLOBAL key_cache_block_size=1536;
194
INSERT INTO t1 VALUES (1,0);
195
INSERT INTO t2(b) SELECT b FROM t1;
196
INSERT INTO t1(b) SELECT b FROM t2;
197
INSERT INTO t2(b) SELECT b FROM t1;
198
INSERT INTO t1(b) SELECT b FROM t2;
199
INSERT INTO t2(b) SELECT b FROM t1;
200
INSERT INTO t1(b) SELECT b FROM t2;
201
INSERT INTO t2(b) SELECT b FROM t1;
202
INSERT INTO t1(b) SELECT b FROM t2;
203
INSERT INTO t2(b) SELECT b FROM t1;
204
INSERT INTO t1(b) SELECT b FROM t2;
205
INSERT INTO t2(b) SELECT b FROM t1;
206
INSERT INTO t1(b) SELECT b FROM t2;
207
INSERT INTO t2(b) SELECT b FROM t1;
208
INSERT INTO t1(b) SELECT b FROM t2;
209
INSERT INTO t2(b) SELECT b FROM t1;
210
INSERT INTO t1(b) SELECT b FROM t2;
211
INSERT INTO t2(b) SELECT b FROM t1;
212
INSERT INTO t1(b) SELECT b FROM t2;
213
SELECT COUNT(*) FROM t1;
214
SELECT @@key_cache_block_size;
217
# Restore changed variables
218
set global key_cache_block_size= @my_key_cache_block_size;
221
# Bug#10473 - Can't set 'key_buffer_size' system variable to ZERO
222
# (One cannot drop the default key cache.)
224
--error ER_WARN_CANT_DROP_DEFAULT_KEYCACHE
225
set @@global.key_buffer_size=0;
226
select @@global.key_buffer_size;
229
# Bug#28478 - Improper key_cache_block_size corrupts MyISAM tables
231
SET @bug28478_key_cache_block_size= @@global.key_cache_block_size;
232
SET GLOBAL key_cache_block_size= 1536;
234
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
240
INSERT INTO t1 (c1, c2, c3) VALUES
241
('a', 'b', 'c'), ('b', 'c', 'd'), ('c', 'd', 'e'), ('d', 'e', 'f'),
242
('e', 'f', 'g'), ('f', 'g', 'h'), ('g', 'h', 'i'), ('h', 'i', 'j'),
243
('i', 'j', 'k'), ('j', 'k', 'l'), ('k', 'l', 'm'), ('l', 'm', 'n'),
244
('m', 'n', 'o'), ('n', 'o', 'p'), ('o', 'p', 'q'), ('p', 'q', 'r'),
245
('q', 'r', 's'), ('r', 's', 't'), ('s', 't', 'u'), ('t', 'u', 'v'),
246
('u', 'v', 'w'), ('v', 'w', 'x'), ('w', 'x', 'y'), ('x', 'y', 'z');
247
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1;
248
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1;
249
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1;
251
SHOW VARIABLES LIKE 'key_cache_block_size';
252
SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size;
258
--echo # Bug#12361113: crash when load index into cache
261
--echo # Note that this creates an empty disabled key cache!
262
SET GLOBAL key_cache_none.key_cache_block_size = 1024;
263
CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) ) ENGINE = MYISAM;
264
INSERT INTO t1 VALUES (1, 1);
265
--error ER_UNKNOWN_KEY_CACHE
266
CACHE INDEX t1 in key_cache_none;
267
--echo # The bug crashed the server at LOAD INDEX below. Now it will succeed
268
--echo # since the default cache is used due to CACHE INDEX failed for
269
--echo # key_cache_none.
270
LOAD INDEX INTO CACHE t1;
276
# Test cases for segmented key caches
279
# Test usage of the KEY_CACHE table from information schema
280
# for a simple key cache
282
set global key_buffer_size=@save_key_buffer_size;
283
set global key_cache_block_size=@save_key_cache_block_size;
284
select @@key_buffer_size;
285
select @@key_cache_block_size;
286
select @@key_cache_segments;
289
p int not null auto_increment primary key,
292
p int not null auto_increment primary key,
293
i int, a char(10), key k1(i), key k2(a));
295
select @@key_cache_segments;
297
select * from information_schema.key_caches where segment_number is null;
299
insert into t1 values (1, 'qqqq'), (2, 'yyyy');
300
insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
301
(3, 1, 'yyyy'), (4, 3, 'zzzz');
304
update t1 set p=3 where p=1;
305
update t2 set i=2 where i=1;
307
--replace_result 1808 KEY_BLOCKS_UNUSED 1670 KEY_BLOCKS_UNUSED 1789 KEY_BLOCKS_UNUSED
308
show status like 'key_%';
310
select * from information_schema.key_caches where segment_number is null;
312
delete from t2 where a='zzzz';
314
select * from information_schema.key_caches where segment_number is null;
319
select * from information_schema.key_caches where segment_number is null;
321
# For the key cache with 2 segments execute the same sequence of
322
# statements as for the simple cache above.
323
# The statistical information on the number of i/o requests and
324
# the number of is expected to be the same.
326
set global key_cache_segments=2;
327
select @@key_cache_segments;
329
select * from information_schema.key_caches where segment_number is null;
331
insert into t1 values (1, 'qqqq'), (2, 'yyyy');
332
insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
333
(3, 1, 'yyyy'), (4, 3, 'zzzz');
336
update t1 set p=3 where p=1;
337
update t2 set i=2 where i=1;
339
--replace_result 1808 KEY_BLOCKS_UNUSED 1670 KEY_BLOCKS_UNUSED 1788 KEY_BLOCKS_UNUSED
340
show status like 'key_%';
342
select * from information_schema.key_caches where segment_number is null;
347
select * from information_schema.key_caches where segment_number is null;
349
# Check that we can work with one segment with the same results
351
set global key_cache_segments=1;
352
select @@key_cache_segments;
354
select * from information_schema.key_caches where segment_number is null;
356
insert into t1 values (1, 'qqqq'), (2, 'yyyy');
357
insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
358
(3, 1, 'yyyy'), (4, 3, 'zzzz');
361
update t1 set p=3 where p=1;
362
update t2 set i=2 where i=1;
364
--replace_result 1808 KEY_BLOCKS_UNUSED 1670 KEY_BLOCKS_UNUSED 1789 KEY_BLOCKS_UNUSED
365
show status like 'key_%';
367
select * from information_schema.key_caches where segment_number is null;
372
select * from information_schema.key_caches where segment_number is null;
374
flush tables; flush status;
376
select * from information_schema.key_caches where segment_number is null;
378
# Switch back to 2 segments
380
set global key_buffer_size=32*1024;
381
select @@key_buffer_size;
382
set global key_cache_segments=2;
383
select @@key_cache_segments;
385
select * from information_schema.key_caches where segment_number is null;
387
insert into t1 values (1, 'qqqq'), (2, 'yyyy');
388
insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
389
(3, 1, 'yyyy'), (4, 3, 'zzzz');
392
update t1 set p=3 where p=1;
393
update t2 set i=2 where i=1;
396
select * from information_schema.key_caches where segment_number is null;
398
# Add more rows to tables t1 and t2
400
insert into t1(a) select a from t1;
401
insert into t1(a) select a from t1;
402
insert into t1(a) select a from t1;
403
insert into t1(a) select a from t1;
404
insert into t1(a) select a from t1;
405
insert into t1(a) select a from t1;
406
insert into t1(a) select a from t1;
407
insert into t1(a) select a from t1;
409
insert into t2(i,a) select i,a from t2;
410
insert into t2(i,a) select i,a from t2;
411
insert into t2(i,a) select i,a from t2;
412
insert into t2(i,a) select i,a from t2;
413
insert into t2(i,a) select i,a from t2;
414
insert into t2(i,a) select i,a from t2;
415
insert into t2(i,a) select i,a from t2;
416
insert into t2(i,a) select i,a from t2;
418
--replace_column 6 # 7 # 10 #
419
select * from information_schema.key_caches where segment_number is null;
421
select * from t1 where p between 1010 and 1020 ;
422
select * from t2 where p between 1010 and 1020 ;
423
--replace_column 6 # 7 # 10 #
424
select * from information_schema.key_caches where segment_number is null;
426
flush tables; flush status;
427
update t1 set a='zzzz' where a='qqqq';
428
update t2 set i=1 where i=2;
429
--replace_column 6 # 7 #
430
select * from information_schema.key_caches where segment_number is null;
432
# Now test how we can work with 7 segments
434
set global keycache1.key_buffer_size=256*1024;
435
select @@keycache1.key_buffer_size;
436
set global keycache1.key_cache_segments=7;
437
select @@keycache1.key_cache_segments;
439
--replace_column 6 # 7 #
440
select * from information_schema.key_caches where segment_number is null;
442
select * from information_schema.key_caches where key_cache_name like "key%"
443
and segment_number is null;
445
cache index t1 key (`primary`) in keycache1;
447
explain select p from t1 where p between 1010 and 1020;
448
select p from t1 where p between 1010 and 1020;
449
explain select i from t2 where p between 1010 and 1020;
450
select i from t2 where p between 1010 and 1020;
451
explain select count(*) from t1, t2 where t1.p = t2.i;
452
select count(*) from t1, t2 where t1.p = t2.i;
454
--replace_column 6 # 7 #
455
select * from information_schema.key_caches where segment_number is null;
457
select * from information_schema.key_caches where key_cache_name like "key%"
458
and segment_number is null;
460
cache index t2 in keycache1;
461
update t2 set p=p+3000, i=2 where a='qqqq';
463
select * from information_schema.key_caches where key_cache_name like "key%"
464
and segment_number is null;
466
set global keycache2.key_buffer_size=1024*1024;
467
cache index t2 in keycache2;
468
insert into t2 values (2000, 3, 'yyyy');
470
select * from information_schema.key_caches where key_cache_name like "keycache2"
471
and segment_number is null;
473
select * from information_schema.key_caches where key_cache_name like "key%"
474
and segment_number is null;
476
cache index t2 in keycache1;
477
update t2 set p=p+5000 where a='zzzz';
478
select * from t2 where p between 1010 and 1020;
479
explain select p from t2 where p between 1010 and 1020;
480
select p from t2 where p between 1010 and 1020;
481
explain select i from t2 where a='yyyy' and i=3;
482
select i from t2 where a='yyyy' and i=3;
483
explain select a from t2 where a='yyyy' and i=3;
484
select a from t2 where a='yyyy' and i=3 ;
485
--replace_column 6 # 7 #
486
select * from information_schema.key_caches where segment_number is null;
488
set global keycache1.key_cache_block_size=2*1024;
489
insert into t2 values (7000, 3, 'yyyy');
490
--replace_column 6 # 7 #
491
select * from information_schema.key_caches where segment_number is null;
493
set global keycache1.key_cache_block_size=8*1024;
494
--replace_column 6 # 7 #
495
select * from information_schema.key_caches where segment_number is null;
496
insert into t2 values (8000, 3, 'yyyy');
497
--replace_column 6 # 7 #
498
select * from information_schema.key_caches where segment_number is null;
500
set global keycache1.key_buffer_size=64*1024;
501
--replace_column 6 # 7 #
502
select * from information_schema.key_caches where segment_number is null;
504
set global keycache1.key_cache_block_size=2*1024;
505
--replace_column 6 # 7 #
506
select * from information_schema.key_caches where segment_number is null;
508
set global keycache1.key_cache_block_size=8*1024;
509
--replace_column 6 # 7 #
510
select * from information_schema.key_caches where segment_number is null;
512
set global keycache1.key_buffer_size=0;
513
--replace_column 6 # 7 #
514
select * from information_schema.key_caches where segment_number is null;
516
set global keycache1.key_cache_block_size=8*1024;
517
--replace_column 6 # 7 #
518
select * from information_schema.key_caches where segment_number is null;
520
set global keycache1.key_buffer_size=0;
521
--replace_column 6 # 7 #
522
select * from information_schema.key_caches where segment_number is null;
524
set global keycache1.key_buffer_size=128*1024;
525
--replace_column 6 # 7 #
526
select * from information_schema.key_caches where segment_number is null;
528
set global keycache1.key_cache_block_size=1024;
529
--replace_column 6 # 7 #
530
select * from information_schema.key_caches where segment_number is null;
534
set global keycache1.key_buffer_size=0;
535
set global keycache2.key_buffer_size=0;
537
set global key_buffer_size=@save_key_buffer_size;
538
set global key_cache_segments=@save_key_cache_segments;