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

Document innodb_table_stats and innodb_index_stats

    XMLWordPrintable

    Details

      Description

      These tables have existed since MariaDB 10.0:

      MariaDB [(none)]> SHOW CREATE TABLE mysql.innodb_table_stats;
      +--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table              | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
      +--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | innodb_table_stats | CREATE TABLE `innodb_table_stats` (
        `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `n_rows` bigint(20) unsigned NOT NULL,
        `clustered_index_size` bigint(20) unsigned NOT NULL,
        `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
        PRIMARY KEY (`database_name`,`table_name`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 |
      +--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.02 sec)
       
      MariaDB [(none)]> SHOW CREATE TABLE mysql.innodb_index_stats;
      +--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table              | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
      +--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | innodb_index_stats | CREATE TABLE `innodb_index_stats` (
        `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
        `stat_value` bigint(20) unsigned NOT NULL,
        `sample_size` bigint(20) unsigned DEFAULT NULL,
        `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
        PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 |
      +--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      But they seem to be missing from the documentation:

      https://mariadb.com/kb/en/library/the-mysql-database-tables/

        Attachments

          Activity

            People

            Assignee:
            greenman Ian Gilfillan
            Reporter:
            GeoffMontee Geoff Montee
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: