Bug #1484311 “Wrong cardinality for InnoDB table” : Bugs : Percona Server moved to https://jira.percona.com/projects/PS

Revision history for this message

Laurynas Biveinis (laurynas-biveinis) wrote on 2015-08-13:

#1

Upstream?

Revision history for this message

Nilnandan Joshi (nilnandan-joshi) wrote on 2015-08-14:

#2

Download full text (21.8 KiB)

Confirmed with PS 5.6

nilnandan@desktop:~/Downloads$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb;
ERROR 1050 (42S01): Table 't1' already exists
mysql>
mysql> drop table t1
->
-> ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(f1) values (1),(2),(3),(4),(5),(6);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 24 rows affected (0.01 sec)
Records: 24 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 48 rows affected (0.00 sec)
Records: 48 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 96 rows affected (0.00 sec)
Records: 96 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 192 rows affected (0.00 sec)
Records: 192 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 384 rows affected (0.01 sec)
Records: 384 Duplicates: 0 Warnings: 0

mysql> update t1 set f2=rand();
Query OK, 768 rows affected, 1 warning (0.01 sec)
Rows matched: 768 Changed: 768 Warnings: 1

mysql>
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. |...

Confirmed with PS 5.6

nilnandan@desktop:~/Downloads$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb;
ERROR 1050 (42S01): Table 't1' already exists
mysql> 
mysql> drop table t1
    -> 
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(f1) values (1),(2),(3),(4),(5),(6);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 24 rows affected (0.01 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 48 rows affected (0.00 sec)
Records: 48  Duplicates: 0  Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 96 rows affected (0.00 sec)
Records: 96  Duplicates: 0  Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 192 rows affected (0.00 sec)
Records: 192  Duplicates: 0  Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 384 rows affected (0.01 sec)
Records: 384  Duplicates: 0  Warnings: 0

mysql> update t1 set f2=rand();
Query OK, 768 rows affected, 1 warning (0.01 sec)
Rows matched: 768  Changed: 768  Warnings: 1

mysql> 
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                  |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          1 | f1       |            1 | f1          | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | f1_2     |            1 | f1          | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | f1_2     |            2 | f2          | A         |          24 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> select count(distinct f1), count(distinct f2), count(distinct f1, f2), count(*) from t1;
+--------------------+--------------------+------------------------+----------+
| count(distinct f1) | count(distinct f2) | count(distinct f1, f2) | count(*) |
+--------------------+--------------------+------------------------+----------+
|                  6 |                  2 |                     12 |      768 |
+--------------------+--------------------+------------------------+----------+
1 row in set (0.00 sec)

mysql> select * from mysql.innodb_index_stats;
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| percona       | checksums  | PRIMARY         | 2015-08-12 13:07:19 | n_diff_pfx01 |          3 |           2 | db                                |
| percona       | checksums  | PRIMARY         | 2015-08-12 13:07:19 | n_diff_pfx02 |         29 |           2 | db,tbl                            |
| percona       | checksums  | PRIMARY         | 2015-08-12 13:07:19 | n_diff_pfx03 |        109 |           2 | db,tbl,chunk                      |
| percona       | checksums  | PRIMARY         | 2015-08-12 13:07:19 | n_leaf_pages |          2 |        NULL | Number of leaf pages in the index |
| percona       | checksums  | PRIMARY         | 2015-08-12 13:07:19 | size         |          3 |        NULL | Number of pages in the index      |
| percona       | checksums  | ts_db_tbl       | 2015-08-12 13:07:19 | n_diff_pfx01 |         49 |           1 | ts                                |
| percona       | checksums  | ts_db_tbl       | 2015-08-12 13:07:19 | n_diff_pfx02 |         50 |           1 | ts,db                             |
| percona       | checksums  | ts_db_tbl       | 2015-08-12 13:07:19 | n_diff_pfx03 |         76 |           1 | ts,db,tbl                         |
| percona       | checksums  | ts_db_tbl       | 2015-08-12 13:07:19 | n_diff_pfx04 |        109 |           1 | ts,db,tbl,chunk                   |
| percona       | checksums  | ts_db_tbl       | 2015-08-12 13:07:19 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| percona       | checksums  | ts_db_tbl       | 2015-08-12 13:07:19 | size         |          1 |        NULL | Number of pages in the index      |
| source_db     | altertest  | PRIMARY         | 2015-07-20 12:44:07 | n_diff_pfx01 |          0 |           1 | id                                |
| source_db     | altertest  | PRIMARY         | 2015-07-20 12:44:07 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| source_db     | altertest  | PRIMARY         | 2015-07-20 12:44:07 | size         |          1 |        NULL | Number of pages in the index      |
| test          | nil        | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | n_diff_pfx01 |         10 |           1 | DB_ROW_ID                         |
| test          | nil        | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | nil        | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | size         |          1 |        NULL | Number of pages in the index      |
| test          | t1         | GEN_CLUST_INDEX | 2015-08-14 13:29:37 | n_diff_pfx01 |        768 |           3 | DB_ROW_ID                         |
| test          | t1         | GEN_CLUST_INDEX | 2015-08-14 13:29:37 | n_leaf_pages |          3 |        NULL | Number of leaf pages in the index |
| test          | t1         | GEN_CLUST_INDEX | 2015-08-14 13:29:37 | size         |          4 |        NULL | Number of pages in the index      |
| test          | t1         | f1              | 2015-08-14 13:29:37 | n_diff_pfx01 |          6 |           1 | f1                                |
| test          | t1         | f1              | 2015-08-14 13:29:37 | n_diff_pfx02 |        768 |           1 | f1,DB_ROW_ID                      |
| test          | t1         | f1              | 2015-08-14 13:29:37 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | t1         | f1              | 2015-08-14 13:29:37 | size         |          1 |        NULL | Number of pages in the index      |
| test          | t1         | f1_2            | 2015-08-14 13:29:37 | n_diff_pfx01 |          6 |           1 | f1                                |
| test          | t1         | f1_2            | 2015-08-14 13:29:37 | n_diff_pfx02 |         12 |           1 | f1,f2                             |
| test          | t1         | f1_2            | 2015-08-14 13:29:37 | n_diff_pfx03 |        768 |           1 | f1,f2,DB_ROW_ID                   |
| test          | t1         | f1_2            | 2015-08-14 13:29:37 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | t1         | f1_2            | 2015-08-14 13:29:37 | size         |          1 |        NULL | Number of pages in the index      |
| test          | t2         | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| test          | t2         | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | t2         | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | size         |          1 |        NULL | Number of pages in the index      |
| test          | t3         | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| test          | t3         | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | t3         | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | size         |          1 |        NULL | Number of pages in the index      |
| test          | testb      | PRIMARY         | 2015-07-24 09:45:39 | n_diff_pfx01 |          0 |           1 | a                                 |
| test          | testb      | PRIMARY         | 2015-07-24 09:45:39 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | testb      | PRIMARY         | 2015-07-24 09:45:39 | size         |          1 |        NULL | Number of pages in the index      |
| test          | testb      | fk2             | 2015-07-24 09:45:39 | n_diff_pfx01 |          0 |           1 | e                                 |
| test          | testb      | fk2             | 2015-07-24 09:45:39 | n_diff_pfx02 |          0 |           1 | e,a                               |
| test          | testb      | fk2             | 2015-07-24 09:45:39 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | testb      | fk2             | 2015-07-24 09:45:39 | size         |          1 |        NULL | Number of pages in the index      |
| test          | testb      | uk1             | 2015-07-24 09:45:39 | n_diff_pfx01 |          0 |           1 | b                                 |
| test          | testb      | uk1             | 2015-07-24 09:45:39 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | testb      | uk1             | 2015-07-24 09:45:39 | size         |          1 |        NULL | Number of pages in the index      |
| test          | testb      | uk2             | 2015-07-24 09:45:39 | n_diff_pfx01 |          0 |           1 | c                                 |
| test          | testb      | uk2             | 2015-07-24 09:45:39 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | testb      | uk2             | 2015-07-24 09:45:39 | size         |          1 |        NULL | Number of pages in the index      |
| test          | testb      | uk3             | 2015-07-24 09:45:39 | n_diff_pfx01 |          0 |           1 | d                                 |
| test          | testb      | uk3             | 2015-07-24 09:45:39 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | testb      | uk3             | 2015-07-24 09:45:39 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
51 rows in set (0.00 sec)

mysql> 
mysql> alter table t1 engine=myisam;
Query OK, 768 rows affected (0.01 sec)
Records: 768  Duplicates: 0  Warnings: 0

mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          1 | f1       |            1 | f1          | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | f1_2     |            1 | f1          | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | f1_2     |            2 | f2          | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> select count(distinct f1), count(distinct f2), count(distinct f1, f2), count(*) from t1;
+--------------------+--------------------+------------------------+----------+
| count(distinct f1) | count(distinct f2) | count(distinct f1, f2) | count(*) |
+--------------------+--------------------+------------------------+----------+
|                  6 |                  2 |                     12 |      768 |
+--------------------+--------------------+------------------------+----------+
1 row in set (0.00 sec)

mysql> 
mysql>

tags:

added: upstream

Revision history for this message

Nilnandan Joshi (nilnandan-joshi) wrote on 2015-08-14:

#3

Reported upstream.

Przemek (pmalkowski) on 2017-03-07

tags:

added: i167478

Sveta Smirnova (svetasmirnova) on 2017-06-29

tags:

added: i193089

Revision history for this message

Yura Sorokin (yura-sorokin) wrote on 2017-07-27:

#5

Another observation:
If the table is created with 'STATS_PERSISTENT=0' option.
The results of 'SHOW INDEXES FROM t1' are even stranger

***************************************************************************************************
SHOW INDEXES FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 1 f1 1 f1 A 12 NULL NULL YES BTREE
t1 1 f1_2 1 f1 A 10 NULL NULL YES BTREE
t1 1 f1_2 2 f2 A 35 NULL NULL YES BTREE
***************************************************************************************************

To sum up
MyISAM / expected : ( 6, 6, 12)
InnoDB STATS_PERSISTENT=1 : (12, 12, 24)
InnoDB STATS_PERSISTENT=0 : (12, 10, 35)

Revision history for this message

Yura Sorokin (yura-sorokin) wrote on 2017-07-28:

#6

Here is a simplified test case (1 field, 1 index and 1 INSERT SELECT)

******************************************************
--source include/have_innodb.inc

CREATE TABLE t1(f1 INT, key(f1)) STATS_PERSISTENT=1 ENGINE=InnoDB;
INSERT INTO t1(f1) VALUES (1), (2), (3), (4), (5), (6);

ANALYZE TABLE t1;
SELECT COUNT(DISTINCT f1), COUNT(*) FROM t1;
SELECT INDEX_SCHEMA, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 't1';

INSERT INTO t1(f1) SELECT f1 FROM t1;

ANALYZE TABLE t1;
SELECT COUNT(DISTINCT f1), COUNT(*) FROM t1;
SELECT INDEX_SCHEMA, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 't1';

DROP TABLE t1;
******************************************************

After the 'INSERT INTO t1(f1) SELECT f1 FROM t1' statement index cardinality changes from expected 6 to 12.

Revision history for this message

Yura Sorokin (yura-sorokin) wrote on 2017-07-28:

#7

It looks like in InnoDB 5.6 this behavior is by design. Here is an excerpt from 'ha_innobase::info_low()' ('ha_innodb.cc')

********************************************
rec_per_key = innodb_rec_per_key(
index, j, stats.records);

/* Since MySQL seems to favor table scans
too much over index searches, we pretend
index selectivity is 2 times better than
our estimate: */

rec_per_key = rec_per_key / 2;

if (rec_per_key == 0) {
rec_per_key = 1;
}
********************************************

Revision history for this message

Yura Sorokin (yura-sorokin) wrote on 2017-07-28:

#8

In 5.7 this fragment is partially reworked, so this is why we get expected results there.

Revision history for this message

Sveta Smirnova (svetasmirnova) wrote on 2017-12-11:

#9

Bug #1733520 was marked as duplicate of this one.

Revision history for this message

Shahriyar Rzayev (rzayev-sehriyar) wrote on 2018-01-25:

#10

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1642

	Status	Importance	Assigned to
MySQL Server	Unknown	Unknown	mysql-bugs #78066
Percona Server moved to https://jira.percona.com/projects/PS	Status tracked in 5.7
5.6	Triaged	Medium	Unassigned
5.7	Invalid	Undecided	Unassigned

Percona Server moved to https://jira.percona.com/projects/PS

Wrong cardinality for InnoDB table

Bug Description

Duplicates of this bug

Other bug subscribers

Remote bug watches