Bug #95507 innodb_stats_method is not honored when innodb_stats_persistent=ON
Description:
The value of innodb_stats_method is not honored when innodb_stats_persistent=ON (which is the default). It works as if innodb_stats_method=nulls_equal.
How to repeat:
== Part#1: let's use non-persistent stats and see how things should work ==
set global innodb_stats_persistent=off;
set global innodb_stats_transient_sample_pages=100;
set global innodb_stats_persistent_sample_pages=100;
show variables like 'innodb%stats%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_dict_stats_disabled_debug | OFF |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | OFF |
| innodb_stats_persistent_sample_pages | 100 |
| innodb_stats_transient_sample_pages | 100 |
+--------------------------------------+-------------+
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k(a int);
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
create table t1 (pk int primary key , a int, key(a)) engine=innodb;
# 400 K rows with NULLs:
insert into t1 select A.a+1000*B.a, null from one_k A , one_k B where B.a< 400;
# 101K rows with different non-NULL values:
insert into t1
select
A.a+1000*B.a,A.a+1000*B.a from one_k A, one_k B
where B.a between 400 and 500;
analyze table t1;
mysql> show keys from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | pk | A | 523081 | NULL | NULL | | BTREE | | | YES | NULL |
| t1 | 1 | a | 1 | a | A | 97346 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
### Cardinality of a = 97K. Close to 100K, good.
set global innodb_stats_method=nulls_unequal;
analyze table t1;
mysql> show keys from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | pk | A | 482866 | NULL | NULL | | BTREE | | | YES | NULL |
| t1 | 1 | a | 1 | a | A | 482866 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
### Cardinality of a is now 482K, which is close to 500K. Good, it works as
### expected.
==== part#2: Now, let's try the same with persistent InnoDB stats ====
drop table t1;
set global innodb_stats_persistent=on;
set global innodb_stats_method=nulls_equal;
mysql> show variables like 'innodb%stats%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_dict_stats_disabled_debug | OFF |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 100 |
| innodb_stats_transient_sample_pages | 100 |
+--------------------------------------+-------------+
create table t1 (pk int primary key , a int, key(a)) engine=innodb;
insert into t1 select A.a+1000*B.a, null from one_k A , one_k B where B.a< 400;
insert into t1
select
A.a+1000*B.a,A.a+1000*B.a from one_k A, one_k B
where B.a between 400 and 500;
analyze table t1;
mysql> show keys from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | pk | A | 518296 | NULL | NULL | | BTREE | | | YES | NULL |
| t1 | 1 | a | 1 | a | A | 100541 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
##### Ok, Cardinality of a is ~100K. Works as expected.
##### Now, let's tell it to consider NULLs unequal.
mysql> set global innodb_stats_method=nulls_unequal;
mysql> analyze table t1;
mysql> show keys from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | pk | A | 548692 | NULL | NULL | | BTREE | | | YES | NULL |
| t1 | 1 | a | 1 | a | A | 100541 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
### Cardinality (a) is still 100K ?
(It is re-calculated. I can set a different value for
innodb_stats_persistent_sample_pages and re-run the command. I get a slightly
different cardinality number but still, it is around 100K, while it should be around
500K).
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.