# replication performance binlog\_cache\_size and binlog\_stmt\_cache\_size

**URL:** <https://forums.percona.com/t/replication-performance-binlog-cache-size-and-binlog-stmt-cache-size/8209>\
**Category:** Percona Server for MySQL 5.7\
**Tags:** mysql, percona\
**Created:** [October 29, 2020, 4:09am UTC](https://forums.percona.com/t/replication-performance-binlog-cache-size-and-binlog-stmt-cache-size/8209 "2020-10-29T04:09:14Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kry](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kry/32/10898_2.png) [@Kry](https://forums.percona.com/u/Kry)\
**Post date:** [October 29, 2020, 4:09am UTC](https://forums.percona.com/t/replication-performance-binlog-cache-size-and-binlog-stmt-cache-size/8209/1 "2020-10-29T04:09:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [November 3, 2020, 7:25am UTC](https://forums.percona.com/t/replication-performance-binlog-cache-size-and-binlog-stmt-cache-size/8209/2 "2020-11-03T07:25:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Deon\_Joubert1](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/deon_joubert1/32/3659_2.png) [@Deon\_Joubert1](https://forums.percona.com/u/Deon_Joubert1)\
**Post date:** [October 13, 2021, 3:22pm UTC](https://forums.percona.com/t/replication-performance-binlog-cache-size-and-binlog-stmt-cache-size/8209/3 "2021-10-13T15:22:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [October 13, 2021, 3:31pm UTC](https://forums.percona.com/t/replication-performance-binlog-cache-size-and-binlog-stmt-cache-size/8209/4 "2021-10-13T15:31:07Z")

</div>

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.