mysql replication delay very serious

Question

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

iostat
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

Answer 1

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.

Suggestion:

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

Answer 2

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.