Debian Codegraph @

mysql-test/suite/innodb/t/histogram-debug.test

raw
1 --source include/have_debug.inc
2
3 SET GLOBAL DEBUG="+d, parallel_read_buffering_print";
4
5 --echo #
6 --echo # Sampling on an empty table
7 --echo #
8
9 SET GLOBAL innodb_monitor_enable = module_sampling;
10
11 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME='sampled_pages_read';
12
13 CREATE TABLE t1 (a INT);
14 ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS;
15 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a';
16 DROP TABLE t1;
17
18 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
19
20 SET GLOBAL innodb_monitor_disable = module_sampling;
21 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
22
23
24 --echo #
25 --echo # Case where only root node is present
26 --echo #
27
28 SET GLOBAL innodb_monitor_enable = module_sampling;
29 SET DEBUG='+d,histogram_force_sampling';
30
31 CREATE TABLE t1 (a INT);
32 INSERT INTO t1 VALUES (10), (20), (30), (40), (50), (60), (70), (80), (90), (100);
33 ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS;
34 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a';
35 DROP TABLE t1;
36
37 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
38
39 SET GLOBAL innodb_monitor_disable = "module_sampling";
40 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
41 SET DEBUG='-d,histogram_force_sampling';
42
43
44 --echo #
45 --echo # Case where only two levels is present with sampling_percentage = 50
46 --echo #
47
48 SET GLOBAL innodb_monitor_enable = module_sampling;
49 SET DEBUG='+d,histogram_force_sampling';
50
51 # Restrict the number of records in a page to 2
52 set global innodb_limit_optimistic_insert_debug = 2;
53
54 CREATE TABLE t1 (a INT);
55 INSERT INTO t1 VALUES (10), (20), (30);
56 ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS;
57 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a';
58 DROP TABLE t1;
59
60 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
61
62 SET GLOBAL innodb_monitor_disable = module_sampling;
63 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
64 set global innodb_limit_optimistic_insert_debug = 0;
65 SET DEBUG='-d,histogram_force_sampling';
66
67
68 --echo #
69 --echo # Case where only two levels is present with sampling_percentage = 100
70 --echo #
71
72 SET GLOBAL innodb_monitor_enable = module_sampling;
73
74 # Restrict the number of records in a page to 2
75 set global innodb_limit_optimistic_insert_debug = 2;
76
77 CREATE TABLE t1 (a INT);
78 INSERT INTO t1 VALUES (10), (20), (30);
79 ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS;
80 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a';
81 DROP TABLE t1;
82
83 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
84
85 SET GLOBAL innodb_monitor_disable = module_sampling;
86 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
87 set global innodb_limit_optimistic_insert_debug = 0;
88
89
90 --echo #
91 --echo # Normal case with small dataset
92 --echo #
93
94 SET GLOBAL innodb_monitor_enable = module_sampling;
95
96 # Restrict the number of records in a page to 2
97 set global innodb_limit_optimistic_insert_debug = 2;
98
99 CREATE TABLE t1 (a INT);
100
101 INSERT INTO t1 VALUES (10), (20), (30), (40), (50);
102 ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS;
103 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a';
104 set global innodb_limit_optimistic_insert_debug = 0;
105 DROP TABLE t1;
106
107 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
108
109 SET GLOBAL innodb_monitor_disable = module_sampling;
110 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
111
112
113 --echo #
114 --echo # Check that histogram with sampling works as expected with large dataset
115 --echo #
116
117 SET GLOBAL innodb_monitor_enable = module_sampling;
118
119 # Restrict the number of records in a page to 2
120 set global innodb_limit_optimistic_insert_debug = 2;
121 CREATE TABLE t1 (col1 DOUBLE);
122 INSERT INTO t1 SELECT RAND(1);
123 INSERT INTO t1 SELECT RAND(2) FROM t1;
124 INSERT INTO t1 SELECT RAND(3) FROM t1;
125 INSERT INTO t1 SELECT RAND(4) FROM t1;
126 INSERT INTO t1 SELECT RAND(5) FROM t1;
127 INSERT INTO t1 SELECT RAND(6) FROM t1;
128 INSERT INTO t1 SELECT RAND(7) FROM t1;
129 INSERT INTO t1 SELECT RAND(8) FROM t1;
130 INSERT INTO t1 SELECT RAND(9) FROM t1;
131 INSERT INTO t1 SELECT RAND(10) FROM t1;
132 ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1 WITH 5 BUCKETS;
133 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'col1';
134
135 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
136
137 SET GLOBAL innodb_monitor_disable = module_sampling;
138 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
139
140
141 --echo #
142 --echo # Check that histogram with sampling works as expected with large dataset and different sampling percentage
143 --echo #
144
145 SET GLOBAL innodb_monitor_enable = module_sampling;
146 SET DEBUG='+d,histogram_force_sampling';
147
148 ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1 WITH 50 BUCKETS;
149
150 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'col1';
151 DROP TABLE t1;
152
153 set global innodb_limit_optimistic_insert_debug = 0;
154
155 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
156
157 SET GLOBAL innodb_monitor_disable = module_sampling;
158 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
159 SET DEBUG='-d,histogram_force_sampling';
160
161
162 --echo #
163 --echo # Normal case with small dataset with different sampling_percentage
164 --echo #
165
166 SET GLOBAL innodb_monitor_enable = module_sampling;
167
168 SET DEBUG='+d,histogram_force_sampling';
169
170 # Restrict the number of records in a page to 2
171 set global innodb_limit_optimistic_insert_debug = 2;
172
173 CREATE TABLE t1 (a INT);
174 INSERT INTO t1 VALUES (10), (20), (30), (40), (50), (60), (70), (80), (90), (100);
175 ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS;
176 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a';
177
178 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
179
180 SET GLOBAL innodb_monitor_disable = module_sampling;
181 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
182
183
184 --echo #
185 --echo # Case to check if same rows are returned for the same seed
186 --echo #
187
188 SET GLOBAL innodb_monitor_enable = module_sampling;
189
190 ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS;
191
192 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a';
193
194 DROP TABLE t1;
195
196 set global innodb_limit_optimistic_insert_debug = 0;
197 SET DEBUG='-d,histogram_force_sampling';
198
199 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
200
201 SET GLOBAL innodb_monitor_disable = module_sampling;
202 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
203
204
205 --echo #
206 --echo # Case were there is an error while buffering
207 --echo #
208
209 SET GLOBAL innodb_monitor_enable = module_sampling;
210
211 SET DEBUG='+d,histogram_force_sampling';
212 SET GLOBAL DEBUG='+d,parallel_reader_histogram_induce_error';
213
214 # Restrict the number of records in a page to 2
215 set global innodb_limit_optimistic_insert_debug = 2;
216
217 CREATE TABLE t1 (a INT);
218 INSERT INTO t1 VALUES (10), (20), (30), (40), (50), (60), (70), (80), (90), (100);
219 ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS;
220 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a';
221 DROP TABLE t1;
222 set global innodb_limit_optimistic_insert_debug = 0;
223 SET GLOBAL DEBUG='-d,parallel_reader_histogram_induce_error';
224 SET DEBUG='-d,histogram_force_sampling';
225
226 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
227
228 SET GLOBAL innodb_monitor_disable = module_sampling;
229 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
230
231
232 --echo #
233 --echo # Case were partitions are involved
234 --echo #
235
236 SET GLOBAL innodb_monitor_enable = module_sampling;
237
238 CREATE TABLE p (col1 INT PRIMARY KEY, col2 INT, col3 INT)
239 PARTITION BY KEY (col1) PARTITIONS 4;
240 INSERT INTO p VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);
241 ANALYZE TABLE p UPDATE HISTOGRAM ON col2 WITH 50 BUCKETS;
242 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'col2';
243 DROP TABLE p;
244
245 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
246
247 SET GLOBAL innodb_monitor_disable = module_sampling;
248 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
249
250
251 --echo #
252 --echo # Case were there is an error while buffering when partitions are involved
253 --echo #
254
255 SET GLOBAL innodb_monitor_enable = module_sampling;
256
257 SET DEBUG='+d,histogram_force_sampling';
258 SET GLOBAL DEBUG='+d,parallel_reader_histogram_induce_error';
259
260 # Restrict the number of records in a page to 2
261 set global innodb_limit_optimistic_insert_debug = 2;
262
263 CREATE TABLE p (col1 INT PRIMARY KEY, col2 INT, col3 INT)
264 PARTITION BY KEY (col1) PARTITIONS 4;
265 INSERT INTO p VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);
266 ANALYZE TABLE p UPDATE HISTOGRAM ON col2 WITH 50 BUCKETS;
267 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'col2';
268
269 SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling';
270
271 DROP TABLE p;
272 set global innodb_limit_optimistic_insert_debug = 0;
273 SET GLOBAL DEBUG='-d,parallel_reader_histogram_induce_error';
274 SET DEBUG='-d,histogram_force_sampling';
275
276 SET GLOBAL innodb_monitor_disable = module_sampling;
277 SET GLOBAL innodb_monitor_reset_all = "module_sampling";
278
279
280 --echo #
281 --echo # RAPID use case : Break from sampling after sampling half the records
282 --echo #
283
284 CREATE TABLE t1 (id int);
285 INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
286
287 SET DEBUG = "+d,sample_read_sample_half";
288 ANALYZE TABLE t1 UPDATE HISTOGRAM ON id WITH 50 BUCKETS;
289
290 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'id';
291
292 SET DEBUG = "-d, sample_read_sample_half";
293
294 DROP TABLE t1;
295
296
297 --echo #
298 --echo # Error case where the table is discared
299 --echo #
300
301 CREATE TABLE t1 (id int);
302
303 # Discard tablespace to force histogram failure.
304 ALTER TABLE t1 DISCARD TABLESPACE;
305 ANALYZE TABLE t1 UPDATE HISTOGRAM ON id WITH 50 BUCKETS;
306
307 DROP TABLE t1;
308
309
310 --echo #
311 --echo # Simulated error case where parallel read thread returns error
312 --echo #
313
314 CREATE TABLE t1 (id int);
315 INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
316
317 SET GLOBAL DEBUG = "+d,simulate_sample_read_error";
318 ANALYZE TABLE t1 UPDATE HISTOGRAM ON id WITH 50 BUCKETS;
319
320 SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'id';
321
322 SET GLOBAL DEBUG = "-d,simulate_sample_read_error";
323
324 DROP TABLE t1;
325
326
327 --echo #
328 --echo # Cleanup
329 --echo #
330
331 SET GLOBAL innodb_monitor_enable = default;
332 SET GLOBAL innodb_monitor_disable = default;
333 SET GLOBAL innodb_monitor_reset_all = default;
334
335 SET GLOBAL DEBUG="-d, parallel_read_buffering_print";