Information Schema INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
INNODB_CMP_PER_INDEX_RESET tables contain status information on compression operations related to compressed XtraDB/InnoDB tables, grouped by individual indexes.
PROCESS privilege is required to query this table.
These tables contains the following columns:
|DATABASE_NAME||Database containing the index.|
|TABLE_NAME||Table containing the index.|
|INDEX_NAME||Other values are totals which refer to this index's compression.|
|COMPRESS_OPS||How many times a page of |
|COMPRESS_OPS_OK||How many times a page of |
|COMPRESS_TIME||Time (in seconds) spent to compress pages of the size |
|UNCOMPRESS_OPS||How many times a page of |
|UNCOMPRESS_TIME||Time (in seconds) spent to uncompress pages of |
These tables can be used to measure the effectiveness of XtraDB/InnoDB compression, per table or per index. The values in these tables show which tables perform better with index compression, and which tables cause too many compression failures or perform too many compression/uncompression operations. When compression performs badly for a table, this might mean that you should change its
KEY_BLOCK_SIZE, or that the table should not be compressed.
INNODB_CMP_PER_INDEX_RESET have the same columns and always contain the same values, but when
INNODB_CMP_PER_INDEX_RESET is queried, both the tables are cleared.
INNODB_CMP_PER_INDEX_RESET can be used, for example, if a script periodically logs the performances of compression in the last period of time.
INNODB_CMP_PER_INDEX can be used to see the cumulated statistics.
Other tables that can be used to monitor XtraDB/InnoDB compressed tables: