比如要查看：SET @@SESSION.GTID_NEXT= '43e4cdae-2fa1-11ee-9049-000c29318629:2886' 这个事务的大小，可以通过binbog日志进行查看
root@mysqldb 20:40: [(none)]> pager grep 'Gtid\|COMMIT'; PAGER set to 'grep 'Gtid\|COMMIT'' root@mysqldb 20:46: [(none)]> show binlog events in 'mybinlog.000030'; | mybinlog.000030 | 196 | Gtid | 1463306 | 275 | SET @@SESSION.GTID_NEXT= '43e4cdae-2fa1-11ee-9049-000c29318629:2885' | | mybinlog.000030 | 496 | Xid | 1463306 | 527 | COMMIT /* xid=25 */ | | mybinlog.000030 | 527 | Gtid | 1463306 | 606 | SET @@SESSION.GTID_NEXT= '43e4cdae-2fa1-11ee-9049-000c29318629:2886' | | mybinlog.000030 | 827 | Xid | 1463306 | 858 | COMMIT /* xid=27 */ | | mybinlog.000030 | 858 | Gtid | 1463306 | 943 | SET @@SESSION.GTID_NEXT= '43e4cdae-2fa1-11ee-9049-000c29318629:2887' | | mybinlog.000030 | 32926813 | Xid | 1463306 | 32926844 | COMMIT /* xid=36 */ | 4029 rows in set (0.07 sec)
可以看到这个事务大小有31M
root@mysqldb 20:55: [(none)]> pager Default pager wasn't set, using stdout. root@mysqldb 20:55: [(none)]> select format_bytes(32926813 - 858); +-----------------------------+ | format_bytes(32926813 - 858) | +-----------------------------+ | 31.40 MiB | +-----------------------------+
这样就计算出了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 | +-----------+------------+---------------------+ | 20.81 MiB | 20.81 MiB | 1 |
注意：要查看sql语句的大小，一定要把这个表先清空，然后再执行sql语句，最后再用这条sql语句进行查看这条事务的大小
truncate table performance_schema.binary_log_transaction_compression_stats;
此外还可以使用MySQL shell plugin列出binlog中binlog事件大小。
MySQL db04:33060+ ssl JS > check.showTrxSizeSort() Transactions in binary log mybinlog.000030 orderer by size (limit 10): 31 mb - 43e4cdae-2fa1-11ee-9049-000c29318629:2887 252 bytes - 43e4cdae-2fa1-11ee-9049-000c29318629:2886 252 bytes - 43e4cdae-2fa1-11ee-9049-000c29318629:2885 20 mb - 43e4cdae-2fa1-11ee-9049-000c29318629:2888 20 mb - 43e4cdae-2fa1-11ee-9049-000c29318629:2892 20 mb - 43e4cdae-2fa1-11ee-9049-000c29318629:2890 20 mb - 43e4cdae-2fa1-11ee-9049-000c29318629:2891 20 mb - 43e4cdae-2fa1-11ee-9049-000c29318629:2889
MySQL shell Plugin的下载地址：
https://github.com/lefred/mysqlshell-plugins/wiki/check#showtrxsizesort