Storing MySQL Binary logs on NFS Volume

July 31, 2010

Author

Peter Zaitsev

Benchmarks

Insight for DBAs

MySQL

Share this Post:

There is a lot of discussions whenever running MySQL storing data on NFS is a good idea. There is a lot of things for and against this and this post is not about them.
The fact is number of people run their databases on NetApp and other forms of NFS storage and this post is about one of discoveries in such setup.

There are good reasons to have binary logs on NFS volume – binary logs is exactly the thing you want to survive the server crash – using them you can do point in time recovery from backup.

I was testing high volume replication today using Sysbench:

<br>sysbench --test=oltp --oltp-table-size=10000000 --db-driver=mysql --mysql-user=root --mysql-db=sbsmall --init-rng=1 --max-requests=100000000 --max-time=600 --oltp-test-mode=nontrx --oltp-nontrx-mode=update_nokey --num-threads=8 run<br>

1	<br>sysbench --test=oltp --oltp-table-size=10000000 --db-driver=mysql --mysql-user=root --mysql-db=sbsmall --init-rng=1 --max-requests=100000000 --max-time=600 --oltp-test-mode=nontrx --oltp-nontrx-mode=update_nokey --num-threads=8 run<br>

On this box I got around 12.000 of updates/sec which is not the perfect number, though it mainly was because of contention issues in MySQL 5.0 rather than any NAS issues.
This number was reachable even with binary log stored on NFS volume. This number is for sync_binlog=0 and innodb_flush_log_at_trx_commit=2

I noted however if I enable replication – connect the slave to this box the throughput on the Master drops to about 2800 updates sec…. which is very close to the magic number how many network roundtrips per second I can get over 1Gb link. It was even more interesting when that. If I would pause replication for prolonged period of time and let few GB of binary logs to accumulate the performance on Master will be high even with replication running, but it will slow down as soon as IO thread on the slave is caught up with master.

When I moved the Binary logs to the local storage I got very similar performance but there have been no degradation when replication is enabled.

I have not checked in details why this could be the case but I guess there is something which requires a network roundtrip when the binary log is written at the same time as slave-feeding thread is reading it.

I’d be curious to know if someone else can observe such behavior and if there is an NFS tuning which can be done to avoid it or if we need to fix MySQL