Debian Codegraph @

mysql-test/suite/ndb_opt/index_stat.test

raw
1 -- source include/have_ndb.inc
2
3 # Notes on index stats in *.test.
4 #
5 # Most tables here have few table rows. Index stats are not very
6 # useful in such cases but the optimizer seems to use them anyway.
7 # One reason may be that nested joins is only join method.
8 #
9 # In real production index stats are computed daily or weekly.
10 # But tests here must compute them at once if "explain" is used.
11 # Thus: insert (or other dml) - analyze table - explain.
12 #
13 # Index stats are approximate since only one replica was scanned
14 # and values are interpolated from samples. MTR however should be
15 # deterministic. If not use --replace column 9 # (rows).
16
17 set @is_enable_default = @@global.ndb_index_stat_enable;
18
19 set @is_enable = 1;
20 source index_stat_enable.inc;
21
22 # test changing suboptions
23 show global variables like 'ndb_index_stat_option';
24 set @save_option = @@global.ndb_index_stat_option;
25 # some options
26 set @@global.ndb_index_stat_option = 'loop_idle=3333,cache_limit=44M';
27 set @@global.ndb_index_stat_option = 'cache_lowpct=85,evict_delay=55';
28 set @@global.ndb_index_stat_option = 'check_delay=234s';
29 show global variables like 'ndb_index_stat_option';
30 set @@global.ndb_index_stat_option = @save_option;
31 show global variables like 'ndb_index_stat_option';
32
33 # TEST: main
34 create table t1 (
35 a1 int unsigned not null,
36 b1 int unsigned not null,
37 c1 int unsigned not null,
38 primary key (a1),
39 index b1x (b1),
40 index c1x (c1)
41 ) engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
42
43 create table t2 (
44 a2 int unsigned not null,
45 b2 int unsigned not null,
46 c2 int unsigned not null,
47 primary key (a2),
48 index b2x (b2),
49 index c2x (c2)
50 ) engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
51
52 # enough rows to make index stats more approximate
53
54 --disable_query_log
55 let $i = 1000;
56 while ($i)
57 {
58 dec $i;
59 eval insert into t1 values ($i, $i % 100, $i % 10);
60 }
61 let $i = 1000;
62 while ($i)
63 {
64 dec $i;
65 eval insert into t2 values ($i, $i % 10, $i % 100);
66 }
67 --enable_query_log
68 analyze table t1, t2;
69
70 # TEST: key equal constant
71
72 --echo # must use b1x
73 --replace_column 10 # 11 #
74 explain select * from t1
75 where b1 = 5 and c1 = 5;
76
77 --echo # must use c2x
78 --replace_column 10 # 11 #
79 explain select * from t2
80 where b2 = 5 and c2 = 5;
81
82 # TEST: keys equal constant in join
83
84 --echo # must use b1x, c2x
85 --replace_column 10 # 11 #
86 explain select * from t1, t2
87 where c1 = c2 and b1 = 5 and b2 = 5;
88
89 --echo # must use c2x, b1x
90 --replace_column 10 # 11 #
91 explain select * from t1, t2
92 where b1 = b2 and c1 = 5 and c2 = 5;
93
94 # TEST: join via keys of different selectivity
95
96 --echo # must use t1, c2x
97 --replace_column 10 # 11 #
98 explain select * from t1, t2
99 where c1 = c2;
100 --echo # must use t2, b1x
101 --replace_column 10 # 11 #
102 explain select * from t1, t2
103 where b1 = b2;
104
105 # TEST: bug#44760 quick distinct
106 # QUICK_GROUP_MIN_MAX_SELECT says "Using index for group-by".
107 # Should happen only for low cardinality index.
108 # wl4124_todo: result is wrong until HA_KEYREAD_ONLY is set
109
110 --echo # should NOT say: Using index for group-by
111 --replace_column 10 # 11 #
112 explain select distinct (a1) from t1;
113
114 --echo # must say: Using index for group by
115 --replace_column 10 # 11 #
116 explain select distinct (b1) from t1;
117
118 --echo # must say: Using index for group by
119 --replace_column 10 # 11 #
120 explain select distinct (c1) from t1;
121
122 # TEST: end
123 drop table t1, t2;
124
125 # turn index stats OFF in client (falls back on other methods)
126 # code snippet moved from ndb_index_ordered.test
127
128 create table t1 (a int, b int, c varchar(10) not null,
129 primary key using hash (a), index(b,c)) engine=ndb;
130 insert into t1 values
131 (1,10,'aaa'),(2,10,'bbb'),(3,10,'ccc'),
132 (4,20,'aaa'),(5,20,'bbb'),(6,20,'ccc'),
133 (7,30,'aaa'),(8,30,'bbb'),(9,30,'ccc');
134 analyze table t1;
135 let $is_loop = 4;
136 while ($is_loop)
137 {
138 # 4-OFF 3-ON 2-OFF 1-ON
139 let $is_enable = `select ($is_loop=3 or $is_loop=1)`;
140 dec $is_loop;
141 eval set @@local.ndb_index_stat_enable = $is_enable;
142
143 select count(*) from t1 where b < 10;
144 select count(*) from t1 where b >= 10 and c >= 'bbb';
145 select count(*) from t1 where b > 10;
146 select count(*) from t1 where b <= 20 and c < 'ccc';
147 select count(*) from t1 where b = 20 and c = 'ccc';
148 select count(*) from t1 where b > 20;
149 select count(*) from t1 where b = 30 and c > 'aaa';
150 select count(*) from t1 where b <= 20;
151 select count(*) from t1 where b >= 20 and c > 'aaa';
152 }
153 drop table t1;
154
155 # bug#XXXXX
156 # autocreate=false,enable=1 is now acceptable
157 # following gives warning while "no stats" is counted as error
158 create table t1 (a int, b int, primary key using hash (a), index x1 (b))
159 engine=ndb;
160 insert into t1 values (1,11),(2,22),(3,33);
161 # make_join_statistics() -> info() -> ndb_index_stat_set_rpk()
162 # error 4715 - no stats
163 select * from t1 order by a;
164 # error 9003 suppressed - previous recent error
165 select * from t1 order by a;
166 # analyze clears previous error at once
167 analyze table t1;
168 drop table t1;
169
170 # bug#XXXXX
171 # wrong byte size from some types to NdbPack
172 # before error fixes causes stats to be ignored silently (error 4716)
173 # best seen with debug and export NDB_PACK_ABORT_ON_ERROR=1
174 # affected types: mediumint datetime date time timestamp
175
176 create table t1 (
177 pk int not null,
178 a tinyint not null,
179 b tinyint unsigned not null,
180 c smallint not null,
181 d smallint unsigned not null,
182 e mediumint not null,
183 f mediumint unsigned not null,
184 g int not null,
185 h int unsigned not null,
186 i bigint not null,
187 j bigint unsigned not null,
188 k float not null,
189 l double not null,
190 m decimal not null,
191 n decimal unsigned not null,
192 primary key using hash (pk),
193 index (a),
194 index (b),
195 index (c),
196 index (d),
197 index (e),
198 index (f),
199 index (g),
200 index (h),
201 index (i),
202 index (j),
203 index (k),
204 index (l),
205 index (m),
206 index (n)
207 ) engine=ndb;
208 insert into t1 values
209 (1,11,11,11,11,11,11,11,11,11,11,11,11,11,11),
210 (2,22,22,22,22,22,22,22,22,22,22,22,22,22,22),
211 (3,33,33,33,33,33,33,33,33,33,33,33,33,33,33);
212 let $i = 2;
213 while ($i)
214 {
215 dec $i;
216 if (!$i)
217 {
218 eval analyze table t1;
219 }
220 eval select count(*) from t1 where a > 22;
221 eval select count(*) from t1 where b > 22;
222 eval select count(*) from t1 where c > 22;
223 eval select count(*) from t1 where d > 22;
224 eval select count(*) from t1 where e > 22;
225 eval select count(*) from t1 where f > 22;
226 eval select count(*) from t1 where g > 22;
227 eval select count(*) from t1 where h > 22;
228 eval select count(*) from t1 where i > 22;
229 eval select count(*) from t1 where j > 22;
230 eval select count(*) from t1 where k > 22;
231 eval select count(*) from t1 where l > 22;
232 eval select count(*) from t1 where m > 22;
233 eval select count(*) from t1 where n > 22;
234 }
235 drop table t1;
236
237 create table t1 (
238 pk int not null,
239 a datetime not null,
240 b date not null,
241 c year not null,
242 d time not null,
243 e timestamp not null,
244 primary key using hash (pk),
245 index (a),
246 index (b),
247 index (c),
248 index (d),
249 index (e)
250 ) engine=ndb;
251 insert into t1 values
252 (1,'1971-01-01 01:01:01','1971-01-01','1971','01:01:01','1971-01-01 01:01:01'),
253 (2,'1972-02-02 02:02:02','1972-02-02','1972','02:02:02','1972-02-02 02:02:02'),
254 (3,'1973-03-03 03:03:03','1973-03-03','1973','03:03:03','1973-03-03 03:03:03');
255 let $i = 2;
256 while ($i)
257 {
258 dec $i;
259 if (!$i)
260 {
261 eval analyze table t1;
262 }
263 eval select count(*) from t1 where a > '1972-02-02 02:02:02';
264 eval select count(*) from t1 where b > '1972-02-02';
265 eval select count(*) from t1 where c > '1972';
266 eval select count(*) from t1 where d > '02:02:02';
267 eval select count(*) from t1 where e > '1972-02-02 02:02:02';
268 }
269 drop table t1;
270
271 create table t1 (
272 pk int not null,
273 a char(10) not null,
274 b varchar(10) not null,
275 c varchar(1000) not null,
276 d binary(10) not null,
277 e varbinary(10) not null,
278 f varbinary(1000) not null,
279 primary key using hash (pk),
280 index (a),
281 index (b),
282 index (c),
283 index (d),
284 index (e),
285 index (f)
286 ) engine=ndb character set latin1;
287 insert into t1 values
288 ('1','111','111','111','111','111','111'),
289 ('2','222','222','222','222','222','222'),
290 ('3','333','333','333','333','333','333');
291 let $i = 2;
292 while ($i)
293 {
294 dec $i;
295 if (!$i)
296 {
297 eval analyze table t1;
298 }
299 eval select count(*) from t1 where a > '222';
300 eval select count(*) from t1 where b > '222';
301 eval select count(*) from t1 where c > '222';
302 eval select count(*) from t1 where d > '222';
303 eval select count(*) from t1 where e > '222';
304 eval select count(*) from t1 where f > '222';
305 }
306 drop table t1;
307
308 #
309 # Check estimates of records per key for partial keys using unique/primary ordered index
310 #
311
312 create table t1 (a1 int, b1 int, primary key(b1), key(a1)) engine=ndbcluster partition by key() partitions 1;
313 create table t2 (b2 int, c2 int, primary key(b2,c2)) engine=ndbcluster partition by key() partitions 1;
314
315 --disable_query_log
316 let $i = 100;
317 while ($i)
318 {
319 eval insert into t1 (a1,b1) values ($i,$i);
320 eval insert into t2 (b2,c2) values ($i mod 2, $i div 2);
321 dec $i;
322 }
323 --enable_query_log
324
325 --echo # table t1 is only for forcing record by key count for table t2 that should be near 50 (not 1)
326 analyze table t1, t2;
327 # Hide Extra column
328 --replace_column 10 #
329 explain select * from t1, t2 where b2 = b1 and a1 = 1;
330
331 drop table t1, t2;
332
333 # bugXXXXX rule b3.3 fix
334
335 CREATE TABLE t1(
336 K INT NOT NULL AUTO_INCREMENT,
337 I INT,
338 J INT,
339 L INT,
340 PRIMARY KEY(K),
341 KEY(I,J),
342 KEY(L)
343 ) ENGINE=ndbcluster
344 partition by key (K) partitions 1;
345
346 INSERT INTO t1(I,J,L) VALUES
347 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
348 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(0,0,0);
349
350 INSERT INTO t1(I,J,L) SELECT I,1,I FROM t1;
351 INSERT INTO t1(I,J,L) SELECT I,2,I FROM t1;
352 INSERT INTO t1(I,J,L) SELECT I,3,I FROM t1;
353 INSERT INTO t1(I,J,L) SELECT I,4,I FROM t1;
354 INSERT INTO t1(I,J,L) SELECT I,5,I FROM t1;
355 INSERT INTO t1(I,J,L) SELECT I,6,I FROM t1;
356 INSERT INTO t1(I,J,L) SELECT I,7,I FROM t1;
357 INSERT INTO t1(I,J,L) SELECT I,8,I FROM t1;
358
359 select i, count(*) from t1 group by 1 order by 1;
360 select l, count(*) from t1 group by 1 order by 1;
361
362 ANALYZE TABLE t1;
363
364 explain SELECT count(*) as Count FROM t1 WHERE I = 5;
365 SELECT count(*) as Count FROM t1 WHERE I = 5;
366
367 # before fix rows was 128 instead of 256
368 explain SELECT count(*) as Count FROM t1 WHERE L = 5;
369 SELECT count(*) as Count FROM t1 WHERE L = 5;
370
371 drop table t1;
372
373 # bug#13407848
374 # signed char in compute length bytes caused ndbrequire in Trix.cpp
375
376 create table t1 (
377 a int unsigned not null,
378 b char(180) not null,
379 primary key using hash (a),
380 index (b)
381 ) engine=ndb charset=binary;
382 insert into t1 values (1,'a'),(2,'b'),(3,'c');
383 analyze table t1;
384 analyze table t1;
385 analyze table t1;
386 drop table t1;
387
388
389 # Bug#18715165
390
391 CREATE TABLE t1 (id int unsigned NOT NULL auto_increment PRIMARY KEY,
392 val int unsigned, UNIQUE (val)) ENGINE=NDBCluster;
393
394 INSERT INTO test.t1 values (1,1), (2,2);
395
396 --exec $NDB_INDEX_STAT --update -d test t1 >> $NDB_TOOLS_OUTPUT
397
398 #clean up
399 DROP TABLE t1;
400
401 # Bug#29611297 NDB: INDEX STAT HAS ISSUES WITH ONLINE ALTER TABLE TO/FROM
402 # READBACKUP. Online ALTER TABLE statements that resulted in the index
403 # version being bumped up left behind orphaned index stats corresponding
404 # to the previous index version.
405 CREATE TABLE t1 (
406 a INT PRIMARY KEY,
407 b INT,
408 c INT,
409 UNIQUE(c)
410 ) ENGINE NDB PARTITION BY KEY(a);
411
412 --echo Insert 100 rows
413 --disable_query_log
414 let $i = 100;
415 while ($i)
416 {
417 dec $i;
418 eval INSERT INTO t1 VALUES($i, $i % 100, $i);
419 }
420 --enable_query_log
421
422 # Trigger index stats creation which creates 1 metadata entry
423 # in the head table (exposed to users via the ndbinfo.index_stats
424 # table) and n sample table entries per index
425 ANALYZE TABLE t1;
426 # Uncomment below to check details
427 #SELECT * FROM ndbinfo.index_stats;
428 SELECT CASE COUNT(*) > 0
429 WHEN 1 THEN 'YES' ELSE 'NO' END
430 AS stats_metadata_present FROM ndbinfo.index_stats;
431 # 21 samples are expected here on little endian systems
432 # 27 samples on big endian
433 replace_regex /^2[17] /21-or-27 /;
434 --exec $NDB_SELECT_COUNT -d mysql ndb_index_stat_sample
435
436 # Record the value of 'rows' in the EXPLAIN output for a later check
437 --let $query=SELECT * FROM t1 WHERE c > 50
438 --let $explain_rows_pre_alter=query_get_value(EXPLAIN $query, rows, 1)
439
440 # Online ALTER that creates indexes with new versions
441 ALTER TABLE t1 COMMENT="NDB_TABLE=READ_BACKUP=0";
442
443 # Trigger index stats creation. The ANALYZE below will clean up
444 # samples corresponding to the old index version in the
445 # ndb_index_stat_sample table and create new samples for the
446 # new index version.
447 ANALYZE TABLE t1;
448 # Uncomment below to check details
449 #SELECT * FROM ndbinfo.index_stats;
450 # Check for duplicates
451 SELECT COUNT(index_id)
452 FROM ndbinfo.index_stats
453 GROUP BY index_id
454 HAVING COUNT(index_id) > 1;
455 # Should still be 21 (or 27) samples. Was 42 (21 created with
456 # <index_id, old index_version> as the key and 21 more
457 # with <index_id, new index version>) before the bug fix
458 # 21 samples are expected here on little endian systems
459 # 27 samples on big endian
460 replace_regex /^2[17] /21-or-27 /;
461 --exec $NDB_SELECT_COUNT -d mysql ndb_index_stat_sample
462
463 # Execute the same EXPLAIN as earlier. The expectation is that the 'rows'
464 # output is the same from before the ALTER
465 --let $explain_rows_post_alter=query_get_value(EXPLAIN $query, rows, 1)
466 --expr $explain_rows_change = $explain_rows_post_alter - $explain_rows_pre_alter
467 if ($explain_rows_change == 0)
468 {
469 --echo EXPLAIN is the same both pre- and post-ALTER
470 }
471 if ($explain_rows_change != 0)
472 {
473 --die EXPLAIN has changed. Pre-ALTER: $explain_rows_pre_alter and post-ALTER: $explain_rows_post_alter
474 }
475
476 DROP TABLE t1;
477
478 # We expect 0 entries in the sample table but "stale" metadata
479 # info is still present in the head table
480 # Uncomment below to check details
481 #SELECT * FROM ndbinfo.index_stats;
482 SELECT CASE COUNT(*) > 0
483 WHEN 1 THEN 'YES' ELSE 'NO' END
484 AS stats_metadata_present FROM ndbinfo.index_stats;
485 --exec $NDB_SELECT_COUNT -d mysql ndb_index_stat_sample
486
487 # Suppress errors which can occur when disconnecting + reconnecting
488 call mtr.add_suppression("Failed to acquire global schema lock");
489 call mtr.add_suppression("Failed to release global schema lock");
490
491 set @is_enable = @is_enable_default;
492 source index_stat_enable.inc;
493 --remove_file $NDB_TOOLS_OUTPUT