[翻译]-Query and Transaction size in MySQL

本文是对这篇文章Query and Transaction size in MySQL[1]的翻译,翻译如有不当的地方,敬请谅解,请尊重原创和翻译劳动成果,转载的时候请注明出处。谢谢!

[译者注]:本人在维护MySQL InnoDB Cluster时,遇到了“[ERROR] [MY-011608] [Repl] Plugin group_replication reported: 'Error on session 4410401. Transaction of size 192892268 exceeds specified limit 150000000. To increase the limit please adjust group_replication_transaction_size_limit option.'“这个错误,在研究这个问题时,看到了这篇文章,觉得写的非常不错。废话不多说,原文翻译如下。

有时候了解事务的大小非常重要,尤其是当你计划将MySQL迁移到HA解决方案时,像MySQL InnoDB Cluster,为了保证集群的最佳性能,默认情况下事务大小有一定限制。

今天我们来看看获取事务大小的不同方法。

首先我们需要将事务分为两种类型:

  • 生成数据的事务(写入操作,例如insert,delete和update等DML操作)
  • 只读事务(查询操作、DQL)

为了实现高可用性,只有第一类事务很重要。

DML的大小

为了知道DML事务的大小,我们唯一的方法就是去解析二进制日志(或者查询binlog event)。

我们需要从binlog文件中检查binlog事件并计算它的大小,为了说明这一点,让我们尝试查找GTID事务标识为:17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541914的事务的大小

SQL > \pager grep 'Gtid\|COMMIT' ;
Pager has been set to '
grep 'Gtid\|COMMIT' ;'.
SQL > show BINLOG EVENTS in 'binlog.000064' ;
| binlog.000064 |     213  | Gtid           |         1 |         298 | SET @@SESSION.GTID_NEXT= '17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541914' |
binlog.000064 | 53904723 | Xid            |         1 |    53904754 | COMMIT /* xid=75 */                                                     |
SQL > \pager
Pager has been disabled.
SQL > select format_bytes(53904754-213);
+----------------------------+
| format_bytes(53904754-213) |
+----------------------------+
| 51.41 MiB                  |
+----------------------------+
1 row in set (0.0005 sec)

[译者注]:这里作者是在mysql shell中执行的SQL语句,如果在mysql客户端,这种写法是会报错的。

我们可以看到这个事务生成了51M大小的binlog event。

这种方法可能很复杂,尤其是当你需要解析多个二进制日志去查找所需的事务时。

幸运的是,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 |
+-----------+------------+---------------------+
| 51.38 MiB | 51.38 MiB  |                   1 |
+-----------+------------+---------------------+

字段TRANSACTION_COUNTER的值非常重要,如果它的值大于1的话,事务大小的值为平均值。

所以,如果你想知道一个事务的准确大小时,你必须先将表清空,然后运行你的DML语句。

让我们看一下这个例子:

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 |
+-----------+------------+---------------------+
| 17.13 MiB | 17.13 MiB  |                   6 |
+-----------+------------+---------------------+
1 row in set (0.0004 sec)

SQL > truncate table performance_schema.binary_log_transaction_compression_stats;
Query OK, 0 rows affected (0.0018 sec)

SQL > update sbtest1 set k=k+4;
Query OK, 132188 rows affected (1.3213 sec)

Rows matched: 132188  Changed: 132188  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 |
+-----------+------------+---------------------+
| 51.38 MiB | 51.38 MiB  |                   1 |
+-----------+------------+---------------------+
1 row in set (0.0017 sec)

[译者注]:生产环境下,同一时刻可能产生了多个事务,所以这个在测试环境非常有效,在生产环境,也可能不尽人意。

我们还可以使用MySQL Shell Plugin[2]来列出一个二进制日志中的所有事务大小

JS > check.showTrxSizeSort()
Transactions in binary log binlog.000064 orderer by size (limit 10):
51 mb - 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541926
51 mb - 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541925
51 mb - 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541921
51 mb - 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541916
51 mb - 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541915
51 mb - 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541918
51 mb - 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541917
51 mb - 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541914
257 bytes - 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541924
257 bytes - 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541923

但是我怎样才能知道事务里面的GTID信息呢? 如果MySQL服务器支持返回信息,那么MySQL可能将GTID信息返回到客户端,MySQL Shell支持这个功能。

为了开启它,我们可以使用session_track_gtids:

SQL > set session_track_gtids='OWN_GTID';
Query OK, 0 rows affected (0.0001 sec)

SQL > update sbtest1 set k=k+1;
Query OK, 132183 rows affected (5.6854 sec)

Rows matched: 132183  Changed: 132183  Warnings: 0
GTIDs: 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541914

如你所见,MySQL Shell会返回事务的GTID信息(update使用auto_commit)

SELECT的大小

但是我们如何知道SELECT的大小呢?

为了计算、得到SELECT语句事务的大小,我们可以计算server端发送给client的字节数,如下所示:

SQL > 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;

SQL > select * from sbtest1;

SQL > 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 |
+------------+
| 26.08 MiB  |
+------------+
1 row in set (0.0010 sec)

总结概括

如你所见, MySQL Server的peformance_schema提供了很多关于二进制日志的信息,解析这些信息,你就可以得到DML事务或DQL的大小。

[译者注]:有时候我们需要定位产生超大事务的SQL语句,文中所讲述的方法和技巧还是有局限性,这里只是翻译原文,了解获取事务大小的一些技巧与方法,不做展开介绍。

参考资料

[1]

原文出处: https://lefred.be/content/query-and-transaction-size-in-mysql/

[2]

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

扫描上面二维码关注我
如果你真心觉得文章写得不错,而且对你有所帮助,那就不妨帮忙“推荐"一下,您的“推荐”和”打赏“将是我最大的写作动力!
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.