Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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. –  Derek Downey Dec 11 '12 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 ? –  RolandoMySQLDBA Dec 11 '12 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.

Suggestion:

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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