MySQL中查询和事务的大小

2023-08-20 15:47 abce 阅读(702) 评论(0) 编辑收藏举报

有时候了解事务的大小非常重要，尤其是当计划迁移到HA环境，为了保证集群的最佳性能，事务的大小是有限制的。

这里来尝试分析一下了解事务大小的不同方法。

首先要将事务分成两种类型：
1.生成数据的事务(写操作，比如insert、delete、update等DML操作)
2.只读的事务(查询操作)

在HA环境，第一类事务很重要。

DML的大小
分析DML事务的大小，唯一的可能方法是解析二进制日志(即查看binlog event)
例如，检查GTID为005fcb39-eb11-11ed-9aec-005056b0aaa3:75506865对应事务的大小。

SQL > pager grep 'Gtid\|COMMIT' ;
PAGER set to 'grep 'Gtid\|COMMIT''
SQL > show BINLOG EVENTS in 'binlog.000339' ;
| binlog.000339 | 25548333 | Gtid           |     25031 |    25548412 | SET @@SESSION.GTID_NEXT= '005fcb39-eb11-11ed-9aec-005056b0aaa3:75506865'   |
| binlog.000339 | 25551637 | Xid            |     25031 |    25551668 | COMMIT /* xid=3158707078 */                                                |
SQL > pager
Default pager wasn't set, using stdout.
SQL > select format_bytes(25551637-25548333);
+---------------------------------+
| format_bytes(25551637-25548333) |
+---------------------------------+
| 3.23 KiB                        |
+---------------------------------+
1 row in set (0.00 sec)

这样可以计算出binlog event的大小。
但是这个方式还是有点麻烦，尤其是要想找出某个事务的大小，可能需要查看多个binlog日志文件。

幸运的是，Performance_Schema能让我们的工作再次变得更轻松。事实上，我们可以通过解析表binary_log_transaction_compression_stats，获得有关事务大小的信息。即使我们不开启二进制日志压缩，也可以使用：

select format_bytes(UNCOMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) size,
       format_bytes(COMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) compressed,
       TRANSACTION_COUNTER 
  from performance_schema.binary_log_transaction_compression_stats;
+----------+------------+---------------------+
| size     | compressed | TRANSACTION_COUNTER |
+----------+------------+---------------------+
| 4.16 KiB | 4.16 KiB   |              844750 |
+----------+------------+---------------------+
1 row in set (0.00 sec)

这里的TRANSACTION_COUNTER列非常重要，因为如果它大于 1，则表示值是平均值。

因此，如果确实需要知道一个事务的确切大小，就需要在运行DML之前先truncate该表。

让我们看看这个示例：

SQL> select format_bytes(UNCOMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) size,
       format_bytes(COMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) compressed,
       TRANSACTION_COUNTER 
  from performance_schema.binary_log_transaction_compression_stats;
+-----------+------------+---------------------+
| size      | compressed | TRANSACTION_COUNTER |
+-----------+------------+---------------------+
| 48.39 MiB | 48.39 MiB  |                   9 |
+-----------+------------+---------------------+
1 row in set (0.0004 sec)
 
SQL > truncate table performance_schema.binary_log_transaction_compression_stats;
Query OK, 0 rows affected (0.0040 sec)
 
SQL > update emp set age=age+2;
Query OK, 982563 rows affected (6.3273 sec)
 
Rows matched: 982563  Changed: 982563  Warnings: 0
 
SQL > select format_bytes(UNCOMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) size,
       format_bytes(COMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) compressed,
       TRANSACTION_COUNTER 
  from performance_schema.binary_log_transaction_compression_stats;
+-----------+------------+---------------------+
| size      | compressed | TRANSACTION_COUNTER |
+-----------+------------+---------------------+
| 87.30 MiB | 87.30 MiB  |                   1 |
+-----------+------------+---------------------+
1 row in set (0.0012 sec)

此外，还可以使用 MySQL Shell Plugin列出binlog中的binlog事件。

JS > check.showTrxSizeSort()
Transactions in binary log binlog.000339 orderer by size (limit 5):
23 mb - 005fcb39-eb11-11ed-9aec-005056b0aaa3:75506844
23 mb - 005fcb39-eb11-11ed-9aec-005056b0aaa3:75506845
23 mb - 005fcb39-eb11-11ed-9aec-005056b0aaa3:75506846
23 mb - 005fcb39-eb11-11ed-9aec-005056b0aaa3:75506847
bytes - 005fcb39-eb11-11ed-9aec-005056b0aaa3:75506848

MySQL Shell Plugin的下载地址：

1	`https://github.com/lefred/mysqlshell-plugins/wiki/check#showtrxsizesort`

如何找出我的事务对应的GTID

SQL > set session_track_gtids='OWN_GTID';
Query OK, 0 rows affected (0.0011 sec)
 
SQL > update emp set age=age+1;
Query OK, 982563 rows affected (6.7834 sec)
 
Rows matched: 982563  Changed: 982563  Warnings: 0
GTIDs: 005fcb39-eb11-11ed-9aec-005056b0aaa3:75506898

SELECT的大小
通过统计mysql server发送给mysql client的字节量来计算select语句的事务大小：

> select count(*) from emp ;
+----------+
| count(*) |
+----------+
|   172583 |
+----------+
1 row in set (48.22 sec)
 
> select variable_value 
       from performance_schema.status_by_thread 
        join performance_schema.threads using(thread_id) 
       where processlist_id=CONNECTION_ID() 
         and variable_name='Bytes_sent' into @before;
Query OK, 1 row affected (0.00 sec)
 
> select * from emp ;
 
> select format_bytes(variable_value - @before) query_size 
         from performance_schema.status_by_thread 
         join performance_schema.threads using(thread_id) 
        where processlist_id=CONNECTION_ID() 
          and variable_name='Bytes_sent' ;
+------------+
| query_size |
+------------+
| 42.81 MiB  |
+------------+
1 row in set (0.00 sec)