Lesson 11 of the SQL Optimization Course: How to test the performance of compressed tables?

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

• DBA
• Database developers
• Students

We will use MySQL as the demonstration database.

In the previous issue, we discussed the concepts of compressed tables, the impact of index pages, and basic usage. This article focuses on how to monitor compressed tables.

1. Key Points About Compressed Tables

1. SELECT Operations
   These operations do not require decompression of compressed pages, making them very suitable for compressed tables.

2. INSERT Operations
   These operations require decompression and recompression of secondary index pages. However, MySQL handles this process using the change buffer, which reduces the frequency of such operations.

3. DELETE Operations
   MySQL marks rows for deletion and waits for the PURGE thread to clean up periodically. This makes compressed tables suitable for delete operations.

4. UPDATE Operations
   Since compressed tables typically compress string data (e.g., TEXT, VARCHAR), updating such data can quickly fill the change buffer, leading to frequent decompression and recompression operations.

In general, compressed tables are suitable for read-intensive, read-only, or scenarios with minimal updates.

2. Monitoring Compressed Tables

Monitoring data for compressed tables is stored in the INFORMATION_SCHEMA under tables prefixed with INNODB_CMP. These tables allow you to check the health of compressed tables, adjust compression page sizes, or determine if compressed tables are appropriate for your use case.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

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                     |
+--------------------------------------+
6 rows in set (0.01 sec)

2.1 INNODB_CMP / INNODB_CMP_RESET

These tables track disk access data for compressed tables.

• INNODB_CMP stores historical data.
• INNODB_CMP_RESET resets the historical data.

Example of monitoring over one hour:

• Capture data from INNODB_CMP.
• Capture data again after one hour.
• Reset the data using INNODB_CMP_RESET.

Fields in INNODB_CMP:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

mysql> DESC information_schema.innodb_cmp;
+-----------------+------+------+-----+---------+-------+
| Field           | Type | Null | Key | Default | Extra |
+-----------------+------+------+-----+---------+-------+
| page_size       | int  | NO   |     |         |       |
| compress_ops    | int  | NO   |     |         |       |
| compress_ops_ok | int  | NO   |     |         |       |
| compress_time   | int  | NO   |     |         |       |
| uncompress_ops  | int  | NO   |     |         |       |
| uncompress_time | int  | NO   |     |         |       |
+-----------------+------+------+-----+---------+-------+

Note: The ratio of compress_ops_ok / compress_ops is a key indicator of compressed table health. A normal ratio should be close to 1. If the ratio is consistently abnormal, consider adjusting the page size or avoiding compressed tables in that scenario.

2.2 INNODB_CMPMEM / INNODB_CMPMEM_RESET

These tables track access data for compressed tables in the InnoDB buffer pool.

• INNODB_CMPMEM stores historical data.
• INNODB_CMPMEM_RESET resets the data.

Fields in INNODB_CMPMEM:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

mysql> DESC information_schema.innodb_cmpmem;
+----------------------+--------+------+-----+---------+-------+
| Field                | Type   | Null | Key | Default | Extra |
+----------------------+--------+------+-----+---------+-------+
| page_size            | int    | NO   |     |         |       |
| buffer_pool_instance | int    | NO   |     |         |       |
| pages_used           | int    | NO   |     |         |       |
| pages_free           | int    | NO   |     |         |       |
| relocation_ops       | bigint | NO   |     |         |       |
| relocation_time      | int    | NO   |     |         |       |
+----------------------+--------+------+-----+---------+-------+

2.3 INNODB_CMP_PER_INDEX / INNODB_CMP_PER_INDEX_RESET

These tables track operations on primary keys and secondary indexes of compressed tables.

• INNODB_CMP_PER_INDEX stores historical data.
• INNODB_CMP_PER_INDEX_RESET stores instantaneous data.
• These tables have high overhead and are disabled by default.

To enable monitoring:

1
2
3
4
5
6
7

mysql> SELECT @@innodb_cmp_per_index_enabled;
+--------------------------------+
| @@innodb_cmp_per_index_enabled |
+--------------------------------+
| 0                              |
+--------------------------------+
mysql> SET PERSIST innodb_cmp_per_index_enabled = 1;

Fields in INNODB_CMP_PER_INDEX:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysql> DESC information_schema.innodb_cmp_per_index;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| database_name   | varchar(192) | NO   |     |         |       |
| table_name      | varchar(192) | NO   |     |         |       |
| index_name      | varchar(192) | NO   |     |         |       |
| compress_ops    | int          | NO   |     |         |       |
| compress_ops_ok | int          | NO   |     |         |       |
| compress_time   | int          | NO   |     |         |       |
| uncompress_ops  | int          | NO   |     |         |       |
| uncompress_time | int          | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+

3. Practical Examples of Monitoring Compressed Tables

Two tables were created for comparison:

• t1: Uncompressed table.
• t2: Compressed table with a page size of 4K.

Disk Size Comparison

1
2
3
4

root@ytt-pc:/var/lib/mysql/3305/ytt# ls -shil
Total: 2.0G
3949029 1.6G -rw-r----- 1 mysql mysql 1.6G Mar 31 21:18 t1.ibd
3946045 405M -rw-r----- 1 mysql mysql 404M Mar 31 21:42 t2.ibd

Query Performance Comparison

• SQL 1:

  1 2 3 4 5 6 7 8

  mysql> SELECT COUNT(*) FROM t1; +----------+ | count(*) | +----------+ | 200000 | +----------+ 1 row in set (4.02 sec)

• SQL 2:
  

  1 2 3 4 5 6 7 8

  mysql> SELECT COUNT(*) FROM t2; +----------+ | count(*) | +----------+ | 200000 | +----------+ 1 row in set (2.69 sec)

• SQL 3:
  

  1 2 3

  mysql> SELECT * FROM t1 WHERE id = 100; 2 rows in set (6.82 sec)

• SQL 4:
  

  1 2 3

  mysql> SELECT * FROM t2 WHERE id = 100; 2 rows in set (3.60 sec)

Result: Compressed tables perform better for individual queries.

Delete Operations

Deleting a row from t2:

1
2

mysql> DELETE FROM t2 WHERE id = 999999;
Query OK, 6 rows affected (3.41 sec)

Monitoring data:

1
2
3
4
5
6
7
8
9

mysql> SELECT * FROM innodb_cmp WHERE page_size=4096\G
*************************** 1. row ***************************
page_size: 4096
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
1 row in set (0.00 sec)

Update Operations

1. Updating a Small Amount of Data
   

   1 2 3 4

   mysql> UPDATE t2 SET r1 = '200' WHERE id = 200; Query OK, 2 rows affected (3.41 sec) Rows matched: 2 Changed: 2 Warnings: 0

   Monitoring data:
   

   1 2 3 4 5 6 7 8 9 10

   mysql> SELECT * FROM innodb_cmp WHERE page_size=4096\G *************************** 1. row *************************** page_size: 4096 compress_ops: 2 compress_ops_ok: 2 compress_time: 0 uncompress_ops: 0 uncompress_time: 0 1 row in set (0.01 sec)

2. Updating a Large Amount of Data
   

   1 2 3 4

   mysql> UPDATE t2 SET r1 = '20000' WHERE 1; Query OK, 199996 rows affected (26.59 sec) Rows matched: 199996 Changed: 199996 Warnings: 0

   Monitoring data:
   

   1 2 3 4 5 6 7 8 9 10

   mysql> SELECT * FROM innodb_cmp WHERE page_size=4096\G *************************** 1. row *************************** page_size: 4096 compress_ops: 48789 compress_ops_ok: 6251 compress_time: 4 uncompress_ops: 21269 uncompress_time: 0 1 row in set (0.01 sec)

Summary

Compressed tables are best suited for read-intensive applications or scenarios with minimal updates or deletions. They are not recommended for write-heavy workloads.

In the next article, we will discuss table statistics calculation.

What MySQL technical topics would you like to learn more about? Let us know in the comments!

👋 See you in the next lesson.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL

• SQLFlash + PostgreSQL