1

We have a MySQL server which is a replication master. We need to change the innodb_buffer_pool_size and innodb_flush_method to (hopefully) improve performance as the database has grown.

What is the "safest" method for us to do this? We have 2 slave servers which will need to continue replication after this is done.

The plan so far is:

  1. Stop replication on the 2 slave servers with STOP SLAVE.
  2. Stop MySQL on all 3 servers with service mysql stop.
  3. Make the changes in the my.cnf file on the master server.
  4. Start MySQL on the master again to get the live database back online ASAP with service mysql start.
  5. Make the changes in the my.cnf file in both the slave servers.
  6. Start MySQL again on both slave servers with service mysql start.

The settings are:

innodb_buffer_pool_size = 54G
innodb_flush_method=O_DIRECT

The server has 64GB of memory and is a dedicated MySQL server, the server doesn't really do anything else at all. It's a Dell PowerEdge R720 running Ubuntu 12.04 Server.

Can anyone foresee any problems with this method? Is there anything else we need to be aware of?

2

What you have seems fine. I would add the following

Run this on the Master.

SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL sync_binlog = 1;
SET GLOBAL sync_master_info = 1;

This will cause everything that has been uncommitted to be committed on shutdown.

Then, it flushes the binlogs to disk.

On the Slaves, run this

SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL sync_binlog = 1;
SET GLOBAL sync_relay_log = 1;
SET GLOBAL sync_relay_log-info = 1;
SET GLOBAL sync_master_info = 1;

This will flush all the binlogs and relay logs. If the Slaves do not have binary logging enabled, then skip SET GLOBAL sync_binlog = 1;.

Your steps look like this now:

  1. Stop replication on the 2 slave servers with STOP SLAVE.
  2. Run SET GLOBAL innodb_fast_shutdown = 0; on all three servers
  3. Run SET GLOBAL sync_binlog = 1; on the Master
  4. Run SET GLOBAL sync_master_info = 1; SET GLOBAL sync_relay_log = 1; SET GLOBAL sync_relay_log-info = 1; SET GLOBAL sync_master_info = 1; on the Slave
  5. Stop MySQL on all 3 servers with service mysql stop.
  6. Make the changes in the my.cnf file on the master server.
  7. Start MySQL on the master again to get the live database back online ASAP with service mysql start.
  8. Make the changes in the my.cnf file in both the slave servers.
  9. Start MySQL again on both slave servers with service mysql start.
  • Finally managed to schedule the time for this last night. Seemed to go well. All changed and no issues found so far! There was a minor typo in your SET GLOBAL commands, "sync_relay_log-info" should have been "sync_relay_log_info" (which it won't let me edit in your post because I didn't change at least 6 characters), but that was easily corrected. Thanks for your help/input! – BT643 Jul 22 '14 at 7:47
  • Found this answer useful so made the edit to fix the "sync_relay_log-info" typo. Got around the 6 character limit by adding some punctuation. :D – Xandel Mar 16 '18 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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