enterprise-support team mailing list archive
-
enterprise-support team
-
Mailing list archive
-
Message #04878
[Bug 1538765] [NEW] Wrong statistics for InnoDB if persistent statistics used
Public bug reported:
When persistent statistics is used for InnoDB it still updates
cardinality with values which are incorrect.
Can be related to https://bugs.launchpad.net/percona-server/+bug/1484311
(but exists in 5.7.10) and http://bugs.mysql.com/bug.php?id=75428
How to repeat:
Option file:
--innodb_stats_auto_recalc=0 --innodb_stats_method=nulls_equal
--innodb_stats_on_metadata=0 --innodb_stats_persistent=1
--innodb_stats_persistent_sample_pages=1000
--innodb_stats_sample_pages=8 --innodb_stats_transient_sample_pages=8
Test file:
--source include/have_innodb.inc
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_PAGES=1000;
insert into sale
(customer_id,product_id,sale_time,sale_value,filler)
values
(1,1,NOW(),100,LPAD('X',250,'X')),
(1,2,NOW(),200,LPAD('X',250,'X')),
(1,3,NOW(),300,LPAD('X',250,'X')),
(2,4,NOW(),100,LPAD('X',250,'X')),
(2,5,NOW(),200,LPAD('X',250,'X')),
(3,6,NOW(),100,LPAD('X',250,'X'));
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
** Affects: mysql-server
Importance: Unknown
Status: Unknown
** Affects: percona-server
Importance: Undecided
Status: Confirmed
** Affects: percona-server/5.6
Importance: Undecided
Status: Confirmed
** Affects: percona-server/5.7
Importance: Undecided
Status: Confirmed
** Tags: i64760
** Also affects: percona-server/5.6
Importance: Undecided
Status: Confirmed
** Also affects: percona-server/5.7
Importance: Undecided
Status: New
** Bug watch added: MySQL Bug System #80178
http://bugs.mysql.com/bug.php?id=80178
** Also affects: mysql-server via
http://bugs.mysql.com/bug.php?id=80178
Importance: Unknown
Status: Unknown
** Changed in: percona-server/5.7
Status: New => Confirmed
--
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1538765
Title:
Wrong statistics for InnoDB if persistent statistics used
To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1538765/+subscriptions
