第12期：压缩表性能监测

爱可生开源社区

发布于 2020-09-14 11:29:08

8200

发布于 2020-09-14 11:29:08

文章被收录于专栏：爱可生开源社区爱可生开源社区

上一篇（第11期：压

一、压缩表的使用场景分类

1. SELECT 业务

这类操作不需要对压缩页进行解压，所以非常适合使用压缩表。

2. INSERT 业务

这类操作需要重新对二级索引数据页解压和以及重新压缩，不过 MySQL 对这部分操作放入 change buffer，所以频率相对来说不是很高。

3. DELETE 业务

由于 MySQL 对删除的操作是直接写标记位，然后等待定期的 PURGE 线程清理，这块也适合用压缩表。

4. UPDATE 业务

由于压缩表一般都是对字符串类的数据，比如 TEXT,VARCHAR 等压缩，所以针对这块的数据做更新，很容易就把更改日志（上篇介绍过）打满，继而造成频繁的解压和压缩操作。

总的来说压缩表适合于读密集、只读、或者极少量更新的业务场景。

二、压缩表监测

对压缩表的监控，保存在 Information_schema 内以 INNODB_CMP 开头的字典表。通过这些表可以监控到压缩表是否健康，是否需要调整压缩页，或者说是否适合用压缩表等。

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)

这些表为内存表，也就是 memory 引擎。对这些表的检索必须具有 process 权限。按照压缩表的操作方式，分为以下三类：

1. INNODB_CMP/INNODB_CMP_RESET

InnoDB 压缩表的磁盘访问相关数据，其中 INNODB_CMP 和 INNODB_CMP_RESET 表结构相同，不同的是 INNODB_CMP 代表压缩表历史访问数据，INNODB_CMP_RESET 用来重置压缩表历史数据。比如要监控一小时内的压缩表访问数据，可以执行下面的简单步骤：

先采集 INNODB_CMP 相关数据；
过一小时再次采集表 INNODB_CMP 相关数据；
完后立刻访问 INNODB_CMP_RESET 表；
初始化表 INNODB_CMP。

相关字段含义为：

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

注意：这两个值的比率（compress_ops_ok/compress_ops）是最直观的数据，可以判断压缩表的健康与否；正常情况下，比率为 0 或者 1 或接近于 1；如果比率长时间不正常，就得考虑压缩表的页大小是否合适或者说压缩表是否应该在这种场景下使用。

2. INNODB_CMPMEM /INNODB_CMPMEM_RESET

这两张表代表在 innodb_buffer_pool 里的压缩表相关访问数据，INNODB_CMPMEM 代表历史数据；INNODB_CMPMEM_RESET 代表当前瞬时数据，只要访问一次，INNODB_CMPMEM 表即被重置。

相关字段含义为：

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

3. INNODB_CMP_PER_INDEX/INNODB_CMP_PER_INDEX_RESET

这两张表代表对压缩表主键、二级索引的检索相关数据，不带 _RESET 为历史数据，带 _RESET 为瞬时数据。和前两类表不一样，这类表是针对索引的操作记录数据，开销很大，默认不开启。相关参数为：

mysql> select @@innodb_cmp_per_index_enabled;
+--------------------------------+
| @@innodb_cmp_per_index_enabled |
+--------------------------------+
| 0 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> set persist innodb_cmp_per_index_enabled = 1;
Query OK, 0 rows affected (0.01 sec)

相关字段含义为：

其他字段类类似以上说明。

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

压缩表监测的实际用例

接下来看看压缩表的监测的实际用例，使用单表空间建立两张表：

t1 ：未压缩表
t2 ：page 为 4K 的压缩表

执行建表

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)

插入一部分数据后，对应的磁盘大小

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

1. 查询速度对比

SQL 1 比 SQL 2 慢，SQL 3 比 SQL 4 慢。

压缩表单独查询时优势明显。

-- 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;
...
2 rows in set (6.82 sec)

-- SQL 4
mysql> select * from t1 where id = 100;
...
2 rows in set (3.60 sec)

2. 删除数据

重启 MySQL 实例，对压缩表 t2 进行删除与更新操作；或者清空表 INNODB_CMP 和 INNODB_CMP_PER_INDEX，也就是执行对应后缀为 _RESET 的表。

从表 t2 删除一条记录

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

对应的 compress_ops/compress_ops_ok 为 0

表 INNODB_CMP_PER_INDEX 无数据，因为没有重建索引。可以看出 DELETE 操作对于压缩表很适合。

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)

mysql> select * from innodb_cmp_per_index;
Empty set (0.00 sec)

3. 更新少量数据

同样执行对应 _RESET 后缀表清空数据

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

查看对应的监测表数据，compress_ops_ok/compress_ops 为 1，也很健康。

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)

mysql> select * from innodb_cmp_per_index\G
*************************** 1. row ***************************
database_name: ytt
table_name: t2
index_name: PRIMARY
compress_ops: 2
compress_ops_ok: 2
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
1 row in set (0.00 sec)

4. 更新大量数据

照例执行后缀 _RESET 的表，清空两张表。

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

查看对应监测表的数据，compress_ops_ok/compress_ops 比率很低，失败的操作占了一半。结果表明大量更新应该规避压缩表。

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)

mysql> select * from innodb_cmp_per_index\G
*************************** 1. row ***************************
database_name: ytt
table_name: t2
index_name: PRIMARY
compress_ops: 48789
compress_ops_ok: 6251
compress_time: 4
uncompress_ops: 21269
uncompress_time: 0
1 row in set (0.00 sec)

这篇主要介绍压缩表在各个场景下的简单监测，可以总结为：压缩表只适合应用在读密集型应用，或者少量删除或者更新的场景，其他的场景不建议用压缩表。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-09-09，如有侵权请联系 cloudcommunity@tencent.com 删除

文件存储