1

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?

1

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.

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.