An Ending and a Beginning: VMware Has Acquired Continuent

As of today, Continuent is part of VMware. We are absolutely over the moon about it.

You can read more about the news on the VMware vCloud blog by Ajay Patel, our new boss. There’s also an official post on our Continuent company blog. In a nutshell the Continuent team is joining the VMware Cloud Services Division. We will continue to improve, sell, and support our Tungsten products and work on innovative integration into VMware’s product line.

So why do I feel exhilarated about joining VMware? There are three reasons. 

1.     Continuent is joining a world-class company that is the leader in virtualization and cloud infrastructure solutions. Even better, VMware understands the value of data to businesses. They share our vision of managing an integrated fabric of standard DBMS platforms, both in public clouds as well as in local data centers. It is a great home to advance our work for many years to come.

2.     We can continue to support our existing users and make Tungsten even better. I know many of you have made big decisions to adopt Continuent technology that would affect your careers if they turned out badly. We now have more resources and a mandate to grow our product line. We will be able to uphold our commitments to you and your businesses.

3.     It’s a great outcome for our team, which has worked for many years to make Continuent Tungsten technology successful. This includes our investors at Aura in Helsinki, who have been dogged in their support throughout our journey.

Speaking of the Continuent team…I am so proud of what all of you have achieved. Today we are starting a new chapter in our work together. See you at VMware!

Exorcising the CAP Demon

Computer science is like an enormous tool box you can rummage through whenever you have a problem to solve. Most of the tools are sturdy and practical, like algorithms for B-trees. Some are also elegant, like consistent hashing in Dynamo. Finally there are some tools that you never quite figure out even after years of reflection. That piece of steel you are looking at could be Excalibur. Or it could be a rusty knife.

The CAP theorem falls into the last category, at least for me.  It was a major topic in the blogosphere a few years ago and Google Trends shows steadily increasing interest in the term since 2010.  It's not my goal to explain CAP fully--a good informal description is here or you can just read the proof yourself.  Instead I would like to talk about how I understand and use the CAP theorem today as well as how that understanding might evolve in the future.

In a nutshell CAP puts a limit on how distributed database systems trade off data consistency and system availability.   Eric Brewer originated the theorem as a conjecture in the late 1990s. Seth Gilbert and Nancy Lynch supplied a proof of the conjecture in 2002.  Brewer described it as follows in 2012:

The CAP theorem states that any networked shared-data system can have at most two of three desirable properties:

• consistency (C) equivalent to having a single up-to-date copy of the data;

• high availability (A) of that data (for updates); and

• tolerance to network partitions (P).

My initial problem in understanding CAP was relating the proof to what happens in the real world, which is not especially easy. Network partitions are an example.  Here's how the Gilbert/Lynch proof defines them in Section 2.3.

When a network is partitioned, all messages sent from nodes in one component of the partition to nodes in another component are lost. (And any pattern of message loss can be modeled as a temporary partition separating the communicating nodes at the exact instant the message is lost.)

So does this include an asymmetric communication failure? That's where a process on one host can see and send messages to a process on another host but the reverse is not true. This happens all the time in group communications for reasons that range from application software bugs to bad cabling and everything in between. Do you model the asymmetry as a sequence of temporary partitions? It's of course possible. But it feels a bit like using Ptolemaic astronomy with epicycles. 

Other people have made similar observations. Eric Brewer even wrote about the "nuances" of partitions in his 2012 retrospective. There are analogous problems with the other terms. There was enough public disagreement their meaning that I wrote a "disproof" of CAP a few years back as an April Fools Day joke. It depended on not being able to distinguish CA and CP choices in real systems. 

That confusion is not a problem with the CAP theorem itself. Nobody has seriously challenged the proof. Instead, it's a matter of what logicians refer to as interpretation, which links a logical model to some domain of discourse so that you can draw valid conclusions about that domain. If you want to reason about real-world systems using the CAP theorem you must first ensure your systems really match the model. Otherwise it's like using a map of Oregon to drive between New York and Boston.  The core difficulty is that the CAP theorem proof assumes binary properties whereas in reality properties like availability operate on a sliding scale. 

My other issue with CAP evaluation is what you might call a suitability problem. There are a lot of issues with operating distributed systems, and the 3-way trade-off is irrelevant to many of them. For instance, what happens when the network is behaving and you don't have to make pesky choices between availability and consistency? Let's look at some examples. 

CAP defines consistency as linearizability, which means that transactions on different replicas look as if they all happened at once in a single place in a single unbroken series. Imagine driving around to different automated teller machines at a bank and making changes to your account balance or checking it. No matter which teller machine you visit next, it knows exactly what happened before and has the right balance amount. Or imagine a shopping cart on a website like Zappos.com. No matter how you jump around the website to select clothing or even if you fold up your laptop and fly to Paris, the items in your shopping care remain consistent without duplicate or missing selections. 

You might say, well, not all systems work that way.  You would be right, and that's the exactly the point.  Real distributed systems do not always try to ensure linearizability. It turns out that many people, most particularly end users who ultimately pay for computer systems, conclude they don't really care so much about consistency of the sort CAP promises.  Here are two different types of reasons: 

1. Linearized consistency is expensive. Keeping active replicas up to date requires round trip messages between hosts, which can reduce transaction commit times by an order of magnitude or more. Users are allergic to slow response, regardless of any other benefits that slowness might bring.  Daniel Abadi pointed out this latency problem some time ago in a great blog post on CAP that is still excellent reading today.  

2. Linearized consistency is irrelevant for many applications. Consider a measurement from a household thermometer or a text message from a cell phone.  There is only one of each generated in a single location. Your servers either get them or they don't.  Multiple copies are just that: replicas of the same thing. Conflicts don't exist. 

The share of immutable data from analytic systems like Hadoop and object stores like Amazon S3 is increasing rapidly, which means that there is an increasing number of applications for which CAP is not the only or even a major design consideration. It might be in the guts of the system but it's just one of many problems at that level and there may be multiple choices. The original Hadoop architecture actually ignored CAP for one critical part of the system--the NameNode, which maps HDFS file names to storage, was a single point of failure.

Which brings us back to understanding CAP at a practical level.  Is it Excalibur or just the rusty knife?  At this point it feels like another tool in the toolbox that you use at the right time, albeit carefully. Imagine a band saw that does not have a very good guard on the blade. Here are my personal instructions for safe use. 

1. Use it for suitable problems.  The CAP theorem applies to a very specific problem involving systems that want to remain consistent and available across multiple networked hosts.  If you design clusters or distributed databases, this is a relatively big deal. The trade-offs are real and you have to think about them. 

For instance at Continuent we have some problems where the theorem is directly applicable. We build clusters that implement failover.  We have to consider how to establish consensus while keeping the cluster available even when members lose messages or respond slowly. The CAP theorem guides you to manage this kind of problem rather than try to solve it using techniques that will not work, such as adding timeouts on messages.  (Continuent Tungsten clusters are generally CP, in case you  are wondering.) 

2. Avoid CAP where it does not obviously apply. It is a tricky theorem to interpret correctly, and many applications are concerned with unrelated problems. I work a lot on transactional replication. There are no CAP issues in Tungsten Replicator.  At the other end of the spectrum if you build systems that link multiple stores using replication, you likely have multiple CAP choices under the covers.  That's a common pattern in complex applications. 

It is therefore important to look with a jaundiced eye upon any product that claims to "beat CAP," like this widely read article. This is just marketing hype. If your application matches the CAP theorem model, it applies and you are subject to the limitations. If the limitations don't seem to make sense you have not evaded them. You are either working on a problem to which CAP is not relevant or you made implicit CAP choices of which you are not aware. It is easy to make a fool of yourself by asserting otherwise. 

3. Other tools are important too.  CAP of course does not even cover all trade-offs in clusters.  There are also many issues to consider when building distributed data systems that actually work.  Latency, durability of data, monitoring, automation, reliability, ability to do zero-downtime maintenance, and security are critical. Especially security. That looks like the next big problem for a lot of existing distributed systems. 

Beyond these, don't stop thinking about CAP. It is one of those ideas that gets under your skin and really bugs you. In addition to Eric Brewer's 2012 article, Seth Gilbert and Nancy Lynch wrote a follow-up perspective on the implications of CAP, so even the originators are continuing to consider the problems. The long term value of CAP is that it has focused attention on a set of difficult data management problems and led to numerous productive ideas about how to manage them. The resulting evolution is not nearly finished.  We will all continue to worry this bone for many years to come. 

No Hadoop Fun for Me at SCaLE 12X :(

I blogged a couple of weeks ago about my upcoming MySQL/Hadoop talk at SCaLE 12X. Unfortunately I had to cancel. A few days after writing the article I came down with an eye problem that is fixed but prevents me from flying anywhere for a few weeks. That's a pity as I was definitely looking forward to attending the conference and explaining how Tungsten replicates transactions from MySQL into HDFS.

Meanwhile, we are still moving at full steam with Hadoop-related work at Continuent, which is the basis for the next major replication release, Tungsten Replicator 3.0.0. Binary builds and documentation will go up in a few days. There will also be many more public talks about Hadoop support, starting in April at Percona Live 2014. I hope you'll consider attending one of our talks there.  It's a great conference.

Since my SCaLE 12X talk won't be happening I would like to repeat the invitation to attend the Continuent webinar on loading from MySQL to Hadoop on Thursday February 27th.  It's essentially the same talk, but no airplanes are involved.

Fun with MySQL and Hadoop at SCaLE 12X

It's my pleasure to be presenting at SCaLE 12X on the subject of real-time data loading from MySQL to Hadoop.  This is the first public talk on work at Continuent that enables Tungsten Replicator to move transactions from MySQL to HDFS (Hadoop Distributed File System).  I will explain how replication to Hadoop works, how to set it up, and offer a few words on constructing views of MySQL data using tools like Hive.

As usual with replication everything we are doing on Hadoop replication is open source.  Builds and documentation will be publicly available by the 21st of February, which is when the talk happens.  Hadoop support is already in testing with Continuent customers, and we have confidence that we can handle basic loading cases already.  That said, Hadoop is a complex beast with lots of use cases, and we need feedback from the community on how to make Tungsten loading support better.  My colleagues and I plan to do a lot of talks about Hadoop to help community users get up to speed.

Here is a tiny taste of what MySQL to Hadoop loading looks like.  Most MySQL users are familiar with sysbench.  Have you ever wondered what sysbench tables would look like in Hadoop?  Let's use the following sysbench command to apply transactions to table db01.sbtest:

sysbench --test=oltp --db-driver=mysql --mysql-host=logos1 --mysql-db=db01 \
    --mysql-user=tungsten --mysql-password=secret \
    --oltp-read-only=off --oltp-table-size=10000 \
    --oltp-index-updates=4 --oltp-non-index-updates=2 --max-requests=200000 \
    --max-time=900 --num-threads=5 run

This results in rows that look like the following in MySQL:

mysql> select * from sbtest where id = 2841\G
*************************** 1. row ***************************
 id: 2841
  k: 2
  c: 958856489-674262868-320369638-679749255-923517023-47082008-646125665-898439458-1027227482-602181769
pad: qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt

After replication into Hadoop with Tungsten, we can crunch the log records using a couple of HiveQL queries to generate a point-in-time snapshot of the sbtest table on HDFS.  By a point-in-time snapshot, I mean that a table that contains not only inserted data but also shows the results of subsequent update and delete operations on each row up to a particular point in time.  We can now run the same query to see the data: 

hive> select * from sbtest where id = 2841;
Total MapReduce jobs = 1
Launching Job 1 out of 1
...
Job 0: Map: 1   Cumulative CPU: 0.74 sec   HDFS Read: 901196 HDFS Write: 158 SUCCESS
Total MapReduce CPU Time Spent: 740 msec
OK
2841 2 958856489-674262868-320369638-679749255-923517023-47082008-646125665-898439458-1027227482-602181769 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt

Tungsten does a lot more than just move transaction data, of course.  It also provides tools to generate Hive schema, performs transformations on columns to make them match the limited HiveQL datatypes, and arranges data in a way that allows you generate materialized views for analytic usage (like the preceding example) with minimal difficulty.

If you want to learn more about how Tungsten does all of this magic, please attend the talk.  I hope to see you in Los Angeles.

p.s., If you cannot attend SCaLE 12X, we will have a Continuent webinar on the same subject the following week.  (Sign up here.)

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.  

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.

MySQL to Vertica Replication, Part 2: Setup and Operation

As described in the first article of this series, Tungsten Replicator can replicate data from MySQL to Vertica in real-time.  We use a new batch loading feature that applies transactions to data warehouses in very large blocks using COPY or LOAD DATA INFILE commands.  This second and concluding article walks through the details of setting up and testing MySQL to Vertica replication.

To keep the article reasonably short, I assume that readers are conversant with MySQL, Tungsten, and Vertica.  Basic replication setup is not hard if you follow all the steps described here, but of course there are variations in every setup.  For more information on Tungsten check out the Tungsten Replicator project at code.google.com site well as current Tungsten commercial documentation at Continuent. 

Now let's get replication up and running!  

What Is the Topology? 

In this exercise we will set up Tungsten master/slave replication to move data from MySQL 5.1 to Vertical 5.1.  Master/slave is the simplest topology to set up because you don't have to mix settings for different DBMS types in each service.  To keep keep things simple, we will install Tungsten directly on the MySQL and Vertica hosts, which are named db1 and db2 respectively.  Here is a diagram:

There are of course many other possible configurations.  You can run replicators on separate hosts to reduce load on the DBMS servers.  You can with a little patience set up direct replication using a Tungsten single replication service, which results in fewer processes to manage.  Finally, you can use both direct as well as master/slave topologies to publish data from Tungsten 1.5 clusters.  Tungsten clusters provide availability and scaling on the MySQL side.

MySQL to Vertica Replication, Part 1: Enabling Real-Time Analytics with Tungsten

Real-time analytics allow companies to react rapidly to changing business conditions.   Online ad services process click-through data to maximize ad impressions.  Retailers analyze sales patterns to identify micro-trends and move inventory to meet them.  The common theme is speed: moving lots of information without delay from operational systems to fast data warehouses that can feed reports back to users as quickly as possible.

Real-time data publishing is a classic example of a big data replication problem.  In this two-part article I will describe recent work on Tungsten Replicator to move data out of MySQL into Vertica at high speed with minimal load on DBMS servers.  This feature is known as batch loading.  Batch loading enables not only real-time analytics but also any other application that depends on moving data efficiently from MySQL into a data warehouse.

The first article works through the overall solution starting with replication problems for real-time analytics through a description of how Tungsten adapts real-time replication to data warehouses.  If you are in a hurry to set up, just skim this article and jump straight to the implementation details in the follow-on article.

Replication Challenges for Real-Time Analytics

To understand some of the difficulties of replicating to a data warehouse, imagine a hosted intrusion detection service that collects access log data from across the web and generates security alerts as well as threat assessments for users.  The architecture for this application follows a pattern that is increasingly common in businesses that have to analyze large quantities of incoming data.  

Access log entries arrive through data feeds, whereupon an application server checks them to look for suspicious activity and commits results into a front-end DBMS tier of sharded MySQL servers.  The front-end tier optimizes for a MySQL sweet spot, namely fast processing of a lot of small transactions.

If You *Must* Deploy Multi-Master Replication, Read This First

An increasing number of organizations run applications that depend on MySQL multi-master replication between remote sites.   I have worked on several such implementations recently.  This article summarizes the lessons from those experiences that seem most useful when deploying multi-master on existing as well as new applications.

Let's start by defining terms.  Multi-master replication means that applications update the same tables on different masters, and the changes replicate automatically between those masters.  Remote sites mean that the masters are separated by a wide area network (WAN), which implies high average network latency of 100ms or more.  WAN network latency is also characterized by a long tail, ranging from seconds due to congestion to hours or even days if a ship runs over the wrong undersea cable.

With the definitions in mind we can proceed to the lessons.  The list is not exhaustive but includes a few insights that may not be obvious if you are new to multi-master topologies.  Also, I have omitted issues like monitoring replication, using InnoDB to make slaves crash-safe, or provisioning new nodes.  If you use master/slave replication, you are likely familiar with these topics already.

1. Use the Right Replication Technology and Configure It Properly

The best overall tool for MySQL multi-master replication between sites is Tungsten.  The main reason for this assertion is that Tungsten uses a flexible, asynchronous, point-to-point, master/slave replication model that handles a wide variety of topologies such as star replication or all-to-all.  Even so, you have to configure Tungsten properly.  The following topology is currently my favorite:

• All-to-all topology.  Each master replicates directly to every other master.  This handles prolonged network outages or replication failures well, because one or more masters can drop out without breaking  replication between the remaining masters or requiring reconfiguration.  When the broken master(s) return, replication just resumes on all sides.  All-to-all does not work well if you have a large number of masters.  
• Updates are not logged on slaves.  This keeps master binlogs simple, which is helpful for debugging, and eliminates the possibility of loops.  It also requires some extra configuration if the masters have their own slaves, as would be the case in a Tungsten Enterprise cluster. 

There are many ways to set up multi-master replication replication, and the right choice varies according to the number of masters, whether you have local clustering, or other considerations.  Giuseppe Maxia has described many topologies, for example here, and the Tungsten Cookbook has even more details.

One approach you should approach with special caution is MySQL circular replication.  In topologies of three or more nodes, circular replication results in broken systems if one of the masters fails.  Also, you should be wary of any kind of synchronous multi-master replication across sites that are separated by more than 50 kilometers (i.e. 1-2ms latency).  Synchronous replication makes a siren-like promise of consistency but the price you pay is slow performance under normal conditions and broken replication when WAN links go down.

2. Use Row-Based Replication to Avoid Data Drift

Replication depends on deterministic updates--a transaction that changes 10 rows on the original master should change exactly the same rows when it executes against a replica.  Unfortunately many SQL statements that are deterministic in master/slave replication are non-deterministic in multi-master topologies.  Consider the following example, which gives a 10% raise to employees in department #35.

   UPDATE emp SET salary = salary * 1.1 WHERE dep_id = 35;

If all masters add employees, then the number of employees who actually get the raise will vary depending on whether such additions have replicated to all masters.  Your servers will very likely become inconsistent with statement replication.  The fix is to enable row-based replication using binlog-format=row in my.cnf.  Row replication transfers the exact row updates from each master to the others and eliminates ambiguity.

3. Prevent Key Collisions on INSERTs

For applications that use auto-increment keys, MySQL offers a useful trick to ensure that such keys do not  collide between masters using the auto-increment-increment and auto-increment-offset parameters in my.cnf.  The following example ensures that auto-increment keys start at 1 and increment by 4 to give values like 1, 5, 9, etc. on this server.

server-id=1
auto-increment-offset = 1

auto-increment-increment = 4

This works so long as your applications use auto-increment keys faithfully.  However, any table that either does not have a primary key or where the key is not an auto-increment field is suspect.  You need to hunt them down and ensure the application generates a proper key that does not collide across masters, for example using UUIDs or by putting the server ID into the key.   Here is a query on the MySQL information schema to help locate tables that do not have an auto-increment primary key. 

SELECT t.table_schema, t.table_name 

  FROM information_schema.tables t 

    WHERE NOT EXISTS 

      (SELECT * FROM information_schema.columns c

       WHERE t.table_schema = c.table_schema  

         AND t.table_name = c.table_name

         AND c.column_key = 'PRI'

         AND c.extra = 'auto_increment')

4. Beware of Semantic Conflicts in Applications

Neither Tungsten nor MySQL native replication can resolve conflicts, though we are starting to design this capability for Tungsten.  You need to avoid them in your applications.  Here are a few tips as you go about this.

First, avoid obvious conflicts.  These include inserting data with the same keys on different masters (described above), updating rows in two places at once, or deleting rows that are updated elsewhere.  Any of these can cause errors that will break replication or cause your masters to become out of sync.  The good news is that many of these problems are not hard to detect and eliminate using properly formatted transactions.  The bad news is that these are the easy conflicts.  There are others that are much harder to address.  

For example, accounting systems need to generate unbroken sequences of numbers for invoices.  A common approach is to use a table that holds the next invoice number and increment it in the same transaction that creates a new invoice.  Another accounting example is reports that need to read the value of accounts consistently, for example at monthly close.  Neither example works off-the-shelf in a multi-master system with asynchronous replication, as they both require some form of synchronization to ensure global consistency across masters.  These and other such cases may force substantial application changes.  Some applications simply do not work with multi-master topologies for this reason. 

5. Remove Triggers or Make Them Harmless

Triggers are a bane of replication.  They conflict with row replication if they run by accident on the slave.  They can also create strange conflicts due to weird behavior/bugs (like this) or other problems like needing definer accounts present.  MySQL native replication turns triggers off on slaves when using row replication, which is a very nice feature that prevents a lot of problems.  

Tungsten on the other hand cannot suppress slave-side triggers.  You must instead alter each trigger to add an IF statement that prevents the trigger from running on the slave.  The technique is described in the Tungsten Cookbook.  It is actually quite flexible and has some advantages for cleaning up data because you can also suppress trigger execution on the master.  

You should regard all triggers with suspicion when moving to multi-master.  If you cannot eliminate triggers, at least find them, look at them carefully to ensure they do not generate conflicts, and test them very thoroughly before deployment.  Here's a query to help you hunt them down: 

SELECT trigger_schema, trigger_name 

  FROM information_schema.triggers;

6. Have a Plan for Sorting Out Mixed Up Data

Master/slave replication has its discontents, but at least sorting out messed up replicas is simple: re-provision from another slave or the master.  No so with multi-master topologies--you can easily get into a situation where all masters have transactions you need to preserve and the only way to sort things out is to track down differences and update masters directly.   Here are some thoughts on how to do this.

1. Ensure you have tools to detect inconsistencies.  Tungsten has built-in consistency checking with the 'trepctl check' command.  You can also use the Percona Toolkit pt-table-checksum to find differences.  Be forewarned that neither of these works especially well on large tables and may give false results if more than one master is active when you run them.  
2. Consider relaxing foreign key constraints.  I love foreign keys because they keep data in sync.  However, they can also create problems for fixing messed up data, because the constraints may break replication or make it difficult to go table-by-table when synchronizing across masters.  There is an argument for being a little more relaxed in multi-master settings. 
3. Switch masters off if possible.  Fixing problems is a lot easier if you can quiesce applications on all but one master.  
4. Know how to fix data.  Being handy with SQL is very helpful for fixing up problems.  I find SELECT INTO OUTFILE and LOAD DATA INFILE quite handy for moving changes between masters.  Don't forget SET SESSION LOG_FILE_BIN=0 to keep changes from being logged and breaking replication elsewhere.  There are also various synchronization tools like pt-table-sync, but I do not know enough about them to make recommendations.  

At this point it's probably worth mentioning commercial support.  Unless you are a replication guru, it is very comforting to have somebody to call when you are dealing with messed up masters.  Even better, expert advice early on can help you avoid problems in the first place.

(Disclaimer:  My company sells support for Tungsten so I'm not unbiased.  That said, commercial outfits really earn their keep on problems like this.)

7. Test Everything

Cutting corners on testing for multi-master can really hurt.  This article has described a lot of things to look for, so put together a test plan and check for them.  Here are a few tips on procedure:

1. Set up a realistic pre-prod test with production data snapshots.  
2. Have a way to reset your test environment quickly from a single master, so you can get back to a consistent state to restart testing. 
3. Run tests on all masters, not just one.  You never know if things are properly configured everywhere until you try. 
4. Check data consistency after tests.  Quiesce your applications and run a consistency check to compare tables across masters. 

It is tempting to take shortcuts or slack off, so you'll need to find ways to improve your motivation.  If it helps, picture yourself explaining to the people you work for why your DBMS servers have conflicting data with broken replication, and the problem is getting worse because you cannot take applications offline to fix things.  It is a lot easier to ask for more time to test.  An even better approach is to hire great QA people and give them time to do the job right.

Summary

Before moving to a multi-master replication topology you should ask yourself whether the trouble is justified.  You can get many of the benefits of multi-master with system-of-record architectures with a lot less heartburn.  That said, an increasing number of applications do require full multi-master across multiple sites.  If you operate one of them, I hope this article is helpful in getting you deployed or improving what you already have.

Tungsten does a pretty good job of multi-master replication already, but I am optimistic we can make it much better.  There is a wealth of obvious features around conflict resolution, data repair, and up-front detection of problems that will make life better for Tungsten users and reduce our support load.  Plus I believe we can make it easier for developers to write applications that run on multi-master DBMS topologies.  You will see more about how we do this in future articles on this blog.

Replication Is Bad for MySQL Temp Tables

Experienced MySQL DBAs know that temp tables cause major problems for MySQL replication.  It turns out the converse is also true:  replication can cause major problems for temporary tables.

In a recent customer engagement we enabled Tungsten Replicator with a MySQL application that originally ran on a server that did not use replication.  QA promptly discovered reports that previously ran in 10 seconds were now running in as many minutes.  It turned out that the reports used temp tables to assemble data, and these were being written into the master binlog.  This created bloated binlogs and extremely slow reports.  We fixed the problem by enabling row replication (i.e., binlog-format=row in my.cnf).

A common DBA response to temp table problems is to try to eliminate them completely, as suggested in the excellent High Performance MySQL, 3rd Edition. (See p. 502.)  Elimination is a good philosophy when applications use temp tables to generate updates.  However, it does not work for reporting.  Temp tables allow you to stage data for complex reports across a series of transactions, then pull the final results into a report writer like JasperReports.  This modular approach is easy to implement and maintain afterwards.  Eliminating temp tables in such cases can create an unmaintainable mess.

The real solution with report temp tables is to keep them out of the master binlog.  Here is a list of common ways to do so.  Let me know if you know others.

* Turn off binlog updates.  Issue 'SET SESSION SQL_LOG_BIN=0' when generating reports.  The downside is that it requires SUPER privilege to set.  Also, if you make a code mistake and update normal tables with this setting, your changes will not be replicated.

* Use a non-replicated database.  Configure the master my.cnf with binlog-ignore-db as follows to ignore any update (including on temp tables) that is issued when database 'scratch' is the default database:

binlog_ignore_db = scratch

This approach does not require special privileges.  However coding errors or connection pool misconfigurations are obvious liabilities.  Your application must either connect to the scratch database or issue an explicit use command. Otherwise, temp table operations will be logged, as in the following example:

use not_scratch;

create temporary table scratch.report1_temp(name varchar(256), entry_time date, exit_time date);

* Use a slave with the binlog disabled.  Remove the log-bin option from my.cnf.  This works well if you have extra reporting slaves that are caught up.  However, it may not work if the reports must be fully up-to-date or you need the ability to promote the slave quickly to a master, in which case the binlog must be enabled.  

* Use row replication.  You can set row replication at the session level using 'SET SESSION binlog_format=row', which requires SUPER privilege, or overall by setting binlog-format in my.cnf.  In this case CREATE TEMPORARY TABLE and updates on temp tables do not appear in the binlog at all.  The downside of enabling row replication fully is that it can lead to bloated logs and blocked servers if you have very large transactions.  SQL operations like DELETE that affect multiple rows are stored far more compactly in statement replication.  Also, reloading mysqldump files can be very slow in row replication compared to statement replication, which can handle block inserts generated by the --extended-insert option.

The proper solution to keep replication from hurting your use of temp tables will vary depending on your application as well as the way you run your site.  For my money, though, this is a good example of where row replication really helps and deserves a closer look.  

MySQL could use some feature improvements in the area of temp tables and replication.  I find it surprising that mixed mode replication does not fully suppress temp table binlog updates.  Only row replication does so.   Second, it would be great to have a CREATE TABLE option to suppress logging particular tables to the binlog.  This would allow applications to make the logging decision at schema design time.  Finally, global options to suppress binlogging of specific table types, such as temp tables and MEMORY tables would be useful.  Perhaps we will see some of these in future MySQL releases.