The information_schema database contains some tables that can be used to monitor the performance of the InnoDB compressed tables. All these tables have their names starting with INNODB_CMP, so they can be listed with the following query:
MariaDB [information_schema]> SHOW TABLES LIKE 'INNODB_CMP%'; +--------------------------------------------+ | Tables_in_information_schema (INNODB_CMP%) | +--------------------------------------------+ | INNODB_CMP | | INNODB_CMP_RESET | | INNODB_CMP_PER_INDEX | | INNODB_CMPMEM_RESET | | INNODB_CMP_PER_INDEX_RESET | | INNODB_CMPMEM | +--------------------------------------------+ 6 rows in set (0.00 sec)
The main InnoDB tables are:
INNODB_CMPMEM: This table stores statistics about the compressed table pages in the buffer poolINNODB_CMP: This table stores information about the compression and uncompression operations on the whole serverINNODB_CMP_PER_INDEX: This table stores information very similar to the previous table, but the information is grouped per individual tables and indexes
The reset tables (the ones whose names end with _RESET) are identical to the matching non-reset tables. The difference is that when a reset table is queried, most of its contents are reset. It is possible to query the reset tables at regular time intervals to monitor how the compressed table's performance varies in time. Or, they could be used to check the effects of a variable's change.
Note
Note that gathering the information stored in the INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET tables can be expensive. For this reason, these tables are always empty, unless the innodb_cmp_per_index_enabled variable is set to ON. Normally, this should not be the case on production servers.
This table shows the statistics of compressed pages in the buffer pool. The statistics are grouped by page size. Each row shows information about how the tables with a particular KEY_BLOCK_SIZE behave. In fact, each table is designed for the DBA who needs to determine the key block size for a table. The following table describes the columns present in the INNODB_CMPMEM table:
When the INNODB_CMPMEM_RESET table is read, the RELOCATION_OPS and RELOCATION_TIME fields are set to 0.
Suppose we have a customer table. From the original table, we create three compressed tables with different KEY_BLOCK_SIZE values: customers_16, customers_8, and customers_4. As mentioned earlier, a good testing requires that we run a realistic workload on each table for a while. However, in this example, we just want to see how these tables work, so we will just execute a SELECT COUNT(*) query for each table. Then, we will query INNODB_CMPMEM_RESET, shown as follows:
MariaDB [information_schema]> CREATE TABLE test.customers_16 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16 SELECT * FROM test.customers_non_comp; Query OK, 1474560 rows affected (59.88 sec) Records: 1474560 Duplicates: 0 Warnings: 0 MariaDB [information_schema]> CREATE TABLE test.customers_8 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 SELECT * FROM test.customers_non_comp; Query OK, 1474560 rows affected (1 min 36.50 sec) Records: 1474560 Duplicates: 0 Warnings: 0 MariaDB [information_schema]> CREATE TABLE test.customers_4 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 SELECT * FROM test.customers_non_comp; Query OK, 1474560 rows affected (6 min 54.86 sec) Records: 1474560 Duplicates: 0 Warnings: 0 MariaDB [information_schema]> SELECT COUNT(*) FROM test.customers_16; +----------+ | COUNT(*) | +----------+ | 1474560 | +----------+ 1 row in set (2.86 sec) MariaDB [information_schema]> SELECT COUNT(*) FROM test.customers_8; +----------+ | COUNT(*) | +----------+ | 1474560 | +----------+ 1 row in set (5.07 sec) MariaDB [information_schema]> SELECT COUNT(*) FROM test.customers_4; +----------+ | COUNT(*) | +----------+ | 1474560 | +----------+ 1 row in set (7.14 sec) MariaDB [information_schema]> SELECT * FROM INNODB_CMPMEM_RESET; +-----------+----------------------+------------+------------+----------------+-----------------+ | page_size | buffer_pool_instance | pages_used | pages_free | relocation_ops | relocation_time | +-----------+----------------------+------------+------------+----------------+-----------------+ | 1024 | 0 | 0 | 0 | 0 | 0 | | 2048 | 0 | 0 | 0 | 0 | 0 | | 4096 | 0 | 5832 | 16 | 3864 | 0 | | 8192 | 0 | 2365 | 557 | 3244 | 0 | | 16384 | 0 | 5597 | 0 | 0 | 0 | +-----------+----------------------+------------+------------+----------------+-----------------+ 5 rows in set (0.00 sec)
After running a realistic workload and examining the contents of this table, we will try to choose a low-key block size, which does not require too many relocation operations.
The
INNODB_CMP_PER_INDEX table shows information about the performance of the compressed pages, grouped by index. As mentioned earlier, gathering this information is expensive, thus INNODB_CMP_PER_INDEX is always empty, unless the innodb_cmp_per_index_enabled variable is set to ON.
This table contains the following columns:
DATABASE_NAME: This is the database that contains the index.TABLE_NAME: This is the table that contains the index.INDEX_NAME: This is the grouping columns. All the remaining values refer to the operations performed on this index page.COMPRESS_OPS: This column specifies the number of times modification log changes are applied to a page.COMPRESS_OPS_OK: This is the number of times a compression operation succeeded and did not result in a compression failure.COMPRESS_TIME: This is the number of seconds elapsed while compressing data.UNCOMPRESS_OPS: This is the number of uncompressed operations. Remember that this number is increased, both when a new index entry is copied to the buffer pool, and when a compression operation fails.UNCOMPRESS_TIME: This is the number of seconds elapsed during uncompress operations.
When the INNODB_CMP_PER_INDEX_RESET table is queried, all the columns except for DATABASE_NAME, TABLE_NAME, and INDEX_NAME are reset to 0.
Tip
This table allows us to understand how much each index performance is negatively affected by the compress and uncompress operations. The number of compression failures is the difference between COMPRESS_OPS and COMPRESS_OPS_OK. If this value is low and the performance is poor, the index is slow because pages are written to the buffer pool and evicted from it too often. If the buffer pool configuration and the index usage cannot be improved, the table should not be compressed.
If there are many compression failures, we should try to reduce them. If compression operations happen for a singular table (or index), we should consider modifying the KEY_BLOCK_SIZE table option; otherwise, we should try to set innodb_compression_failure_threshold_pct and innodb_compression_pad_pct_max to their optimal values.
The INNODB_CMP table is identical to INNODB_CMP_PER_INDEX, except that the values are global and not grouped per index or table. This table is generally less useful, but gathering its contents is less expensive. Thus, the table is always populated.
The INNODB_CMP table has the same columns as INNODB_CMP_PER_INDEX, except for DATABASE_NAME, TABLE_NAME, and INDEX_NAME.
If INNODB_CMP_RESET is queried, its contents are reset completely.