mariadb generates a huge binlog with unreadable entries in a short time

user_8640356 0
Viewed 119

MariaDB 10.4.6 (Windows)
binlog_format = ROW

My binlog is exploding. Since a few days I have a lot of such entries in it:

#201005  9:35:11 server id 14020  end_log_pos 262175992 CRC32 0xce45fdf1    Update_rows: table id 433 flags: STMT_END_F

BINLOG '
r8x6XxPENgAAewAAACY/nw8AALEBAAAAAAEAE215c3FsbW9uaXRvcl9lbmdpbmUAGm1pc19teXNx
bF9pbnN0YW5jZXNfc3RhdHVzABYBDxL+AgICDw8DCQkBCQ8BAg8ICA8SEDIAAP4DCgAeAPoAFABQ
wwAAoBOICLzi ...

with really a lot of rows. I found such big entries multiple times a second. Now every 6 minutes a new 250MB-binlog-file is created (before that it was every few hours). I recently changed the binlog_format from the default (MIXED) to ROW. That's it.

What is the cause for that behaviour, what purpose are these entries for? And what can I do to suppress these unreadable entries?

mariadb configuration mysql
1 Answers

Most likely reason is that you have relatively few statements that modify a large number of rows. Enable the general log (or slow log with long_query_time=0), and you'll capture all the statements that the server executes. That should help you find the queries that behave this way.

The statement based binlog stores statements that modify the data. There are edge cases for which this is not deterministic. Row based binlog stores all the rows that were modified. If you have a single statement that modifies every row in a large table, the entire table will end up in the binlog twice (original row and new row are stored in row based binlog - full old row for matching purpose, and the full replacement row).

There is nothing you can do to change this behaviour, it is the nature of how row based binlog works. The only thing you can do to reduce the space requirement is to put the binlogs on a compressed file system or reduce their retention period.

user_18426770
Related