| Bug #74935 | innodb index stats inadequate using constant innodb_stats_sample_pages | ||
|---|---|---|---|
| Submitted: | 20 Nov 2014 8:09 | Modified: | 22 Nov 2014 11:43 |
| Reporter: | Jan Lindstrom | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.5,5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Nov 2014 8:10]
Jan Lindstrom
MariaDB [test]> select count(*) from obs_daily; +----------+ | count(*) | +----------+ | 1048576 | +----------+ 1 row in set (2.22 sec) MariaDB [test]> select count(distinct sLocType) from obs_daily; +--------------------------+ | count(distinct sLocType) | +--------------------------+ | 1 | +--------------------------+ 1 row in set (0.01 sec) MariaDB [test]> select count(distinct sLocCode) from obs_daily; +--------------------------+ | count(distinct sLocCode) | +--------------------------+ | 1007624 | +--------------------------+ 1 row in set (5.65 sec) MariaDB [test]> select count(distinct dtDate) from obs_daily; +------------------------+ | count(distinct dtDate) | +------------------------+ | 15 | +------------------------+ 1 row in set (0.00 sec) MariaDB [test]> set global innodb_stats_sample_pages = 8; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> analyze table obs_daily; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | test.obs_daily | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.02 sec) MariaDB [test]> select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily'; +--------------+------------+-----------------+--------+--------------+-------------------+------------------+ | table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages | +--------------+------------+-----------------+--------+--------------+-------------------+------------------+ | test | obs_daily | PRIMARY | 1 | 1 | 2852 | 2844 | | test | obs_daily | idx_obs_daily_1 | 3 | 115183, 0, 0 | 3628 | 3138 | | test | obs_daily | idx_obs_daily_2 | 2 | 115183, 0 | 865 | 814 | +--------------+------------+-----------------+--------+--------------+-------------------+------------------+ 3 rows in set (0.00 sec) MariaDB [test]> analyze table obs_daily; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | test.obs_daily | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.02 sec) MariaDB [test]> select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily'; +--------------+------------+-----------------+--------+--------------+-------------------+------------------+ | table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages | +--------------+------------+-----------------+--------+--------------+-------------------+------------------+ | test | obs_daily | PRIMARY | 1 | 1 | 2852 | 2844 | | test | obs_daily | idx_obs_daily_1 | 3 | 116842, 1, 0 | 3628 | 3138 | | test | obs_daily | idx_obs_daily_2 | 2 | 116842, 0 | 865 | 814 | +--------------+------------+-----------------+--------+--------------+-------------------+------------------+ 3 rows in set (0.00 sec) MariaDB [test]> analyze table obs_daily; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | test.obs_daily | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.02 sec) MariaDB [test]> select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily'; +--------------+------------+-----------------+--------+--------------+-------------------+------------------+ | table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages | +--------------+------------+-----------------+--------+--------------+-------------------+------------------+ | test | obs_daily | PRIMARY | 1 | 1 | 2852 | 2844 | | test | obs_daily | idx_obs_daily_1 | 3 | 116407, 1, 1 | 3628 | 3138 | | test | obs_daily | idx_obs_daily_2 | 2 | 116407, 0 | 865 | 814 | +--------------+------------+-----------------+--------+--------------+-------------------+------------------+ 3 rows in set (0.00 sec)
[20 Nov 2014 8:10]
Jan Lindstrom
For more discussion see: https://mariadb.atlassian.net/browse/MDEV-7084
[20 Nov 2014 8:11]
Jan Lindstrom
Test file
Attachment: innodb_mdev7084.test (application/octet-stream, text), 3.34 KiB.
[20 Nov 2014 8:11]
Jan Lindstrom
mtr options
Attachment: innodb_mdev7084-master.opt (application/octet-stream, text), 22 bytes.
[20 Nov 2014 8:12]
Jan Lindstrom
Suggested fix for 5.5. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: patch.diff (text/x-patch), 17.44 KiB.
[22 Nov 2014 11:43]
MySQL Verification Team
Hello Jan Lindstrom, Thank you for the report and contribution. Thanks, Umesh
Description: If you set the number of analyzed pages to very low number compared to actual pages on that table/index it randomly pics those pages (default 8 pages), this leads to fact that query after analyze table returns different results. If the index tree is small, smaller than 10 * n_sample_pages + total_external_size, then the estimate is ok. For bigger index trees it is common that we do not see any borders between key values in the few pages we pick. But still there may be n_sample_pages different key values, or even more. And it just tries to approximate to n_sample_pages (8). How to repeat: drop table if exists obs_daily; CREATE TABLE `obs_daily` ( `iId` int(11) NOT NULL AUTO_INCREMENT, `sLocType` varchar(10) NOT NULL DEFAULT '', `sLocCode` varchar(30) NOT NULL DEFAULT '', `dtDate` date NOT NULL DEFAULT '0000-00-00', PRIMARY KEY (`iId`), UNIQUE KEY `idx_obs_daily_1` (`sLocType`,`sLocCode`,`dtDate`), KEY `idx_obs_daily_2` (`dtDate`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; insert into obs_daily values (null, 'A', 'c1', '2014-11-11'),(null, 'A', 'c2', '2014-11-11'),(null, 'A', 'c3', '2014-11-11'),(null, 'A', 'c4', '2014-11-11'),(null, 'A', 'c5', '2014-11-11'),(null, 'A', 'c6', '2014-11-11'),(null, 'A', 'c7', '2014-11-11'),(null, 'A', 'c8', '2014-11-11'); insert into obs_daily select null, 'A', sLocCode, '2014-08-12' from obs_daily; insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-13' from obs_daily; insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-12' from obs_daily; insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-11' from obs_daily; insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-10' from obs_daily; insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-16' from obs_daily; insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-19' from obs_daily; insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-22' from obs_daily; insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-21' from obs_daily; insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-31' from obs_daily; insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-30' from obs_daily; insert into obs_daily select null, 'A', concat('code-1-',iId), '2014-08-31' from obs_daily; insert into obs_daily select null, 'A', concat('code-2-',iId), '2014-08-31' from obs_daily; insert into obs_daily select null, 'A', concat('code-2-',iId), '2014-08-29' from obs_daily; insert into obs_daily select null, 'A', concat('code-3-',iId), '2014-08-09' from obs_daily; insert into obs_daily select null, 'A', concat('code-4-',iId), '2014-08-05' from obs_daily; insert into obs_daily select null, 'A', concat('code-5-',iId), '2014-08-03' from obs_daily; select count(*) from obs_daily; select count(distinct sLocType) from obs_daily; select count(distinct sLocCode) from obs_daily; select count(distinct dtDate) from obs_daily; select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily'; set global innodb_stats_sample_pages = 8; analyze table obs_daily; select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily'; analyze table obs_daily; select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily'; analyze table obs_daily; select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily';