[翻译]-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的事务的大小

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

复制代码

[译者注]：这里作者是在mysql shell中执行的SQL语句，如果在mysql客户端，这种写法是会报错的。
我们可以看到这个事务生成了51M大小的binlog event。
这种方法可能很复杂，尤其是当你需要解析多个二进制日志去查找所需的事务时。
幸运的是，performance_schema可以让我们的工作更轻松一点，事实上，即使我们不开启二进制日志压缩功能，我们也能解析表binary_log_transaction_compression_stats获取事务大小的信息

1. select format_bytes(UNCOMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) size,
   
2.        format_bytes(COMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) compressed,
   
3.        TRANSACTION_COUNTER 
   
4.   from performance_schema.binary_log_transaction_compression_stats;
   
5. +-----------+------------+---------------------+
   
6. | size      | compressed | TRANSACTION_COUNTER |
   
7. +-----------+------------+---------------------+
   
8. | 51.38 MiB | 51.38 MiB  |                   1 |
   
9. +-----------+------------+---------------------+

复制代码

字段TRANSACTION_COUNTER的值非常重要，如果它的值大于1的话，事务大小的值为平均值。
所以，如果你想知道一个事务的准确大小时，你必须先将表清空，然后运行你的DML语句。
让我们看一下这个例子：

1. SQL> select format_bytes(UNCOMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) size,
2.        format_bytes(COMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) compressed,
3.        TRANSACTION_COUNTER 
4.   from performance_schema.binary_log_transaction_compression_stats;
5. +-----------+------------+---------------------+
6. | size      | compressed | TRANSACTION_COUNTER |
7. +-----------+------------+---------------------+
8. | 17.13 MiB | 17.13 MiB  |                   6 |
9. +-----------+------------+---------------------+
10. 1 row in set (0.0004 sec)
12. SQL > truncate table performance_schema.binary_log_transaction_compression_stats;
13. Query OK, 0 rows affected (0.0018 sec)
15. SQL > update sbtest1 set k=k+4;
16. Query OK, 132188 rows affected (1.3213 sec)
18. Rows matched: 132188  Changed: 132188  Warnings: 0
20. SQL > select format_bytes(UNCOMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) size,
    
21.        format_bytes(COMPRESSED_BYTES_COUNTER/TRANSACTION_COUNTER) compressed,
    
22.        TRANSACTION_COUNTER 
    
23.   from performance_schema.binary_log_transaction_compression_stats;
    
24. +-----------+------------+---------------------+
    
25. | size      | compressed | TRANSACTION_COUNTER |
    
26. +-----------+------------+---------------------+
    
27. | 51.38 MiB | 51.38 MiB  |                   1 |
    
28. +-----------+------------+---------------------+1 row in set (0.0017 sec)

复制代码

[译者注]：生产环境下，同一时刻可能产生了多个事务，所以这个在测试环境非常有效，在生产环境，也可能不尽人意。
我们还可以使用MySQL Shell Plugin[2]来列出一个二进制日志中的所有事务大小

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

复制代码

但是我怎样才能知道事务里面的GTID信息呢？如果MySQL服务器支持返回信息，那么MySQL可能将GTID信息返回到客户端，MySQL Shell支持这个功能。
为了开启它，我们可以使用session_track_gtids:

1. SQL > set session_track_gtids='OWN_GTID';
   
2. Query OK, 0 rows affected (0.0001 sec)
   
3. 
   
4. SQL > update sbtest1 set k=k+1;
   
5. Query OK, 132183 rows affected (5.6854 sec)
   
6. 
   
7. Rows matched: 132183  Changed: 132183  Warnings: 0
   
8. GTIDs: 17f6a975-e2b4-11ec-b714-c8cb9e32df8e:7541914

复制代码

如你所见，MySQL Shell会返回事务的GTID信息（update使用auto_commit）
SELECT的大小

但是我们如何知道SELECT的大小呢？
为了计算、得到SELECT语句事务的大小，我们可以计算server端发送给client的字节数，如下所示：

1. SQL > select variable_value 
   
2.       from performance_schema.status_by_thread 
   
3.        join performance_schema.threads using(thread_id) 
   
4.       where processlist_id=CONNECTION_ID() 
   
5.         and variable_name='Bytes_sent' into @before;
   
6. 
   
7. SQL > select * from sbtest1;
   
8. 
   
9. SQL > select format_bytes(variable_value - @before) query_size 
   
10.         from performance_schema.status_by_thread 
    
11.         join performance_schema.threads using(thread_id) 
    
12.        where processlist_id=CONNECTION_ID() 
    
13.          and variable_name='Bytes_sent' ;
    
14. +------------+
    
15. | query_size |
    
16. +------------+
    
17. | 26.08 MiB  |
    
18. +------------+
    
19. 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
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源:https://www.cnblogs.com/kerrycode/p/17960809
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！