In contrast to the previous benchmark , I enabled foreign keys, used REPEATABLE-READ isolation level, and I used UTF8 character sets. Because of these changes, the results are not really comparable with the previous results.

The dataset is the same: sysbench-tpcc with ten tables and 100 warehouses, resulting in a total of 1000 warehouses, and about a 90GB dataset size.

I will use innodb_buffer_pool_size80GB, 70GB, and 60GB to emulate different IO loads and evaluate how that affects binary logs writes.

For the first run, let's check the results without binary logs vs. with binary log enabled, but with sync_binlog=0:

We can see that results without binary logs are generally better, but we can also see that with binary logs enabled and sync_binglog=0, there are regular drops to 0 for 1-2 seconds. This basically results in stalls in any connected application.

So, enabling binary logs may result in regular application stalls. The reason for this is that there is a limit on the size of the binary log file (max_binlog_size), which is 1GB. When the limit is reached, MySQL has to perform a binary log rotation. With sync_binlog=0, all previous writes to the binary log are cached in the OS cache, and during rotation, MySQL forces synchronous flushing of all changes to disk. This results in complete stalls every ~40 seconds (the amount of time it takes to fill 1GB of binary log in the above tests).

How can we deal with this? The obvious solution is to enable more frequent sync writes of binary logs. This can be achieved by setting sync_binlog > 0 . The popular choice is the most strict, sync_binlog=1 , providing the most guarantees. The strict setting also comes with noted performance penalties. I will also test sync_binlog=1000and sync_binlog=10000 , which means perform synchronous writes of binary logs every 1000 and 10000 transactions, respectively.

The Results

The same results in a tabular format with median throughput (tps, more is better).

Some conclusions we can make:

sync_binlog=1 comes with the biggest performance penalty, but with minimal variance. This is comparable to running without binary logs.

sync_binlog=0 provides best (for enabled binary logs) performance, but the variance is huge.

sync_binlog=1000 is a good compromise, providing better performance than sync_binlog=1 with minimal variance.

sync_binlog=10000 might not be good, showing less variance than with 0, but it is still big.

So what value should we use? This is probably a choice between sync_binlog=1or some value like 1000. It depends on your use case and your storage solution. In the case of slow storage, sync_binlog=1may show a bigger penalty compared to what I can see on my enterprise SATA SSD SAMSUNG SM863.

Filesystems

All of the above results were on an EXT4 filesystem. Let's compare to XFS. Will it show different throughput and variance?

The median throughput in tabular format:

We can observe the general trend that median throughput on XFS is a little worse than with EXT4, with practically identical variance.

The difference in throughput is minimal. You can use either XFS or EXT4.

Hardware Specs

Supermicro server:

Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz

2 sockets / 28 cores / 56 threads

Memory: 256GB of RAM

Storage: SAMSUNG SM863 1.9TB Enterprise SSD

Filesystem: ext4/xfs

Percona-Server-5.7.21-20

OS: Ubuntu 16.04.4, kernel 4.13.0-36-generic

Extra Raw Results, Scripts and Config

My goal is to provide fully repeatable benchmarks. To that effect, I've shared all the scripts and settings I used in this following GitHub repo.