InnoDB TABLE/INDEX stats

InnoDB TABLE/INDEX stats

PREVIOUS POST
NEXT POST

In Released and new coming features I did not mentioned two additional INFORMATION_SCHEMA tables available in XtraDB:
It is

  • INNODB_TABLE_STATS
  • INNODB_INDEX_STATS

These table show statistics about InnoDB tables ( taken from InnoDB data dictionary).

INNODB_TABLE_STATS is

  • | table_name | table name in InnoDB internal style (‘database/table’) |
  • | rows | estimated number of all rows |
  • | clust_size | cluster index (table/primary key) size in number of pages|
  • | other_size | other index (non primary key) size in number of pages|
  • | modified | internal counter to judge whether statistics recalculation should be done |

INNODB_INDEX_STATS is

  • | table_name | table name in InnoDB internal style (‘database/table’) |
  • | index_name | index name |
  • | fields | How many fields the index key has. (it is internal structure of InnoDB, it may be larger than the ‘CREATE TABLE’) |
  • | row_per_keys | estimate rows per 1 key value. ([1 column value], [2 columns value], [3 columns value], …) |
  • | index_size | index size in pages |
  • | leaf_pages | number of leaf pages |

Using these stats you can estimate how big is index is, and also what is statistics per index (or at least what InnoDB thinks about statistics in index)

PREVIOUS POST
NEXT POST

Share this post

Comments (8)

  • Arjen Lentz Reply

    For both, can you add a column counting access?
    This could then easily replace the server based table/index statistics and possibly in a more efficient manner.

    March 20, 2010 at 7:39 pm
  • Shlomi Noach Reply

    Would you add “index_depth” column for indexes?

    March 21, 2010 at 2:03 am
  • Vadim Reply

    Arjen, Shlomi,

    What we show there is already available information in InnoDB data dictionary.
    What you ask needs some additional changes in InnoDB data dict, which may be not easy to
    archive (may break compatibility with current data dict), but it is hard to say, I did not look into yet.

    March 21, 2010 at 8:03 am
  • Emily Reply

    Is this coming to core, or is it exclusively percona?

    March 22, 2010 at 10:59 am
  • Vadim Reply

    Emily,

    For now it is only in Percona-XtraDB, but it is not exclusive.
    Everyone, including core, can implement it.

    March 22, 2010 at 11:04 am
  • 张三 Reply

    Cannot find or open table mysql/innodb_index_stats from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of the engine doesn’t support. why?

    October 21, 2012 at 8:12 pm
  • Jyothi Reply

    I have installed mysql5.6.10, following error is seen in .err
    InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.

    When Mysql services is started, after deleting ib log and data files.

    Please help

    February 6, 2013 at 5:13 am
  • Sri Reply

    Hi,

    Currently, we are using Mariadb , in order to know the analyze stats are stale, i build below SQL, using INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS , mysql.innodb_table_stats and INFORMATION_SCHEMA.STATISTICS.

    December 25, 2016 at 2:08 pm

Leave a Reply