Bug #73598 Index cardinality is same for indexes on table when innodb_stats_persistent=ON

• Submitted: 15 Aug 2014 9:29
• Modified: 25 Sep 2017 14:13
• Reporter: Muhammad Omair
• Status: No Feedback
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.6.17-log MySQL Community Server (GPL)
• OS: Linux (Red Hat Enterprise Linux Server release 5.10 (Tikanga))
• CPU Architecture: Any
• Tags: innodb_stats_persistent = ON

[15 Aug 2014 9:29] Muhammad Omair

Description:
Index cardinality has the same value for all indexes on a table when innodb_stats_persistent = ON no matter how many times you run ANALYZE TABLE. In my case I ran it twenty times. This seems to be a wrong behaviour as the index cardinality should be more accurate. Its causing serious performance issues when the wrong index is selected. The index cardinality is correct when innodb_stats_persistent = OFF. 

How to repeat:
mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_stats%';
+--------------------------------------+---------------+
| Variable_name                        | Value         |
+--------------------------------------+---------------+
| innodb_stats_auto_recalc             | ON            |
| innodb_stats_method                  | nulls_ignored |
| innodb_stats_on_metadata             | OFF           |
| innodb_stats_persistent              | ON            |
| innodb_stats_persistent_sample_pages | 20            |
| innodb_stats_sample_pages            | 32            |
| innodb_stats_transient_sample_pages  | 32            |
+--------------------------------------+---------------+
7 rows in set (0.00 sec)

mysql> ANALYZE TABLE TestTbl;
+----------------------------+---------+----------+----------+
| Table                      | Op      | Msg_type | Msg_text |
+----------------------------+---------+----------+----------+
| testdb.TestTbl | analyze | status   | OK       |
+----------------------------+---------+----------+----------+
1 row in set (0.04 sec)

mysql> EXPLAIN SELECT                         name,                         id                 FROM                         TestTbl                 WHERE                         nameid = 130478989                         AND name = 'LinksTbl2'                         AND id IN ('1538540', '1538306');
+----+-------------+---------------+------+--------------------+------------+---------+-------+------+--------------------------+
| id | select_type | table         | type | possible_keys      | key        | key_len | ref   | rows | Extra                    |
+----+-------------+---------------+------+--------------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | TestTbl | ref  | PRIMARY,name | name | 102     | const |    2 | Using where; Using index |
+----+-------------+---------------+------+--------------------+------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> SHOW INDEX FROM TestTbl;
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| TestTbl |          0 | PRIMARY           |            1 | nameid      | A         |     1585550 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          0 | PRIMARY           |            2 | name        | A         |     1585550 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          0 | PRIMARY           |            3 | id          | A         |     1585550 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          0 | PRIMARY           |            4 | shopid       | A        |     1585550 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            1 | shopid       | A        |     1585550 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            2 | name        | A         |     1585550 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            3 | id          | A         |     1585550 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            4 | nameid      | A         |     1585550 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | name              |            1 | name        | A         |     1585550 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | name              |            2 | id          | A         |     1585550 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.01 sec)

mysql> ANALYZE TABLE TestTbl;                                                                                                                                                                                                            +----------------------------+---------+----------+----------+
| Table                      | Op      | Msg_type | Msg_text |
+----------------------------+---------+----------+----------+
| testdb.TestTbl             | analyze | status   | OK       |
+----------------------------+---------+----------+----------+
1 row in set (0.04 sec)

mysql> SHOW INDEX FROM TestTbl;
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| TestTbl |          0 | PRIMARY           |            1 | nameid            | A         |     1541903 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          0 | PRIMARY           |            2 | name              | A         |     1541903 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          0 | PRIMARY           |            3 | id                | A         |     1541903 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          0 | PRIMARY           |            4 | shopid            | A         |     1541903 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            1 | shopid            | A         |     1541903 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            2 | name              | A         |     1541903 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            3 | id                | A         |     1541903 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            4 | nameid            | A         |     1541903 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | name              |            1 | name              | A         |     1541903 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | name              |            2 | id                | A         |     1541903 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)

mysql> EXPLAIN SELECT                         name,                         id                 FROM                         TestTbl                 WHERE                         nameid = 130478989                         AND name = 'John'                         AND id IN ('2323232', '143132');
+----+-------------+---------------+------+--------------------+------------+---------+-------+------+--------------------------+
| id | select_type | table         | type | possible_keys      | key        | key_len | ref   | rows | Extra                    |
+----+-------------+---------------+------+--------------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | TestTbl | ref  | PRIMARY,name | name | 102     | const |    2 | Using where; Using index |
+----+-------------+---------------+------+--------------------+------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

*******************************************************************

mysql> SET GLOBAL innodb_stats_persistent = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_stats%';
+--------------------------------------+---------------+
| Variable_name                        | Value         |
+--------------------------------------+---------------+
| innodb_stats_auto_recalc             | ON            |
| innodb_stats_method                  | nulls_ignored |
| innodb_stats_on_metadata             | OFF           |
| innodb_stats_persistent              | OFF           |
| innodb_stats_persistent_sample_pages | 20            |
| innodb_stats_sample_pages            | 32            |
| innodb_stats_transient_sample_pages  | 32            |
+--------------------------------------+---------------+
7 rows in set (0.00 sec)

mysql> ANALYZE TABLE TestTbl;
+----------------------------+---------+----------+----------+
| Table                      | Op      | Msg_type | Msg_text |
+----------------------------+---------+----------+----------+
| testdb.TestTbl             | analyze | status   | OK       |
+----------------------------+---------+----------+----------+
1 row in set (0.04 sec)

mysql> EXPLAIN SELECT                         name,                         id                 FROM                         TestTbl                 WHERE                         nameid = 3434343                         AND name = 'John'                         AND id IN ('2323232', '143132');
+----+-------------+---------------+-------+--------------------+---------+---------+------+------+--------------------------+
| id | select_type | table         | type  | possible_keys      | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------------+-------+--------------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | TestTbl       | range | PRIMARY,name       | PRIMARY | 110     | NULL |    2 | Using where; Using index |
+----+-------------+---------------+-------+--------------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> SHOW INDEX FROM TestTbl;
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| TestTbl |          0 | PRIMARY           |            1 | nameid            | A         |        3267 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          0 | PRIMARY           |            2 | name              | A         |       11892 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          0 | PRIMARY           |            3 | id                | A         |     1581673 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          0 | PRIMARY           |            4 | shopid            | A         |     1581673 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1	   |            1 | shopid            | A         |     1581673 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            2 | name              | A         |     1581673 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            3 | id                | A         |      790836 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | TestTbl_IX1       |            4 | nameid            | A         |     1581673 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | name              |            1 | name              | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| TestTbl |          1 | name              |            2 | id                | A         |     1581673 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)

Suggested fix:
Index cardinality should be more or less similar to when innodb_stats_persistent = ON.

[25 Aug 2017 14:13] MySQL Verification Team

Hi!

First and first of all, we do not see your definition of the table, nor are we able to see the data. Hence, a full dump of the table would be necessary. Use the "Files" tab for the upload.

Before you do that, you can try latest 5.7 and check whether the behaviour is changed, because, there have been many bugs fixed on the persistent stats in the version 5.7.

Third, have you checked whether InnoDB tables for statistics have been created in the `mysql` schema ??

Fourth, and last, what kind of the behaviour you get when the persistent statistics is OFF ???

Please, first check last three items and if you do not see anything different, then upload the table.

[26 Sep 2017 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".