1
drop table if exists t1, t2;
2
create temporary table initial
3
select variable_name,variable_value from
4
information_schema.global_status where variable_name like "Maria_pagecache_read%";
6
a int not null auto_increment,
10
) engine=maria row_format=dynamic;
12
a int not null auto_increment,
16
) engine=maria row_format=dynamic;
17
insert into t1(b) values
26
insert into t2(b) select b from t1;
27
insert into t1(b) select b from t2;
28
insert into t2(b) select b from t1;
29
insert into t1(b) select b from t2;
30
insert into t2(b) select b from t1;
31
insert into t1(b) select b from t2;
32
insert into t2(b) select b from t1;
33
insert into t1(b) select b from t2;
34
insert into t2(b) select b from t1;
35
insert into t1(b) select b from t2;
36
insert into t2(b) select b from t1;
37
insert into t1(b) select b from t2;
38
insert into t2(b) select b from t1;
39
insert into t1(b) select b from t2;
40
insert into t2(b) select b from t1;
41
insert into t1(b) select b from t2;
42
insert into t2(b) select b from t1;
43
insert into t1(b) select b from t2;
44
select count(*) from t1;
47
select count(*) from t2;
52
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
53
variable_name g.variable_value-i.variable_value
54
MARIA_PAGECACHE_READ_REQUESTS 211644
55
MARIA_PAGECACHE_READS 3
56
select count(*) from t1 where b = 'test1';
59
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
60
variable_name g.variable_value-i.variable_value
61
MARIA_PAGECACHE_READ_REQUESTS 211926
62
MARIA_PAGECACHE_READS 11
63
select count(*) from t1 where b = 'test1';
66
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
67
variable_name g.variable_value-i.variable_value
68
MARIA_PAGECACHE_READ_REQUESTS 212208
69
MARIA_PAGECACHE_READS 12
72
select @@preload_buffer_size;
75
load index into cache t1;
76
Table Op Msg_type Msg_text
77
test.t1 preload_keys status OK
78
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
79
variable_name g.variable_value-i.variable_value
80
MARIA_PAGECACHE_READ_REQUESTS 212535
81
MARIA_PAGECACHE_READS 84
82
select count(*) from t1 where b = 'test1';
85
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
86
variable_name g.variable_value-i.variable_value
87
MARIA_PAGECACHE_READ_REQUESTS 212817
88
MARIA_PAGECACHE_READS 85
91
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
92
variable_name g.variable_value-i.variable_value
93
MARIA_PAGECACHE_READ_REQUESTS 213073
94
MARIA_PAGECACHE_READS 86
95
set session preload_buffer_size=256*1024;
96
select @@preload_buffer_size;
99
load index into cache t1 ignore leaves;
100
Table Op Msg_type Msg_text
101
test.t1 preload_keys status OK
102
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
103
variable_name g.variable_value-i.variable_value
104
MARIA_PAGECACHE_READ_REQUESTS 213400
105
MARIA_PAGECACHE_READS 158
106
select count(*) from t1 where b = 'test1';
109
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
110
variable_name g.variable_value-i.variable_value
111
MARIA_PAGECACHE_READ_REQUESTS 213682
112
MARIA_PAGECACHE_READS 165
115
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
116
variable_name g.variable_value-i.variable_value
117
MARIA_PAGECACHE_READ_REQUESTS 213938
118
MARIA_PAGECACHE_READS 166
119
set session preload_buffer_size=1*1024;
120
select @@preload_buffer_size;
121
@@preload_buffer_size
123
load index into cache t1, t2 key (primary,b) ignore leaves;
124
Table Op Msg_type Msg_text
125
test.t1 preload_keys status OK
126
test.t2 preload_keys status OK
127
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
128
variable_name g.variable_value-i.variable_value
129
MARIA_PAGECACHE_READ_REQUESTS 214308
130
MARIA_PAGECACHE_READS 281
131
select count(*) from t1 where b = 'test1';
134
select count(*) from t2 where b = 'test1';
137
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
138
variable_name g.variable_value-i.variable_value
139
MARIA_PAGECACHE_READ_REQUESTS 214604
140
MARIA_PAGECACHE_READS 285
143
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
144
variable_name g.variable_value-i.variable_value
145
MARIA_PAGECACHE_READ_REQUESTS 214860
146
MARIA_PAGECACHE_READS 286
147
load index into cache t3, t2 key (primary,b) ;
148
Table Op Msg_type Msg_text
149
test.t3 preload_keys Error Table 'test.t3' doesn't exist
150
test.t3 preload_keys error Corrupt
151
test.t2 preload_keys status OK
152
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
153
variable_name g.variable_value-i.variable_value
154
MARIA_PAGECACHE_READ_REQUESTS 215159
155
MARIA_PAGECACHE_READS 330
158
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
159
variable_name g.variable_value-i.variable_value
160
MARIA_PAGECACHE_READ_REQUESTS 215415
161
MARIA_PAGECACHE_READS 331
162
load index into cache t3 key (b), t2 key (c) ;
163
Table Op Msg_type Msg_text
164
test.t3 preload_keys Error Table 'test.t3' doesn't exist
165
test.t3 preload_keys error Corrupt
166
test.t2 preload_keys Error Key 'c' doesn't exist in table 't2'
167
test.t2 preload_keys status Operation failed
168
select g.variable_name,g.variable_value-i.variable_value from information_schema.global_status as g,initial as i where g.variable_name like "Maria_pagecache_read%" and g.variable_name=i.variable_name order by g.variable_name desc;
169
variable_name g.variable_value-i.variable_value
170
MARIA_PAGECACHE_READ_REQUESTS 215671
171
MARIA_PAGECACHE_READS 332
173
drop temporary table initial;
174
show status like "key_read%";