关于检测:第12期压缩表性能监测

上一篇曾经解了压缩表的相干概念、索引页的影响以及简略应用。这篇次要来介绍如何观测压缩表。

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

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_instance	innodb 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)

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

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)

这篇次要介绍压缩表在各个场景下的简略监测，能够总结为：压缩表只适宜利用在读密集型利用，或者大量删除或者更新的场景，其余的场景不倡议用压缩表。下一篇我具体介绍表统计信息计算。
对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

关于检测:第12期压缩表性能监测

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

1. SELECT 业务

2. INSERT 业务

3. DELETE 业务

4. UPDATE 业务

二、压缩表监测

1、INNODB_CMP/INNODB_CMP_RESET

相干字段含意为：

2、INNODB_CMPMEM /INNODB_CMPMEM_RESET

相干字段含意为：

3、INNODB_CMP_PER_INDEX/INNODB_CMP_PER_INDEX_RESET

相干字段含意为：

压缩表监测的理论用例

1、查问速度比照

2、删除数据

3、更新大量数据

4、更新大量数据

评论

发表回复取消回复

更多文章

苹果iOS打包的ipa应用无法安装？一篇文章带你了解可能的原因及排查方法

图解Golang：从零开始实现简易版过期LRU缓存

深入解析：基于Delta的线性数据结构模型，打造高效富文本编辑器

轻松管理社交媒体：使用Automa插件实现一键拉黑功能

关于检测:第12期压缩表性能监测

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

1. SELECT 业务

2. INSERT 业务

3. DELETE 业务

4. UPDATE 业务

二、压缩表监测

1、INNODB_CMP/INNODB_CMP_RESET

相干字段含意为：

2、INNODB_CMPMEM /INNODB_CMPMEM_RESET

相干字段含意为：

3、INNODB_CMP_PER_INDEX/INNODB_CMP_PER_INDEX_RESET

相干字段含意为：

压缩表监测的理论用例

1、查问速度比照

2、删除数据

3、更新大量数据

4、更新大量数据

评论

发表回复 取消回复

更多文章

苹果iOS打包的ipa应用无法安装？一篇文章带你了解可能的原因及排查方法

图解Golang：从零开始实现简易版过期LRU缓存

深入解析：基于Delta的线性数据结构模型，打造高效富文本编辑器

轻松管理社交媒体：使用Automa插件实现一键拉黑功能

发表回复取消回复