Wrong statistics for InnoDB if persistent statistics used
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned |
Bug Description
When persistent statistics is used for InnoDB it still updates cardinality with values which are incorrect.
Can be related to https:/
How to repeat:
Option file:
--innodb_
Test file:
--source include/
create table sale
(
id int primary key auto_increment,
customer_id int not null,
product_id int not null,
sale_time datetime not null,
sale_value decimal(10,2) not null,
filler varchar(250) not null,
key(customer_id, sale_time),
key(product_id)
)
engine innodb STATS_AUTO_RECALC=0 STATS_SAMPLE_
insert into sale
(customer_
values
(1,1,NOW(
(1,2,NOW(
(1,3,NOW(
(2,4,NOW(
(2,5,NOW(
(3,6,NOW(
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale;
show indexes from sale;
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
analyze table sale;
show indexes from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 441/5;
show indexes from sale;
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 581/2;
show indexes from sale;
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
Actual result:
show indexes from sale;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sale 0 PRIMARY 1 id A 128 NULL NULL BTREE
sale 1 customer_id 1 customer_id A 128 NULL NULL BTREE
sale 1 customer_id 2 sale_time A 128 NULL NULL BTREE
sale 1 product_id 1 product_id A 128 NULL NULL BTREE
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
count(distinct customer_id) count(distinct sale_time) count(distinct product_id)
3 2 6
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
count(*) count(distinct customer_id) count(distinct customer_id, sale_time) count(distinct product_id)
384 3 6 6
analyze table sale;
Table Op Msg_type Msg_text
test.sale analyze status OK
show indexes from sale;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sale 0 PRIMARY 1 id A 384 NULL NULL BTREE
sale 1 customer_id 1 customer_id A 6 NULL NULL BTREE
sale 1 customer_id 2 sale_time A 12 NULL NULL BTREE
sale 1 product_id 1 product_id A 12 NULL NULL BTREE
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 441/5;
show indexes from sale;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sale 0 PRIMARY 1 id A 461 NULL NULL BTREE
sale 1 customer_id 1 customer_id A 7 NULL NULL BTREE
sale 1 customer_id 2 sale_time A 14 NULL NULL BTREE
sale 1 product_id 1 product_id A 14 NULL NULL BTREE
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
count(distinct customer_id) count(distinct sale_time) count(distinct product_id)
3 3 6
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
count(*) count(distinct customer_id) count(distinct customer_id, sale_time) count(distinct product_id)
461 3 9 6
insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 581/2;
show indexes from sale;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sale 0 PRIMARY 1 id A 694 NULL NULL BTREE
sale 1 customer_id 1 customer_id A 10 NULL NULL BTREE
sale 1 customer_id 2 sale_time A 21 NULL NULL BTREE
sale 1 product_id 1 product_id A 21 NULL NULL BTREE
select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale;
count(distinct customer_id) count(distinct sale_time) count(distinct product_id)
3 4 6
select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale;
count(*) count(distinct customer_id) count(distinct customer_id, sale_time) count(distinct product_id)
694 3 12 6
Expected result:
all calls of show indexes from sale; (except first one) should return
show indexes from sale;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
sale 0 PRIMARY 1 id A 384 NULL NULL BTREE
sale 1 customer_id 1 customer_id A 6 NULL NULL BTREE
sale 1 customer_id 2 sale_time A 12 NULL NULL BTREE
sale 1 product_id 1 product_id A 12 NULL NULL BTREE
FLUSH TABLE fixes SHOW INDEX output.