replication performance binlog_cache_size and binlog_stmt_cache_size

MySQL & MariaDB Percona Server for MySQL 5.7
,
1

Hey.

It is not entirely clear how to configure two variables to improve replication performance.

binlog_cache_size and binlog_stmt_cache_size

The description says that the Binlog_stmt_cache_use and Binlog_stmt_cache_disk_use state variables can be useful for adjusting the size for setting binlog_stmt_cache_size,

The description says that the Binlog_cache_use and Binlog_cache_disk_use state variables can be useful for adjusting the size for setting binlog_cache_size ,

There is no understanding .

But how exactly to tune

Maybe like this:

binlog_cache_size = Binlog_cache_use + Binlog_cache_disk_use

binlog_stmt_cache_size = Binlog_stmt_cache_use + Binlog_stmt_cache_disk_use

or:

binlog_cache_size = Binlog_cache_use

binlog_stmt_cache_size = Binlog_stmt_cache_use

or:

binlog_cache_size = Binlog_cache_disk_use

binlog_stmt_cache_size = Binlog_stmt_cache_disk_use

thanks for the help.

1 Like
2

Hi Kry, the way to tune those variables is to check every few min

SHOW GLOBAL STATUS LIKE ‘Binlog_cache_disk_use’ and/or

SHOW GLOBAL STATUS LIKE ‘Binlog_stmt_cache_disk_use’

if you see that is increasing a lot then might be worth increasing the caches.

However I encourage you to first look at other options like enabling parallel replication, or relaxing consistency settings to improve the replication speed.

1 Like
3

Hey @Ivan_Groenewold, I’m also looking at these now following your suggestion during our sessions.

You say “if you see that is increasing a lot”. How would you define “a lot” - the server I’m looking at increases binlog_cache_disk_use about ~650 times a day which doesn’t seem like it’s enough to bother to optimize?

I wanted to see how many rows are typically recorded in the binary logs, but couldn’t quickly find a way to do that, but I would guess we’re logging in the region of 2 million rows per day.

So 650 out of the 2 million is a very small portion.

1 Like
4

Hi Deon, indeed 650 times a day doesn’t seem worth tuning. PMM has a dashboard with this info as well you can refer to.

2 Likes