Changing tx_isolation and binlog format on master server – do i also need to adjust slave?

Posted on

Question :

We are running a Drupal Commerce site with MariaDB 10.0 and master/slave replication. Because of the way our application works, we end up running in to a ton of deadlocks under heavy load. Apparently this is a fairly common problem for large Drupal Commerce sites.

We’ve successfully tested changing two settings on our test servers (which are standalone; not master/slave) which has eliminated the deadlock problem:

transaction-isolation = READ-COMMITTED
binlog_format = ROW

My questions are:

1) When we put this change on our production master/slave setup, do we need to apply this to both the master and slave, or just the master?

2) Is there any special sequence we need to do things in? or can we just adjust my.cnf and restart mariadb?

3) Do we need to ‘stop slave’ while applying this change?

Answer :

The answers we got back from one of the senior DBA’s at our hosting provider are as follows. I’m sharing in case anyone else runs in to this.

It is generally quite critical to have the same settings on the slave
as the master. An easy reason recommend this is that you might promote
that slave to master some day and do not want surprises. There are
other technical reasons for this, so I suggest that the slave use the
same settings (aside from server-id and maybe the names of log files)

You can technically change the settings dynamically without a restart
but this is pretty tricky to get right since existing connections will
use the old settings and a restart is sometime faster than trying to
update all sessions to use a new binary log format by forcefully
disconnecting them, then repeating the process with tx isolation (this
matters for long lived connections which may not exist in your
environment)

I would suggest you add the settings to the slave and master’s my.cnf,
then restart them in any order. You do not need to manually run “stop
slave” or “start slave”

The slave does not have binary logging on, nor log-slave-updates, so
there is nothing specific about the order of operations to manage.

To recap:

1) When we put this change on our production master/slave setup, do we need to apply this to both the master and slave, or just the master?

Both, primarily to be able to promote a slave to master without surprises.

2) Is there any special sequence we need to do things in? or can we just adjust my.cnf and restart mariadb?

Not really. Just adjust settings and restart in any order.

3) Do we need to ‘stop slave’ while applying this change?

No.

Leave a Reply

Your email address will not be published. Required fields are marked *