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

July 20, 2010

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

» See All Articles by Columnist Sean Hull