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.

I'm setting up a mysql master-slave configuration, where slave is located on a much weaker sever.

Since, if I understand correctly, slave only works on updates/inserts, what are the most critical configuration parameters that I can adjust in slave's my.cnf to ensure best performance and smallest possible latency?

  • Master: 32Gb RAM, Slave: 4Gb RAM
  • Mostly innodb tables
  • Percona-server 5.5 on both master and slave
share|improve this question

2 Answers 2

up vote 5 down vote accepted

If you concern is about performance and you don't care about loose a small chunck of data(usually 1 second) in case of server crashes, I would change the follow variables:

A good option is to read this article, it's a general overview to how optimize innodb, it will work for your master and also to your slave.

If you are planning to upgrade to the percona server which implement features from MySQL 5.6, I would recommend that you increase the number of slave sql threads slave-parallel-workers

share|improve this answer
    
Thank you! Even innodb_flush_log_at_trx_commit=0 alone made 18 hour latency go away in just a few hours! –  Silver Light Apr 30 '13 at 9:27
    
disable innodb_doublewrite will also brings you some improvement on I/O –  altmannmarcelo Apr 30 '13 at 9:35

You just need to optimize for InnoDB environment. innodb_buffer_pool_size is still the key for performance.

And beacuse your slave server is weak than master, you might need to deal with replication lag. (i.e. Write to master, but cannot read data from slave immediately)

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.