Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.17
-
10.1.18
Description
This is a continuation of MDEV-10649, but this time, it is about index cardinalities.
Under concurrent load, if you're lucky, you may get a very wrong index cardinality estimates.
Here's a way to observe:
Apply psergey-sept11-stop-at-index-update.diff. This patch adds capability to stop the index cardinality update code at the right place.
Then, start the server and create the test dataset:
create table ten(a int) engine=myisam;
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
|
create table one_k(a int) engine=myisam;
|
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
|
|
|
create table t0 (a int) engine=myisam;
|
insert into t0 values (1),(2),(3);
|
|
|
create table t1 (
|
pk int primary key,
|
col1 int not null,
|
col2 int not null,
|
key(col1)
|
) engine= innodb;
|
|
|
insert into t1 select a,a,a from one_k;
|
analyze table t1;
|
Run a query and observe t1.rows=1 which is a precise index statistics:
|
explain select * from t0, t1 where t1.col1=t0.a;
|
+------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
|
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
|
| 1 | SIMPLE | t1 | ref | col1 | col1 | 4 | j5.t0.a | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
|
Now, make innodb's statistics update stop in the middle of update:
system touch /tmp/dict_stats_analyze_index_must_stop
|
Do something to cause auto statistics update (we are running with innodb_stats_auto_recalc=ON, which is the default).
insert into t1 select 5000+pk,5000+col1, 5000+col2 from t1 limit 2000;
|
Query OK, 1000 rows affected (0.37 sec)
|
Records: 1000 Duplicates: 0 Warnings: 0
|
OPTIONAL: You can check the server stderr and see this line:
AAA: dict_stats_analyze_index called dict_stats_empty_index for col1
|
Then, run the test query again:
flush tables;
|
explain select * from t0, t1 where t1.col1=t0.a;
|
+------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
|
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
|
| 1 | SIMPLE | t1 | ref | col1 | col1 | 4 | j5.t0.a | 1000 | |
|
+------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
|
Note rows=1000 ! The real value is rows=1, both before and after the update.
The value one is seeing is about table_rows/2.
Make the stats calculation finish:
system rm /tmp/dict_stats_analyze_index_must_stop
|
wait a few seconds, and check again:
flush tables;
|
explain select * from t0, t1 where t1.col1=t0.a;
|
+------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
|
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
|
| 1 | SIMPLE | t1 | ref | col1 | col1 | 4 | j5.t0.a | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+---------+------+-------------+
|
rows=1, the correct value is back.
Attachments
Issue Links
- relates to
-
-
- Closed
-
- links to