Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7084

innodb index stats inadequate using constant innodb_stats_sample_pages

    Details

      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

            Activity

              People

              • Assignee:
                jplindst Jan Lindström
                Reporter:
                danblack Daniel Black
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: