Issue 12: performance monitoring of compression meter

Time:2021-2-25

Issue 12: performance monitoring of compression meter

LastHas solved the concept of compressed table, the impact of index page and simple use. This article mainly introducesHow to observe the compressed table.

1、 Classification of usage scenarios of compressed tables

1. Select business

This kind of operation does not need to decompress the compressed page, soPerfectUse compressed tables.

2. Insert service

This kind of operation needs to decompress and recompress the secondary index data page, but MySQL puts this part of operation into the change buffer, so the frequency is relatively low.

3. Delete service

Since MySQL writes tag bits directly for deletion, and then waits for regular purge thread cleaning, this is also suitable for compressed tables.

4. Update service

Because compression tables generally compress string data, such as text, varchar, etc., it is easy to fill up the change log (described in the previous article) by updating this data, resulting in frequent decompression and compression operations.

In general, compressed tables are suitable for read intensive, read-only, or very few updates business scenarios.

2、 Compression table monitoring

The monitoring of compressed tables is stored in the information center_ InnoDB is used in the schema_ Dictionary table starting with CMP. Through these tables, we can monitor whether the compressed table is healthy, whether the compressed page needs to be adjusted, or whether the compressed table is suitable for use.

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)

These tables are memory tables, or memory engines. Retrieval of these tables must have process permission. According to the operation mode of compressed table, it can be divided into the following three categories:

1、INNODB_CMP/INNODB_CMP_RESET

The disk access related data of InnoDB compression table, where InnoDB_ CMP and InnoDB_ CMP_ The structure of reset table is the same, but InnoDB is different_ CMP stands for compressed table historical access data, InnoDB_ CMP_ Reset is used to reset the compressed table history data. For example, to monitor the compressed table access data within one hour, you can perform the following simple steps:

  • Collect InnoDB first_ CMP related data;
  • Collect table InnoDB again in an hour_ CMP related data;
  • Visit InnoDB as soon as you finish_ CMP_ Reset table;
  • Initialization table InnoDB_ CMP。
The related fields are as follows:
field meaning remarks
page_size Represents the corresponding page of the compressed table (1K / 2K / 4K / 8K / 16K), and corresponds to the attribute key of the compressed table_ block_ size The difference is page_ Size unit: byte, key_ block_ Size in KB
compress_ops Represents the corresponding page_ The number of times a page of size has been compressed Empty page creation + 1; modification log full + 1
compress_ops_ok Represents the corresponding page_ The number of times the size page was compressed successfully Compression success + 1;
compress_time Represents the corresponding page_ The time, in seconds, that the size page is compressed
uncompress_os Represents the corresponding page_ The number of times the size page was decompressed Compression failure + 1; compression page does not exist in InnoDB buffer pool + 1
uncompress_time On behalf of the opposite page_ The time that the size page was decompressed
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)

Note: the ratio of these two values (compress)_ ops_ ok/compress_ OPS) is the most intuitive data, which can judge whether the compressed table is healthy or not; under normal circumstances, the ratio is 0 or 1 or close to 1; if the ratio is abnormal for a long time, we have to consider whether the page size of the compressed table is appropriate or whether the compressed table should be used in this scenario.

2、INNODB_CMPMEM /INNODB_CMPMEM_RESET

These two tables represent in InnoDB_ buffer_ Compressed table related access data in pool, InnoDB_ Cmpmem stands for historical data; InnoDB stands for historical data_ CMPMEM_ Reset represents the current instantaneous data. Only one access is needed, InnoDB_ The cmpmem table is reset.

The related fields are as follows:
field meaning remarks
page_size Corresponding page size
buffer_pool_instance Instance ID of InnoDB buffer pool
pages_used Number of pages used
pages_free The number of remaining pages, representing the page fragmentation rate The normal value is 0, and the ideal value is 1 at most
relocation_ops Number of operations to reassign the page
relocation_time The operation time of reassigning a page, in microseconds
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

These two tables represent the retrieval related data of the primary key and secondary index of the compressed table, without the_RESETFor historical data, with_RESETIt is instantaneous data.Different from the first two types of tables, this kind of table records data for index operation, which costs a lot and is not turned on by default. The related parameters are as follows

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)
The related fields are as follows:
field meaning remarks
database_name Database name
table_name Table name
index_name Index name

Other field classes are similar to the above description.

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)

Practical use cases of compressed table monitoring

Next, let’s take a look at the actual use cases of monitoring compressed tables. We use a single table space to create two tables

  • T1: uncompressed table
  • T2: compressed table with 4K page

Implementation of table building

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)

Insert part of the data, the corresponding disk size

[email protected]:/var/lib/mysql/3305/ytt# ls -shil
The total dosage is 2.0g
3949029 1.6g - rw-r --- 1 MySQL MySQL 1.6g March 31 21:18 t1.ibd
3946045 405m - rw-r --- 1 MySQL MySQL 404m March 31 21:42 t2.ibd

1. Query speed comparison

SQL 1 is slower than SQL 2, and SQL 3 is slower than SQL 4.

The advantage of compressed table is obvious when querying alone

-- 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. Delete data

Restart the MySQL instance, delete and update the compressed table t2, or empty the table InnoDB_ CMP and InnoDB_ CMP_ PER_ Index, that is, the corresponding suffix is_RESETTable for**

Delete a record from table t2

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

Corresponding compress_ ops/compress_ ops_ OK is 0

Table InnoDB_ CMP_ PER_ Index has no data because the index was not rebuilt. You can see that the delete operation is very suitable for compressing tables.

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. Update a small amount of data

The same is true_RESETSuffix table clear data

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

To view the corresponding monitoring table data, press_ ops_ ok/compress_ Ops is 1, and it’s healthy.

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. Update a lot of data

Do as usual_RESETEmpty two tables.

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

To view the data of the corresponding monitoring table, press_ ops_ ok/compress_ Ops ratio is very low, and half of the operations failed. The results show that a large number of updates should avoid compressed tables.

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)

This article mainly introduces the simple monitoring of compressed table in various scenarios, which can be summarized as follows: compressed table is only suitable for read intensive applications, or a small number of deleted or updated scenarios, and it is not recommended to use compressed table in other scenarios. In the next article, I will introduce the calculation of table statistics in detail.
What else do you want to know about the technical content of MySQL? Leave a message and tell Xiaobian!


Issue 12: performance monitoring of compression meter