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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
|
CREATE TABLE t (
id INT PRIMARY KEY,
val INT NOT NULL DEFAULT 0,
pad1 CHAR(250) NOT NULL DEFAULT "a",
pad2 CHAR(250) NOT NULL DEFAULT "a",
pad3 CHAR(250) NOT NULL DEFAULT "a",
pad4 CHAR(250) NOT NULL DEFAULT "a"
);
INSERT INTO t (id) VALUES (1);
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
INSERT INTO t (id) SELECT id + (SELECT MAX(id) FROM t) FROM t;
SELECT COUNT(*) FROM t;
COUNT(*)
65536
SELECT (SUM(IF(type='t',cnt,0))/SUM(cnt)) BETWEEN 0.25 AND 0.75 AS is_ratio_ok
FROM (
SELECT
IF(SPACE=(SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='test/t'),'t','other') AS type,
COUNT(*) as cnt
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
GROUP BY 1
ORDER BY 2 DESC
) AS aux;
is_ratio_ok
1
CREATE PROCEDURE log_spammer(f INT,t INT)
BEGIN
WHILE 1 DO
START TRANSACTION;
UPDATE t SET val = val + 1 WHERE id BETWEEN f AND t-1;
COMMIT;
END WHILE;
END|
CALL log_spammer(0*1310,(0+1)*1310);
CALL log_spammer(1*1310,(1+1)*1310);
CALL log_spammer(2*1310,(2+1)*1310);
CALL log_spammer(3*1310,(3+1)*1310);
CALL log_spammer(4*1310,(4+1)*1310);
CALL log_spammer(5*1310,(5+1)*1310);
CALL log_spammer(6*1310,(6+1)*1310);
CALL log_spammer(7*1310,(7+1)*1310);
CALL log_spammer(8*1310,(8+1)*1310);
CALL log_spammer(9*1310,(9+1)*1310);
CALL log_spammer(10*1310,(10+1)*1310);
CALL log_spammer(11*1310,(11+1)*1310);
CALL log_spammer(12*1310,(12+1)*1310);
CALL log_spammer(13*1310,(13+1)*1310);
CALL log_spammer(14*1310,(14+1)*1310);
CALL log_spammer(15*1310,(15+1)*1310);
CALL log_spammer(16*1310,(16+1)*1310);
CALL log_spammer(17*1310,(17+1)*1310);
CALL log_spammer(18*1310,(18+1)*1310);
CALL log_spammer(19*1310,(19+1)*1310);
CALL log_spammer(20*1310,(20+1)*1310);
CALL log_spammer(21*1310,(21+1)*1310);
CALL log_spammer(22*1310,(22+1)*1310);
CALL log_spammer(23*1310,(23+1)*1310);
CALL log_spammer(24*1310,(24+1)*1310);
CALL log_spammer(25*1310,(25+1)*1310);
CALL log_spammer(26*1310,(26+1)*1310);
CALL log_spammer(27*1310,(27+1)*1310);
CALL log_spammer(28*1310,(28+1)*1310);
CALL log_spammer(29*1310,(29+1)*1310);
CALL log_spammer(30*1310,(30+1)*1310);
CALL log_spammer(31*1310,(31+1)*1310);
CALL log_spammer(32*1310,(32+1)*1310);
CALL log_spammer(33*1310,(33+1)*1310);
CALL log_spammer(34*1310,(34+1)*1310);
CALL log_spammer(35*1310,(35+1)*1310);
CALL log_spammer(36*1310,(36+1)*1310);
CALL log_spammer(37*1310,(37+1)*1310);
CALL log_spammer(38*1310,(38+1)*1310);
CALL log_spammer(39*1310,(39+1)*1310);
CALL log_spammer(40*1310,(40+1)*1310);
CALL log_spammer(41*1310,(41+1)*1310);
CALL log_spammer(42*1310,(42+1)*1310);
CALL log_spammer(43*1310,(43+1)*1310);
CALL log_spammer(44*1310,(44+1)*1310);
CALL log_spammer(45*1310,(45+1)*1310);
CALL log_spammer(46*1310,(46+1)*1310);
CALL log_spammer(47*1310,(47+1)*1310);
CALL log_spammer(48*1310,(48+1)*1310);
CALL log_spammer(49*1310,(49+1)*1310);
Waiting for checkpoint age to be big enough to force a checkpoint even during recovery
SET GLOBAL innodb_checkpoint_disabled = 1;
# Kill the server
Killed the server while it had large checkpoint age
Starting server making it crash as soon as it creates a checkpoint
Starting the usual way
# restart
SELECT COUNT(*) FROM t;
COUNT(*)
65536
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 0*1310 AND (0+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 1*1310 AND (1+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 2*1310 AND (2+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 3*1310 AND (3+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 4*1310 AND (4+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 5*1310 AND (5+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 6*1310 AND (6+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 7*1310 AND (7+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 8*1310 AND (8+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 9*1310 AND (9+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 10*1310 AND (10+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 11*1310 AND (11+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 12*1310 AND (12+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 13*1310 AND (13+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 14*1310 AND (14+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 15*1310 AND (15+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 16*1310 AND (16+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 17*1310 AND (17+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 18*1310 AND (18+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 19*1310 AND (19+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 20*1310 AND (20+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 21*1310 AND (21+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 22*1310 AND (22+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 23*1310 AND (23+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 24*1310 AND (24+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 25*1310 AND (25+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 26*1310 AND (26+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 27*1310 AND (27+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 28*1310 AND (28+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 29*1310 AND (29+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 30*1310 AND (30+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 31*1310 AND (31+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 32*1310 AND (32+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 33*1310 AND (33+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 34*1310 AND (34+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 35*1310 AND (35+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 36*1310 AND (36+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 37*1310 AND (37+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 38*1310 AND (38+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 39*1310 AND (39+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 40*1310 AND (40+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 41*1310 AND (41+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 42*1310 AND (42+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 43*1310 AND (43+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 44*1310 AND (44+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 45*1310 AND (45+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 46*1310 AND (46+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 47*1310 AND (47+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 48*1310 AND (48+1)*1310-1;;
COUNT(DISTINCT(val))
1
SELECT COUNT(DISTINCT(val)) FROM t WHERE id BETWEEN 49*1310 AND (49+1)*1310-1;;
COUNT(DISTINCT(val))
1
CHECK TABLE t;
Table Op Msg_type Msg_text
test.t check status OK
DROP PROCEDURE log_spammer;
DROP TABLE t;
|
