Detecting Large Transactions in MySQL Binlog Using Linux Commands

We dislike large transactions in MySQL because they cause maintenance problems, so it is important to detect them by examining the binlog.

The experiment creates a test database with GTID enabled, then generates transactions of varying sizes using methods from a previous tutorial.

By decoding the binlog, we can locate the GTID_event that marks the start of each transaction. Filtering the binlog to isolate these events allows us to calculate the size of each transaction.

Two grep tricks are used: inserting a literal tab character with "$(printf '\t')" to filter tab‑separated fields, and using the -B option to include the preceding line that contains the GTID_event position in bytes.

After extracting the position values, we subtract consecutive lines to obtain the byte size of each transaction, sort the sizes, and take the top values. In the example, the largest transaction occupies about 658 KB in the binlog.

The following command line performs the entire analysis:

~ /opt/mysql/5.7.20/bin/mysqlbinlog data/mysql-bin.000001 \
| grep "GTID$(printf '\t')last_committed" -B 1 \
| grep -E '^# at' \
| awk '{print $3}' \
| awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp);tmp=$1}' \
| sort -n -r | head -n 10

This approach, using only standard Linux utilities, provides a quick and efficient way to monitor binlog activity and identify unusually large transactions.