第12期:压缩表性能监测

爱可生云数据库

image

上一篇已经解了压缩表的相关概念、索引页的影响以及简单使用。这篇主要来介绍如何观测压缩表。

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

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。
相关字段含义为:
字段含义备注
page_size代表压缩表的对应页(1K/2K/4K/8K/16K),对应压缩表的属性 key_block_size不同的是 page_size 单位 Byte,key_block_size 单位 KB
compress_ops代表对应 page_size 的页被压缩的次数空页创建 +1;修改日志打满 +1
compress_ops_ok代表对应 page_size 页被压缩成功的次数压缩成功 +1;
compress_time代表对应 page_size 页被压缩占用的时间,单位为秒
uncompress_os代表对应 page_size 页被解压缩的次数压缩失败+1;压缩页在innodb buffer pool 里不存在时+1
uncompress_time代表对用 page_size 页被解压缩操作的时间
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 表即被重置。

相关字段含义为:
字段含义备注
page_size对应页大小
buffer_pool_instanceinnodb buffer pool 的实例 ID
pages_used使用的页面个数
pages_free剩余的页面个数,代表页面碎片率正常为 0,理想情况下最多为 1
relocation_ops重新分配页面的操作次数
relocation_time重新分配页面的操作时间,单位为微秒
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)
相关字段含义为:
字段含义备注
database_name数据库名字
table_name表名字
index_name索引名字

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

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)

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

[email protected]:/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)

这篇主要介绍压缩表在各个场景下的简单监测,可以总结为:压缩表只适合应用在读密集型应用,或者少量删除或者更新的场景,其他的场景不建议用压缩表。下一篇我详细介绍表统计信息计算。
关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!


image

阅读 986

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

344 声望
170 粉丝
0 条评论

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

344 声望
170 粉丝
文章目录
宣传栏