We use a ssd disk for master database and SATA disk for slave.

On high write load (300 writes/s), slave delay is very serious. I have set:

  • innodb_flush_log_at_trx_commit = 0
  • increased innodb_log_file_size

on the slave, but the delay continues.

the slave is for backup only, no read or write request.

Seconds_Behind_Master: 92265, continue increasing.

some conf:

innodb_buffer_pool_size=15G innodb_log_file_size=1G innodb_log_buffer_size=32M

du -hs mysql
338G    mysql

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sde               0.00    73.00   34.50  185.00   552.00  7086.00    69.59     5.63   25.63   19.19   26.83   3.65  80.20
  • Hi Simon, welcome to the site. Can you give us some more information on your setup? Does your application use this slave for read queries? How is the load on the slave, cpu-wise? Is your sata disk saturated? Some stats would be good. Commented Dec 11, 2012 at 14:58
  • Questions : Is the Slave read-only? Are any writes coming to the Slave besides the SQL thread? How high is Seconds_Behind_Master ? Is the Slave read-heavy ? Commented Dec 11, 2012 at 15:50

2 Answers 2


I wrote about something similar on MySQL replication: most important config parameters for performance on slave server?

Basically you should focus on the follow variables on slave (see the other post for more information):

  • innodb_buffer_pool_size
  • innodb_log_file_size
  • innodb_flush_log_at_trx_commit
  • innodb_doublewrite
  • innodb_flush_method
  • innodb_max_dirty_pages_pct
  • sync_binlog
  • sync_master_info
  • sync_relay_log
  • sync_relay_log_info

One thing to have in mind is that the slave have just a single thread to process all writes from master, let's say you master are able to handle 300 connections simultaneously, it's 300 threads writing at the same time (respecting locking issues, disk, ...) but on the slave you have just a single one to deal with all this transactions.


If you use MySQL 5.6 and have your data partitioned per database, you can take advantage of slave-parallel-workers


If the slave is for backup and read-only, you can remove unnecessary index such as full-text and non-unique index in the slave. It will reduce the chance of indexing and read-write I/O.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.