MySQL replication: most important config parameters for performance on slave server?

Question

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

Answer 1

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

• innodb_buffer_pool_size - try to use 80% of your total ram (in this case 3.2Gb)
• innodb_log_file_size - chose a good value in here to optimize the i/o in your slave
• innodb_flush_log_at_trx_commit - set it to 2 or 0
• innodb_doublewrite - disable innodb doublewrite
• innodb_flush_method - change the flush method to O_DIRECT
• sync_binlog - if you have binary log enable on slave, disable sync_binlog
• make sure you have sync slave variables disabled sync_master_info sync_relay_log and sync_relay_log_info

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

Answer 2

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)