1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
drop table if exists t1, t2, t3;
SET @save_myisam_key_cache_size=@@myisam_key_cache_size;
SET @save_myisam_key_cache_block_size=@@myisam_key_cache_block_size;
SET @save_myisam_key_cache_division_limit=@@myisam_key_cache_division_limit;
SET @save_myisam_key_cache_age_threshold=@@myisam_key_cache_age_threshold;
SELECT @@myisam_key_cache_size;
@@myisam_key_cache_size
1048576
SELECT @@myisam_key_cache_block_size;
@@myisam_key_cache_block_size
1024
SELECT @@myisam_key_cache_division_limit;
@@myisam_key_cache_division_limit
100
SELECT @@myisam_key_cache_age_threshold;
@@myisam_key_cache_age_threshold
300
SET @@global.myisam_key_cache_size=16*1024*1024;
SET @@global.myisam_key_cache_size=0;
Warnings:
Error 1292 Truncated incorrect key_cache_size value: '0'
SET @@global.myisam_key_cache_size=0;
Warnings:
Error 1292 Truncated incorrect key_cache_size value: '0'
SET @@global.myisam_key_cache_size=16*1024*1024;
SHOW VARIABLES like "myisam_key_cache_size";
Variable_name Value
myisam_key_cache_size 16777216
SELECT @@myisam_key_cache_size;
@@myisam_key_cache_size
16777216
SELECT @@global.myisam_key_cache_size;
@@global.myisam_key_cache_size
16777216
SELECT @@global.myisam_key_cache_size;
@@global.myisam_key_cache_size
16777216
SELECT @@global.`myisam_key_cache_size`;
@@global.`myisam_key_cache_size`
16777216
SELECT @@global.`myisam_key_cache_size`;
@@global.`myisam_key_cache_size`
16777216
SELECT @@myisam_key_cache_size;
@@myisam_key_cache_size
16777216
SET @@global.myisam_key_cache_size=@save_myisam_key_cache_size;
SELECT @@myisam_key_cache_size;
@@myisam_key_cache_size
1048576
SELECT @@skr.storage_engine="test";
ERROR HY000: Variable 'storage_engine' is not a variable component (can't be used as XXXX.variable_name)
select @@keycache1.myisam_key_cache_block_size;
ERROR HY000: Variable 'myisam_key_cache_block_size' is not a variable component (can't be used as XXXX.variable_name)
show status like 'key_blocks_used';
Variable_name Value
Key_blocks_used #
set global myisam_key_cache_size=100;
Warnings:
Error 1292 Truncated incorrect key_cache_size value: '100'
CREATE temporary TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=MYISAM;
SET GLOBAL myisam_key_cache_block_size=1536;
INSERT INTO t1 VALUES (1);
SELECT @@myisam_key_cache_block_size;
@@myisam_key_cache_block_size
1024
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
DROP TABLE t1;
CREATE TEMPORARY TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int) ENGINE=MYISAM;
CREATE TEMPORARY TABLE t2(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int) ENGINE=MYISAM;
SET GLOBAL myisam_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;
COUNT(*)
4181
SELECT @@myisam_key_cache_block_size;
@@myisam_key_cache_block_size
1024
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
DROP TABLE t1,t2;
set global myisam_key_cache_block_size= @save_myisam_key_cache_block_size;
SET GLOBAL myisam_key_cache_block_size= 1536;
CREATE TEMPORARY TABLE t1 (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c1 CHAR(50),
c2 CHAR(50),
c3 CHAR(50),
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');
CREATE TABLE t2 AS SELECT * FROM t1;
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
SHOW VARIABLES LIKE 'myisam_key_cache_block_size';
Variable_name Value
myisam_key_cache_block_size 1024
SET GLOBAL myisam_key_cache_block_size= @save_myisam_key_cache_block_size;
DROP TABLE t1,t2;
SET GLOBAL myisam_key_cache_division_limit= 0;
Warnings:
Error 1292 Truncated incorrect key_cache_division_limit value: '0'
SELECT @@myisam_key_cache_division_limit;
@@myisam_key_cache_division_limit
100
SET GLOBAL myisam_key_cache_division_limit= 101;
Warnings:
Error 1292 Truncated incorrect key_cache_division_limit value: '101'
SELECT @@myisam_key_cache_division_limit;
@@myisam_key_cache_division_limit
100
SET GLOBAL myisam_key_cache_age_threshold= 98;
Warnings:
Error 1292 Truncated incorrect key_cache_age_threshold value: '98'
SELECT @@myisam_key_cache_age_threshold;
@@myisam_key_cache_age_threshold
300
SET GLOBAL myisam_key_cache_age_threshold= 5000000000;
Warnings:
Error 1292 Truncated incorrect key_cache_age_threshold value: '5000000000'
SELECT @@myisam_key_cache_age_threshold;
@@myisam_key_cache_age_threshold
300
|