Replicating from MySQL to Amazon RDS

There have been a number of comments that Amazon RDS does not allow users access to MySQL replication capabilities (for example here and here).  This is a pity. Replication is one of the great strengths of MySQL and the lack of it is a show-stopper for many users.  As of the latest build of Tungsten Replicator half of this problem is on the way to being solved. You can now set up real-time replication from an external MySQL master into an Amazon RDS instance.

In the remainder of this article I will explain how to set up Tungsten replication to an Amazon RDS slave, then add a few thoughts about why this feature is useful along with some suggestions for improvement.   To keep the article reasonably short I assume you understand the basics of installing Tungsten Replicator.  If you need more information, check out the online documentation.

Readying an RDS Test Instance

Amazon RDS is Amazon's on-demand relational database.  RDS supports several database types including MySQL, which I will use for this demonstration.  Launching a new instance is simple.  Login to the Amazon AWS Console using an account that has RDS enabled, then switch to the Amazon RDS Console.  Press the Launch a DB Instance button, whereupon a screen like the following appears:

RDS Database Selection

Press the Select button for MySQL Community Edition, which starts the configuration window.  (You can also replicate into Oracle if you are up for a challenge.  If you do this, please post what you did to get it to work!)  Next fill out properties for MySQL.

MySQL Instance Configuration

Among other things, you create a master login and password.  Note these carefully as you will need them to configure replication.  Then continue for another couple of screens, at which point you can launch your instance.  It takes about 10 minutes for new instances to spin up, after which you can see the instance properties in the AWS RDS Console.  Here's a screen shot of my test instance.

AWS RDS Console

Once the instance is up, test access.  This is of course necessary to prove the instance is running properly and that we can login from a remote location.  Note the host name in the Endpoint field.  This is a DNS entry for the new MySQL instance.  Using that and the master login, we can now fire up the mysql client from a remote host where we plan to run replication.

$ mysql -utungsten -p -htest.c4villnbpuq1.us-west-1.rds.amazonaws.com
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2125
Server version: 5.5.27-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

This looks quite good.  We are now ready to install and start replication.

Important note!  If you have trouble connecting you may need to tweak your Amazon security group settings to open up ports, especially if you are replicating from non-Amazon locations.  Amazon has a very cool feature that can guess your originating host IP and offer a CIDR address that covers the port range from which you are operating.  I used this when configuring my security groups.

Setting Up Tungsten Replication

It is possible to set up replication from a MySQL master directly to Amazon RDS using a single Tungsten Replicator process.  However, it is more versatile and simpler to set up two replicators:  one to read from the MySQL master, and another replicator to apply to the Amazon RDS slave.  I will therefore demonstrate this configuration.

We will assume you have a MySQL master already running and that it meets prerequisites for running Tungsten.  Let's now grab the Tungsten code and install a master replicator.  You can get fresh builds from the Tungsten Replicator builds page.

We will take a recent replicator build that contains the RDS changes, which are documented in Issue 425.  Use 2.0.7 build 177 or later. The main improvement is to add a non-privileged slave mode that avoids invoking any of the operations forbidden by Amazon.  Among other things Tungsten normally uses commands like 'SET SESSION SQL_LOG_BIN=0' to suppress writing to the binlog when it applies data on a slave.  This command requires SUPER privilege, hence causes problems for underprivileged RDS logins.

Unpack the code and install the master replicator in /opt/continuent.  This is no different from installing a normal Tungsten master.  My master host is logos1.  Here are sample commands to pull the code and set up the master.  The example shows the minimum options--if you have MySQL installed in a non-standard location or otherwise differ from a stock installation you may need to add additional options.

mkdir ~/staging
cd staging
wget --no-check-certificate https://s3.amazonaws.com/files.continuent.com/builds/nightly/tungsten-2.0-snapshots/tungsten-replicator-2.0.7-177.tar.gz
tar -xf tungsten-replicator-2.0.7-177.tar.gz
tungsten-replicator-2.0.7-177/tools/tungsten-installer \
  --master-slave  \
  --master-host=logos1 \
  --datasource-user=tungsten  \
  --datasource-password=your_passord  \
  --service-name=aws \
  --home-directory=/opt/continuent \
  --cluster-hosts=logos1 \
  --start-and-report

Next, set up the slave replicator.  For convenience I am going to install the slave on a separate host, named logos2, to avoid port clashes between the two replicators.  If you install on the same host, you'll need to install into another release directory location and use the --rmi-port and --thl-port options to avoid port overlaps.  Here is the command to set up the Amazon RDS slave.  Note that the tungsten-installer program can install code between hosts, which is an extremely useful feature.

tungsten-replicator-2.0.7-177/tools/tungsten-installer \
  --master-slave  \
  --cluster-hosts=logos2 \
  --master-host=logos1 \
  --datasource-host=test.c4villnbpuq1.us-west-1.rds.amazonaws.com \
  --datasource-user=tungsten  \
  --datasource-password=your_password  \
  --service-name=aws \
  --slave-privileged-updates=false \
  --home-directory=/opt/continuent \
  --skip-validation-check=InstallerMasterSlaveCheck \
  --skip-validation-check=MySQLPermissionsCheck \
  --start-and-report

You may see a few warnings during the RDS installation, as the tungsten-installer cannot verify some settings on the Amazon RDS host.  These can be ignored.  If everything goes well, you now have two replicators up and running.  You can check the status of the master and slave using the trepctl command, as in:

/opt/continuent/tungsten/tungsten-replicator/bin/trepctl -host logos1 status

/opt/continuent/tungsten/tungsten-replicator/bin/trepctl -host logos2 status

Both replicators should report that they are online.  Now complete the exercise by proving that replication works between the replicators.  We start by logging into the local MySQL instance, creating a new table in the test schema, and adding data.

$ mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 231488
Server version: 5.5.21-rel25.1-log Percona Server with XtraDB (GPL), Release rel25.1, Revision 234

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table foo(id int primary key);
Query OK, 0 rows affected (0.24 sec)

mysql> insert into foo values (256);
Query OK, 1 row affected (0.00 sec)

Now login to the Amazon RDS instance and look for table foo.  

$ mysql -utungsten -p -htest.c4villnbpuq1.us-west-1.rds.amazonaws.com test

Enter password: 

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2161

Server version: 5.5.27-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from foo;

+-----+

| id  |

+-----+

| 256 |

+-----+

1 row in set (0.01 sec)

Mission accomplished!  We have real-time replication enabled from a MySQL master to Amazon RDS.  At this point you can replicate more or less normally.  There are some obvious limitations due to the fact that Amazon RDS is locked down and does not grant our login full privileges.  

1. Temp table replication may not work.  Tungsten depends on being able to issue commands of the form "set @@session.pseudo_thread_id=23531" and the like.  This prevents clashes between temp tables of the same name on different master sessions.  You may need to enable row replication on the master, which suppresses temp table replication.  (For other approaches, see my previous article on temp tables and the binlog.) 
2. Any command that requires SUPER privilege will not work.  As an obvious example, you will not be able to grant SUPER privilege to new accounts.  Such commands will break replication. 
3. All replicated commands go into the binlog, which is a potential performance drag and may slow down Amazon RDS slaves.  Parallel replication may not help in this case, since committing to the binlog is a serialization point that blocks other transactions.  This problem may be cured if Amazon picks up group commit fixes from MySQL 5.6 and/or MariaDB.  

All things considered, however, these are minor inconveniences.  Most applications should be able to replicate without difficulties, especially if the master transaction rate is not too high.

Configuring SSL for Connections to RDS

In the previous demonstration I used a master host running outside Amazon.  This means my test transactions traveled across the Internet, where they were visible to all and sundry along the way.  To illustrate, we can run tcpdump and watch traffic as it goes by.

$ sudo tcpdump -A -vvv -s 256 host test.c4villnbpuq1.us-west-1.rds.amazonaws.com
...
logos2.46657 > ec2-54-241-56-140.us-west-1.compute.amazonaws.com.mysql: Flags [P.], cksum 0xb0f7 (incorrect -> 0x995d), seq 3012:3073, ack 3412, win 94, options [nop,nop,TS val 89630514 ecr 74223746], length 61
E..qw>@.@......n6.8..A..4..L...f...^.......
.W.2.l..9....insert into foo values (256) /* ___SERVICE___ = [aws] */
22:55:36.316049 IP (tos 0x8, ttl 51, id 26226, offset 0, flags [DF], proto TCP (6), length 63)

If we were handling confidential data, exposing traffic like this to possible evildoers would be a serious problem.  Fortunately, Amazon RDS supports SSL encrypted connections from clients.  Here is how to use it with Tungsten.

First, you need to get the Amazon RDS certificate, which is used to sign certificates for individual RDS instances.

mkdir /opt/continuent/certs
cd /opt/continuent/certs
wget https://rds.amazonaws.com/doc/mysql-ssl-ca-cert.pem

Next, you need to create a trust store that Java can access containing the certificates of signing authorities whom you trust.  For this you will need the Java keytool utility, which is included in the JDK.  If you are just using the Java runtime in production, you will need to generate the store on another host, then copy it over to your test hosts.  I used the password "secret" in this example.

$ keytool -import -alias rds -file mysql-ssl-ca-cert.pem -keystore truststore
Enter keystore password:  
Re-enter new password: 
Owner: CN=aws.amazon.com/rds/, OU=RDS, O=Amazon.com, L=Seattle, ST=Washington, C=US
Issuer: CN=aws.amazon.com/rds/, OU=RDS, O=Amazon.com, L=Seattle, ST=Washington, C=US
...
Trust this certificate? [no]:  yes
Certificate was added to keystore

We now need to tell the slave replicator about the truststore file using Java VM options.  On the slave host, edit /opt/continuent/tungsten/tungsten-replicator/conf/wrapper.conf and add the extra options shown in bold face.  

# Java Additional Parameters

wrapper.java.additional.1=-Dreplicator.home.dir=../../tungsten-replicator/

wrapper.java.additional.2=-Dreplicator.log.dir=../../tungsten-replicator/log

wrapper.java.additional.3=-Dcom.sun.management.jmxremote

wrapper.java.additional.4=-Djavax.net.ssl.trustStore=/opt/continuent/certs/truststore

wrapper.java.additional.5=-Djavax.net.ssl.trustStorePassword=secret

The last step is to enable SSL encryption when applying data.  We need to set an extra URL option on the drizzle JDBC driver to turn on SSL. For this we need to edit the static-svc.properties file that configures replication.  In my example this file is located in /opt/continuent/tungsten/tungsten-replicator/conf/static-aws.properties.  Open the file and look for the section that starts with APPLIERS. Edit the text to add additional urlOptions line as shown below.  

############

# APPLIERS #

############

replicator.applier.dbms=com.continuent.tungsten.replicator.applier.MySQLDrizzleApplier

replicator.applier.dbms.host=${replicator.global.db.host}

replicator.applier.dbms.port=${replicator.global.db.port}

replicator.applier.dbms.user=${replicator.global.db.user}

replicator.applier.dbms.password=${replicator.global.db.password}

replicator.applier.dbms.urlOptions=?useSSL=true

replicator.applier.dbms.ignoreSessionVars=autocommit

replicator.applier.dbms.getColumnMetadataFromDB=true

Restart the replicator process (/opt/continuent/tungsten/tungsten-replicator/bin/replicator restart) and you will now be using SSL encryption.  If we now look back at the tcpdump outout, it looks like garbage as the following example shows. 

...

23:46:52.370904 IP (tos 0x0, ttl 64, id 5899, offset 0, flags [DF], proto TCP (6), length 105)

    logos2.44717 > ec2-54-241-56-140.us-west-1.compute.amazonaws.com.mysql: Flags [P.], cksum 0xb0ef (incorrect -> 0xd834), seq 3087:3140, ack 4838, win 102, options [nop,nop,TS val 89938121 ecr 74992771], length 53

E..i..@.@.r....n6.8......zSR.b.....f.......

.\X..xL.....07..|..x.)...T..888.H/...iz...^.W8....'...... ..J

...

This is much better.  We are replicating to an Amazon RDS, and the transactions are safe from prying eyes.  If you have gotten this far you are ready to try your own applications.  

Benefits of Replication into Amazon RDS

Amazon RDS is convenient thanks to its quick and simple setup, but the lack of replication is a severe limitation in building systems that need more than a single MySQL instance.  In particular it makes it hard to integrate RDS into systems that consist of more than Amazon RDS itself.  Adding the ability to replicate in real-time into RDS therefore has a number of benefits.  The most obvious include using RDS to extend existing systems.

First, Amazon RDS can offer a quick way to add read capacity to existing MySQL applications.  This is especially useful if you have a cluster, such as Tungsten, which handles your transaction processing and overall HA.  You can now add Amazon RDS read slaves that you discard when no longer needed.  Tungsten Replicator has a number of other useful features like the ability to read from a group of nodes, not just one, that make such topologies easy to set up and maintain.  Clusters other than Tungsten will likewise benefit from this feature. 

Second, Amazon RDS is suitable for applications that do not need 24x7 high availability (limitations include slow failover, no online maintenance, no cross-cloud capabilities, etc.)  You can now pull data from other sources and send them to Amazon RDS slaves for processing, which amounts to extending overall processing capacity.  For example, you could use RDS to run back-office tasks using transactions replicated in from MySQL masters.  Tungsten Replicator also replicates data from Oracle, so this is an additional source of transactions.  

There are of course other ways to replicate data to and from RDS, for example using batch ETL tools like Talend.  However, these are not real-time and often require application changes to add timestamp columns or otherwise mark transactions that need to be extracted.  Log-based replication as implemented by Tungsten is fast and has minimal impact on applications or MySQL itself.  

Thoughts about Further Improvements for Amazon RDS Replication

On our side, i.e., at Continuent, we need to do more testing, add documentation, and fix problems as they arise.  We are starting a beta test with one of our customers in the next few days, who incidentally was the same customer who requested this feature in the first place after hacking it for themselves.  RDS also has some interesting provisioning capabilities that I would like to understand better.  We are also adding options that eliminate the need for manual configuration of security settings.  This will keep us busy for a while.

Other improvements depend on changes to RDS itself.  An obvious and huge improvement would be to permit replication out of Amazon RDS.  Unfortunately, Tungsten needs a login that has REPLICATION_SLAVE privilege so that we can download binlog data.  That privilege is not yet available to Amazon users.  Once it is, Tungsten extraction will also work in very short order. We actually don't need other commands like START/STOP SLAVE or FLUSH LOGS--just ability to issue a COM_BINLOG_EVENT from a client connection and receive binlog records.  I am sure other products would use this capability as well.  (RDS developers, if you are listening here's an easy way to extend your product usability significantly...)

Replication is such a valuable feature of MySQL that Amazon RDS feels somewhat crippled without it.  For this reason I would imagine that Amazon will open up additional capabilities in the future.  Until then we will polish up replication from MySQL masters to Amazon RDS slaves, awaiting a time when we can add more features.  

In the meantime, I hope you will try the new replication to Amazon RDS.  As noted in this article you can grab the latest builds and try it yourself.  Please report your experiences through either Continuent Support if you are a customer or the Tungsten discussion list if you use the open source Tungsten Replicator.  I look forward to your feedback and suggestions for making Amazon RDS support better. 

Tungsten University

We have started a new series of webinars at Continuent that we call Tungsten University.  They provide education on Tungsten clustering and replication in handy one-hour chunks.  These are not sales pitches.  Our goal is to provide accessible education about setting up and operating Tungsten without any marketing fluff.

The first Tungsten University webinar entitled "Configure & provision Tungsten clusters" will take place on Thursday January 17th at 10:00 PST.  It will show you how to set up a cluster in Amazon EC2.  There will be a repeat on January 22nd at 15:00 GMT.  We usually record webinars, so you can look at them later as well. 

You do not have to be a customer to attend these webinars, just interested in Tungsten.  I hope users of our open source Tungsten Replicator will attend, since we will have a number of presentations on replication.  Here are some of the future webinar topics we are considering: 

• Setting up, deploying, and upgrading Tungsten Replicator 
• Setting up multi-master and fan-in replication topologies
• Configuring Tungsten Connector for transparent SQL routing and load balancing
• Replication tips and tricks (such as how to improve performance and fix broken replicators)
• Implementing zero-downtime maintenance and schema upgrade
• Replicating between MySQL and Oracle
• Loading MySQL data into a Vertica data warehouse

There will be more titles out soon, so watch our webinar list and the announcements on the Continuent Tungsten blog.  If there are topics that you would like to hear about please suggest them as comments on this blog or the official Continuent blog.  

Meanwhile, I am looking forward to doing some of the replicator presentations and attending the talks on clustering.  I mostly write code for the replicator, so talks about other parts of Tungsten tend to be learning experiences.  The Tungsten Connector is particularly interesting because it makes off-the-shelf database replicas look like a single DBMS server and transparently switches connections between them.  It is our secret sauce for creating master/slave clusters.  If you have not seen Tungsten Connector  before, I recommend attending that talk when it comes up.  I'm still amazed how well it functions even after working with it for a number of years.  

Questions about MariaDB JDBC Driver

The recent release of the MariaDB client libraries has prompted questions about their purpose as well as provenance.  Colin Charles posted that some of these would be answered in the very near future.  I have a couple of specific questions about the MariaDB JDBC driver, which I hope will be addressed at that time.  

1.) What is really in the MariaDB JDBC driver and how exactly does it differ from the drizzle JDBC driver?  What, if any, relation is there to Connector/J code?  There is a JIRA project but it contains only four bugs, hence is not very informative.  The launchpad bzr history shows detailed check-ins but not overall intent. 

2.) Why relicense from BSD to LGPL?  I have checked the class headers and so far as attributions are concerned everything seems to be done quite properly.  However, the license change appears to prevent those of us currently using the drizzle JDBC driver from transferring code changes back to the drizzle driver.  If so, that seems a little unneighborly.  

Here is some background on the relationship between the drivers.  The MariaDB JDBC client is a fork of the BSD-licensed drizzle JDBC driver originally developed by Marcus Eriksson, who continues to maintain the code.  According to the bzr change history the code forked after rev 253, which was 24 April 2011.  There are still many similarities in the Java classes.  For instance, a number of classes in the org.mariadb.jdbc.internal.common package differ by little other than licensing headers and package names.  The MariaDB code is now up to rev 375 and includes substantial changes that appear to be designed to bring the MariaDB JDBC driver closer to the capabilities of the MySQL Connector/J driver.  

At Continuent we have a lively interest in the drizzle JDBC driver, as we adopted it for Tungsten Replicator some time ago.  The code had fewer bugs than Connector/J, which was attractive.  More importantly, Marcus kindly accepted a patch from my colleague Stephane Giron (working as a Continuent employee) that made it easy for us to send queries using binary data rather than the usual Unicode data required by the JDBC standard.  This fix allows Tungsten to replicate codesets and binary data correctly.  We have since contributed a few other patches.  Our modest contribution in part reflects the quality of the base code. 

While waiting for answers I would like to commend Marcus as well as other drizzle contributors for their work.  We are particularly indebted to Marcus for starting and continuing the drizzle JDBC project.  Tungsten Replicator users have applied many trillions of transactions using the drizzle driver.  If the MariaDB JDBC driver gains wide acceptance, the rest of the MySQL community owes Marcus Eriksson substantial thanks as well.  

The MySQL Community: Beleaguered or Better than Ever?

The  MariaDB Foundation announcement spawned some interesting commentary about the state of open source databases.  One recent headline cited the "beleaguered MySQL community." Beleaguered is a delightful adjective.  The OED tells us that it means beset, invested, or besieged.  Much as I like the word, I do not think it is an accurate or useful description of the MySQL community.  This article and others like it miss the point of what is happening to MySQL and its users.

Let's start by disproving that the notion that the MySQL community is beleaguered.  I don't know everyone who uses MySQL, but in my job I talk to numerous companies that have made sizable investments in MySQL and stand to lose big if they are wrong.  They do not seem especially nervous.

1.  Nobody seriously questions MySQL viability.  I have yet to meet a manager with a substantial business on MySQL who is deeply worried about it disappearing or being ruined by Oracle.  They are too busy working on software upgrades or keeping their sites running.  The future of MySQL is well down the list of problems keeping them awake at night.  

2.  MySQL meets or beats the immediate alternatives.  There is of course discussion about dropping MySQL for PostgreSQL but it is mostly idle talk.  I'm sure some companies have switched (actually in both directions), but I not seen a single customer migrate a working business app from MySQL to PostgreSQL.  Once you get past the religion, it's clear MySQL and PostgreSQL are just too similar to supplant each other easily:  reliable, row-based stores with single threaded SQL query engines that handle a few terabytes of data at most.  Companies need far stronger reasons to switch to something new, especially given the large ecosystem and deep pool of MySQL expertise. 

3. MySQL is not the only game in town.  Virtually every large web site I know uses at least one NoSQL store alongside MySQL.  Column stores are increasingly common for data warehouses.  Production Hadoop clusters are no longer a novelty.  On the surface this might look like a failure of MySQL.  What's really happening in many cases is that small businesses that started on MySQL are now large, profitable enterprises that require more than just economical OLTP.  This is a mark of success, not a deficiency.

If this is what beleaguered looks like I can't wait to see something that's actually successful.

Turning the argument around, can we say that the MySQL community is better than it was?  In at least one important way, yes.  The community is now multi-polar.  MySQL long benefitted from having a large community of open source users to find bugs, help focus development direction, and construct a wide range of robust tools like language bindings.  However, innovation on MySQL itself was largely gated by a single company:  MySQL AB.  Multiple groups are now competing to improve MySQL, and it's a very good thing for users.  Let me count the ways.

There are three major versions of MySQL:  Oracle, Percona, and MariaDB, not to mention cloud-only versions like Amazon RDS.  There are at least four companies working directly on major upgrades to replication:  Continuent, Oracle, Codership, and Monty Program.  Oracle is continuing to make improvements in InnoDB like online schema change and multi-core scaling, efforts that are complemented by Percona's persistent focus on all aspect of performance.  Aside from Amazon RDS, all of this work is available in open source, and there is an unusual degree of sharing across otherwise competitive groups.  I could keep going for a while but to be frank there's so much it's hard to track all the improvements or give them their proper due.

The MySQL community is therefore competitive in a way that did not exist a few years ago. That's good, because innovation in data management is no longer centered around the web-facing applications that MySQL helped enable. Businesses are grappling with massive data volumes that far exceed the capacity of single DBMS servers while simultaneously moving to Amazon or VMWare. There is a whole new set of problems such as deploying in unstable cloud environments, adjusting to polyglot persistence, managing sharded data effectively, distributing data across multiple regions, and enabling real-time analytics on MySQL transactions. As a group, the MySQL community is well-positioned to address them.

If there is a problem, it is how to keep a strong multi-polar community going for as long as possible.   Competition creates uncertainty for users, because change is a given.  Pointy-haired bosses have to make decisions with incomplete information or even reverse them later. Competition is hard for vendors, because it is more difficult to make money in efficient markets.  Competition even strikes against the vanity of community contributors, who have to try harder to get recognition.  It is clear there will be pressures to make the community less competitive.  They won't necessarily be from Oracle, which thrives on competition.

This gets back to the MariaDB Foundation reference that started this article. Anything that ensures long-term competitiveness and vitality of MySQL is good.   Foundations in general seem well suited to this task.  At Continuent we have already had some discussions about joining. So far we are undecided, for reasons that are somewhat similar to Peter Zaitsev's comments on this subject.  If the MariaDB Foundation helps maintain a stable multi-polar community, we're in.  

Slides from Percona Live London and a Request

Percona hosted another excellent Percona Live conference this past December 3-4 in London.  It was my pleasure to deliver 3 talks including the first keynote following Peter Zaitsev.  Percona does a great job of organizing these conferences--this year's London conference was well attended and in an excellent location in Kensington.  My thanks to the entire Percona team for putting this together.

Here are the slides for my talks in case you would like to see them.

Keynote:  Future-Proofing MySQL for the World-Wide Data Revolution -- Covering the greatly exaggerated death of MySQL and design patterns for robust MySQL systems that can last for decades

Talk:  Why, What, and How of Data Warehouses for MySQL -- Why you need a data warehouse for MySQL, some standard choices, and how to move data in real time from MySQL to Vertica.  There was even a demo of sysbench data replicating into Vertica automatically.

Talk:  Multi-Master, Multi-Site MySQL Databases Made Easy with Continuent Tungsten -- How to build clusters that span multiple sites using multi-master and primary/DR techniques.  I did this with Giuseppe Maxia, who did a couple of great demos along the way, including one that I found kind of terrifying to do in front of an audience.   (It involved killing a lot of database servers, something Giuseppe does for a living.)

Speaking of talks, there are many conferences with database tracks coming up in the next few months.  If you have not done a talk on MySQL before I would encourage you to think about submitting for upcoming conferences like future Percona Live events, OSCON, O'Reilly Strata, or one of the many  local meet-ups.  There are many people doing interesting things with open source databases.  It's great to hear your stories, so speak up!

Data Fabrics and Other Tales: Percona Live and MySQL Connect

The fall conference season is starting.  I will be doing a number of talks including a keynote on "future proofing" MySQL through the use of data fabrics.  Data fabrics allow you to build durable, long-lasting systems that take advantage of MySQL's strengths today but also evolve to solve future problems using fast-changing cloud and big data technologies.  The talk brings together ideas that Ed Archibald (our CTO) and I have been working on for over two decades.  I'm looking forward to rolling them out to a larger crowd.

Here are the talks in calendar order.  The first two are at MySQL Connect 2012 in San Francisco on September 30th:

• CON11591 - Managing Worldwide Data with MySQL and Continuent Tungsten (Tech talk)
• CON9319 - Replicating from MySQL to Oracle Database and Back Again (Tech talk)

MySQL Connect is an add-on to Oracle Open World.  You know the conference is big if they have to use 5-digit codes to keep track of talk titles.  It's almost worth the price of admission to look at Larry Ellison's boat.  Well maybe not quite, but you get the idea.

Next up is the Percona Live MySQL Conference in New York on October 2nd:

• Future-Proofing MySQL for the Worldwide Data Revolution (Keynote) 
• Solving Large-Scale Database Administration with Tungsten (Tech talk with Neil Armitage) 

Percona has been doing an amazing job of organizing conferences.  This will be my fifth one.  The previous four were great.  If you are in the New York area and like MySQL, sign up now.  This conference is the single best way to get up to speed on state-of-the-art MySQL usage.

Database Failure Is Not the Biggest Availability Problem

There have been a number of excellent articles about the pros and cons of automatic database failover triggered by Baron's post on the GitHub database outage.  In the spirit of Peter Zaitsev's article "The Math of Automated Failover," it seems like a good time to point out that database failure is usually not the biggest source of downtime for websites or indeed applications in general.  The real culprit is maintenance.

Here is a simple table showing availability numbers out to 5 nines and what they mean in terms of monthly down-time.

Uptime	Downtime per 30-Day Month
0.9	3 days
0.99	07:12:00
0.999	00:43:12
0.9999	00:04:20
0.99999	00:00:26

Now let's do some math.  We start with Peter's suggested number that the DBMS fails once a year.   Let's also say you take a while to wake up (because it's the middle of the night and you don't like automatic failover), figure out what happened, and run your failover procedure.  You are back online in an hour. Amortized over the year an hour of downtime is 5 minutes per month.  Overall availability is close to 99.99% or four nines.  

Five minutes per month is small potatoes compared to the time for planned maintenance.  Let's say you allow yourself a one-hour maintenance window each month for DBMS schema changes, database version upgrades, and other work that takes the DBMS fully offline from applications.  Real availability in this simple (and conservative) example is well below 99.9% or less than three nines. Maintenance accounts for over 90% of the downtime.   The real key to improved availability is to be able to maintain the DBMS without taking applications offline.  

We have been very focused on the maintenance problem in Tungsten.  Database replication is a good start for enabling rolling maintenance where you work on one replica at a time.  In Tungsten the magic sauce is an intervening connectivity layer that can transparently switch connections between DBMS servers while applications are running.  You can take DBMS servers offline and upgrade safely without bothering users.  Managing planned failover in this way is easier to solve than providing bombproof automatic failover, I am happy to say.  It is also considerably more valuable for many users. 

Automated Database Failover Is Weird but not Evil

Github had a recent outage due to malfunctioning automatic MySQL failover.  Having worked on this problem for several years I felt sympathy but not much need to comment.  Then Baron Schwartz wrote a short post entitled "Is automated failover the root of all evil?"  OK, that seems worth a comment:  it's not.  Great title, though.

Selecting automated database failover involves a trade-off between keeping your site up 24x7 and making things worse by having software do the thinking when humans are not around.  When comparing outcomes of wetware vs. software it is worth remembering that humans are not at their best when woken up at 3:30am.  Humans go on vacations, or their cell phones run out of power.  Humans can commit devastating unforced errors due to inexperience.  For these and other reasons, automated failover is the right choice for many sites even if it is not perfect. 

Speaking of perfection, it is common to hear claims that automated database failover can never be trusted (such as this example).  For the most part such claims apply to particular implementations, not database failover in general.  Even so, it is undoubtedly true that failover is complex and hard to get right.  Here is a short list of things I have learned about failover from working on Tungsten and how I learned them.  Tungsten clusters are master/slave, but you would probably derive similar lessons from most other types of clusters. 

1. Fail over once and only once.  Tungsten does so by electing a coordinator that makes decisions for the whole cluster.  There are other approaches, but you need an algorithm that is provably sound.  Good clusters stop when they cannot maintain the pre-conditions required for soundness, to which Baron's article alludes.  (We got this right more or less from the start through work on other systems and reading lots of books about distributed systems.) 

2. Do not fail over unless the DBMS is truly dead.  The single best criterion for failure seems to be whether the DBMS server will accept a TCP/IP connection.  Tests that look for higher brain function, such as running a SELECT, tend to generate false positives due to transient load problems like running out of connections or slow server responses.  Failing over due to load is very bad as it can take down the entire cluster in sequence as load shifts to the remaining hosts.  (We learned this through trial and error.) 

3. Stop if failover will not work or better yet don't even start.  For example, Tungsten will not fail over if it does not have up-to-date slaves available.  Tungsten will also try to get back to the original pre-failover state when failover fails, though that does not always work.  We get credit for trying, I suppose.  (We also learned this through trial and error.) 

4. Keep it simple.  People often ask why Tungsten does not resubmit transactions that are in-flight when a master failover occurs.  The reason is that there are many reasons why resubmission does not work on a new master and it is difficult to predict when such failures will occur.  Imagine you were dependent on a temp table, for example.  Resubmitting just creates more ways for failover to fail.  Tungsten therefore lets connections break and puts the responsibility on apps to retry failed transactions.  (We learned this from previous products that did not work.) 

Even if you start out with such principles firmly in mind, new failover mechanisms tend to encounter a lot of bugs.  They are hard to find and fix because failover is not easy to test.  Yet the real obstacle to getting automated failover right is not so much bugs but the unexpected nature of the problems clusters encounter.  There is a great quote from J.B.S. Haldane about the nature of the universe that also gives a flavor of the mind-bending nature of distributed programming: 

My own suspicion is that the universe is not only queerer than we suppose, but queerer than we can suppose. 

I can't count the number of times where something misbehaved in a way that would never have occurred to me without seeing it happen in a live system.  That is why mature clustering products can be pretty good while young ones, however well-designed, are not.  The problem space is just strange. 

My sympathy for the Github failures and everyone involved is therefore heartfelt.  Anyone who has worked on failover knows the guilt of failing to anticipate problems as well as as the sense of enlightenment that comes from understanding why they occur.  Automated failover is not evil.  But it is definitely weird. 

Life in the Amazon Jungle

In late 2011 I attended a lecture by John Wilkes on Google compute clusters, which link thousands of commodity computers into huge task processing systems.  At this scale hardware faults are common.  Google puts a lot of effort into making failures harmless by managing hardware efficiently and using fault-tolerant application programming models.  This is not just good for application up-time.  It also allows Google to operate on cheaper hardware with higher failure rates, hence offers a competitive advantage in data center operation.

It's becoming apparent we all have to think like Google to run applications successfully in the cloud.  At Continuent we run our IT and an increasing amount of QA and development on Amazon Web Services (AWS).   During the months of July and August 2012 at least 3 of our EC2 instances were decommissioned or otherwise degraded due to hardware problems.  One of the instances hosted our main website www.continuent.com.

In Amazon failures are common and may occur with no warning.  You have minimal ability to avoid them or some cases even understand the root causes.  To survive in this environment, applications need to obey a new law of the jungle.  Here are the rules as I understand them. 

First, build clusters of redundant services.  The www.continuent.com failure brought our site down for a couple of hours until we could switch to a backup instance.  Redundant means up and ready to handle traffic now, not after a bridge call to decide what to do.  We protect our MySQL servers by replicating data cross-region using Tungsten, but the website is an Apache server that runs on a separate EC2 instance.  Lesson learned.  Make everything a cluster and load balance traffic onto individual services so applications do not have to do anything special to connect.  

Second, make applications fault-tolerant.  Remote services can fail outright, respond very slowly, or hang.  To live through these problems apply time-honored methods to create loosely coupled systems that degrade gracefully during service outages and repair themselves automatically when service returns.  Here are some of my favorites.  

1. If your application has a feature that depends on a remote service and that service fails, turn off the feature but don't stop the whole application.  
2. Partition features so that your applications operate where possible on data copies.  Learn how to build caches that do not have distributed consistency problems.  
3. Substitute message queues for synchronous network calls.  
4. Set timeouts on network calls to prevent applications from hanging indefinitely.  In Java you usually do this by putting the calls in a separate thread.  
5. Use thread pools to limit calls to remote services so that your application does not explode when those services are unavailable or fail to respond quickly. 
6. Add auto-retry so that applications reconnect to services when they are available again. 
7. Add catch-all exception handling to deal with unexpected errors from failed services.  In Java this means catching RuntimeException or even Throwable to ensure it gets properly handled. 
8. Build in monitoring to report problems quickly and help you understand failures you have not previously seen.  

Third, revel in failure.  Netflix takes this to an extreme with Chaos Monkey, which introduces failures in running systems.  Another approach is to build scaffolding into applications so operations fail randomly.  We use that technique (among others) to test clusters.  In deployed database clusters I like to check regularly that any node can become the master and that you can recover any failed node.  However, this is just the beginning.  There are many, many ways that things can fail.  It is better to provoke the problems yourself than have them occur for the first time when something bad happens in Amazon.  

There is nothing new about these suggestions.  That said, the Netflix approach exposes the difference between cloud operation and traditional enterprise computing.  If you play the game applications will stay up 24x7 in this rougher landscape and you can tap into the flexible cost structure and rapid scaling of Amazon.  The shift feels similar to using database transactions or eliminating GOTO statements--just something we all need to do in order to build better systems.  There are big benefits to running in the cloud but you really need to step up your game to get them.  

Is Synchronous Data Replication over WAN Really a Viable Strategy?

Synchronous data replication over long distances has the sort of seductive appeal that often characterizes bad ideas.  Why wouldn't you want every local credit card transaction simultaneously stored on the other side of the planet far away from earthquake, storms and human foolishness?  The answer is simple: conventional SQL applications interact poorly with synchronous replication over wide area networks (WANs).

I spent a couple of years down the synchronous replication rabbit hole in an earlier Continuent product.  It was one of those experiences that make you a sadder but wiser person.  This article digs into some of the problems with synchronous replication and shows why another approach, asynchronous multi-master replication, is currently a better way to manage databases connected by long-haul networks.

Synchronous Replication between Sites

The most obvious problem with any form of synchronous replication is the hit on application performance.  Every commit requires a round-trip to transfer and acknowledge receipt at the remote site, which in turn reduces single-thread transaction rates to the number of pings per second between sites.  As a nice article by Aaron Brown demonstrates, you can show the effect easily using MySQL semi-synchronous replication between hosts in Amazon regions.  Aaron's experiment measured 11.5 transactions per second, or about 100 times less than single-thread performance on a local master between hosts with 85 millisecond latency.  At that rate you would theoretically expect transaction throughput of ~11.7 transactions per second (1000 / 85 = 11.7), so the agreement between practice and theory is very close.  It's great when science works out like this.

You might argue that applications could tolerate the slow rate assuming it were at least constant.  Sadly that's not the case for real systems.  Network response varies enormously between sites in ways that are quite easy to demonstrate.

To illustrate variability I set up an Amazon m1.small instance in the us-east-1 region (Virginia) and ran 24 hours of ping tests to instances in us-west-2 (Oregon) and ap-southeast-1 (Singapore).   As the following graph shows, during a 4 hour period ping times to Singapore remain within a band but vary up to 10%.  Ping times to Oregon on the other hand hover around 100ms but spike up randomly to almost 200ms.  During these times, synchronous replication throughput would be cut by 50% to approximately 5 transactions per second.

Amazon ping times from us-east-1 to us-west-2 and ap-southeast-1 (240 minute interval)

Moreover, it's not just a question of network traffic.  Remote VMs also become busy, which slows their response.  To demonstrate,  I ran two-minute sysbench CPU tests to saturate processing on the us-west-2 instance while observing the ping rate.  Here is the command:

sysbench --test=cpu --num-threads=10 --max-time=120 --cpu-max-prime=1000000 run

As the next graph illustrates, CPU load has a unpredictable but substantial effect on ping times.  As it happens, the ping variation in the previous graph may be due to resource contention on the underlying physical host.   (Or it might really be network traffic--you never really know with Amazon.)

Effect of sysbench runs on ping times to US-West (20 minute interval)

Slow response on resource-bound systems is a problem that is familiar to anyone with experience with distributed systems, including systems where everything is on a single LAN.  You cannot even count on clock ticks being delivered accurately within various types of virtual machines.  The timing delays are magnified in WANs, as they already have high latency to begin with.  Between busy hosts and network latency, it's reasonable to expect that at some point most systems would at least briefly experience single session transaction rates of 1 transaction per second or less.  Even with parallelized replication you would see substantial backups on the originating DBMS servers as commits begin to freeze.

To complete the tale of woe, failures of various kinds can cause remote hosts to stop responding at all for periods of time that vary from seconds to days.  Amazon is generally quite reliable but had two outages in the Virginia data center in June 2012 alone that brought down applications from hours to days.  If you replicate synchronously to a host affected by such an outage, your application just stops and you no longer store transactions at all, let alone securely.  You need to turn off synchronous replication completely to stay available.

So is synchronous replication really impossible between sites?  Given the problems I just described it would be silly to set up MySQL semi-synchronous replication between over WAN for a real application.  However, there are other ways to implement synchronous replication.  Let's look at two of them.

First, there is Galera, which uses a distributed protocol called certification-based replication to agree on commit order between all cluster nodes combined with execution of non-conflicting transactions in parallel.  Certification-based replication is a great algorithm in many ways, but Galera comes with some important practical limitations.  First it replicates rows rather than statements.  The row approach handles large transactions poorly, especially over distances, due to the large size of change sets.  Also, not all workloads parallelize well, since transactions that conflict in any way must be fully serialized.   Overall DBMS throughput may therefore reduce to the single-session throughput discussed above at unexpected times due to variations in workload.  Finally, full multi-master mode between sites (as opposed to master/slave) is likely to be very problematic as nodes drop out of the cluster due to transient communication failures and require expensive reprovisioning.  This is a general problem with group communications, which Galera depends on to order transactions.

Second, there are theoretical approaches that claim many of the benefits of synchronous replication without killing throughput or availability.  One example is the Calvin system developed by Daniel Abadi and others, which seeks to achieve both strong transaction consistency and high throughput when operating across sites.  The secret sauce in Calvin is that it radically changes the programming model to replicate what amount to transaction requests while forcing actual transaction processing to be under control of the Calvin transaction manager, which orders transaction order in advance across nodes.   That should at least in principle reduce some of the unpredictability you may see in systems like Galera that do not constrain transaction logic.  Unfortunately it also means a major rewrite for most existing applications.  Calvin is also quite speculative.  It will be some time before this approach is available for production systems and we can see whether it is widely applicable.

There's absolutely a place for synchronous replication in LANs, but given the current state of the art it's hard to see how most applications can use effectively it to link DBMS servers over WAN links.  In fact, the main issue with synchronous replication is the unpredictability it introduces into applications that must work with slow and unreliable networks.  This is one of the biggest lessons I have learned at Continuent.

The Alternative:  Asynchronous Multi-Master Replication

So what are the alternatives?  If you need to build applications that are available 24x7 with high throughput and rarely, if ever, lose data, you should consider high-speed local clusters linked by asynchronous multi-master replication between sites.  Here is a typical architecture, which is incidentally a standard design pattern for Tungsten.

Local clusters linked by asynchronous, multi-master replication

The big contrast between synchronous and and asynchronous replication between sites is that while both have downsides, you can minimize asynchronous multi-master problems using techniques that work now.  Let's look at how async multi-master meets requirements and the possible optimizations.

1. Performance. Asynchronous replication solves WAN performance problem as completely as possible.  To the extent that you use synchronous or near-synchronous replication technologies it is on local area networks, which are extremely fast and reliable, so application blocking is minimal.  Meanwhile, long-haul replication can be improved by compression as well as parallelization, because WANs offer good bandwidth even if there is high end-to-end latency. 
2. Data loss.  Speedy local replication, including synchronous and "near synchronous" methods, minimizes of data loss due to storage failures and configuration errors.   Somewhat surprisingly you do not need fully synchronous replication for most systems even at the local level--that's a topic for a future blog article--but replication does need to be quite fast to ensure local replicas are up-to-date.  Actually, one of the big issues for avoiding local data loss is to configure systems carefully (think sync_binlog=1 for MySQL, for example).  
3. Availability.  Async multi-master systems have the delightful property that anything interrupts transaction flow between sites, replication just stops and then resumes when the problem is corrected.  There's no failover and no moving parts.  This is a major strength of the multi-master model.  

So what are the downsides?  Nothing comes for free, and there are at least two obvious issues.

1. Applicability.  Not every application is compatible with asychronous multi-master.  You will need to do work on most existing applications to implement multi-master and ensure you got it right.  I touched on some of the MySQL issues in an earlier article.  If multi-master is not a possibility, you may need the other main approach to cross-site replication:  a system-of-record design where applications update data on a single active site at any given time with other sites present for disaster recovery.  (Tungsten also does this quite well, I should add.) 
2. Data access.  While you might not lose data it's also quite likely you might not be able to access it for a while either.  It's rare to lose a site completely but not uncommon for sites to be inaccessible for hours to days.  The nice thing is that with a properly constructed multi-master application you will at least know that the data will materialize on all sites once the problem is solved.  Meanwhile, relax and work on something else until the unavailable site comes back. 

In the MySQL community local clusters linked by asynchronous multi-master are an increasingly common architecture for credit card payment gateways, which I mentioned at the beginning of this article.  This is a telling point in favor of asynchronous cross-site replication, as credit card processors have a low tolerance for lost data.

Also, a great deal of current innovation in distributed data management is headed in the direction of asynchronous mechanisms.  NoSQL systems (such as Cassandra) tend to use asynchronous replication between sites.  There is interesting research afoot, for example in Joe Hellerstein's group at UC Berkeley, to make asynchronous replication more efficient by accurately inferring cases where no synchronization is necessary.  Like other research, this work is quite speculative, but the foundations are in use in operational systems today.  

For now the challenge is to make the same mechanisms that NoSQL systems have jumped on work equally well for relational databases like MySQL.  We have been working on this problem for the last couple of years at Continuent.  I am confident we are well on the way to solutions that are as good as the best NoSQL offerings for distributed data management.