How to Monitor MySQL Compressed Tables and Their Suitable Use Cases

This article explains how to observe MySQL compressed tables, covering appropriate usage scenarios, monitoring methods, and practical performance examples.

Usage scenario classification : SELECT‑heavy workloads are ideal because they do not require decompressing pages; INSERT operations are acceptable due to MySQL's change buffer reducing frequency; DELETE operations are suitable as they only set a flag for later PURGE; UPDATE operations on string columns (TEXT, VARCHAR) can quickly fill the change log, making frequent decompress/compress cycles costly. Overall, compressed tables fit read‑intensive, read‑only, or minimally updated workloads.

Monitoring compressed tables : The monitoring data resides in information_schema tables whose names start with INNODB_CMP. The relevant tables are INNODB_CMP, INNODB_CMP_RESET, INNODB_CMPMEM, INNODB_CMPMEM_RESET, INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET. The most direct health indicator is the ratio compress_ops_ok / compress_ops, which should be 0, 1, or close to 1; a prolonged deviation suggests inappropriate page size or misuse of compression.

mysql> show tables from information_schema like '%cmp%';
+--------------------------------------+
| Tables_in_information_schema (%CMP%) |
+--------------------------------------+
| INNODB_CMP                           |
| INNODB_CMPMEM                        |
| INNODB_CMPMEM_RESET                  |
| INNODB_CMP_PER_INDEX                 |
| INNODB_CMP_PER_INDEX_RESET           |
| INNODB_CMP_RESET                     |
+--------------------------------------+

Example queries to view table definitions:

mysql> desc information_schema.innodb_cmp;
+-----------------+------+------... (fields: page_size, compress_ops, compress_ops_ok, compress_time, uncompress_ops, uncompress_time)

mysql> desc information_schema.innodb_cmpmem;
+----------------------+--------+------... (fields: page_size, buffer_pool_instance, pages_used, pages_free, relocation_ops, relocation_time)

mysql> desc information_schema.innodb_cmp_per_index;
+-----------------+--------------+------... (fields: database_name, table_name, index_name, compress_ops, compress_ops_ok, compress_time, uncompress_ops, uncompress_time)

Practical use case : Create a normal table t1 and a compressed table t2 with a 4 KB page size.

mysql> create table t1(id int, r1 text, r2 text, primary key(id)) row_format=dynamic;
Query OK, 0 rows affected (2.35 sec)

mysql> create table t2(id int, r1 text, r2 text, primary key(id)) key_block_size=4;
Query OK, 0 rows affected (0.06 sec)

After inserting data, disk usage shows t1.ibd ~1.6 GB versus t2.ibd ~405 MB, demonstrating space savings.

Query speed comparison :

-- SQL 1
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 200000   |
+----------+
1 row in set (4.02 sec)

-- SQL 2
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 200000   |
+----------+
1 row in set (2.69 sec)

-- SQL 3
mysql> select * from t1 where id = 100;
... (6.82 sec)

-- SQL 4
mysql> select * from t2 where id = 100;
... (3.60 sec)

Result: the compressed table is noticeably faster for both full‑table counts and point queries.

Delete operation : Deleting a row from t2 and checking INNODB_CMP shows compress_ops and compress_ops_ok both zero, confirming that deletions are well‑suited for compressed tables.

mysql> delete from t2 where id = 999999;
Query OK, 6 rows affected (3.41 sec)

mysql> select * from innodb_cmp where page_size=4096\G
page_size: 4096
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0

Small update : Updating a few rows in t2 yields a healthy ratio (e.g., compress_ops = 2, compress_ops_ok = 2).

mysql> update t2 set r1='200' where id = 200;
Query OK, 2 rows affected (3.41 sec)

mysql> select * from innodb_cmp where page_size=4096\G
compress_ops: 2
compress_ops_ok: 2

Large update : Updating almost all rows in t2 dramatically lowers the success ratio ( compress_ops = 48789, compress_ops_ok = 6251), indicating that massive updates should avoid compressed tables.

mysql> update t2 set r1='20000' where 1;
Query OK, 199996 rows affected (26.59 sec)

mysql> select * from innodb_cmp where page_size=4096\G
compress_ops: 48789
compress_ops_ok: 6251

In summary, compressed tables are best applied to read‑intensive or low‑update scenarios; heavy INSERT/UPDATE workloads can degrade performance and should be avoided.