MySQL Bugs: #61270: parallel fysnc operations (transaction commits) do not scale on the slave

Bug #61270	parallel fysnc operations (transaction commits) do not scale on the slave
Submitted:	23 May 2011 19:16	Modified:	16 Jan 2013 17:13
Reporter:	Mark Stier	Email Updates:
Status:	Closed	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:	performance, replication

Description:
I did some tests with scalability. MySQL generally seems to scale extremely well with parallel write transactions. I think this is because fsyncs can be merged at a lower level.

However, the sequential nature of async replication (binlog format mixed) prevents the exploitation of that feature, which effectively destroys the scalability of the server because the slave will lag behind indefinitely and never be able to catch up without throwing enormous resources at it.

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:
Does it make any sense to wrap statements on the slave into longer transactions in order to avoid most fsyncs?

I think ongoing work on "parallel slave" may help. See:

http://forge.mysql.com/worklog/task.php?id=5569
http://forge.mysql.com/worklog/task.php?id=5754

and other related worklogs.

Sorry, cannot login to forge.mysql.com, so I give my 2 cents here:

you are basically talking of "sharding" there. My guess is that it could be helpful where synchronuous (consistent) disk writes on the slave are switched off. However, in any paranoid setup with full sync enabled on the slave(s), sharding won't help that much. A much better solution would be (IMHO) to group transactions, thereby enlarging the average transaction and reducing the number of actual commits. Think of "transaction replication granularity".

This issue has been resolved 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

I will mark this as closed, fixed in MySQL 5.6. If you feel that there is any specific case not covered in the 5.6 implementation, then please let me know.

For reference:
  http://mysqlmusings.blogspot.fr/2012/06/binary-log-group-commit-in-mysql-56.html

> Binary Log Group Commit - New in the 5.6 RC, replication *masters* now group writes to the Binlog on disk, rather than committing them one at a time, significantly improving performance on the *master side* of the topology.

So, no, the performance difference between master and slave seems to have become even more excessive now.