How to Use Multi-Master MySQL to Get a Leg Up on Database Performance

Introduction

In
our last article we introduced multi-master
mysql, the replication topology which provides you with two masters, one
active and one passive.  When combined with the MMM manageability tool,
this setup provides high availability and additional reliability as well as
automatic failover.

Once
you have all these components configured, it provides you with a host of new
features to your MySQL environment.

Perform Upgrades with No Downtime

Rolling
upgrades are a great thing if you can execute them correctly. 
Multi-master MySQL gives you this flexibility.  How do you do it?  In
simple steps, you would do the following:

1. Login to your passive server (db2)

stop the slave

2. shutdown mysql
3. perform your MySQL software upgrade
4. startup the database
5. run the migration scripts included with the version jump you are doing
6. start the slave
7. test the passive server thoroughly
8. use mmm_control to switch the writer role to db2
9. execute steps 1 through 8 again with db1 as passive server

use mmm_control to switch the writer role back to db1

Perform Online Alter of a Table

With
MySQL, many operations such as ALTER commands cannot be done online. 
ALTER commands might include adding a new column, changing a column’s datatype,
or changing the tables’ storage engine.  It is somewhat of an
overstatement; there are a few that can be done partially online, or it may
vary depending on your storage engine.  At any rate, most cannot be done
online.  This means that the object you’re modifying becomes inaccessible
to your application while the database is doing its work.  

If
this is a particularly small table, it is not very noticeable, however if you’re
dealing with a huge table say 50 or 100 million rows, the full table lock will
block other processes from accessing that data for some time.

That’s
where multi-master replication comes in.  By following the steps we
describe for doing a rolling upgrade, we can also do what one my call a rolling
ALTER.  

Simply
perform the ALTER statement on the passive server, then switch roles and do the
operation again on the other server.  

Run a Heavy Reporting Query Without Impact to Production

With
your Multi-master setup and humming along comfortably, you’ll find all sorts of
interesting uses for that passive master.  With MMM in charge of things,
you’ll have a writer role, and a reader role.  They’ll always point to the
right place, so your application doesn’t need to worry if they moved.

Want
to run some heavy duty reporting queries?  These typically take a long
time to run because you’re visiting lots of rows, comparing and joining between
subsets of large tables, and so on.  So, it doesn’t make sense to have
those long running queries hit your primary database server.  MMM exposes
a reader role IP just for this purpose.  Point your slow running queries,
or the reporting portion of your applications backend interface at this IP
address.  If and when you switch roles, perhaps doing maintenance like we
described above, the IP address of the reader or passive master will remain the
same thanks to MMM.

Robust Non-blocking Backups

Backups
are another area of administration that can negatively impact performance of the
primary database.   If you’re using mysqldump to do your backup, MySQL
will perform full table scans on all the tables you dump.  That means
visiting every block of every table, reading it into cache, and then writing it
out to your dump file.  Slow, a lot of table locking, a lot of full table
scans.  Performance nightmare!

For
that reason, some administrators choose to use the passive master for this
purpose as well. 

Easily Add Slaves

MMM
also has some additional features for adding slaves.  Enter the mmm_clone utility,
which performs this function. Specify the remote host to clone from with
–host, and the type of clone with –clone-mode including slave-slave, which is
cloning from a slave, master-slave, which is cloning from a master, and master-master,
which is also cloning from a master, but creating a circular replication setup.
 

Feature Testing and Benchmarking

With
Multi-master setup as we’ve outlined, you can do rolling upgrades.  What
that means is that you can have one server running a newer version of MySQL
than the other does.  You might then exploit this ability to feature test
your application.  

Perhaps
you want to run some queries against the new version of MySQL and compare their
performance, explain plan or other behavior against the new version of
MySQL.  This setup can facilitate that.  Alternatively, as above with
our slow running report example, perhaps you want to do some
benchmarking.  The very nature of benchmarking is you want to really
hammer away at your server and push it to its limits.  However, you
probably don’t want to do this against the primary production server.  The
passive master makes a perfect choice.

Beware Inconsistencies Between Masters

As
we’ve discussed at length in the past, and hopefully you’re very aware of by
now, MySQL’s replication out of the box is by no means bulletproof.  It
can fail in ideal cases with an error message.  When this happens, we
quickly find out about it if we’re monitoring for errors.  I call this
ideal because usually the error message sends you on the trail of the problem,
and solution.  

However,
it can very often also get out of sync silently.  This is unfortunate, but
also fixable.  Enter Maatkit’s mk-table-checksum tool.  Run it on one
master, and checksums of all your tables are calculated for a point in time,
and dumped to a checksum holding table.  This table then propagates
through replication making its way to the passive master.  You then run an
additional check on that side to verify everything is consistent.  

Depending
on the volume of data that is changing in your database, you may only need to
run this periodically.  Consider running it weekly, and see if a lot of
problems show up every week.  If so, increase the frequency to a few times
a week or daily.  You don’t want your data to be out of sync for long if
you’re reporting, or backing up based on it.  

When
you identify inconsistencies, use the mk-table-sync tool to bring them back in
line together.

Conclusion

As
you can see the Multi-master MySQL configuration, especially when managed by
MMM, brings to the table a whole host of new functionality and flexibility to
your MySQL database environment.  With all that’s offered, you’ll
definitely want to take a look at and try out some of this technology.

Additional Resources

MySQL 17.6.10. MySQL Cluster Replication: Multi-Master and Circular Replication

Installing and Managing Multi
Master Replication Manager (MMM) for MySQL High Availability

»


See All Articles by Columnist
Sean Hull