Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.40
-
None
-
centos6-amd64
Description
The rows_per_key for idx_obs_daily_1 and idx_obs_daily_2 are nowhere near the same. The rows_per_key for the first element on ibx_obs_daily_2 on the other 5 database slave servers of identical content was been ~2K and ~8K.
Its highly suspicious that exactly the same number got calculated here for rows_per_field when the first element of the each of these indexes is a different field.
ANALYZE table was run on all tables 2 weeks ago when the first instance of an incorrect query plan was observed. Though data changes this index stats also got corrupted again.
MariaDB [weather]> select * from information_schema.INNODB_INDEX_STATS where table_schema='weather' and table_name='obs_daily';
|
+--------------+------------+-----------------+--------+------------------+-------------------+------------------+
|
| table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages |
|
+--------------+------------+-----------------+--------+------------------+-------------------+------------------+
|
| weather | obs_daily | PRIMARY | 1 | 1 | 204608 | 178951 |
|
| weather | obs_daily | idx_obs_daily_1 | 3 | 2708708, 1670, 1 | 66944 | 58327 |
|
| weather | obs_daily | idx_obs_daily_2 | 2 | 2708708, 0 | 27904 | 24298 |
|
+--------------+------------+-----------------+--------+------------------+-------------------+------------------+
|
3 rows in set (0.00 sec)
|
|
|
MariaDB [weather]> analyze table weather.obs_daily;
|
+-------------------+---------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+-------------------+---------+----------+----------+
|
| weather.obs_daily | analyze | status | OK |
|
+-------------------+---------+----------+----------+
|
1 row in set (0.04 sec)
|
|
|
MariaDB [weather]> select * from information_schema.INNODB_INDEX_STATS where table_schema='weather' and table_name='obs_daily';
|
+--------------+------------+-----------------+--------+------------------+-------------------+------------------+
|
| table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages |
|
+--------------+------------+-----------------+--------+------------------+-------------------+------------------+
|
| weather | obs_daily | PRIMARY | 1 | 1 | 206976 | 180899 |
|
| weather | obs_daily | idx_obs_daily_1 | 3 | 2884335, 3533, 1 | 67328 | 58696 |
|
| weather | obs_daily | idx_obs_daily_2 | 2 | 8472, 1 | 28032 | 24442 |
|
+--------------+------------+-----------------+--------+------------------+-------------------+------------------+
|
3 rows in set (0.00 sec)
|
| 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 AUTO_INCREMENT=23562482 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC |
|
Attachments
Issue Links
- relates to
-
MDEV-7196 ANALYZE TABLE should do complete table scan
-
- Open
-
-
-
- Open
-
-
-
- Open
-