MySQL Bugs: #61268: replication is not transaction-aware, which results in extremely excessive fsync

Bug #61268	replication is not transaction-aware, which results in extremely excessive fsync
Submitted:	23 May 2011 19:06	Modified:	16 Jan 2013 17:08
Reporter:	Mark Stier	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S5 (Performance)
Version:	5.5.12	OS:	Any
Assigned to:	Matthew Lord	CPU Architecture:	Any
Tags:	fsync, replication, slave, slow, transaction

Description:
Applies to mixed binlog format, and row-based replication.

I tried to speed up things a bit by putting write statements into transaction batches to eliminate most fsyncs. That works on the master. The slave, however, totally ignores the fact that statements are inside transactions and syncs master.info and relay-log.bin *after each single statement*, which is IMHO insane because you don't need that, and the slave is fsyncing *two* files after each statement, forcing the physical disk head to do two seeks per statement.

This is a serious performance bug because it practically eliminates the replication feature in any realistic scenario.

How to repeat:
OS: Debian 6.0.1, on i686

[mysqld]
character-set-server= utf8
user                = mysql55
port                = 3306
socket              = /tmp/mysql55.sock
datadir             = /var/lib/mysql55
log_error           = /var/log/mysql55_error.log
log                 = /tmp/sql.log
sync_binlog         = 1
sync_master_info    = 1
sync_relay_log      = 1
sync_relay_log_info = 1
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
server-id       = 2
slave_compressed_protocol = 0
slave-net-timeout = 60
log-bin=mysql-bin
innodb_flush_log_at_trx_commit = 1
innodb_file_format = Barracuda
innodb_file_per_table = 1

Suggested fix:
sync master.info and relay-logl.bin to disk only at transaction boundaries, and use the fact that transaction may be repeated after a crash.

Downgraded to "S5 (Performance)"? Are you sure?

Do you really think it is a performance issue if replication speed is reduced to 1%?

I certainly don't. That's a total blocker for everyone who wants to run a reliable and speedy database setup on commodity hardware.

The scenario that you described:
"The slave, however, totally ignores the fact that statements are inside transactions and syncs master.info and relay-log.bin *after each single statement*, which is IMHO insane because you don't need that, and the slave is fsyncing *two* files after each statement"

Is entirely expected, given your configuration:
sync_master_info    = 1
sync_relay_log      = 1
sync_relay_log_info = 1

You can find the manual entries on these here:
  http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html

You've explicitly said that you want these things to be sync'd to disk after very event. A transaction can be made up of many events (the actual binary log position is incremented for each *event* and not simply for each transaction).

fsync's are not free and the tradeoff is well documented.

The general issue is greatly improved in MySQL 5.6, with the functionality changed to store all the replication related info in transactional InnoDB tables (crash safe slaves) rather than these separate non-transactional files. There are also many other relevant improvements, such as binlog group commit and multi-threaded execution on the slave.

http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html#replication
http://dev.mysql.com/doc/refman/5.6/en/slave-logs.html