The Scale-Out Blog

Tungsten on the Beach--LA MySQL Meetup on Jan 11, 2012

2012-01-06T15:43:00.000-08:00

It is my pleasure to announce that I will be presenting on Tungsten Replicator next Wednesday, January 11th at the Los Angeles MySQL Meetup. The presentation title is Fast, Flexible, and Fun--The Tungsten Replicator Magical Mystery Tour. This talk is going to be fun for two reasons.

First, it's a great opportunity to meet people in the LA MySQL community and talk about my favorite replication software. Tungsten is like a Swiss Army Knife for data replication. It solves a wide range of problems involving HA, scaling, and data movement. The presentation gives a quick intro to the replicator, then surveys how to use the most interesting features, including parallel slave apply, multi-master replication, transaction filtering, and replicating to MongoDB, Oracle, or data warehouses. I'll even show you how to grab the GPL V2 sources from code.google.com and code up your own replicator extensions using Java or Javascript.

Second, the talk venue is in Santa Monica about 10 blocks up from the ocean. Who doesn't like beaches? I certainly do. See you next week!

p.s., Thanks to Joe Devon and the other LA MySQL Meetup folks for the kind invitation.

Replicating Data Now and Then with Tungsten

2011-11-18T23:11:00.000-08:00

What do cruise ship management software and data warehouses have in common? One answer: they both depend on intermittent data replication. Large vessels collect data to share with a home base whenever connectivity permits. If there is no connection, they just wait until later. Data warehouses also do not replicate constantly. Instead, it is often far faster to pool updates and load them in a single humongous batch using SQL COPY commands or native loaders. Replicating updates in this way is sometimes known as batch replication. Tungsten Replicator supports it quite easily.

To illustrate we will consider a Tungsten master/slave configuration. (Sample setup instructions here.) In this example MySQL-based web sales data upload to a data warehouse. The master receives constant updates, which then apply at controlled intervals on the slave.

The first step is to turn off the slave replicator. Login to the prod2 host and execute the following command.

$ trepctl offline

The prod2 slave will disconnect from the master as well as the data warehouse. Updates now accumulate on the master. We can turn on the slave to fetch and apply them all, then go offline again using one of three methods. The first method uses the current sequence number on the master. Here are sample commands to fetch and apply all transactions from the master up to the current master position.

$ trepctl -host prod1 status |grep appliedLastSeqno
appliedLastSeqno : 19600
$ trepctl online -seqno 19600
$ trepctl wait -state OFFLINE -limit 300

As you can see, the first command locates the master sequence number. The second command tells the slave to go online and replicate to sequence number 19600. Finally the third command waits until either slave is back in the offline state or 300 seconds elapse, whichever comes first. This is not strictly necessary for replication but is very handy for scripts, as it eliminates a potentially awkward polling loop.

The second method is to use the MySQL binlog position on the master. The idea is the same as the previous example. We get the master binlog position, then tell the slave to apply transactions to that point and go offline. Here's an example:

$ mysql -utungsten -psecret -hprod1 -e 'show master status'

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.002023 | 92395851 | | |

+------------------+----------+--------------+------------------+

$ trepctl online -event mysql-bin.002023:0000000092395851

$ trepctl wait -state OFFLINE -limit 300

Note in this example that you must pad the binlog offset out to 16 digits, which means you must add the extra zeros shown in bold. Tungsten compares native replication IDs as strings, so that we can handle other databases besides MySQL. This normally a minor inconvenience, unless you don't know the trick. In that case it could be a bit of a head-scratcher.

There is a final way to implement batch replication using Tungsten's built-in heartbeat mechanism. With this method we insert a named heartbeat event on the master, then ask the slave to replicate until the heartbeat appears. Here's an example:

$ trepctl -host prod1 heartbeat -name batch1

$ trepctl online -heartbeat batch1

$ trepctl wait -state OFFLINE -limit 300

This method is perhaps the simplest of all, because there is no need to check for either sequence numbers or binlog offsets on the master. The only downside is that you must have a master and a slave replicator to use it. It does not work with direct replication, in which a single replicator moves data from the master DBMS to the slave. (This limitation will be removed in the future when Issue 228 is fixed.)

When using any of these techniques, we may want to know whether Tungsten will really go offline at the correct point. Fortunately, there's a simple way to find out. The trepctl status command shows pending requests to go offline. Let's say you check status after requesting the slave to replicate to a heartbeat as in the previous example.

$ trepctl status

Processing status command...

NAME VALUE

---- -----

appliedLastEventId : mysql-bin.002023:0000000104369615;37978

appliedLastSeqno : 220126

appliedLatency : 470.589

...

offlineRequests : Offline at heartbeat event: batch1

...

state : ONLINE

timeInStateSeconds : 2.436

uptimeSeconds : 1742.0

Finished status command...

It is simple to see from the status output that Tungsten will go offline when it sees a heartbeat named batch1.

As this article shows, the trepctl online and trepctl wait commands make it very simple to implement batch replication. You can simplify still further by wrapping the commands in a short script written in your favorite scripting language. Either way you have a handy solution to a problem that affects a diverse set of applications.

This is not the end of Tungsten features to enable batch replication. Tungsten has a new applier that can submit transactions using CSV files, which is critical to load transactions quickly into data warehouses. We have been testing it out with Vertica, where early results show that it improves load performance by a factor of 100 or more in some cases. I will describe this new feature in an upcoming article.

Why So Many Proprietary Rewrites of MySQL and InnoDB?

2011-11-16T23:02:00.000-08:00

Every couple of weeks or so I get marketing email from a Continuent competitor advertising a closed-source clone of MySQL. It is said to be pumped up on illegal substances and therefore the solution to all my problems. I like this sort of spam because it makes it easier to track what the neighbors are up to. However it does bring up a question. Why are so many companies offering what amount to proprietary replacements of MySQL? This does not mean alternative builds like Percona or MariaDB. It means products like Clustrix, Schooner, or Xeround, which replace MySQL entirely, or like ScaleDB, or Tokutek, which replace InnoDB.

There's nothing wrong with proprietary software, of course. And there is nothing wrong with rewriting things to make them better. The rewrites are a tribute to the vitality of the MySQL marketplace and in some cases quite clever as well. However, the proprietary offerings tend to obscure an important truth about MySQL. Most businesses that run on open source software have problems with MySQL management, not with MySQL itself.

Here is a simple example. Say you have 2 Terabytes in MySQL 5.1. How do you upgrade from MySQL 5.1 to 5.5 without incurring an application outage? This is a big problem for 24x7 web-facing applications. You don't need to rewrite MySQL to do zero-downtime upgrades. MySQL with InnoDB already works fine. You just need a way to shift connections transparently to a new master database, upgrade the old master, and shift back when you are done. Similar reasoning applies for slave provisioning, automated failover, spreading load over replicas to improve performance, or operating across multiple sites.

At Continuent we concluded a number of years ago that you don't need to change MySQL to manage data effectively. We therefore designed Tungsten Enterprise, Continuent's commercial clustering solution, to work with unaltered MySQL. Tungsten Enterprise uses master/slave replication (i.e., my favorite replicator), distributed management, and transparent connectivity to make a set of standard MySQL or PostgreSQL servers look like a single highly available DBMS that distributes load across all replicas. This architecture has tremendous advantages, because it complements the strengths of MySQL itself. Here are a few of the principle benefits.

Transparency. Everything runs on standard MySQL from initial development to large-scale deployment. Application code runs the same way on a dev laptop or production. Application bugs in production are reproducible on the laptop. Standard MySQL configuration and tuning also work, because this is standard MySQL.

No lock-in. Don't like Tungsten Enterprise? Use something else or revert back to simple MySQL. There's no need to change your database or migrate data.

Data integrity. InnoDB has had years to shake out bugs, especially those involving data corruption. There are still a few but they do not typically show up unless there is a bad hardware failure or you configure your system incorrectly. (Hint #1: don't use MyISAM.) Do you really want to give this up for a new store implementation?

Scalability. MySQL performance is improving constantly, driven by competition between builds, an active community, investment from Oracle and large web properties like Facebook. SSDs are also increasingly affordable and make a lot of performance problems evaporate. As MySQL improves in this and other areas, you get the benefits. The trick is to have a way to upgrade. I mentioned the MySQL 5.1 to 5.5 upgrade problem for precisely this reason.

Operational costs. There is a deep pool of administrators and administrative tools for MySQL. Thanks to books like High Performance MySQL, abundant talks, and a wealth of community resources as well as consulting, there is little mystery about how things work. I probably don't even need to discuss license costs.

Viability. MySQL is not going anywhere. Oracle is continuing to invest in the core database, and Percona, MariaDB and most important Microsoft will ensure Oracle stays on its toes. At Continuent we do our best to keep our friends at Oracle competitive on replication. Innovation on open source MySQL will continue for years to come. (Psst, MySQL guys at Oracle are welcome to come work for us. :)

Given the number of advantages that off-the-shelf MySQL confers, the real question is why our approach is not more popular. Actually it is. For all the marketing attention generated by proprietary MySQL or InnoDB rewrites, many hundreds of billions of transactions per day run on unaltered MySQL. Switching to proprietary versions of MySQL is a substantial wrench for most businesses, because the economics run so strongly in favor of open source DBMS. However, the open source tools for managing MySQL are by-and-large inadequate, in part because some of the problems turn out to be rather difficult to solve.

When we started to rethink database clustering at Continuent back in 2007, we therefore focused on solving the problems outside MySQL that make data management hard. That includes building fast replication with global transaction IDs, so you can fail over easily to up-to-date live replicas. It includes building distributed, rule-based management that has simple primitives like "recover" to fix a broken slave. It includes speedy, transparent connectivity that can spread reads intelligently across multiple servers and reroute connections transparently to allow maintenance without halting applications. Finally, it includes simplifying management so that users don't spend much time worrying about their data. These capabilities are now very robust and help customers handle hundreds of millions of transactions per day.

It is obvious off-the-shelf MySQL (and PostgreSQL too) are already very good and continuing to get better. For most users there is no need to migrate to proprietary offerings that give up the leverage conferred by open source databases. Tungsten Enterprise solves the difficult problems that are critical to building businesses on standard MySQL. If you are building new systems based on MySQL or scaling old ones you should look hard at what we have done.

I Really Dislike Anonymous Attacks

2011-11-13T20:33:00.000-08:00

If you are interested in NoSQL databases (or maybe not) perhaps you have seen the anonymous "warning" about using MongoDB. It concludes with the following pious request:

Please take this warning seriously.

Now there are a lot of great resources about data management on the web but the aforementioned rant is not one of them. If you plan to write technical articles and have people take them seriously, here are a few tips.

Sign your name. Readers are more impressed when they see you are not afraid to stand behind your words.
Explain what problem you were trying to solve. Otherwise uncharitable readers might think you just started pumping information into a new database without thinking about possible consequences and now want to blame somebody else for your bad decision.
Explain how you could do better. Not all designs work out, so propose alternatives. Readers love to see authors demonstrate that they are not discouraged by adversity.

As for most of the points made by the anonymous author, all I can say is: well, duh!

MongoDB behavior with respect to global write locking and transaction durability is obvious from the official documentation. These features are not my cup of tea, but it's also not as if 10gen is hiding them either. Moreover, most people understand that new DBMS implementations have problems, not least of all losing data now and then. You usually pick them because they have features that make it worth putting up with the immaturity. I am not an expert on MongoDB, but I can say from experience it is amazingly easy to load JSON objects into it. The up-front usability alone demonstrates excellent engineering. I am sure for this reason that there are many other good features.

p.s., Here is a point-by-point response from 10gen, helpfully pointed out by Alex Popescu.

Benchmarking Tungsten Parallel Replication

2011-10-31T07:40:00.000-07:00

Tungsten parallel apply on slaves, or parallel replication for short, has been available for about a year. Until recently we did not have many formal benchmarks of its performance. Fortunately the excellent Percona Live Conference in London accepted my talk on Tungsten parallel replication (slides available here), so Giuseppe Maxia and I finally allocated a block of time for systematic performance testing.

In a nutshell, the results were quite good. In the best cases Tungsten parallel apply out-performs single-threaded native replication by about 4.5 to 1. Both Giuseppe and I have verified this using slightly different test methodologies, which helps avoid dumb counting mistakes. Our results also match field tests at a customer site over the previous summer, so we regard them as fairly robust. In the remainder of this article I would like to expand a bit on the details of the benchmarks as well as the results. The results shown here are from my tests.

Benchmark Test Design

Both Giuseppe and I used a similar testbed for replication testing:

HP Proliant server, dual Xeon L5520 CPUs with hyper-threading enabled, 72Gb of RAM
1TB HP Smart Array RAID 1+0
Centos 5.6
XFS file system
MySQL 5.1.57 with InnoDB buffer pool set to 10Gb and using O_DIRECT purge method
Tungsten Replicator 2.0.5 build 347

For convenience we use MySQL sandbox to set up a master with two slaves, as shown in the following diagram. It turns out that for measuring replication throughput there is no reason to set up on separate hosts, as the master does little or nothing during the test and we only operate one slave at a time.

The Tungsten Slave is configured as described in a previous article in this blog, except that there are 30 channels instead of 10. The exact installation command is given at the end of this article.

The test run uses sysbench to spread transactions evenly across 30 databases of identical size, then measure time to process them. This is also known as a replication catch-up test.

Load all MySQL servers with an identical dataset consisting of 30 databases pre-populated with data from sysbench. Giuseppe wrote a new tool called Large Data Generator that is very helpful for capturing and loading such datasets.
With the slaves shut down, store the master binlog start position and then run 30 sysbench oltp test processes against the master to update and read from all schemas simultaneously for one hour.
Start the MySQL slave from the stored master binlog position and measure time to process the sysbench transactions. Shut down the MySQL slave at the end of the test.
Start the Tungsten slave from the stored master binlog position and measure time to process the sysbench transactions using Tungsten Replicator with 30 channels (i.e. threads).

Test Results

Database performance is substantially different depending on whether data are fully resident in the buffer pool (cache-resident) or largely read from disk (I/O-bound). Tungsten parallel replication over 30 databases varies from 1.8 to 4.5 depending on which case you look at, as shown in the following table. Processing times are in minutes (m).

Test Scenario	Rows/Db	Data Size	MySQL Slave	Tungsten Slave	Ratio
Cache-resident	10K	430Mb	30m	17m	1.8
I/O-Bound	10M	68Gb	228m	51m	4.5

Let's look at the results in detail. In the cache-resident test the base dataset is relatively small and loads fully into the buffer cache within a minute or two. Both MySQL and Tungsten slaves complete in well under an hour. Here is a graph showing throughput as measured in bytes of binlog processed per 10 second increment.

Cache-Resident Slave Catch-Up - MySQL vs. Tungsten Replicator, 30 Databases

In the cache-resident case there are virtually no reads from disk as all data are fully resident in the InnoDB buffer pool. Tungsten Replicator is faster because multiple writes can occur in parallel but the speed-up versus native replication is not especially large. Note that Tungsten processes around 40Mb every 10 seconds or about 1Gb of binlog every four minutes.

With I/O bound workloads, on the other hand, we see a profound difference in performance. Tungsten Replicator is at least 6x slower than in the cache-resident case, but still processes updates faster than the master (51 minutes on the slave vs. 60 minutes on the master). Buffer cache loading is correspondingly fast and Tungsten reaches steady-state performance within about 20 minutes. MySQL native replication on the other hand is far slower. The slave not only does not catch up, but it would quickly lag far behind the master under this workload. It takes about 90 minutes for native replication even to achieve steady state performance after buffer pool loading.

I/O-Bound Slave Catch-Up - MySQL vs. Tungsten Replicator, 30 Databases

Overall we can safely say that single-threaded native replication is likely non-workable in the I/O-bound case without going to some combination of SSDs and/or slave pre-fetch.

Further Improvements and Caveats

The current results of parallel replication benchmarks on Tungsten are gratifying especially when you consider that two years ago Tungsten Replicator performance was around 10% of the speed of MySQL replication. Nevertheless, these benchmarks are not the final word. It is clear there is room for optimization as we observe that Tungsten processes the cache-bound binlog at least 6 times faster than the I/O bound workload. Much of the difference seems to be time spent reading from disk. If this could be improved, Tungsten would go even faster.

During the London conference Yoshinori Matsunobu published some excellent performance results using slave pre-fetch, which has encouraged us to build pre-fetch into Tungsten as well.   I am curious to see if we can further boost throughput by adding pre-fetching on each parallel thread, though other people at the conference such as Domas Mituzas were not optimistic. Either way, I am certain we will improve performance, if not using pre-fetch then with other tricks like batching inserts.

Finally, some caveats. Our sysbench load is nice because it is evenly distributed across schemas of exactly the same size. Most application workloads do not behave this way, though some do come very close. The slides for my talk discuss practical issues in maximizing performance in real applications. I suspect that a combination of parallelization with pre-fetch will in fact turn out to be a very good solution for a wide variety of workloads.

Fine Print

If you would like to repeat our results (or attack them as fraudulent), here are some parameters that may help. The database settings in the MySQL sandbox instances are as follows:
default-storage-engine=InnoDB
innodb-additional-mem-pool-size=100M
innodb-flush-method=O_DIRECT
innodb-log-buffer-size=4M
innodb-log-file-size=50M
innodb-thread-concurrency=0
innodb_buffer_pool_size=10G
innodb_file_format=barracuda
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
innodb_strict_mode=1log-bin=mysql-binmax-connections=500
max_allowed_packet=48M
skip_slave_start
sync_binlog=0

Next, here is the sysbench command used to generate load on each schema. We run 30 of these simultaneously varying the database name for each invocation. This example is for the I/O-bound case.

sysbench --test=oltp --db-driver=mysql --mysql-db=${db} \ --mysql-user=msandbox --mysql-password=msandbox \
  --mysql-host=127.0.0.1 --mysql-port=33306 \
  --oltp-read-only=off --oltp-table-size=10000000 \
  --oltp-index-updates=4 --oltp-non-index-updates=2 \
  --max-requests=200000 \
  --max-time=3600 --num-threads=5 run

The replicator configuration is given in the slides for the talk, but here it is again. Options in red are required for sandboxes. Production installations are therefore simpler than what is shown here.

tools/tungsten-installer tools/tungsten-installer --direct -a \

--service-name=parallel --native-slave-takeover \

--master-host=127.0.0.1 --master-port=33306 \

--master-user=msandbox --master-password=msandbox \

--slave-host=127.0.0.1 --slave-port=33307 \

--slave-user=msandbox --slave-password=msandbox \

--home-directory=/opt/continuent \

--property=replicator.store.parallel-queue.maxOfflineInterval=5 \

--svc-parallelization-type=disk --buffer-size=100 \

--channels=30 --thl-port=2115 --rmi-port=10010 \

--skip-validation-check=MySQLPermissionsCheck \

--skip-validation-check=MySQLApplierServerIDCheck \

--start-and-report

To equal the results shown above you will also need to assign databases explicitly to channels in the shard.list file. Otherwise, databases will be assigned channels using a hashing function, which tends to result in somewhat uneven distributions. Look in the comments of the shard.list file for instructions on how to do this.

Finally, all of our tests depend on two excellent tools from Giuseppe Maxia: MySQL Sandbox and the new Large Data Generator program in the Tungsten Toolbox. Once you get the hang of them you will become completely addicted as they make test setup both reliable as well as quick.

Open Source Hardware

2011-10-01T12:06:00.000-07:00

Back in 2010 I stopped buying test servers from Dell and began building them from components using Intel i7 processors, X58-based mother boards, and modular power supplies from Ultra. It was a good way to learn about hardware. Besides, it was getting old to pay for Dell desktop systems with Windows, which I would then wipe off when installing Linux. Between the educational value of understanding the systems better, selecting the exact components I wanted, and being able to fix problems quickly, it has been one of the best investments I have ever made. And it didn't cost any more than equivalent Dell servers.

For this reason, a couple of recent articles about computer hardware caught my attention. First, Dell is losing business as companies like Facebook build their own customized servers. Open source database performance experts like Peter Zaitsev have been talking about full-stack optimization including hardware for years. Google built their original servers using off-the-shelf parts. Vertical integration of applications and hardware has since gone mainstream. If you deploy the same application(s) on many machines, balancing characteristics like cost, performance, and power utilization is no longer a specialist activity but a necessity of business. It's not just cutting out the Microsoft tax but many other optimizations as well.

Second, developments in hardware itself are making custom systems more attractive to a wide range of users. A recent blog post by Bunnie Huang describes how decreases in the slope of CPU clock speed increase over time mean you can get better cost/performance by building optimized, application-specific systems now than waiting for across-the-board improvements. Stable standards also drive down the difficulty of rolling your own. Components on mid-range servers are sufficiently standardized it is easier to build basic systems from components than to put together a bicycle from scratch. Try building your own wheels sometime if you don't believe this.

Easily customizable hardware has important consequences. At a business level, Dell and other mainline hardware vendors will adapt to lower margins, but the market for generic, mid-range appliances has evaporated. Starting around 2005 there was a wave of companies trying to sell open source databases, memcached, and datamarts on custom hardware. Most seem to have moved away from hardware, like Schooner, or folded entirely (like Gear6 and Kickfire). The long-term market for such appliances, to the extent it exists, is in the cloud.

The other consequence is potentially far more significant. The traditional walls that encapsulated hardware and software design are breaking down. Big web properties or large ISPs like Rackspace run lean design teams that integrate hardware with open source software deployment. This not just a matter of software engineers learning about hardware or vice-versa. It is the tip of a much bigger iceberg. Facebook recently started the Open Compute Project, which is a community-based effort to design server infrastructure. In their own words:

By releasing Open Compute Project technologies as open hardware, our goal is to develop servers and data centers following the model traditionally associated with open source software projects. That’s where you come in.

Facebook and others are opening up data center design. Gamers have been building their own systems for years. Assuming Bunnie's logic is correct, open hardware will apply to wide range of devices from phones up to massive clusters. Community-based, customized system designs are no longer an oddity but part of a broad movement that will change the way all of us think about building and deploying applications on any kind of physical hardware. It will upset current companies but also create opportunities for new kinds of businesses. The "cloud" is not the only revolution in computing. Open source hardware has arrived.

Quick Installation of Replication from MySQL to MongoDB

2011-09-29T21:57:00.000-07:00

Proof-of-concept Tungsten support for MongoDB arrived last May, when I posted about our hackathon effort to replicate from MySQL to MongoDB. That code then lay fallow for a few months while we worked on other things like parallel replication, but the period of idleness has ended. Earlier this week I checked in fixes to Tungsten Replicator to add one-line installation support for MongoDB slaves.

MySQL to MongoDB replication will be officially supported in the Tungsten Replicator 2.0.5 build, which will be available in a few weeks. However, you can try out MySQL to MongoDB replication right now. Here is a quick how-to using my lab hosts logos1 for the MySQL master and logos2 for the MongoDB slave.

1. Download the latest development build of Tungsten Replicator. See the nightly builds page for S3 URLs.

$ cd /tmp

$ wget --no-check-certificate https://s3.amazonaws.com/files.continuent.com/builds/nightly/tungsten-2.0-snapshots/tungsten-replicator-2.0.5-332.tar.gz

2. Untar and cd into the release.

$ tar -xzf tungsten-replicator-2.0.5-332.tar.gz

$ cd tungsten-replicator-2.0.5-332

3. Install a MySQL master replicator on a host that has MySQL installed and is configured to use row replication, i.e. binlog_format=row. Note that you need to enable the colnames and pkey filters. These add column names to row updates and eliminate update and delete query columns other than those corresponding to the primary key, respectively. Last but not least, ensure strings are converted to Unicode rather than transported as raw bytes, which we have to do in homogeneous MySQL replication to finesse character set issues.

$ tools/tungsten-installer --master-slave -a \

--datasource-type=mysql \

--master-host=logos1 \

--datasource-user=tungsten \

--datasource-password=secret \

--service-name=mongodb \

--home-directory=/opt/continuent \

--cluster-hosts=logos1 \

--mysql-use-bytes-for-string=false \

--svc-extractor-filters=colnames,pkey \

--svc-parallelization-type=disk --start-and-report

4. Finally, install a MongoDB slave. Before you do this, ensure mongod 1.8.x is up and running on the host as described in the original blog post on MySQL to MongoDB replication. My mongod is running on the default port of 27017, so there is no --slave-port option necessary.

$ tools/tungsten-installer --master-slave -a \

--datasource-type=mongodb \

--master-host=logos1 \

--datasource-user=tungsten \

--datasource-password=secret \

--service-name=mongodb \

--home-directory=/opt/continuent \

--cluster-hosts=logos2 \

--skip-validation-check=InstallerMasterSlaveCheck \

--svc-parallelization-type=disk --start-and-report

That's it. You test replication by logging into MySQL on the master, adding a row to a table, and confirming it reaches the slave. First the SQL commands:

$ mysql -utungsten -psecret -hlogos1 test

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

...

mysql> create table bar(id1 int primary key, data varchar(30));

Query OK, 0 rows affected (0.15 sec)

mysql> insert into bar values(1, 'hello from mysql');

Query OK, 1 row affected (0.00 sec)

Now check the contents of MongoDB:

$ mongo logos2:27017/test

MongoDB shell version: 1.8.3

connecting to: logos2:27017/test

system.indexes

> db.bar.find()

{ "_id" : ObjectId("4e85269484aef8fcae4b0010"), "id1" : "1", "data" : "hello from mysql" }

Voila! We may still have bugs, but at least MySQL to MongoDB replication is now easy to install.

Speaking of bugs, I have been fixing problems as they pop up in testing. The most significant improvement is a feature I call auto-indexing on MongoDB slaves. MongoDB materializes collections automatically when you put in the first update, but it does nothing about indexes. My first TPC-B runs processed less than 100 transactions per second on the MongoDB slave, which is pretty pathetic. The bottleneck is due to MongoDB update operations of the form 'db.account.findAndModify(myquery,mydoc)'. You must index properties used in the query or things will be very slow.

Auto-indexing cures the update bottleneck by ensuring that there is an index corresponding to the SQL primary key for any table that we update. MongoDB makes this logic very easy to implement--you can issue a command like 'db.account.ensureIndex({account_id:1})' to create an index. What's really cool is that MongoDB will do this even if the collection is not yet materialized--e.g., before you load data. It seems to be another example of how MongoDB collections materialize whenever you refer to them, which is a very useful feature.

TPC-B updates into MongoDB are now running at over 1000 transactions per second on my test hosts. I plan to fix more bugs and goose up performance still further over the next few weeks. Through MongoDB we are unlearning assumptions within Tungsten that are necessary to work with non-relational databases. It's great preparation for big game hunting next year: replication to HBase and Cassandra.

What's Next for Tungsten Replicator

2011-09-08T12:15:00.000-07:00

As Giuseppe Maxia recently posted we released Tungsten Replicator 2.0.4 this week. It has a raft of bug fixes and new features of which one-line installations are the single biggest improvement. I set up replicators dozens of times a day and having a single command for standard cluster topologies is a huge step forward. Kudos to Jeff Mace for getting this nailed down.

So what's next? You can get see what we are up to in general by looking at our issues list. We cannot do everything at once, but here are the current priorities for Tungsten Replicator 2.0.5.

Parallel replication speed and robustness. I'm currently working on eliminating choke points in performance (like this one) as well as eliminating corner cases that cause the replicator to require manual intervention, such as aging out logs that are still needed by slaves.
Multi-master replication. This includes better support for system of record architectures, many masters to one slave, and replication between the same databases on different sites. Stephane Giron nailed a key MyISAM multi-master bug for the last release. We will continue to polish this as we work through our current projects.
Better installations for more types of databases. Jeff recently hacked in support for PostgreSQL as well as Oracle slaves, and we are contemplating addition of MongoDB support. Heterogeneous replication is getting simpler to set up.
Filter usability. Giuseppe has a list of improvements for filters, which are one of the most powerful Tungsten Replicator features but not as easy for non-developers to use as we would like. Better installation support is first on the list followed by ability to load and unload dynamically.
Data warehouse loading. We have a design for fast data warehouse loading that I hope we'll be able to implement in the next few weeks. Linas Virbalas has also been working on this problem along with a number of other heterogeneous projects for customers.

This is a lot of work and not everything will necessarily be finished when 2.0.5 goes out. However, I hope we'll make progress on all of them. In case you are wondering how we pick things, replicator development is largely driven by customer projects. If you have something you need in the replicator, please contact Continuent.

After this build we will... Er, let's get 2.0.5 done first. Suffice it to say we have a long list of useful and interesting features to discuss in future blog articles.

The Inimitable Mr. Steven Jobs

2011-09-06T21:56:00.000-07:00

There have been countless articles praising Steve Jobs since he announced his retirement from Apple on August 25th. Most either catalogue Steve Job's many triumphs or assess the impact of his creativity on society. Those are entertaining topics but not especially useful. A more practical question is why Steve Jobs is so good at creating new products and whether the rest of us can imitate him.

Steve Job's best work seems to follow a repeated pattern. Let's call it the Apple pattern, though of course it could just as well be the Pixar pattern or Next pattern:

See the whole picture of some crucial human/technology interaction and recognize gaps.
Design products to fill those gaps that combine artistic sensibility and innovative technology.
Get a large organization to implement designs in a way that makes the end result like the handiwork of a single highly-focused craftsman.

Two things about the pattern seem particularly striking. First, Steve Jobs is a complete package. I have been in the tech industry for over three decades and have met people who did one or at most two of these things at the level necessary to create products that move large markets. Almost nobody does all three. The fact that Steve is excellent in all areas simultaneously may be a root cause behind his long run of successes.

Second, Job's ability to drive implementation teams is extraordinary. Maybe it's just the manager in me, but I find his ability to pick the right people to run teams and to keep those teams pointed in a clear direction without product-destroying compromises quite remarkable. This is far harder than generating ideas in the first place. The heart of the Apple pattern as as much about understanding people as technology--not just users but the creators as well. I have never heard Jobs make pronouncements on team management, but there is an excellent talk from Ed Catmull of Pixar that summarizes the tensions quite well.

Steve Jobs is commonly compared to great inventors like Edison, Ford, and Disney. When thinking about imitation, another parallel seems more illuminating: John Churchill, Duke of Marlborough and hands-down the greatest English general of all time.

A possible Jobs ancestor?

Marlborough possessed a seldom equalled ability to see war as an integrated whole across geography and branches of arms, devise unexpected strategies to exploit the weaknesses of his enemies, and execute them flawlessly in the difficult conditions of early 18th Century campaigns. Execution extended from handling fractious allies down to the painstaking work to ensure his men had proper meals after each day's march. In other words: analogous problem-solving abilities to Steve Jobs, translated into the field of warfare. The parallel extends to the lavish praise of contemporaries and later historians. Winston Churchill famously described Marlborough as follows.

He commanded the armies of Europe against France for ten campaigns. He fought four great battles and many important actions ... He never fought a battle that he did not win, nor besieged a fortress that he did not take ... He quitted war invincible.

Grand problem-solvers like Marlborough and Jobs are sufficiently rare they tend to be one-offs who change society but leave no obvious successors. English military superiority on the Continent waned after Marlborough's retirement. Something similar will likely befall Apple after Jobs, current happy talk about product pipelines and cash position notwithstanding. It is simply not possible to imitate Jobs by committee, which is effectively what will happen once he is completely absent. The driving force is gone.

That said, we can all imitate Steve Jobs, albeit on a smaller scale. Many highly successful products start with a single person who conceives the idea and drives at least the first couple of iterations to completion. Seeing the whole problem, applying innovative designs to solve it, and managing the team to get it done is a fundamental pattern that applies across a wide range of endeavors. Here is just one of many examples.

Many years ago at Sybase I worked for a manager named Mark Deppe. Early in the 1990s Mark learned that Wall Street firms were patching together crude publish/subscribe messaging applications to move data between financial systems in order to speed up trades. He recognized that there was a much better way to do this using log-based data replication and built the Sybase Replication Server product. The Rep Server went on to generate hundreds of millions of dollars in sales. It still sells well today, over 15 years later. Mark was a great architect but also a great builder of teams. He paid as much or more attention to hiring and managing people as he did to technology. He trusted the people he hired, and he gave them the freedom and support to do great work. At the same time Mark was also incredibly attentive to detail and did all the project management for the first releases himself. Years later he said it was too important a task to hand off to anyone else.

Mark Deppe was the best technical manager I ever worked for. I have consciously imitated his best practices for many years. Looking back it seems I was unconsciously imitating the Apple design pattern. But perhaps that was not a complete coincidence. Before joining Sybase Mark was at Apple where he worked with (guess who?) Steve Jobs.

------------------
NOTE: After this article was published I found the flow hard to understand and edited it a week or so later to make it more readable. The argument is the same as before.

Practical Multi-Master Replication using Shard Filters

2011-08-30T01:51:00.000-07:00

Earlier this month I published an article on this blog describing the system of record approach to multi-master replication. As mentioned in that article my colleagues and I at Continuent have been working on improving Tungsten to make system of record design patterns easier to implement. This article describes how to set up system of record using Tungsten Replicator shard filters, which are a new feature in Tungsten 2.0.4. By doing so we will create a multi-master configuration that avoids replication loops and transaction conflicts. On top of that, it is quite easy to set up.

There are many possible system of record patterns depending on how many schemas are shared and across how many masters. The following diagram shows three of them. In contrast to many so-called MySQL multi-master implementations, all masters are live and accept updates. (By contrast, schemes such as MySQL-MHA make extra masters read-only. Don't be fooled!)

For today's exercise we will implement the basic system of record. Once you understand this you can quickly set up other multi-master scenarios.

Defining Shard Master Locations

The first step is to tell Tungsten where each shard is mastered. By mastered we mean it is the one master that receives application updates on that shard, whereas all other masters have copies only or may not even contain the shard at all. Tungsten uses a variant of CSV (comma-separated format) where the first line contains column names. You can have any amount of whitespace between entries. Create a file called shards.map with your favorite editor and type in the following lines.

shard_id master critical

tungsten_nyc nyc false

tungsten_sjc sjc false

acme sjc false

pinnacle nyc false

The first column is the name of the shard. This must be unique--because a shard can only live on one master. The next column is the "home" master for the shard. This is the one and only master that should receive shard updates. The third column defines whether the shard is critical and requires full serialization. It will be linked to parallel replication in a later release.

It turns out you do not need to add entries for Tungsten catalog schemas such as tungsten_nyc. Tungsten Replicator will create them automatically. They are shown here for completeness only.

Creating Replication Services

Next we need to define services to replicate bi-directionally between DBMS servers and set options to filter shards using the ShardFilter class, which is new in Tungsten 2.0.4. The shard filter helps ensure that shards replicate from their home masters only and not from other locations. If you do not know what replication services are, you can find a description of them in this article.

Multi-master replication is easy to mis-configure, so to prevent accidents we will tell the shard filter to generate an error any time it processes a shard it has never seen before. The replication service will immediately fail, which signals that we have to update shard definitions. This is the safest way to implement system of record or any multi-master configuration for that matter. It is generally easier to restart replication after correcting the configuration than to mix up data, which can lead to major outages.

The first step is to set replication services for each master. These read the binlog and make transactions available to slave replication services. Here are the commands. Note that the sjc master is on host logos1, while the nyc master is on logos2. The remaining examples use these names consistently.

# Define common master settings.

COMMON_MASTER_OPTS="--datasource-user=tungsten --datasource-password=secret \

--home-directory=/opt/continuent --svc-parallelization-type=disk \

--svc-extractor-filters=shardfilter \

--property=replicator.filter.shardfilter.unknownShardPolicy=error"

# Set up sjc master.

tools/tungsten-installer --master-slave -a --master-host=logos1 \

--cluster-hosts=logos1 --service-name=sjc $COMMON_MASTER_OPTS --start-and-report

# Set up nyc master.

tools/tungsten-installer --master-slave -a --master-host=logos2 \

--cluster-hosts=logos2 --service-name=nyc $COMMON_MASTER_OPTS --start-and-report

The --svc-extractor-filters option adds shard filtering immediately after event extraction. The unknownShardPolicy=error setting will cause the masters to die if they process an undefined shard.

Now we can define the remote slave services for sjc and nyc. These are special slaves that write transactions onto another master as opposed to a normal slave. We would like slave services to error out on unknown shards as well. Also (and this is important) we want them to enforce shard homes. Here are the commands to create the services and start each one.

COMMON_SLAVE_OPTS="--release-directory=/opt/continuent/tungsten \

--service-type=remote --allow-bidi-unsafe=true --svc-parallelization-type=disk \

--svc-applier-filters=shardfilter \

--property=replicator.filter.shardfilter.unknownShardPolicy=error \

--property=replicator.filter.shardfilter.enforceHome=true"

# Set up sjc remote slave.

tools/configure-service -C -a --host=logos2 \

--local-service-name=nyc --role=slave \

--datasource=logos2 --master-host=logos1 $COMMON_SLAVE_OPTS sjc

$trepctl -host logos2 -service sjc start

# Set up nyc remote slave.

tools/configure-service -C -a --host=logos1 \

--local-service-name=sjc --role=slave \

--datasource=logos1 --master-host=logos2 $COMMON_SLAVE_OPTS nyc

trepctl -host logos1 -service nyc start

The --svc-applier-filters option adds shard filtering before applying to the DBMS. The unknownShardPolicy=error setting will cause the slaves to die if they process an undefined shard. Finally, the enforceHome=true option means that each slave will drop any transaction that lives on a different service from that slave's master.

At the end of this procedure, your services should be online and read to run. Use 'trepctl services' to make sure.

Loading Shard Definitions

To make shard definitions take effect, you must load the shard.map contents into each replication service. You can do this any time the replicator is running but after loading new definitions you must put the replicator online again. Here are the commands to load the shard maps onto each of the four replication services. For each replication service, you must delete the old definitions, reload new ones, and get the replicator to go online again.

#!/bin/bash

MAP=shard.map

for host in logos1 logos2

for service in sjc nyc

trepctl -host $host -service $service shard -deleteAll

trepctl -host $host -service $service shard -insert < $MAP

trepctl -host $host -service $service offline

trepctl -host $host -service $service wait -state OFFLINE

trepctl -host $host -service $service online

done

This looks a little clunky and will be reduced to a single command instead of five in a later release. I put it in a script to make it quicker to run. The good news is that there is just one shard map that works for all replication services, regardless of location or role.

Once you finish this step, you can go to any replication service and list the shards it knows about. Let's pick a service and demonstrate:

$ trepctl -host logos1 -service sjc shard -list

shard_id master critical

tungsten_nyc nyc false

tungsten_sjc sjc false

acme sjc false

pinnacle nyc false

With this we are ready to start processing some transactions.

Multi-Master Operation

At this point we have multi-master replication enabled between hosts logos1 and logos2. You can try it out. Let's add the acme database to the sjc master on logos1 as an example.

mysql -utungsten -psecret -hlogos1

mysql> create database acme;

mysql> use acme

mysql> create table foo (id int);

mysql> insert into foo values(1);

We can see that all of these commands replicate over to the logos2 server quite easily with the following command:

mysql -utungsten -psecret -hlogos2 -e 'select * from acme.foo'

+------+

| id |

+------+

| 1 |

+------+

That seems pretty simple. In fact it is. You can go over to logos2 and enter transactions for pinnacle in the same way. Data replicate back and forth. There are no replication loops. There are also no conflicts.

Adding a New Shard

So what happens when we add a new shard? The simplest way to see is to create a database using a schema name that does not exist in the shard map. Let's try to create a database named superior on the nyc master.

mysql -utungsten -psecret -hlogos2 -e 'create database superior'

Now check the status of the nyc master replication service. We see it has failed with an error due to the unknown shard. (Tungsten parses the create database command and assigns it the shard ID "superior.")

$ trepctl -host logos2 -service nyc status

Processing status command...

NAME VALUE

---- -----

...

pendingError : Stage task failed: binlog-to-q

pendingErrorCode : NONE

pendingErrorEventId : mysql-bin.000157:0000000000002475;1287

pendingErrorSeqno : 8

pendingExceptionMessage: Rejected event from unknown shard: seqno=8 shard ID=superior

...

state : OFFLINE:ERROR...

Finished status command...

This problem is quite easy to fix. We just open up the shard.map file and add a row for superior so that the file contents look like the following:

shard_id master critical

tungsten_nyc nyc false

tungsten_sjc sjc false

acme sjc false

pinnacle nyc false

superior nyc false

Reload the shard.map file as shown previously and you will be back in business. Incidentally, if you do not want the superior database to be replicated to other masters, you can also specify this in the rules. Just give superior the special master name #LOCAL as in the following example and it will not replicate outside the nyc service.

superior #LOCAL false

In fact, #LOCAL means that any schema named superior will not replicate outside the service in which it is defined. You can have an unshared schema named superior on every master.

Where to Next?

The shard support described in this article is now part of Tungsten 2.0.4 and will appear in the official build when it is finally ready. You can try it out right now using one of our handy nightly builds.

We plan to build out shard filtering quite a bit from the current base. One immediate fix is to put in a check so that if an application commits shard updates on the wrong DBMS server, the master replication service on that server will detect it and fail. This will tell you there's a problem immediately rather than letting you wallow in blissful ignorance while your data become hopelessly mixed up. We will also simplify the commands to update shards while replicators are online.

Longer term we will be adding features to propagate shard definitions through replication itself. Stay tuned for more work in this area. If you want to help fund work to enable your own applications, please get in contact with me at Continent. I can think of at least a dozen ways to make our multi-master support better but it's always nicer to spend the effort on features that enable real systems. In the meantime, I hope you find multi-master with shard filtering useful and look forward to your feedback.

First the Blog, now the Webinar: Adding Parallel Replication to MySQL in a Hurry

2011-08-24T12:08:00.000-07:00

My recent post on setting up Tungsten parallel replication in a hurry got a lot of hits, though to be fair it was probably not the great writing but the fact that at least one popular MySQL blog posted a link to it. (Thanks, we like you guys too.) Anyway, I would like to invite anybody who is interested in parallel replication to attend a webinar on Thursday September 1st at 10am PDT to cover installing and using Tungsten. It's straight-up technical talk to help you start quickly.

Bringing up Tungsten on an existing MySQL slave only takes a few minutes, so once we have that out of the way I will explain how Tungsten works inside and show you some of the tricks for getting your applications to play nice with parallel replication as well as how to tune performance. The idea is to minimize fluffy architectural stuff and maximize lab demos that help you bend replication to your will. The talk will also cover how to get help, log bugs, and even add your own code. Plus there will be lots of time for questions.

As most readers of this blog know, Tungsten Replicator is open source (GPL V2) and hosted on code.google.com. If you miss the webinar you may be able to catch up on parallel replication in-person in London toward the end of October. I just submitted a talk to the next Percona Live and hope it gets accepted. If so, see you there!

The System of Record Approach to Multi-Master Database Applications

2011-08-19T12:51:00.000-07:00

Multi-master database systems that span sites are an increasingly common requirement in business applications. Yet the way such applications work in practice is not quite what you would think from accounts of NoSQL systems like Cassandra or SQL-based systems like Oracle RAC. In this article I would like to introduce a versatile design pattern for multi-master SQL applications in which individual schemas are updated in a single location only but may have many copies elsewhere both locally as well as on other sites. This pattern is known as a system of record architecture. You can build it with off-the-shelf MySQL and master/slave replication.

Let's start by picking a representative software-as-a-service (SaaS) application: call center automation. Call center software integrates with a local PBX or VOIP to allow agents to answer and make phone calls for telemarketing campaigns in a systematic and automated way using standard procedures known as "agent scripts." Admins set up agent scripts and define lists of people to call as well as marketing campaigns. Finally and perhaps most importantly, managers receive a wide variety of detailed reports that allow them to optimize current work, examine historical performance, and make predictions about the future for planning purposes. Here is a typical application architecture.

Figure 1: Call Center Application Architecture

Bear in mind that this is a greatly simplified view. Like most business applications, call center automation systems may contain hundreds of database tables and many types of user services. There are also practical complications that go beyond the application itself. Call center automation is vital to the businesses that use it. Customers want assurance they can continue processing on another site if a SaaS vendor site goes dark. This means we have to think about maintaining applications and data on multiple sites.

The ideal solution for most SaaS vendors would be to have call center data and applications for all customers live on multiple sites at all times. Multiple live sites mean that failover is instantaneous since both applications and database servers are already up and running. Constant update means there is little or no data loss on failure. Customers could connect to the nearest site. Here is a picture of that dream that includes two sites and two customers, Acme Inc. and Pinnacle, Ltd.

Figure 2: Dream Architecture for Call Center Automation

This solution has only one problem. It is impossible to build. Readers may nod wisely and say this is because of CAP Theorem limitations, but that somewhat misses the point. Let's say we use a NoSQL DBMS like Cassandra that permits updates in multiple locations and reconciles the data using eventual consistency. However, there's a catch: as we saw above, much of the value of call center automation is in operational queries and reports. That drives you back to an RDBMS with cross-table joins, aggregation functions, referential integrity, and convenient SQL-based report writing tools. For this reason alone, Cassandra is a non-starter for call center automation.

What about a SQL DBMS? MySQL obviously has all the features you need for query-intensive solutions on smallish data sets (e.g. hundreds of millions of rows, not many billions or trillions). The problem is multi-master replication. Updating the same table from two or more places on a LAN is already quite difficult: witness the complexity of Oracle RAC or MySQL Cluster. The problem becomes intractable when you combine complex SQL transactions, referential integrity, and high-latency WAN connections. If you want full SQL semantics you cannot have updates on multiple sites. This is a serious dilemma and not just for call center automation. The same problems or worse affect a multitude of valuable business applications including market automation, credit card processing, customer relationship management (CRM), time/expense tracking, accounting, and many others.

Fortunately we are not really stuck. If we give up some requirements customers do not really want anyway, there is a perfectly good solution that will work for a wide range of problems. Data warehousing architects long ago developed the notion of a system of record. Bill Inmon's classic Building the Data Warehouse defined system of record as follows:

The definitive and singular source of operational data. If data element abc has a value of 25 in a database record but a value of 45 in the system of record, by definition the first value is incorrect and must be reconciled.

System of record applies to multi-master systems in the form of a simple rule. We just assert that every customer has master data in one and only one location and copies everywhere else. When particular customers update information they do so on their own master. Customers can have masters on different hosts or sites, but the system of record rule says that no customer has one in two places. This eliminates conflicts between masters, and multi-master replication now works without a lot of difficulty.

System of record thus meets the original requirement of having data on multiple sites, which was to handle a site failure. We can store data economically using off-the-shelf MySQL. We can update copies within and across sites using master/slave replication. We can shard customer data into independent schemas. The result looks like the following. Acme has a master in San Jose, whereas Pinnacle has its master in New York.

System of Record Architecture for Call Center Automation

Using the system of record approach simplifies other problems as well. Standard backup and restore techniques still work. If you mess up a customer copy, you re-provision from the master shard. You can implement failover across sites and also fail over locally onto slave copies, which can be complete copies containing data for all customers.

Meanwhile, most users are fine with a single site. Pinnacle is close to New York, which is why the SaaS vendor puts Pinnacle's data there and gives them the New York site DNS for login. It is also possible to run reports on the cross-site copies as well. You can even run full applications provided you forward writes to the system of record, as shown above for Acme.

The real issue in implementing system of record architectures is that existing replication and clustering tools are not quite up to the job of handling cross-site applications build on system of record. We are extending Tungsten to handle some of the obvious problems in building these types of systems using MySQL.

Locating the customer master and connecting applications to it.
Moving the customer master from one location to another. This happens more often than you would think, for example to minimize multi-master replication which can introduce problems beyond conflicts.
Detecting accidental updates to copies and preventing them from either reaching the DBMS and/or preventing them from propagating to other locations.
Proving a clean failover model that works on both cross-site as well as local copies of data.
Recovering corrupted copies of customer data from the master.

I will discuss two of the upcoming Tungsten features in follow-up articles. The first is assigning a shard master in the Tungsten Replicator using the new shard API. The shard API enables multi-master but enforces system of record constraints to avoid messing up data should you accidentally update in the wrong location. The second feature is cross-site management and connectivity using Tungsten Enterprise. This handles failovers within and between sites and automatically connects applications to the active master regardless of which site or DBMS it lives on.

The need for availability is pushing an increasing number of SaaS vendors and other application providers to operate systems across multiple sites. Applications like call center automation depend on the features of SQL and cannot be implemented using NoSQL DBMS's like Cassandra. The system of record architecture eliminates replication conflicts and enables multi-master updates to work on ordinary SQL databases between sites. If you are building complex SQL applications and thinking about going multi-site, this design pattern should be in your toolbox.

So Where's the Fall MySQL Community Conference?

2011-08-18T20:38:00.000-07:00

Last week Percona announced plans to sponsor the Percona MySQL Conference in Santa Clara in April 2012. It is meant to replace the O'Reilly conferences of previous years. The announcement led to some reasonable questions, for example from Giuseppe Maxia. These and other online posts initiated a thoughtful exchange of views about the pros and cons of Percona's conference announcement by various members of the MySQL community.

Not everyone agrees with what Percona is doing. However, you have to give Peter, Baron, and others at Percona credit for taking the risk to organize a replacement conference. It's a big financial commitment to rent space in Santa Clara. The conference will be a huge amount of work, much of it quite thankless. Even so I imagine it might be tempting for some people to try to organize an anti-conference just to spite Percona, much as Percona did at the MySQL Conference in 2009. This would be a mistake.

Here is a better suggestion. Why not put the energy into organizing a Fall 2012 MySQL Community Conference in Europe? Make it somewhere pleasant like Barcelona or Verona and everybody will want to go. Two solid conferences in different locations at opposite ends of the calendar would benefit the MySQL community at all levels and make it possible for more people to attend. For those of you who want to grow the community, it's time to stand up and be counted. Let's get another conference off the ground.

Adding Parallel Replication to MySQL in a Hurry

2011-08-13T01:18:00.000-07:00

A previous article on this blog described Tungsten parallel replication using on-disk queues. On-disk queues are now more or less finished, and I just closed the covering issue for the feature. The work is bug fixing and performance testing from here on out. Speaking of performance, that looks fairly good. A recent on-site test using production workloads showed 3.3X improvement over native MySQL replication while holding resources like memory down to much more reasonable levels than in-memory queues. We have further optimizations on the way, so this should improve.

Now that parallel replication is working a lot better, what is it good for? Here is a good start: assuming your workload is suitable for shard-based replication, Tungsten offers a nice replacement for MySQL slave replication that will give you immediate parallel replication for any MySQL server starting with version 5.0 and up. We call this native slave takeover. It looks like the following diagram.

You can migrate MySQL slaves to Tungsten very easily thanks to some great work on the Tungsten installer by Jeff Mace that Giuseppe Maxia also praised in a recent article.  Seeing that Giuseppe is also the Continuent QA director, I guess this means it passed. :) Here is how to set up.

First, configure MySQL master/slave replication. Here are typical commands after you synchronize the slave with a backup from the master. For these examples, we assume the slave is running on host mercury, and the master is on host saturn.

mysql -utungsten -psecret -hmercury -e "CHANGE MASTER TO \
  MASTER_HOST='saturn', \
  MASTER_USER='repl', \
  MASTER_PASSWORD='s3cr3t', \
  MASTER_LOG_FILE='mysql-bin.000338', \
  MASTER_LOG_POS=4534"
mysql -utungsten -psecret -hmercury -e "START SLAVE"
mysql -utungsten -psecret -hmercury -e "SHOW SLAVE STATUS\G"

Next, download and unpack Tungsten from the latest dev build. Here are sample commands for build 231.

wget --no-check-certificate https://s3.amazonaws.com/files.continuent.com/builds/nightly/tungsten-2.0-snapshots/tungsten-replicator-2.0.4-231.tar.gz
tar -xvzf tungsten-replicator-2.0.4-231.tar.gz
cd tungsten-replicator-2.0.4-231

Finally, run the installation using the following handy command. This will copy the download into its proper location (here /opt/tungsten) and start Tungsten. Tungsten will stop MySQL replication if it is running and start Tungsten replication from the exact point where native replication left off.
tools/tungsten-installer \

--direct \
--native-slave-takeover \

--master-host=saturn \

--master-user=repl \

--master-password=s3cr3t \

--slave-host=mercury \

--slave-user=tungsten \

--slave-password=secret \

--service-name=takeover \

--home-directory=/opt/continuent \

--svc-parallelization-type=disk \

--channels=10 \

--start-and-report

If there is a problem with your pre-requisites the installer will print out a message and stop. Most of these messages should be pretty self-explanatory. (In some builds there's a problem with OpenJDK failing the install check. Use --no-validation to get around that.)

Here is another interesting tip. If you try Tungsten and want to go back to MySQL replication, you can flip back to MySQL native replication with two commands. Just take Tungsten offline cleanly, which shuts down all the replicator channels at the same point, and start MySQL replication again:

trepctl offline

mysql -urepl -ps3cr3t -e 'START SLAVE'

We are currently doing a lot of performance profiling and testing of parallel replication. I hope to post more about performance results in a future article. Meanwhile, there are 36 new features (including on-disk queues) and bug fixes that will roll into a final Tungsten 2.0.4 in the next week or so. Try out the latest builds and see what you think.

Mobile Internet Access in Germany for Open Source Road Warriors

2011-07-24T09:35:00.000-07:00

Reliable Internet access is a long-standing problem for road warriors visiting foreign countries. Open source developers in particular have problems reconciling travel with addiction to high-bandwidth network access from laptop computers. Wi-Fi hotspots are scarce, costly, often slow, and in some cases complicated by inconvenient local laws like Italy's Pisanu Decree. International mobile network access plans are ridiculously expensive or like DROAM have download limits that make them useless for serious programming.

The best solution in many cases is to look for a local pre-paid mobile access plan in each country you visit. Mobile networks are widely available and fast in developed regions, and there are cheap plans that limit the amount you pay while providing solid connectivity. On a recent trip to Germany I found a great solution for local Internet access from FONIC, which offers a prepaid data plan using their FONIC Surf-Stick. The Surf-Stick is a USB modem that plugs into a USB port on your laptop and looks like the following.

After you buy the Surf Stick, you can add money to it using credits purchased in local stores. It's a relatively simple solution provided you understand a little about networking and can work your way through a bit of German.

The rest of this article is a description of how to use FONIC in Germany, as well as a review of the performance and a couple of downsides. I do not have any connection with FONIC other than using their products. You may have different experiences or find something better. If so, please write an article about it.

Getting Started

I bought got my Surf Stick for 49 Euro at Saturn, a big German consumer electronics chain. You get the modem itself plus a SIM card and one day of free surfing. You can get also the stick from other FONIC partners or order it off the FONIC website. Surprisingly it does not appear to be available in places like Frankfurt or Munich Airport.

The first step once you have the modem is to initialize the SIM on FONIC's site by registering yourself as a new user. To do this you'll need to have your FONIC phone number and activation code ("Freischaltungsnummer"), which are written on the side of the envelope that contains the SIM and that you should try to avoid losing. You'll also need a German address that you enter as part of the sign-up. A hotel address is fine. It takes a couple of hours after registration to complete provisioning.

Once provisioned, connecting is easy. Put the SIM in the surf stick and plug the stick in a USB port on your computer. On Mac OS X, you'll see an application called "Mobile Partner." Here are the steps to activate a connection:

Click the Mobile Partner app.
Enter your PIN (also on the SIM envelope) and press Return.
The application will stall for a few seconds while it looks for the modem. Once complete it will show a screen entitled "Verbinden" (connect).
Click Verbinden. A couple of pop-ups will appear and you are online.
When you want to drop the connection click "Trennen" (disconnect).

You will now need to load some money into your account. There's no way to top up online with a credit card--you either have to register a German bank account or purchase credits. For foreigners the only practical thing is therefore to buy a credit, which is called an "Aufladebon." You can get them at stores like Lidl, Rossman, and Jet as well as Esso gas stations. Just ask for the following in German: FONIC Aufladebon für 20 Euro, which is a 20 Euro credit and gives you 8 days of surfing. I recommend buying this at the same store where you get your modem, which means the start-up cost is about 70 Euros.

The Aufladebon itself a bit dissapointing: a printed receipt with a 12 digit number and some instructions in German. Start your Mobile Partner app, connect to the Internet, and click on "Guthaben verwalten" (manage credit). Select "Guthabenkarte aufladen" (load credit), enter your 12 digit number, and press enter. Your credit is now topped up for a while. You can check the level using the "Guthabenabfrage" (check credit) button.

Performance

I used the FONIC modem for about a week. It functioned well in every location that had at all reasonable connectivity. FONIC handles transitions across cells really well--provided there's connectivity it seems to use the fastest available protocol, such as HSPA, and switches seamlessly down to lower capacity protocols like Edge if that is all that is available. You can use it in a car or train without experiencing application problems--I used it on the Autobahn between Hamburg and Lübeck and it worked surprisingly well. (Just be clear, somebody else was driving at the time.)

With good connectivity, the rated speed is 7.2Mbps down and 5.76 upload, which is comparable to average household connections. PDFs and similar files downloaded at 150K/sec. Upload was similarly snappy.

Applications like ssh and even remote debugging/profiling worked very well. In my case this included running Yourkit Java profiler connecting into an application running on servers elsewhere in Germany. I also hosted GotoMeeting web conferences. The latency seems higher than DSL--typical latencies run about 100 to 200ms, which is about the same latency you get when connecting from the US West Coast to sites in Europe.

Daily transfer limits are very competitive. The current limit is 500Mb per day at full speed access, though in practice it seemed to cut off around 600Mb for me. This is better than offerings from the big guys like T-Mobile and Vodafone. FONIC implements the limit quite elegantly. First you get a nice SMS that you are near the limit, followed by another that you are over the limit. However, after that you do not get cut off. The connection just switches to 64kbps until the next day. This is still faster than the Wi-Fi connectivity in many hotel rooms.

If you don't want to wait for the friendly SMS message about limits, you can check your limits easily on using the Mobile Partner app.

Speaking of the app, Mobile Partner seems really solid. There were no bugs, crashes or weird hangs the way you get with some of the US providers like Verizon's prepaid Internet app. I only ran it on Mac OS X but I would guess it is just as good on Windows.

Caveats

In general FONIC is outstanding. Life is not perfect, so here are a few of the limitations I found.

German. FONIC is 100% German, including the website and the app. You'll need to know some German or make friends fast. Also, the data plan apparently only works in Germany.
Phone connectivity. If your cell phone does not work, FONIC won't either. The nice thing is that you can have connectivity drop out for a bit while crossing cells in a train or car without losing TCP/IP sessions.
Occasional routing problems. On one evening in one particular location the FONIC modem did not work at all well, reporting ping latencies of 50+ seconds or dropping out entirely. It was not apparent whether this was due to poor cell coverage or another issue. It worked fine everywhere else.

There appear to be some competitors to FONIC but I have no idea how well they work. One of the advantages of FONIC is that they have many partners in Germany that sell both modems as well as the credits. That would also be a consideration in evaluating competing products.

Summary

Some day it may be possible to get decent mobile data plans that work for international travel. Given the complexity of telecom regulation and the motivations of local carriers to prevent competition, you probably don't want to hold your breath until they arrive.

In the meantime, FONIC looks like a good Internet access solution for anybody traveling to Germany for more than a few days who needs constant, high-capacity access. I was stuck in a couple of locations that had no network access, so it was a life-saver in those cases. However, FONIC is also cheaper than hotel Wi-Fi and has better performance than many Wi-Fi hotspots. If you move about or uncertain about the quality of the Internet connectivity where you are staying, I highly recommend it.

If you know of similar plans for other countries, please share your experiences. I would love to find something like FONIC for Italy, Spain, and the US.

Introducing Tungsten On-Disk Queues for Parallel Replication

2011-07-03T13:45:00.000-07:00

Tungsten Replicator has offered shard-based parallel replication to slaves since late 2010. The initial implementation uses in-memory queues. Working purely in memory keeps latency low and throughput high. On the other hand, working in memory consumes valuable RAM. It also forces us to buffer all in-flight transactions and therefore greatly limits the span of time permissible between the slowest and fastest shard.

Hence our newest improvement: on-disk parallel queues. In this article I will cover how parallel replication works in general, how on-disk queues help with parallel replication, and then show how to set up from the latest builds.

First, let's review the basic mechanics. Parallel replication is really "parallel apply," which means taking a stream of serialized transactions, splitting them into separate streams, and applying them in parallel to a slave DBMS. In the Tungsten pipeline architecture, we implement this kind of flow using a combination of stages and stores. One stage reads transactions from the persistent transaction history log (aka THL) to a "parallel store." The parallel store splits the stream into a set of queues. The next stage extracts from those queues and applies to the slave. It looks like the following picture:

From a conceptual point of view the incoming thl-to-q task thread performs an indexing function. It guides construction of the queues read by task threads in the q-to-dbms stage. Within this framework there are many ways to feed events into the parallel queues. In the case of on-disk queues there are two obvious design options.

Read data out of the THL and split them into separate transaction logs per parallel queue. This is very similar to the in-memory approach, except that the queues are now on disk (or SSD or whatever storage you pick). It can be implemented without adding any extra threads to the parallel store.
Leave all data in THL. Add a cursor for each parallel queue that scans the THL and picks only the transactions that belong in that parallel queue. This requires extra threads to do the scans, hence is more complex to implement.

Both approaches achieve the primary goal, which is to keep the transactions in storage until we actually need them and thereby minimize memory usage. This in turn solves a major problem, namely that individual shards can now be many thousands or even millions of transactions apart in the serial history. Beyond that, it is not completely obvious which approach is better.

For example, option 1 isolates the reads to individual files. This minimizes overall I/O at the cost of making it more random, since reads and writes are spread over many files. Option 2 avoids extra writes and keeps I/O sequential, but introduces a bunch of threads doing the equivalent of table scans across the same patch of storage. Up to a point we can assume that pages are coming out of the OS page cache rather than storage but this assumption will not hold for all operating environments and workloads. The only way to prove the trade-offs is to implement and test. (We may end up implementing both.)

After some discussion internally at Continuent as well as with Harrison Fisk from Facebook, we picked option 2 for the initial implementation. Here is a diagram that shows how it works.

Here is a quick tour of the implementation in Java class THLParallelQueue. This class maintains an in-memory blocking queue for each channel. Each queue has a corresponding read thread that scans the THL and places matching events into the queue. The THLParallelQueue class synchronizes read threads and handles issues like serialization and clean shutdown. Some memory is therefore consumed, for queues, but they are quite small and amount to far less than keeping all transactions in memory.

So much for the theoretical description. If you would like to test on-disk queues yourself, you can get started in three steps.

Download the latest nightly build of Tungsten Replicator to /tmp.
Untar and cd into the resulting release directory.
Install using the new tungsten-installer as described by Guiseppe Maxia.

Here is an example of set-up commands. My test system uses logos1 as the master and logos2 as the slave. The MySQL DBMS build is Percona Server 5.1.54. Tungsten is installed in /opt/tungsten.

# Download and unpack build.
cd /tmp
wget --no-check-certificate https://s3.amazonaws.com/files.continuent.com/builds/nightly/tungsten-2.0-snapshots/tungsten-replicator-2.0.4-154.tar.gz
# Untar.
tar -xvzf tungsten-replicator-2.0.4-154.tar.gz
cd tungsten-replicator-2.0.4-154
# Set up and start replicators.
export TUNGSTEN_HOME=/opt/tungsten
/tmp/tungsten-replicator-2.0.4-154/tools/tungsten-installer \
  --master-slave \
  --master-host=logos1 \
  --datasource-user=tungsten \
  --datasource-password=secret \
  --service-name=percona \
  --home-directory=${TUNGSTEN_HOME} \
  --cluster-hosts=logos1,logos2 \
  --relay-directory=${TUNGSTEN_HOME}/relay-logs \
  --datasource-log-directory=/usr/local/percona-5.1.54/data \
  --thl-directory=${TUNGSTEN_HOME}/thl-logs \
  --channels=10 \
  --svc-parallelization-type=disk \
  --start-and-report

Note the bold options to select disk queues--"memory" is the other option--and the number of channels. You can confirm you have the right queue installed by running the following command against any slave replicator. You should see the storage class THLParallelQueue in the status output.

$ trepctl -host logos2 status -name stores
Processing status command (stores)...
NAME VALUE
---- -----
doChecksum : false
logDir : /opt/rhodges4/thl-logs/percona
logFileRetention : 7d
logFileSize : 100000000
maximumStoredSeqNo: 0
minimumStoredSeqNo: 0
name : thl
storeClass : com.continuent.tungsten.replicator.thl.THL
NAME VALUE
---- -----
criticalPartition : -1
discardCount : 0
eventCount : 1
headSeqno : 0
maxSize : 10
name : parallel-queue
queues : 10
serializationCount: 0
serialized : false
stopRequested : false
store.0 : THLParallelReadTask task_id=0 thread_name=store-thl-0 hi_seqno=0 lo_seqno=0 read=1 discarded=1 events=0
store.1 : THLParallelReadTask task_id=1 thread_name=store-thl-1 hi_seqno=0 lo_seqno=0 read=1 discarded=1 events=0
store.2 : THLParallelReadTask task_id=2 thread_name=store-thl-2 hi_seqno=0 lo_seqno=0 read=1 discarded=1 events=0
store.3 : THLParallelReadTask task_id=3 thread_name=store-thl-3 hi_seqno=0 lo_seqno=0 read=1 discarded=1 events=0
store.4 : THLParallelReadTask task_id=4 thread_name=store-thl-4 hi_seqno=0 lo_seqno=0 read=1 discarded=1 events=0
store.5 : THLParallelReadTask task_id=5 thread_name=store-thl-5 hi_seqno=0 lo_seqno=0 read=1 discarded=1 events=0
store.6 : THLParallelReadTask task_id=6 thread_name=store-thl-6 hi_seqno=0 lo_seqno=0 read=1 discarded=1 events=0
store.7 : THLParallelReadTask task_id=7 thread_name=store-thl-7 hi_seqno=0 lo_seqno=0 read=1 discarded=1 events=0
store.8 : THLParallelReadTask task_id=8 thread_name=store-thl-8 hi_seqno=0 lo_seqno=0 read=1 discarded=1 events=0
store.9 : THLParallelReadTask task_id=9 thread_name=store-thl-9 hi_seqno=0 lo_seqno=0 read=1 discarded=0 events=0
storeClass : com.continuent.tungsten.replicator.thl.THLParallelQueue
syncEnabled : true
syncInterval : 2000
Finished status command (stores)...

Now for some fine print. On-disk queues are implemented but are still undergoing QA. There are bugs. The most important problem is performance--the latency is a lot higher than expected on some of our systems, which I suspect is due to an as-yet undiagnosed bug. If you try them out now you can expect to hit a few problems. On the other hand, we take any and all feedback quite seriously, so this is your chance provide input and help guide the final implementation. Please log issues on the Tungsten Replicator issue tracker or bring up questions on the tungsten-discuss mailing list.

Finally, if you would like to learn more about the parallel queue implementation, check out the design documentation on our wiki as well as the source code. They are both pretty readable.

Introducing MySQL to MongoDB Replication

2011-05-14T16:49:00.000-07:00

The last article on this blog described our planned MySQL to MongoDB replication hackathon at the recent Open DB Camp in Sardinia. Well, it worked, and the code is now checked into the Tungsten Replicator project. This article describes exactly what we did to write the code and set up replication. You can view it as a kind of cookbook both for implementing new database types in Tungsten as well as setting up replication to MongoDB.

The Team

MySQL to MongoDB replication was a group effort with three people: Flavio Percoco, Stephane Giron, and me. Flavio has worked on MongoDB for a couple of years and is extremely well-informed both on database setup as well as application design. Stephane Giron is a replication engineer at Continuent and has done a substantial amount of the work on data extraction from MySQL, especially row replication. I work on the core execution framework as well as performance.

Getting Started with MongoDB

There were a couple of talks on MongoDB during the first morning of Open DB camp (Saturday May 7th), which Stephane and I dutifully attended to raise our consciousness. We got cracking on implementation around 2pm that afternoon. The first step was to bring up MongoDB 1.8.1 and study its habits with help from Flavio.

MongoDB is definitely easy to set up. You get binary builds from the MongoDB download page. Here is a minimal set of commands to unpack MongoDB 1.8.1 and start the mongod using directory data to hold tables.

$ tar -xvzf mongodb-osx-x86_64-1.8.1.tgz
$ cd mongodb-osx-x86_64-1.8.1
$ mkdir data
$ bin/mongo --dbpath data
(... messages ...)

You connect to mongod using the mongo client. Here's an example of connecting and creating a table with a single row.

$ bin/mongo localhost:27017
MongoDB shell version: 1.8.1
connecting to: localhost:27017/test
> use mydb
switched to db mydb
> db.test.insert({"test": "test value", "anumber" : 5 })                 
> db.test.find()
{ "_id" : ObjectId("4dce9a4f3d6e186ffccdd4bb"), "test" : "test value", "anumber" : 5 }
> exit

This is schema-less programming in action. You just insert BSON documents (BSON = Binary JSON) into collections, which is Mongolese for tables. MongoDB creates the collection for you as soon as you put something in it. The automatic materialization is quite addictive once you get used to it, which takes about 5 minutes.

The MongoDB client language is really handy. It is based on JavaScript. There are what seem to be some non-Javascript commands like "show dbs" to show databases or "show collections" to list the tables. Everything else is object-oriented and easy to understand. For example, to find all the records in collection test, as we saw above, you just connect to the database and issue a command on the local db object. Collections appear as properties of db, and operations on the collection are methods.

It helps that the MongoDB folks provide very accessible documentation, for example a SQL to MongoDB translation chart. I put together a little practice program using the MongoDB Java driver to insert, referring to the Javadoc for the class library when in doubt about API calls. There are also a couple of very helpful examples, like this one, included with the driver.

All told, setup and orientation took us about 45 minutes. It helped enormously that Flavio is a MongoDB expert, which minimized flail considerably.

Implementing Basic Replication from MySQL to MongoDB

After setup we proceeded to implement replication. Here is an overview of the replicator pipeline to move data from MySQL to MongoDB. Pipelines are message processing flows within the replicator.

Direct pipelines move data from DBMS to another within a single replicator. They are already a standard part of Tungsten Replicator and most of the code shown above already exists, except for the parts shown in red. Before we started, we therefore needed to set up a replicator with a direct pipeline.

We first built the code according to the instructions on the Tungsten project wiki, uploaded the binary to our test host, and configured the replicator. First, we ran the Tungsten configure script to set defaults for the MySQL server (user name, extract method, etc.). Next we ran the configure-service command to set up the direct pipeline configuration file. Both commands together look like the following:


./configure
./configure-service -C --role=direct mongodb

The second command created a file called tungsten-replicator/conf/static-mongodb.properties with all the information about the direct pipeline implementation but of course nothing yet about MongoDB.

Now we could start the implementation. To move data to MongoDB, we needed two new components:

A Tungsten RawApplier to apply row updates to MongoDB. RawApplier is the basic interface you implement to create an applier to a database.
A Tungsten Filter to stick column names on row updates after extracting from MySQL. MySQL row replication does not do this automatically, which makes it difficult to construct JSON at the other end because you do not have the right property names.

To get started on the applier I implemented a very simple class named MongoApplier that could take an insert from MySQL, turn it into a BSON document, and add it to an equivalently named database and collection in MongoDB. I added this to the replicator code tree, then built and uploaded tungsten-replicator.jar. (Use 'ant dist' in the replicator directory to build the JAR.)

To start using the new MongoDB applier, we needed to edit the service properties file to use this component instead of the standard MySQL applier that configuration adds by default. To do this, you can open up static-mongodb.properties with your favorite editor. Add the following properties at the bottom of the APPLIERS section.


# MongoDB applier.  You must specify a connection string for the server. 
# This currently supports only a single server. 
replicator.applier.mongodb=com.continuent.tungsten.replicator.applier.MongoApplier
replicator.applier.mongodb.connectString=localhost:27017

Next, you need to fix up the direct pipeline so that the last stage uses the new applier. We located the direct pipeline definition (around line 208 in the properties file) and set the applier to mongodb as shown in the following example.


# Write from parallel queue to database.
replicator.stage.d-pq-to-dbms=com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask
replicator.stage.d-pq-to-dbms.extractor=parallel-q-extractor
replicator.stage.d-pq-to-dbms.applier=mongodb
replicator.stage.d-pq-to-dbms.filters=mysqlsessions
replicator.stage.d-pq-to-dbms.taskCount=${replicator.global.apply.channels}
replicator.stage.d-pq-to-dbms.blockCommitRowCount=${replicator.global.buffer.size}

We then started the replicator using 'replicator start.' At that point we could do the following on MySQL:


mysql> create table foo(id int primary key, msg varchar(35));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into foo values(1, 'hello from MySQL!');
Query OK, 1 row affected (0.00 sec)

...And within a second we could see the following over in MongoDB:


> show collections
foo
system.indexes
> db.foo.find();
{ "_id" : ObjectId("4dc55e45ad90a25b9b57909d"), "1" : "1", "2" : "hello from MySQL!" }

This kind of progress was very encouraging. It took roughly 2 hours to get to move the first inserts across. Compared to replicating to a new SQL database like Oracle that's lightning fast. However, there were still no property names because we were not adding column names to row updates.

Meanwhile, Stephane had finished the column name filter (ColumnNameFilter) and checked it in. I rebuilt and refreshed the replicator code, then edited static-mongodb.properties as follows to add the filter. First put in the filter definition in the FILTERS section:


# Column name filter.  Adds column name metadata to row updates.  This is
# required for MySQL row replication if you have logic that requires column
# names.
replicator.filter.colnames=com.continuent.tungsten.replicator.filter.ColumnNameFilter

Next, make the first stage of the direct pipeline use the filter:


# Extract from binlog into queue.
replicator.stage.d-binlog-to-q=com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask
replicator.stage.d-binlog-to-q.extractor=mysql
replicator.stage.d-binlog-to-q.filters=colnames,pkey
replicator.stage.d-binlog-to-q.applier=queue

We then restarted the replicator. Thereupon, we started to see inserts like the following, complete with property names:

> db.foo.find()
{ "_id" : ObjectId("4dc77bacad9092bd1aef046d"), "id" : "25", "data" : "data value" }

That was better, much better! To this point we had put in exactly 2 hours and 45 minutes wall clock time. It was enough to prove the point and more than enough for a demo the next day. The hackathon was a rousing success.

Further Development

Over the next couple of days I rounded out the MongoApplier to add support for UPDATE and DELETE operations, as well as to implement restart. The full implementation is now checked in on code.google.com, so you can repeat our experiences by downloading code and building yourself or by grabbing one of the Tungsten nightly builds.

Restart is an interesting topic. Tungsten uses a table to store the sequence number of the last transaction it applied. We do this by creating an equivalent collection in MongoDB, which is updated after each commit. There is a problem in that MongoDB does not have transactions. Each update is effectively auto-commit, much like MyISAM table type on MySQL. This means that while Tungsten can restart properly after a clean shutdown, slave replication is not crash safe. Lack of atomic transactions is a bigger issue with MongoDB and other NoSQL databases that goes far beyond replication. For now, this is just how Tungsten's MongoDB support works.

Speaking of things that don't work, the current implementation is a prototype only. We have not tested it with more than a few data types. It only works with a single MongoDB daemon. It does not set keys properly or specify indexes on tables. There are no guarantees about performance, except to say that if you had more than a small amount of data it would be quite slow. (OK, that's a guarantee after all.)

Epilog

Overall all the hackathon was a great success, not to mention lots of fun. It went especially well because we had a relatively small problem and three people (Stephane, Flavio, and Robert) with complementary skills that we could combine easily for a quick solution. That seems to be a recipe for succeeding on future hackathons.

From a technical point of view, it helped that MongoDB is schema-less. Unlike SQL databases, just adding a document materializes the table in MongoDB. This made our applier implementation almost trivially easy, because processing row updates takes only a few dozen lines of Java code in total. It also explains why a lot of people are quite attached to the NoSQL programming model.

I am looking forward to learning a lot more about MongoDB and other NoSQL databases. It would take two or three weeks of work to get our prototype to work with real applications. Also, it looks as if we can implement replication going from MongoDB to MySQL. According to Flavio there is a way to search the transaction log of MongoDB as a regular collection. By appropriately transforming BSON objects back to SQL tuples, we can offer replication back to MySQL.

There are many other lessons about MongoDB and NoSQL in general but it seems best to leave them for a future article when I have more experience and actually know what I'm talking about. Meanwhile, you are welcome to try out our newest Tungsten replication feature.

Time for a SQL/NoSQL Group Hug

2011-05-03T07:13:00.000-07:00

European Open Database Camp 2011 is this weekend in the hills above Cagliari, Sardinia. In honor of the increasing number sites that use both NoSQL and SQL databases, I am going to be running a MySQL to NoSQL Hackathon to prototype Tungsten Replicator support for transferring data from MySQL to MongoDB. The conference will have at least one well-informed MongoDB expert, so we should have enough critical mass to get this done. It helps that I'll be completely jet-lagged after flying in from the US and unable to sleep anyway.

Over the past year SQL vs. NoSQL rants have started to abate as people get down to the practical work to make both types of systems work most effectively, often within the same site if not the same application. I have spoken to a small but growing number of users who want to move data between MySQL and Cassandra, HBase, MongoDB, and others. (Here's a typical example from StackOverflow.) Some of them are even willing to pay money for Tungsten Replicator implementations. You know there is a real need when that happens. I expect such requests will grow as more applications implement NoSQL stores and have to set up feeds to or from SQL. Heterogeneous replication in this sense is a good proxy for solution maturity, and at least a couple of the NoSQL stores seem to be getting there.

The MySQL to MongoDB hackathon is our first crack at this problem. There is no guarantee the results will be especially usable, because Tungsten will not have a built-in solution for SQL to JSON mapping. (I'm still noodling about that problem--suggestions welcome.) Still, we will start to understand the problem space better and pave the way for more robust solutions over the course of the next year or so. I'm looking forward to learning more a lot more about non-SQL stores along the way.

If you are attending the conference you can hear about our results in person. You can also tune into the #tungsten IRC channel at irc.freenode.net starting Friday May 5th around 10pm GMT. I'll post regular updates over the weekend. Feel free to drop by on IRC to see how we are doing or even to help out. We will not single-handedly end the SQL/NoSQL wars, but at least we can help them learn how to share.

Tungsten Supports Logical Replication on PostgreSQL

2011-05-02T16:53:00.000-07:00

Thanks to my colleague Linas Virbalas, Tungsten Replicator has just taken the next step to support full logical replication for PostgreSQL. Linas posted an article today on his new blog describing PostgreSQL logical replication using SLONY triggers. I saw a demo of his implementation and was really impressed. For more information you should read the article, which provides an excellent description of how Tungsten replicates from SLONY logs.

It is pretty exciting whenever Tungsten replicates data to or from a new DBMS type, but PostgreSQL logical replication is really special. Tungsten Replicator has been able to manage native warm standby and log streaming replication more than a year using a script-based plugin. This is fine for offering copies for failover but rather limited for problems beyond simple availability. Logical replication opens up a whole new set of solutions that include multi-master replication, heterogeneous data transfer, and large-scale read scaling on replicas. It is also a key building block for advanced clustering capabilities like zero-downtime upgrade as well as for building multi-tenant systems for SaaS applications. After years of working on these problems for MySQL we are glad to finally attack them head on for PostgreSQL as well.

There is still a lot of work to achieve fully functional, easy-to-use PostgreSQL logical replication, but the work that Linas described gives Tungsten a clear path forward. I expect we will make rapid progress, because so many of the other parts of Tungsten Replicator are already in place. Meanwhile, Linas has put together a very readable blog that should make interesting reading for years to come.

Belated Thanks to MySQL Community

2011-04-20T17:33:00.000-07:00

Tungsten Replicator won O'Reilly Application of the Year at the 2011 O'Reilly MySQL Conference, together with Percona's XtraBackup. Giuseppe Maxia also received an award for Community Contributor of the Year. Having now worked with Giuseppe for almost half a year I know from personal experience his reward is truly deserved. All in all we had a very good week, especially since the replicator award was a complete surprise.

Things were so busy during and after the MySQL conference it was difficult to write a timely thank-you note. I hope it's not too late to thank the committee now for both awards.

More importantly, I would like to thank the MySQL community as a whole. Replicated data is the lifeblood of MySQL applications. There has been a long history of innovation both within the MySQL engineering team as well as the community as a whole. Working on replication for MySQL is a bit like building cars to operate on the German Autobahn. If you can compete here you can compete anywhere.

Settling in at code.google.com

2011-04-14T12:22:00.000-07:00

Tungsten Replicator code is now fully open source and published on code.google.com. Here is our new home in case you do not yet know it: http://code.google.com/p/tungsten-replicator. I hope you will visit our new digs and admire the furniture.

The fact that the replicator is now fully open source under GPL V2 is kind of old news, so I would instead like to talk about something else: our initial experience setting up the replicator project at code.google.com. In a nutshell, it has been excellent. There are several things that stand out.

The site is incredibly easy to use. You can customize the home page, add members, add external links, etc. quickly and without having to resort to help.
It has everything we need. The front page is excellent--clean but also all the information users need to get started. Useful features like issue trackers and Google Groups are cleanly integrated.
It is very fast.
So far it seems to have just the right mix of open and closed for our project. Anybody can post to the groups or log issues, but only committers on the project have write access to code and ability to move bugs through issue status.

The only problems I have run into personally involve SVN code access. For example, say you check out using the http rather than https URL as in:
svn co http://tungsten-replicator.googlecode.com/svn/trunk/builder

If you edit something and try to check in you get a message like the following:

$ svn commit -m "This does not work"
svn: Commit failed (details follow):
svn: Server sent unexpected return value (405 Method Not Allowed) in response to MKACTIVITY request for '/svn/!svn/act/8d94e398-83ba-46f3-aae2-bd10cb707c4b'
svn: Your commit message was left in a temporary file:
svn:    '/home/rhodges/google/tungsten-replicator/builder/svn-commit.tmp'

This message is definitely in the "not helpful" category. Perhaps it is some sort of defense against evildoers. However, this might be subversion behavior and nothing to do with Google. If you receive such a message, run svn info to check the SVN URL. If you see http instead of https you have found the cause. Unfortunately the cure seems to be to check out again properly in another location, copy in your changes, and then commit.

Site credentials are a more insidious problem. Android phone users need to have a Google GMail account to access updates and download apps. (At least that's true for my provider.) Browsers like Firefox do not keep accounts separated properly, so you may run into account confusion when you first get started. On Mac OS X you can get the wrong account in your keychain, which in turn leads to more confusing error messages. This is a Google problem. There is a creeping form of web single-signon using Google, Facebook, and other accounts as identifiers with unintended side-effects for work and personal interactions. It makes you wonder what other problems are out there.

But I digress. As far as the project is concerned the issues look pretty minor. At this point I would recommend code.google.com wholeheartedly for open source projects.

p.s., Tungsten Replicator 2.0.2 is on the way. More on that in another post.

O'Reilly Conference Tungsten Talks and Some Welcome Open Source Progress

2011-04-02T10:50:00.000-07:00

The O'Reilly MySQL 2011 conference is coming up fast. It should be a good conference as it covers the increasingly diverse MySQL community and MySQL alternatives very well. As usual, there are some painful choices about which talks to attend. I'm doing two talks myself that I hope you have on your list:

Curing Replication Deprivation with Tungsten -- A tutorial together with my colleague Ed Archibald. It covers everything you ever wanted to know about how to use parallel replication, handle multi-master/multi-source, replication to PostgreSQL/Oracle, etc. We will have a short section at the end about how to build full clusters with Tungsten Enterprise. Giuseppe Maxia is threatening to join and do some of his famous demos. Apparently doing a tutorial on replication in the morning is not enough to tire him out.
Preparing for the Big Oops: How to Build Disaster Recovery Sites for MySQL -- Survey of methods as well as things to have in mind when building a disaster recovery site. This will cover everything I can think of, not just Tungsten.

If you do not get your fill from our tutorial, Ed Archibald will also be doing another talk on Tungsten Enterprise explaining how you can build Database-as-a-service using Tungsten.

Finally, the open source news. We have been working on getting Tungsten Replicator fully open sourced including features like parallel replication, cool multi-master capabilities, and our fast disk log. Getting there required jumping through a few hoops internally, but I'm happy to say the jumping is done. As soon as we finish a couple of merges between branches we will post a full copy of Tungsten Replicator 2.0 on our new home at code.google.com under a GPL V2 license.

It will take us a while to get used to the new bug tracker, wikis, and forums based on Google Groups, but we should be starting to settle in by 7 April when Giuseppe presents Advanced MySQL Replication for the Masses at the MySQL SFO meetup. Come visit us!

Slouching towards Multi-Master Conflict Resolution

2011-03-30T02:08:00.000-07:00

This title is not nearly as snappy as Yeats' line from "The Second Coming," but it will have to do. Conflict resolution has been a holy grail for us on Tungsten--we had people asking for it when MySQL was still MySQL AB, lo these many years ago. Well, it's finally starting to happen after about a year of work.

Let's start with a simple multi-master configuration. To replicate bi-directionally between two masters, you typically run Tungsten replicator on each master with two replication services on each master. The first local service reads the master log. The second remote service is a slave of the other master. It looks like the following picture:

One of the big problems in multi-master replication is to avoid loops. Let's say you update something in the SJC master. The transaction replicates to NYC and appears in the log. Then it wants to replicate back to SJC and appear in the log there. If we don't do anything, the poor transaction will loop forever.

Tungsten solves this problem using a filter named BidiRemoteSlaveFilter that runs on slave pipelines It has a simple job, which is to identify where transactions originated and keep local transactions that are returning from the other master from being applied again. We use a variety of tricks to "tag" SQL in a way that allows us to deduce the origin--the most common are comments added to SQL statements or specially formatted row updates that we add to user transactions. As long as you set things up properly and don't break some simple rules you can now replicate bi-directionally between 2 or more masters.

This brings us to conflict resolution. Conflict resolution prevents incompatible transactions from colliding with each other. The BidiRemoteSlaveFilter does a simple form of conflict resolution by preventing transactions from the local service from looping back and being applied again. However, it's a very short hop to filters that that address application conflicts.

Here's a simple example that is next on my list to implement. It is not unusual to split customers in multi-tenant applications across sites so that they have active updates on only one site and backup copies on the others. You could imagine a filter that works off a simple file like the following:

[sjc]
   sharks
   athletics
[nyc]
   mets
   yankees

This tells the filter that transactions with shard ID sharks and athletics are allowed on the sjc master from local application. However, if the nyc master updates these, we will reject the updates when the nyc remote service tries to apply them and generate an error message in the log, or perhaps put them in a special log file for later inspection. You could even generate a dummy update on the local master that would result in the sjc data being sent back over to correct the nyc DBMS information via replication.

What we have just done is implemented conflict resolution for a system-of-record approach to multi-site data management. There are many types of conflicts as well as ways to manage them. Tungsten Replicator filters have a lot of potential to implement other schemes as well. Filters are pluggable, so there is a convenient escape hatch if you need to do specialized rules of your own. Meanwhile, there is plenty of scope for Tungsten development to provide useful conflict resolution mechanisms.

The Yeats poem I referred to at the beginning of the article is one of my all-time favorites. Here are the last two lines:

And what rough beast, its hour come round at last,
Slouches towards Bethlehem to be born?

This could certainly describe a lot of software projects. However, Tungsten is not like that at all. We Tungsten engineers wear white lab jackets with pocket protectors and have shiny clipboards to take notes on our breakthroughs. We rarely slouch.

P.s. Speaking of Tungsten engineers my colleague Giuseppe Maxia and I will be doing a webinar on multi-master replication on Thursday March 31st. It will be mostly technical with only a small amount of marketing fluff. As usual Giuseppe has cooked up a cool demo. Sign up at www.continuent.com if you would like to find out more.

Parallel Replication Using Shards Is the Only Workable Approach for SQL

2011-03-22T22:12:00.000-07:00

There have been a couple of recent blog articles (here and here) asking for parallel replication based on something other than schemas. These articles both focus on the problem of parallelizing updates within a single MySQL schema. I read these with great interest, not least because they both mentioned Tungsten (thanks!) and also found that our schema-based parallelization approach is too limited. It is therefore worth a short article explaining exactly what the Tungsten approach is and why we chose it.

First of all, Tungsten does not exactly use schema-based parallel replication. Tungsten is actually based on what I call the serialized shard model of replication. We assign global transaction IDs to all transactions, which means that for any particular set of transactions we can always figure out the correct serialization and apply in the right order. This is true even if the transactions travel across independent replication paths or if we have master failover.

Second, we assign a shard ID to all transactions. Shards are independent streams of transactions that execute correctly when applied by themselves in serial order. Shards are typically independent, which means transactions in different shards can execute in parallel without deadlocking or corrupting data. This is the case when each shard contains data for a single customer in a multi-tenant application. We also have a notion of "critical shards," which are shards that contain global data, such as shared currency rate tables. Updates in critical shards cause full serialization across all shards.

You can define shards in a variety of ways, but as a practical matter identifying durable shards inside individual MySQL schemas is hard for most applications, especially if there are constraints between tables or you have large transactions. Many SQL applications tend to make most of their updates to a small number of very large tables, which makes finding stable dividing lines even more difficult. Schemas are therefore a natural unit of sharding, and Tungsten uses these by default.

Schema-based sharding seems pretty limiting, but for current SQL databases it is really the only approach that works. Here are some important reasons that give you a flavor of the issues.

* Restart. To handle failures you need to mark the exact restart point on each replication apply thread or you will either repeat or miss transactions. This requires precise and repeatable serialization on each thread, which you get with the serialized shard model.

* Deadlocks. If there are conflicts between updates you will quickly hit deadlocks. This is especially true because one of the biggest single thread replication optimizations is block commit, where you commit dozens of success transactions at once--it can raise throughput by 100% in some cases. Deadlocks on the other hand can reduce effective throughput to zero in pathological cases. Shard-based execution avoids deadlocks.

* Ordering. SQL gives you a lot of ways to shoot yourself in the foot through bad transaction ordering. You can't write to a table before creating it. You can't delete a row before it is inserted. Violating these rules does not just lead to invalid data but also causes errors that stop replication. The workarounds are either unreliable and slow (conflict resolution) or impractical for most applications (make everything an insert). To avoid this you need to observe serialization very carefully.

* Throughput. SQL transactions in real systems vary tremendously in duration, which tends to result in individual long transactions blocking simpler parallelization schemes that use in-memory distribution of updates. In the Tungsten model we can solve this by letting shard progress vary (by hours potentially), something that is only possible with a well-defined serialization model that deals with dependencies between parallel update streams. I don't know of another approach that deals with this problem.

If you mess up the solution to any of the foregoing problems, chances are good you will irreparably corrupt data, which leads to replication going completely off the rails. Then you reprovision your slave(s). The databases that most need parallel replication are very large, so this is a multi-hour or even multi-day process. It makes for unpleasant calls with customers when you tell them they need to do this.

I don't spend a lot of time worrying that Tungsten parallel replication is not well suited to the single big schema problem. So far, the only ways I can think of making it work scalably require major changes to the DBMS or the applications that use it. In many cases your least costly alternative may be to use SSDs to boost slave I/O performance.

My concerns about Tungsten's model lie in a different area. The serialized shard model is theoretically sound--it has essentially the same semantics as causally dependent messaging in distributed systems. However, if we fail to identify shards correctly (and don't know we failed) we will have crashes and corrupt data. I want Tungsten either to work properly or tell users it won't work and degrade gracefully to full serialization. If we can't do one of these two for every conceivable sequence of transactions that's a serious problem.

So, to get back to my original point, serialized shards are the best model for parallel replication in SQL databases as we find them today. I suspect if you look at some of the other incipient designs for parallel replication on MySQL you will find that they follow this model in the end if not at first. I would think in fact that the next step is to add MySQL features that make sharded replication more effective. The drizzle team seems to be thinking along these lines already.

Is Apple Good for Innovation?

2011-03-20T20:56:00.000-07:00

Just about everyone on the planet agrees that Apple products are the soul of innovative design. But are they good for innovators? For me the answer is "not so much."

I have been using Apple laptops and iPhones for years. As a software developer, I have a list of annoyances with Mac OS X starting with Apple's incomprehensible management of Java. However, Mac OS X is far more productive than MS Windows, with its viruses, crummy OS releases, and bloatware. iPhones are close to worthless as telephones in the area where I live in large part due to ATT's network. But you can now switch to Verizon, so that's not such a problem either.

The real problem with Apple is that their products are closed. Want to install a new file system? Not here. Want to pick a different motherboard to play around with power utilization? Try somewhere else. Want to know what the OS is really doing under the covers or (gasp) inspect the source code? Dieu forfend!

Innovation in my chosen field of databases is increasingly based on breaking down the dividing lines between hardware and software to manage massive quantities of data economically and quickly. The more I learn about hardware, the less I want fully integrated products. I want devices I can interact with and learn from. I want visibility into internals. I want works-in-progress, not ready-made perfection. In short, I want open platforms that give me the parts but do not tell me what to build with them.

A few weeks ago my iPhone dropped on the floor and shattered. The replacement is a Droid 2 Global running Android. The user interface is clumsy. You have to watch out for viruses again. But the hardware is lightning fast. There is a free-for-all of people inventing new Android applications. The source code for Android itself is available on code.google.com. The open nature of Android is rapidly making it the locus of innovation for mobile devices. I feel at home already.

Tuning Tungsten Parallel Replication Performance

2011-03-20T02:15:00.000-07:00

Last month my colleague Giuseppe Maxia described how to operate Tungsten parallel replication. Since then we have been doing a good bit of benchmarking on both synthetic as well as real production loads. In this article I would like to follow up with some tips about how you can goose up parallel replication performance. These apply to Tungsten Replicator 2.0.1, which you can find here.

The first way to get good performance with Tungsten is to have the right workload. As explained in an earlier article on this blog, Tungsten parallel replication works by replicating independent databases (aka shards) in parallel. Here is a picture that summarizes what is going on.

If you have a lot of schemas, if the updates are distributed evenly across schemas, and if you don't have many dependencies between schemas that require full serialization, parallel replication can speed things up significantly for I/O-bound workloads. For example, Tungsten runs three times faster than MySQL native replication on large datasets when the slave is catching up to the master following mysqld restart.

Catch-up is a famous slave lag case and one where Tungsten can be quite helpful. (I think we will be faster in the future, but this is a good start.) Nevertheless, there's a chance you'll need to do a bit of tuning to see such benefits yourself.

Tungsten currently uses a structure called a parallel queue to enable parallelization. The parallel queue typically sits at the end of a replicator pipeline in front of the parallel apply threads, as shown in the following handy diagram.

One key to getting decent parallel replication performance is to watch the parallel queue in operation. In Tungsten Replicator 2.0.1 we introduced a new status command trepctl status -name stores that goes a long way to help diagnose how well parallel replication is performing. Here's a typical example using a 6 channel queue store.

$ trepctl status -name stores
Processing status command (stores)...
NAME                VALUE
----                -----
criticalPartition : -1
discardCount      : 0
eventCount        : 3217
maxSize           : 1000
name              : parallel-queue
queues            : 6
serializationCount: 1
serialized        : false
stopRequested     : false
store.queueSize.0 : 0
store.queueSize.1 : 480
store.queueSize.2 : 310
store.queueSize.3 : 1000
store.queueSize.4 : 739
store.queueSize.5 : 407
storeClass        : com.continuent.tungsten.enterprise.replicator.store.ParallelQueueStore
storeSize         : 2936
syncEnabled       : true
syncInterval      : 100
Finished status command (stores)...

The two most important things to look at are distribution of transactions across queues and serialization. Let's start with transaction distribution. In this particular example we were running a parallel queue with 6 channels but only 5 databases. The distribution therefore looks pretty good. One queue is empty but the other have a fairly even distribution of transactions.

Notice that one queue has exactly 1000 transactions. In Tungsten Replicator 2.0.1 the parallel queue has a maximum size parameter (maxSize), which is set to 1000 for this example run. Once an individual queue hits the maxSize limit, the entire parallel queue blocks. It is not uncommon to see one queue blocking in this way if the replicator is catching up, which is exactly what is happening here. In fact, if the queues are all empty it is possible Tungsten is somehow not supplying transactions to the queue fast enough. That is not a problem here.

Bad workloads on the other hand tend to have a lot of transactions in one or two queues and few or none in all the rest. The following is an example of a possibly bad distribution.


$ trepctl status -name stores
Processing status command (stores)...
NAME                VALUE
----                -----
...
store.queueSize.0 : 0
store.queueSize.1 : 4
store.queueSize.2 : 3
store.queueSize.3 : 972
store.queueSize.4 : 0
store.queueSize.5 : 15
...
Finished status command (stores)...

If you see such skewed distributions persistently, you may want to try to adjust the queue partitioning using the shard.list file. The default parallel queue partitioning algorithm hashes shards into channels. This does not always gives optimal performance if your shards mostly happen to hash into the same channel. The other possibility is that the workload is just badly distributed across databases.

You can decide whether the workload or partitioning is at fault using the trepctl status -name shards command. Here's an example.


$ ./trepctl status -name shards
Processing status command (shards)...
NAME                VALUE
----                -----
appliedLastEventId: 000007:0000000000000384;20
appliedLastSeqno  : 1471201
appliedLatency    : 0.0
eventCount        : 6
shardId           : #UNKNOWN
stage             : d-pq-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: 000005:0000000326365895;41
appliedLastSeqno  : 1470999
appliedLatency    : 0.0
eventCount        : 311605
shardId           : db1
stage             : d-pq-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: 000005:0000000326512277;95
appliedLastSeqno  : 1471200
appliedLatency    : 0.0
eventCount        : 298522
shardId           : db2
stage             : d-pq-to-dbms
...

This shows that the distribution of transactions between the db1 and db2 databases is pretty even. If you have many databases with roughly even values in the eventCount parameter, the workload is well suited for parallelization. In that case you may want to assign shards explicitly in the shard.list file if you don't like the distribution in the parallel queue.

Meanwhile, the previous example shows an example of another potential problem. We also see counts for #UNKNOWN, which is a special shard ID that means "I could not tell what schema this is." #UNKOWN transactions can occur if Tungsten cannot parse a SQL statement properly or there is a transaction that updates multiple schemas. In either case, Tungsten serializes the parallel queue.

However it occurs, serialization is a performance killer because it means we have to block the parallel queue until all parallel transactions complete, execute one or more transactions serially, and then reopen the parallel queue. You can see how often this is happening from the serializationCount value on trepctl status -name stores. For many workloads a serializationCount value that is more than a few percent of the number in eventCount means the entire transaction stream is effectively serialized.

If the serialization is occurring due to #UNKNOWN shards, you may be able to improve things using a new value in the replicator service properties file that was added in version 2.0.1. It controls whether we assign the shard ID using the default schema even if Tungsten cannot tell from the SQL command what you are doing.


# Policy for shard assignment based on default database.  If 'stringent', use
# default database only if SQL is recognized.  For 'relaxed' always use the
# default database if it is available.
replicator.shard.default.db=relaxed

Setting the parameter to relaxed can help quite a bit if the problem is due to unusual SQL that confuses the parser. On one workload we were able to reduce the serializationCount from about 10% of transactions to 0% in this way. We then saw the expected speed-up from parallel replication.

Understanding Tungsten Replication Services

2011-03-07T18:46:00.000-08:00

If you follow Giuseppe Maxia's Datacharmer blog you have seen several recent articles on Tungsten Replicator. Giuseppe and I work closely together on replication at Continuent, and I have promised a matching set of articles about replication internals that match the practical introduction provided by Giuseppe. In this first article I will describe replication services, which are message processing flows that run in the Tungsten Replicator.

Unlike many replication engines, Tungsten Replicator can run multiple replication services concurrently. There is a central management interface that allows you to start new replication services without disturbing services that are already running. Each replication service also has its own management interface so that you can put the loaded service online, offline, etc. without disturbing other replication work. As Tungsten is written in Java, the management interfaces are based on JMX, a standard administrative interface for Java applications.

Here is a simple diagram that shows a Tungsten Replicator with two replication services named fra and nyc that replicate from separate DBMS masters in Frankfurt and NYC into a single slave in San Francisco. You can immediately see the power of replication services--a single Tungsten Replicator process can simultaneously replicate between several locations. Replication services are an important building block for the type of complex setups that Giuseppe Maxia discussed in his blog article on Replication Topologies.

Users who are handy with Java can write their own programs to manipulate the JMX interfaces directly. If not, there is the trepctl utility, which is supplied with the Tungsten Replicator and works off the command line.

If the Tungsten Replicator architecture reminds you of a Java application server, you are absolutely right. Java VMs have a relatively large resource footprint compared to ordinary C programs, so it is typically more efficient to put multiple applications in a single VM rather than running a lot of individual Java processes. Tungsten replication services follow the same design pattern, except that instead of serving web pages they replicate database transactions.

Let's now look a little more deeply at how Tungsten Replicator organizes replication services. Each replication service runs a single pipeline, which is Tungsten parlance for a configurable message flow. (For more on pipelines, read here.) When the service starts, it loads an instance of a Java class called OpenReplicatorManager that handles the state machine for replication (online, offline, etc.) and provides the management interfaces for the services. The OpenRepicatorManager instance in turn depends on a number of external resources from the file system and DBMS.

Here is another diagram showing how Tungsten Replicator organizes all of the various parts for services. Services need a configuration file for the pipeline, as well as various bits of disk space to store transaction logs and replication metadata. The big challenge is to ensure things do not accidentally collide.

This layout seems a bit complex at first but is reasonably simple once you get used to it. Let's start with service configuration using our fra service as an example.

Service configuration files are stored in the tungsten-replicator/conf directory.   There are up to two files for each service. The static-fra.properties file defines all properties of the service, pipeline organization and properties like the replication role or master address that may change during operation. The dynamic-fra.properties contains overrides to selected properties. For instance, if you switch the replication role from slave to master as part of a failover operation, it goes in the dynamic-fra.properties file. Tungsten Replicator reads the static file first, then applies the overrides when it starts the service.

Next, we have Tungsten transaction logs, also known as the Transaction History Log. This is a list of all transactions to be replicated along with metadata like global transaction IDs and shard IDs. THL files for each service are normally stored in the logs directory at the same level as the tungsten release directory itself. There is a separate directory for each service, as for example logs/fra.

Next we have Tungsten relay logs. These are downloaded binlogs from a MySQL master DBMS from which the replication service creates the Tungsten transaction logs. Not every replication service uses these. They are required when the MySQL master is on another host, or the binlogs are on an NFS-mounted file system, which Tungsten does not parse very efficiently yet. Tungsten relay logs use the same pattern as the THL--everything is stored under relay-logs with a separate subdirectory for each service, for example relay-logs/fra.

Finally, there is metadata in the DBMS itself. Each replication service has a database that it uses to store restart points for replication (table trep_commit_seqno) as well as heartbeats and consistency checks (tables heartbeat and consistency, respectively).   The name of this database is tungsten_servicename as in tungsten_fra.

Setting up services is difficult to do manually, so Tungsten Replicator 2.0 has a program named configure-service that defines new replication services and removes old ones by deleting all traces including the database catalogs. You can find out all about installation and starting services by looking the Tungsten Replicator 2.0 Installation and Configuration Guide, which is located here.

Services have been part of Tungsten Replicator for a while but we have only recently begun to talk about them more widely as part of the release of Tungsten Replicator 2.0.0 in February 2011, especially as we are start to do more work with multi-master topologies. One of the comments we get is that replication services make Tungsten seem complicated and therefore harder to use, especially compared with MySQL replication, which is relatively easy to set up. That's a fair criticism. Tungsten Replicator is really a very configurable toolkit for replication and does far more than MySQL replication or just about any other open source replicator for that matter. Like most toolkits, the trade-off for power is complexity.

We are therefore working on automating as much of the configuration as possible, so that you can set up even relatively complex topologies with just a couple of commands. You'll see more of this as we make additional replicator releases (version 2.0.1 will be out shortly) and push features fully into open source.   Meanwhile, if you have comments on Tungsten 2.0.0 please feel free to post them back to us.

Virtual IP Addresses and Their Discontents for Database Availability

2011-01-30T18:36:00.000-08:00

Virtual IP addresses or VIPs are commonly used to enable database high availability. A standard failover design uses an active/passive DBMS server pair connected by replication and watched by a cluster manager. The active database listens on a virtual IP address; applications use it for database connections instead of the normal host IP address. Should the active database fail, the cluster manager promotes the passive database server and shifts the floating IP address to the newly promoted host. Application connections break and then reconnect to the VIP again, which points them to the new database.

VIP-Based Database Availability Design

Virtual IP addresses are enticing because they are completely transparent to applications--no changes to database API behavior, no changes to connection strings, etc. While virtual IP addresses seem simple, they depend on arcane TCP/IP behavior that is not especially well understood and not always consistent across different TCP/IP implementations. Unless properly managed, virtual IP addresses can induce problems that run the gamut from simple lack of availability to split-brain, which can in turn lead to unrepairable data corruption.

This blog article describes in some detail how virtual IP addresses work, specifically the behavior of Address Resolution Protocol (ARP) which is a core part of the TCP/IP stack that maps IP numbers to hardware addresses and permits VIPs to move. We will then dig into how split-brain arises as a consequence of ARP behavior. Finally we will look at ways to avoid split-brain or at least make it less dangerous when it occurs.

Note: the examples below use MySQL, in part because mysqld has a convenient way to show the server host name. However you can set up the same test scenarios with PostgreSQL or most other DBMS implementations for that matter.

What is a Virtual IP Address?

Network interface cards (NICs) typically bind to a single IP address in TCP/IP networks. However, you can also tell the NIC to listen on extra addresses using the Linux ifconfig command. Such addresses are called virtual IP addresses or VIPs for short.

Let's say for example you have an existing interface eth0 listening on port 192.168.128.114. Here's the configuration of that interface as shown by the ifconfig command:

saturn# ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 08:00:27:ce:5f:8e  
          inet addr:192.168.128.114  Bcast:192.168.128.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fece:5f8e/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:10057681 errors:0 dropped:0 overruns:0 frame:0
          TX packets:8902384 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:6941125495 (6.9 GB)  TX bytes:6305062533 (6.3 GB)

You can allow the eth0 interface to accept traffic for another address 192.168.128.130 using the following simple command.

saturn# ifconfig eth0:0 192.168.128.130 up

This command tells the TCP/IP stack to accept packets directed to IP address 192.168.128.130 as well as the original address 192.168.128.114. This means that if you are running a MySQL server on the host users can connect using either of the following commands:

mysql -utungsten -psecret -h192.168.128.114 (or)
mysql -utungsten -psecret -h192.168.128.130

Finally, you can move VIPs from host to host very easily by dropping them on the first host and binding to them on a second host, as shown in the following example:

saturn# ifconfig eth0:0 192.168.128.130 down
...
neptune# ifconfig eth0:0 192.168.128.130 up

Meanwhile, virtual IP addresses behave in every other way like standard IP addresses. You can put them in DNS, applications can connect to them, etc. VIP-based high availability is therefore a minimally invasive implementation for most applications--about the only requirement is that applications need to reconnect if their connection breaks.

How Virtual IP Addresses Work

To understand the weaknesses of virtual IP addresses for database high availability, it helps to understand exactly how the TCP/IP stack actually routes messages between IP addresses, including those that correspond to VIPs. The following diagram shows moving parts of the TCP/IP stack in a typical active/passive database set-up with a single client host. In this diagram host saturn currently has virtual IP address 192.168.128.130. Neptune is the other database host. Mercury is the client host.

Applications direct TCP/IP packets using IP addresses, which in IPV4 are four byte numbers. The IP destination address is written into the header by the IP layer of the sending host and read by the IP layer on the receiving host.

However, this is not enough to get packets from one host to another. At the hardware level within a single LAN, network interfaces use MAC addresses to route messages over physical connections like Ethernet. MAC addresses are 48-bit addresses that are typically burned into the NIC firmware or set in a configuration file if you are running a virtual machine. To forward a packet from host mercury to saturn, the link layer writes in the proper MAC address, in this case 08:00:27:ce:5f:8e. The link layer on host saturn accepts this packet, since it corresponds to its MAC address. It forwards the packet up into the IP layer for acceptance and further processing.

Yet how does host mercury figure out which MAC address to use for particular IP addresses? This is the job of the ARP cache, which maintains an up-to-date mapping between IP and MAC addresses. You can view the ARP cache contents using the arp command, as shown in the following example.

mercury# arp -an
? (192.168.128.41) at 00:25:00:44:f3:ce [ether] on eth0
? (192.168.128.1) at 00:0f:cc:74:64:5c [ether] on eth0

Note that the ARP cache does not have a mapping for the VIP address 192.168.128.130. Let's say we now make a client connection to the MySQL server at the other end of the VIP address on mercury:

# mysql -utungsten -psecret -h192.168.128.130
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33826
...
mysql>

To route traffic, host mercury gets the IP address to MAC address mapping using an ARP request. You can watch this happen in real time using the tcpdump command to track ARP traffic.

mercury# tcpdump -n -i eth0 arp 
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
09:37:43.755081 ARP, Request who-has 192.168.128.130 tell 192.168.128.110, length 28
09:37:43.755360 ARP, Reply 192.168.128.130 is-at 08:00:27:ce:5f:8e, length 46

Now if you look at the ARP cache again on host mercury, you will see a proper mapping for the VIP in mercury's ARP cache:

# arp -an
? (192.168.128.130) at 08:00:27:ce:5f:8e [ether] on eth0
? (192.168.128.41) at 00:25:00:44:f3:ce [ether] on eth0
? (192.168.128.1) at 00:0f:cc:74:64:5c [ether] on eth0

Now if you go back and look at the picture (or still recall the details), you will notice that the MAC address maps to the NIC on host saturn. This is exactly what we expect since saturn is listening on the corresponding virtual IP address 192.168.128.130.

Virtual IP Addresses and Split-Brain

Most real problems with VIPs appear when you try to move them. The reason is simple: TCP/IP does not stop you from having multiple hosts listening on the same virtual IP address. For instance, let's say you issue the following command on host neptune without first dropping the virtual IP address on saturn.

neptune# ifconfig eth0:0 192.168.128.130 up

Let's further clear the ARP mapping for the virtual IP on mercury using the handy arp -d command and reconnect to MySQL.

mercury # arp -d 192.168.128.130
root@logos1:~# mysql -utungsten -psecret -h192.168.128.130
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 294
...
mysql>

So far so good--we logged into MySQL and everything appears normal. But in fact it is not normal at all. If you run tcpdump and watch the ARP requests during this login, you see the following:

# tcpdump -n -i eth0 arp 
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
09:59:32.643518 ARP, Request who-has 192.168.128.130 tell 192.168.128.110, length 28
09:59:32.643768 ARP, Reply 192.168.128.130 is-at 08:00:27:68:cd:7d, length 46
09:59:32.643793 ARP, Reply 192.168.128.130 is-at 08:00:27:ce:5f:8e, length 46

This is not not just bad--it's very bad. Both saturn and neptune responded to mercury's ARP request! Mercury can pick only one for the mapping; which one it picks depends on timing as well as the exact implementation of the TCP/IP stack. In other words we have a race condition and the winner is essentially random.

You can demonstrate the randomness for yourself with a simple experiment. Let's create a test script named mysql-arp-flush.sh, which clears the ARP cache entry for the VIP and then connects to MySQL, all in a loop.

#!/bin/bash
for i in {1..5}; 
do 
  arp -d 192.168.128.130
  sleep 1
  mysql -utungsten -psecret -h192.168.128.130 -N \
    -e "show variables like 'host%'"
done

If you run the script you'll see results like the following. Note the random switch to Neptune on the fourth connection.

# ./mysql-arp-flush.sh
+----------+---------+
| hostname | saturn  | 
+----------+---------+
+----------+---------+
| hostname | saturn  | 
+----------+---------+
+----------+---------+
| hostname | saturn  | 
+----------+---------+
+----------+---------+
| hostname | neptune |
+----------+---------+
+----------+---------+
| hostname | saturn  | 
+----------+---------+

At this point you have successfully created a split-brain. If you use database replication and both databases are open for writes, as would be the default case with MySQL replication, Tungsten, or any of the PostgreSQL replication solutions like Londiste, your applications will randomly connect to each DBMS server. Your data will quickly become irreparably mixed up. All you can do is hope that the problem will be discovered quickly.

A Half-Hearted Solution using Gratuitous ARP

You might think that it would be handy if the ARP protocol provided a way to get around split-brain problems by invalidating client host ARP caches. In fact, there is such a feature in ARP--it's called gratuitous ARP. While useful, it is not a solution for split-brain issues. Let's look closely to see why.

Gratuitous ARP works by sending an unsolicited ARP response to let hosts on the LAN know that an IP address mapping has changed. On Linux systems you can issue the arping command as shown below to generate a gratuitous ARP response.

neptune# arping -q -c 3 -A -I eth0 192.168.128.130

This tells host neptune to send 3 ARP reply messages with its MAC address for the VIP address. If we look at tcpdump output again, we see the following:

# tcpdump -n -i eth0 arp 
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
11:02:27.154279 ARP, Reply 192.168.128.130 is-at 08:00:27:68:cd:7d, length 46
11:02:28.159291 ARP, Reply 192.168.128.130 is-at 08:00:27:68:cd:7d, length 46
11:02:29.162403 ARP, Reply 192.168.128.130 is-at 08:00:27:68:cd:7d, length 46

Linux hosts that receive the response will generally then update their ARP caches, though as we will see, there are some important exceptions. But first, we need to show the effect of gratuitous ARP on MySQL connections. Let's start with the following ARP cache contents on host mercury. This shows an existing mapping to the MAC address of host neptune, which is what we expect from the previous arping command on neptune.

mercury# arp -an
? (192.168.128.130) at 08:00:27:68:cd:7d [ether] on eth0
? (192.168.128.41) at 00:25:00:44:f3:ce [ether] on eth0
? (192.168.128.1) at 00:0f:cc:74:64:5c [ether] on eth0

Next, we run a loop that connects to MySQL and prints the host name every second. The loop code is shown below and stored in a script named mysql-no-arp-flush.sh. Unlike the previous script this does not release the ARP cache mapping between connections to MySQL.

#!/bin/bash
for i in {1..30}; 
do 
  sleep 1
  mysql -utungsten -psecret -h192.168.128.130 -N \
    -e "show variables like 'host%'"
done

While the test script is running is running, we run an arping command from saturn.
saturn# arping -q -c 3 -A -I eth0 192.168.128.130
What we see in the MySQL output is the following. Once the gratuitous ARP is received, mercury switches its connection from neptune to saturn and stays there, at least for the time being.

mercury# ./mysql-no-arp-flush.sh
+----------+---------+
| hostname | neptune | 
+----------+---------+
+----------+---------+
| hostname | neptune | 
+----------+---------+
+----------+---------+
| hostname | saturn  | 
+----------+---------+
+----------+---------+
| hostname | saturn  | 
+----------+---------+

There is one more interesting property of gratuitous ARP responses. If you issue one during a session, it will cause client sessions to switch between hosts without waiting for a timeout. Here's an example. First login with MySQL and see which host we are on.

root@logos1:~# mysql -utungsten -psecret -h192.168.128.130
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33853
mysql> show variables like 'hostname';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| hostname      | neptune | 
+---------------+---------+
1 row in set (0.00 sec)

Now issue an arping command on saturn using a separate window.
saturn# arping -q -c 3 -A -I eth0 192.168.128.130
Finally, go back and check the host name again in the MySQL session. The session switches over to the other server, which you see at the client level as a lost connection followed by a reconnect.

mysql> show variables like 'hostname'; 
ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:   33854 
Current database: *** NONE ***  
+---------------+--------+ 
| Variable_name | Value  | 
+---------------+--------+ 
| hostname      | saturn |  
+---------------+--------+ 
1 row in set (0.00 sec)

So is gratuitous ARP the solution to virtual IP split-brain? It announces that there is a mapping change, which can make failover work much more quickly. This is useful in its own right. However, it does not solve split-brain.

First, not all TCP/IP stacks even recognize gratuitous ARP responses. Second, gratuitous ARP only takes effect on hosts that actually have a current mapping in their ARP cache. Other hosts will wait until they actually need the mapping and then issue a new ARP request. Finally, ARP mappings automatically time out after a few minutes. In that case the host will issue a new ARP request, which as in the two preceding cases brings us right back to the split-brain scenario we were trying to cure.

Avoiding Virtual IP Split -Brains

Avoiding a VIP-induced split-brain not a simple problem. The best approach is combination of sound cluster management, amelioration, and paranoia.

Proper cluster management is the first line of defense. VIPs are an example of a unique resource in the system that only one host may hold at a time. An old saying that has been attributed to everyone from Genghis Khan to Larry Ellison sums up the problem succinctly:

It is not enough to succeed. All others must fail.

The standard technique to implement this policy is called STONITH, which stands for "Shoot the other node in the head." Basically it means that before one node acquires the virtual IP address the cluster manager must make every effort to ensure no other node has it, using violent means if necessary. Moving the VIP thus has the following steps.

The cluster manager executes a procedure to drop the VIP on all other hosts (for example using ssh or by cutting off power). Once these procedures are complete, the cluster manager executes a command to assign the VIP to the new owner.
Isolated nodes automatically release their VIP. "Isolated" is usually defined as being cut off from the cluster manager and unable to access certain agreed-upon network resources such as routers or public DNS servers.
In cases of doubt, everybody stops. For most systems it is far better to be unavailable than to mix up data randomly.

Cluster managers like Tungsten and Pacemaker handle this kind of process very well. PaceMaker for example has a number of specialized hooks to cut power or otherwise use extreme violence to ensure proper fencing of databases. Tungsten has fewer such hooks but has a much richer set of operations for databases and also has a wide set of connectivity options for HA besides using VIPs.

Incidentally, you want to be very wary about re-inventing the wheel, especially when it comes to DBMS clustering and high availability. Clustering has a lot of non-obvious corner cases; even the "easy" problems like planned failover are quite hard to implement correctly. You are almost always better off using something that already exists instead of trying to roll your own solution.

Amelioration is the next line of defense, namely to make split-brain situations less dangerous when they actually occur. Failover using shared disks or non-writable slaves (e.g., with DRBD or PostgreSQL streaming replication) have a degree of protection because it is somewhat harder to have multiple databases open for writes. However, it is definitely possible and the cluster manager is your best bet to prevent this. However, when using MySQL with either native or Tungsten replication, databases are open and therefore susceptible to data corruption, unless you ensure slaves are not writable.

Fortunately, this is very easy to do in MySQL. To make a database readonly to all accounts other than those with SUPER privilege, just issue the following commands to make the server readonly and ensure the setting is in effect:

neptune# mysql -uroot -e "set global read_only=1"
neptune# mysql -uroot -e "show variables like 'read_only'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    | 
+---------------+-------+

This protects you not just in cases of actual failover but also from administrative mistakes or software failures that switch the VIP by accident.   Many cluster managers implement read-only slaves.   Tungsten clustering has explicit support for read-only slaves. Other cluster managers like MMM and Pacemaker can do the same.

Lastly paranoia is always a good thing. You should test the daylights out of clusters that depend on VIPs before deployment, and also check regularly afterwards to ensure there are no unexpected writes on slaves. Regular checks of logs are a good idea. Another good way to check for problems in MySQL master/slave setups is to run consistency checks.   Tungsten Replicator has built-in consistency checking designed for exactly this purpose. You can also run Maatkit mk-table-checksum at regular intervals.   Another best practice is to "flip" masters and slaves on a regular basis to ensure switch and failover procedures work properly.   Don't avoid trouble--look for it!

Conclusion and Note on Sources

Virtual IP addresses are a convenient way to set up database high availability but can lead to very severe split-brain situations if used incorrectly.   To deploy virtual IP addresses without problems you must first of all understand how they work and second use a sound cluster management approach that avoids split-brain and minimizes the impact if it does occur. As with all problems of this kind you need to test the implementation thoroughly before deployment as well as regularly during operations. This will help avoid nasty surprises and corrupt data that are otherwise all but inevitable.

Finally it is worth talking a bit about sources. I wrote this article because I could not find a single location that explained virtual IP addresses in a way that drew out the consequences of their behavior for database failover. That said, there are a couple of good general sources for information on Internet tools and high availability:

http://linux-ip.net/ -- Guide to IP Layer Network Administration with Linux.
http://linux-ha.org/ -- Linux HA wiki pages

Beyond that you can look at general networking sources like Radia Perlman's Interconnections, Second Edition or Internetworking with TCP/IP by Douglas Comer. These are more high-level. If you get really desperate for details, try the RFCs, for example RFC-826, which is the original specification for ARP. Some of them are surprisingly good reads even 30 years after the fact.

Tungsten Replicator Overview Webinar

2011-01-25T23:14:00.000-08:00

On Thursday January 27th at 10am PST I will doing a webinar on Tungsten Replicator together with my colleague Giuseppe Maxia. The title is "What MySQL Replication Cannot Do. And How to Get Around It." Basically it is a nuts and bolts description of Tungsten Replicator capabilities like multi-master replication, failover, parallel apply, and using replication for zero-downtime upgrade. If you have ever wanted an in-depth look at the Tungsten Replicator this is a good opportunity.

During 2010 we implemented an amazing number of new replication features ranging from pipelines early in the year to fast disk logs, multiple replication services per process, bi-directional replication, and parallel apply by the end. We will be building out all of these in the coming year and releasing increasingly capable features into open source as well.

This presentation is part of Continuent's regular webinar series which means we will also talk a bit about commercial products and services at the end. However, it's mostly cool replication stuff. You can sign up on the Continuent webinar page. Hope to see you there.

Fixing Replication with Replication

2011-01-11T22:30:00.000-08:00

A couple of days ago I ran into a Tungsten Replicator case where several MySQL tables became corrupted on slaves and needed to be restored from the master. We identified the tables that had problems fairly quickly using Tungsten Replicator's consistency checks. However, that led to another problem: how to restore the slave tables efficiently from the master. The MySQL server in question processes around 10M tranactions per day--there is virtually no downtime. Though the tables were not large, we could not be sure whether they were in use.

Fortunately, you can use a simple MySQL trick to get all the rows of a table to replicate through to slaves. The idea is to dump the table, delete the rows, then reload it again. The delete and subsequent reload replicate out to slaves, after which everything is consistent again. Let's say we have a table called tpcb.history that needs to be fixed. Login with mysql and run the following commands:

BEGIN;
SELECT * FROM tpcb.history 
  INTO OUTFILE '/tmp/tpcb.history.dmp' FOR UPDATE;
DELETE FROM tpcb.history;
LOAD DATA INFILE '/tmp/tpcb.history.dmp' REPLACE
  INTO TABLE tpcb.history FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n';
COMMIT;

You can do the reload several ways in MySQL, but this particular code has some advantages over other approaches, such as using LOCK TABLES. First, it uses a transaction, so if something goes wrong the changes roll back and you do not lose your data. Second, the SELECT ... FOR UPDATE locks your data and ensures serialization. You can run this while applications are running without problems.

This seems useful enough that I put together a simple script called reload-table.sh with a README and checked them into the Tungsten Replicator codeline on SourgeForge.net. You can refresh the same table shown above using the following command:

[sudo] ./reload-table-b.sh -u tungsten -p secret -t tpcb.history

I tested the reload using Tungsten 1.3.1 on MySQL 5.1 with statement replication. However, it would work equally well with row replication. Moreover, you can do the same trick in MySQL replication, as this involves base replication capabilities that are directly equivalent. There are a few caveats: you need to use InnoDB (or another transactional engine), large tables may be a problem, and you would need to tread carefully in cases where tables contain referential constraints. Finally, it would be wise to save the master table somewhere else before running the script.

Interested in Sponsoring Tungsten Open Source Features?

2010-12-12T21:52:00.000-08:00

Over the last few months I have been pleasantly surprised by the number of people using open source builds of Tungsten. My company, Continuent, has therefore started to offer support for open source users and will likely expand these services to meet demand.

There have also been a number of requests to add specific features to open source builds, especially for replication. We have added a few already but are now considering pushing even more features into open source if we can find sponsors. These add to a number of great features already in open source like global transaction IDs, MySQL 5.0/5.1, basic drizzle replication, transaction filtering, and many others.

Do you have special replication or clustering features you would like to see added to Tungsten? Specialized MySQL to PostgreSQL replication? Management and monitoring commands? Cool parallel replication problems? High-performance logging? Weird multi-master topologies? Talk to us about sponsoring new open source features. We're happy to do projects that solve interesting problems, benefit the open source databases community, and help grow Tungsten as a product.

Visit the Continuent website or send email directly to robert dot hodges at continuent dot com.

It's All about the Team

2010-11-07T09:15:00.000-08:00

Earlier this week Giuseppe Maxia blogged about joining Continuent as Director of QA. Creating high quality systems for distributed data management is a hard but fascinating problem. I have been hooked on it myself for many years. Guiseppe brings the creativity as well as humor our team needs to nail this problem completely. I'm therefore delighted to know he will be focused on it.

That said, I'm even happier for another reason. Beyond solving any single problem, Giuseppe strengthens an already strong team. Ed Catmull of Pixar gave a great speech a few years ago about managing creative teams and why successful companies eventually fail. Among other things he asked the question whether it is the idea or the people who implement it that count most. His conclusion: great teams implement good ideas to build great products. But even more important, great teams can turn bad ideas into good ones, then go on to build great products from those ideas too. Pixar has proved this many times over.

I believe strongly in the power of great teams to create great products. Giuseppe, welcome to the team.

Parallel Replication on MySQL: Report from the Trenches

2010-10-24T11:01:00.000-07:00

Single-threaded apply is one of the big downsides of MySQL's built-in replication, as Baron Schwartz pointed out a couple of days ago. While a master can process dozens of updates at once, slaves must apply them one after the other on a single thread. Add in disk I/O, and the result is very slow performance indeed. The obvious answer is parallel apply, namely writing multiple non-conflicting updates to the slave at once.

I have spent the last few months implementing parallel apply for Tungsten 2.0, which we are now testing at customer sites. In this article I would like to describe how Tungsten's parallel apply works as well as some of the lessons that have become apparent through the implementation.

There are a couple of big challenges in parallel apply. There is of course the practical problem of separating transactions into parallel streams, for example splitting them by database. This is known as sharding. Row updates are easy enough but MySQL also has statement replication. Transactions with statements require parsing, and there are ambiguous cases. If that's not enough, features like LOAD DATA INFILE have a complex implementation in the binlog and require specialized logic to shard correctly. In addition, parallel apply of any kind has a lot of corner cases that you have to solve completely or risk unpredictable failures. Here's an example: skipping transactions on the slave. You have to wait for the event, but what if some of the threads are already past it when you ask to skip? How do you synchronize access to the list of transactions to skip without creating a choke point for threads?

The next challenge is performance. Parallel replication offers a rich choice of ways to lower throughput, not raise it. Multiple disk logs are the best I have found so far, as they can convert sequential reads and writes on the disk log to random I/O when more replication threads contend for different parts of the disk. Implementing multiple queues in memory is far faster and simpler but limits the queue sizes. Another excellent way to slow things down is to try to parallelize SQL transactions with a lot of dependencies, which means you end up effectively serialized *and* paying the extra cost of parsing transactions and synchronizing threads. In this case it can be better to keep everything sequential but use block commit to apply 50 or 100 transactions simultaneously on the slave.

With all that said, the parallel apply problem is still quite tractable, but you need to pick your battles carefully. Tungsten's parallel apply implementation has a very clear problem focus: speeding up slave updates for multi-tenant applications that have a high degree of natural partitioning and concurrent updates across customers. This is not as limiting as it might sound to readers unfamiliar with MySQL. SaaS applications for the most part follow the multi-tenant model on MySQL, with each customer assigned to a particular database. So do large ISPs or cloud providers that host customers on shared servers using separate databases.

Tungsten parallel apply is based on automatic sharding of transactions. The following diagram shows the parallel apply algorithm conceptually.

Tungsten Parallel Apply

Tungsten has a flexible architecture based on replication pipelines, described in a previous article on this blog. To recap the model, pipelines are divided into stages, which represent processing steps. Each stage consists of an extract-filter-apply loop with symmetric interfaces and identical processing logic for each stage. The parallel apply implementation builds on replication pipelines as follows:

A new filter called EventMetadataFilter automatically parses incoming transactions to figure out which database(s) they affect. This is simple for row updates but involves parsing for statements and specialized extract handling for odd-ball operations like LOAD DATA INFILE.
The shard ID is assigned from the database name. This is glommed into the EventMetadataFilter but will shortly be broken out into a separate filter so that it is possible to support alternate shard assignment algorithms.
There is a new kind of in-memory buffer between stages called a ParallelQueue that supports multiple queues that feed the final apply stage. Stages have a corresponding extension to allow them to have multiple threads, which must match the number of parallel queues or you get an error.
The ParallelQueue implementation calls a new component called a Partitioner to assign transactions a partition number (i.e., a parallel queue). You can substitute different algorithms by providing different partitioner implementations. The default implementation uses a configuration file called shard.list to map shards to queues. Unless you say otherwise it hashes on the shard ID to make this assignment.

Extensions #1 and #2 run on the master, while #3 and #4 run on the slave. I really like diagrams, so here is a picture of the fully implemented parallel apply architecture. The master replicator extracts, assigns the shard, and logs each transaction. The slave replicator fetches transactions, logs them locally, then applies in parallel.

Full Master/Slave Architecture for Parallel Apply

So how does this work? Pretty well actually. Local lab tests indicate that parellel apply roughly doubles throughput on a multi-database TPC-B benchmark we use for testing. We should be able to publish some real-world performance numbers in the near future, but so far things look quite promising. During the implementation a number of interesting issues have arisen, which I would like to discuss now.

The first issue is the ratio between parallel apply threads and shards. While it might seem obvious to have a thread per shard, in real deployments the situation is not so clear. For one thing actual deployments in SaaS and ISP situations often have hundreds or even thousands of databases, which has a number of practical consequences for implementation. Less obviously, spreading transactions thinly across a lot of queues means fewer opportunities to use block commit, hence more work for slave servers and less overall throughput. Performance optimization is a very uncertain matter, so Tungsten lets users configure the ratio.

Dependencies between shards are yet another issue. While I mentioned that Tungsten is designed for applications with "a high degree of natural partitioning," dependencies between databases as well as individual transactions do occur and cannot be ignored. For example, many SaaS applications have reference data that are used by all customer databases. Even if parallel SQL works here, applications may get sick from seeing updates appear in the wrong order. Or you could have global operations like CREATE USER that affect all databases. Or you might not be able to tell which shard a piece of SQL belongs to. Tungsten allows users to declare reference databases and automatically serializes these databases as well as global or "don't know" cases.

There are also numerous issues around startup and shutdown. Remember how MySQL replication slaves will not restart after unclean shutdown with open temp tables? (If not, take a quick break and read this now. You'll thank me later.) Parallel apply introduces similar issues, because you have multiple threads all updating different positions in the database. Tungsten handles crash recovery by tracking the apply position of each queue in InnoDB and then recommencing from that point on restart in each queue. I am putting finishing touches on clean shutdown, which ensures that all queues are empty, much like automatically checking that temp tables are closed on MySQL.

In short, over the last few months Tungsten has climbed a fair distance up a pretty big hill to get parallel apply to work. The flexibility of the replicator architecture, particularly pipelines, has been very helpful as it is quite easy to extend. The parallelization algorithm builds on terrific work by other colleagues at Continuent, especially Stephane Giron and Linas Virbalas. They have both put enormous effort into building up MySQL and PostgreSQL replication capabilities.

Here are a couple of parting thoughts about parallelization based on the experience so far.

Thought number one: parallel replication is not magic. To use parallel apply effectively, applications need to play nice: mostly short transactions and not too many dependencies between shards are the biggest requirements to see a substantial boost in throughput. For example, if you let one user write 50M statements to the binlog in a single transaction, things are going to get kind of quiet on the slave no matter what you do. Also, you can forget about MyISAM or other non-transactional engines. As I have written before, these engines offer a number of opportunities for databases to get messed up or out-of-sync even using conventional MySQL replication. Tungsten's block commit and parallel apply increase the window for problems significantly. If you are still using MyISAM for replicated data, it's time to man up and convert to InnoDB.

Thought number two: The long-term answer to effective parallel replication is to change how MySQL works by interleaving transactions within the binlog along the lines suggested by Kristian Nielsen and others. MySQL currently completely serializes transactions to the binlog, an accomplishment that makes slave apply logic a lot simpler. Tungsten parallel apply then has to undo this good work and recreate streams of non-conflicting updates, which is complex and does not help all workloads.

It is doubtful that replicating interleaved transactions will be less complex than handling a serial binlog as it stands today. There is also some heavy lifting inside MySQL to get to an interleaved binlog. However, interleaved transactions would have the advantage that transactions for any workload would be parallelized, which would widen the scope of benefits to users. I'm happy to see that Kristian and other people are now working this feature for future releases of MySQL.

Meanwhile, we have a workable solution for Tungsten and are pushing it forward as quickly as we can. Contact Continuent if you would like to test it out.

MySQL Disaster Recovery With Tungsten

2010-10-16T11:00:00.000-07:00

Disaster recovery (DR) is not the first thing most DBAs think of when putting up a new database application. However, it's one of the top issues for people using the data--what happens if the site goes down and everything disappears? So even if DR is not the first issue in every deployment, it is a very high priority as soon as your application is the least bit successful.

At the database level DR has a fairly simple solution: keep copies of data on a backup site that is up-to-date at all times. This article explains the architecture for MySQL DR with Tungsten and a couple of key features that make it work, namely floating IP addresses and global transation IDs. We will dig into those at the end.

First a bit of introduction. Tungsten manages clusters of off-the-shelf database connected by master/slave replication. There are replication and management services on each host with automated policies to handle failover as well as low-level tasks like recognizing new cluster members. There is a simple management client that lets you log into any host and manage all nodes in the cluster. Tungsten also has connectivity options to let applications find databases easily. However, for this article we are going to focus on the database only and how you solve the problem of ensuring your data are protected.

DR Setup

To implement disaster recovery, you actually create two clusters--one on your main site and one on a backup site which we will henceforth call the DR site. It looks like the following picture.

Standard Main/DR Architecture with Backups

Here is an outline of the setup. There are additional details of course but those are covered in Tungsten documentation and support procedures. The goal here is to give you a sense of how things work at the top level.

Main site. Set up the main site cluster as a master/slave pair with a floating IP address on the master. Enable automatic policy mode so that in the event of a master failure the local slave will immediately take over. Set up backups and run them on the slave on a regular basis.
DR site. Next, set up the DR cluster by provisioning both databases with a recent backup from the main cluster. Configure it identically to the main site with a master IP address and with backups but with two exceptions. First, use manual policy mode so that the cluster does not try to fail over. Second, do not start replication automatically. Instead, manually configure the DR master to be a slave of the main site master using the master floating IP address and start services. Set up backups on this site as well.

Handling Failures

At the end of setup you have a main site with a cluster and a DR site with a cluster that slaves efficiently off the main site master. Both sites have regular backups. As long as there are no failures, you operate both sites and everything is fine. Let us now consider a couple of different types of failures and how to handle them.

Let's suppose the main site master fails. Tungsten will automatically fail over to the main site slave and move the master floating IP address. The DR site relay slave TCP/IP connection to the master will then break, or more accurately time out. When the relay slave reconnects to the floating IP, it will have shifted to the new master and replication to the DR site will continue without any human intervention.

Failed Master on Main Site

This protocol is handy because failures are not the only reason that the main site master may move. You can also move masters for maintenance or upgrades. Tungsten has a switch command that makes this very easy to do. The floating IP moves as before and the DR site continues to receive updates properly after it reconnects.

If you lose the main site, you initiate a site switch procedure. At the database level this consists of running a script to "unconfigure" your DR relay slave node so that it becomes a master again and then reload the configuration. When the node comes up as a master it will then automatically install its own master floating IP address. The commands are simple and run in a few seconds. In most cases it will take a lot longer to switch applications properly than switch databases, because you have to change DNS entries, start and/or reconfigure applications, and potentially activate other resources to have a functioning system.

In fact, the real problem with site failover at the database level is not so much failing over but getting the main site back in operation without losing too much data and with as little interruption to users as possible. You first need to check for any transactions that did not make it off the main site and apply them to the DR site master. In MySQL you can do this by carefully applying transactions from the main site binlog. You can help yourself considerably by including a step in the site failover process where you fence (i.e., turn off) the old site as quickly as possible by shutting down applications and taking applications offline. The fewer extra transactions on the main site, the simpler it is to clean up.

Next, you need to get the master site resynchronized with the slave. If there are more than a few differences, you will probably just restore the main site master and slave from local backups, then manually configure them to make the main site master a relay slave of the DR site. If you have large databases, you may want to look at SAN or NAS products like NetAPP that offer snapshot capabilities. I have been working lately with NetApp; the snap restore command is really impressive for rolling back file system state quickly.

DR Site Operation and Main Site Recovery

Once the main site is caught up, you can switch applications back the main site by taking a short outage to move applications. This step is not fully transparent, but unlike the original DR failover, you get to pick the time that is least inconvenient for your users. Also, you can use Tungsten features like consistency checks to verify that data are consistent across sites.

Underlying Tungsten Features to Enable DR

As promised at the beginning, here is a look at the Tungsten features that make DR work. First, there is automated failover with floating IP address management. Tungsten uses a rules engine combined with group communications to manage failover and master floating IPs efficiently. The rules take care of many of the weird failure cases as well as handling tasks like automatically making slave servers readonly, etc. Setting up DR without floating IP addresses is more complex because it means your relay slave needs to know when the main site master moves for any reason.

As useful as floating IP addresses are, Tungsten has a much more important feature that underlies the entire DR site architecture: global transaction IDs. Unlike native MySQL replication, Tungsten assigns a global ID or seqno to each transaction as it is read from the binlog. Tungsten replicator processes track position using the seqno values rather than the file name and offset used by MySQL slaves. Here is a picture that illustrates how the replicator log works.

Global IDs, Epoch Numbers, and Backups

As already mentioned, the Tungsten master replicator assigns the seqno to each transaction as it is extracted. Tungsten slave replicators always use the seqno to request the next event from the master. This means that you can switch the master without worrying whether slaves will lose track of their positions, because they will just ask for the seqno from the new master.

The other important feature of global IDs is that they make backups fungible across different databases and even sites. Tungsten marks the database with the current seqno and epoch number. As long as your backup (or file system snapshot) is transactionally consistent, you can load it on any server and bring it back online as a slave. The new slave will connect to and catch up with the master, wherever it happens to be. This makes database recovery both simple and very flexible.

The phrase "transactional consistency" brings up another issue. To make the disaster recovery architecture work reliably I strongly recommend you switch to InnoDB or another fully transactional engine. MyISAM does not have a place in this architecture--there are just too many ways to end up with corrupt data and a massive outage.

There is one final aspect of Global IDs in Tungsten that is worth mentioning. What if the master log is corrupted or a slave from a different cluster accidentally logs into the wrong master? In both cases the slave could get bad data if it just asked for the next seqno without some how checking that the master and slave logs are somehow consistent. This would at best lead to errors and in the worst case to badly messed up data.

Tungsten deals with log consistency problems using epoch numbers. Whenever the master goes online it sets a new epoch number, which works like a parity check on the sequence number. Each time a slave connects to the master, it offers the last seqno it received along with the epoch number. If the values match the same seqno/epoch number in the master log, we assume the logs have the same master and proceed. Otherwise, we assume somebody is confused and do not allow the slave to fetch transactions.

Conclusion

DR site setup is complex and this article obviously glosses over a lot of details even for databases. One final bit of advice is that whatever you do, test the daylights out of it before deploying. Site failures may be karmic but dealing with them is certainly not. Site failover is a really bad time to find out you don't have the password to your DNS provider handy or that you have a network configuration problem on the DR site. One customer I know put all the computers from his main site and DR site in a pile on his conference room table and tested (and retested and retested and retested) until he was completely satisfied with the results. That is the soul of true disaster recovery.

MySQL Conference Slides and Thoughts on State of the Dolphin

2010-04-23T17:07:00.000-07:00

I did two talks on replication and clustering at the recent MySQL Conference in Santa Clara. Thanks to all of you who attended as well as the fine O'Reilly folks who organized everything. Slides are posted on the talk descriptions at the following URLs:

Clustering for the Masses - A Gentle Introduction to Tungsten for MySQL

Not Your Grandpa’s Replication-The New Wave of MySQL Replication and How It Helps Your Applications (A collaborative talk with Jay Pipes)

Conferences like the MySQL UC are fun because you get to see all your virtual pals in the flesh and have a beer with them. This is one of the fundamental open source bonding experiences. Unfortunately the taps for draft beer stopped working at the bar, and Tungsten is in the middle of a big crunch to get parallel replication working. I didn't get to hang around a lot this year. A few things still stood out compared to 2009.

First of all, long-term effects of the Oracle acquisition are clear.   Edward Screven's keynote on "The State of the Dolphin" was sensible and boring. It seemed a telling metaphor for life in the community going forward. Oracle is going to do an adequate job of MySQL engineering and better than adequate for Windows. This is of course "adequate" in the same way that the word applies to products like Microsoft Word.

An adequate MySQL is probably the worst possible outcome for the groups trying to build businesses on alternative builds. It looks like an effective way for Oracle to neutralize competitive threats from below for a few years to come.   On the other hand, it's good for most users, who won't be greatly inclined to switch unless Oracle tries to soak them for big licensing fees. At least one conference attendee, a licensee of other Oracle products, mentioned that had already happened. He's a MariaDB fan now.

Second, solid state devices (SSDs) are for real.   Andreas Bechtolsheim gave a great talk on the coming SSD revolution at the 2009 MySQL Conference. It sounded good. At the 2010 conference we started to see some real test results. The hype on SSDs is completely justified.

There was an excellent panel talk sponsored by Fusion-IO that presented some very compelling results including 10x throughput improvements that allowed one of the companies doing the testing to pull out and repurpose 75% of their hosts. PCI-based Fusion-IO cards have a 300- to 400X price differential compared to basic rotating disk, but the cost is likely to drop pretty quickly as the technology matures and more competitors enter the field. Much cheaper SATA alternatives like the Intel X-25 are already starting to flood the low-end market. Anybody building database systems has to have a plan that accounts for SSDs now.

Third, innovation is continuing apace but the problems (and solutions) are moving away from MySQL.   Mark Callaghan really put his finger on it at his Ignite MySQL talk when he said, "In 3 years MySQL won't be the default DBMS for high-scale applications." New system investment is going into applications that handle big data, have to utilize new hardware efficiently to operate economically, and require multi-tenancy. These are good targets for Drizzle, PBXT, Tungsten, and other new projects working to make names for themselves.   We all have to raise our game or MySQL will start to become irrelevant. It's going to be an interesting year. :)

Customized Data Movement with Tungsten Replicator Pipelines

2010-04-20T22:14:00.000-07:00

Have you ever run into a problem where MySQL replication did 95% of what you needed but not the remaining 5% to solve a real problem? Hacking the binlog is always a possibility, but it typically looks like this example. Not a pretty sight. Wouldn't it be easier if replication were a bunch of building blocks you could recombine to create custom replicator processes?

Tungsten 1.3 has a new feature called pipelines that allows you to do exactly that. A pipeline consists of one or more stages that tie together generic components to extract, filter, store, and apply events, which is Tungsten parlance for transactions. Each stage has a processing thread, so multi-stage pipelines can process data independently and without blocking. The stages also take care of important but tedious issues like remembering the transactional state of each stage so Tungsten can restart without forgetting events or applying them twice.

Here is a picture of how a pipeline is put together.

When Tungsten Replicator starts it loads a pipeline corresponding to its role, for example master or slave. The preceding picture shows a slave pipeline consisting of two stages. The first stage pulls replicated events over the network from a master Tungsten Replicator and stores them in a local transaction history log, which we call the THL. The second stage extracts the stored events and applies them to the database. This pipeline is analogous to the I/O and SQL threads on a MySQL slave.

Where Tungsten departs from MySQL and most other replicators in a big way is that pipelines, hence the replication flows, are completely configurable. The configuration is stored in file replicator.properties. Here are the property settings to create the slave pipeline. Note how the role is the name of a pipeline. This determines which pipeline to run when the replicator goes online.

# Replicator role.  
replicator.role=slave

...
# Generic pipelines.  replicator.pipelines=master,slave,direct

...
# Slave pipeline has two stages:  extract from remote THL to local THL; 
# extract from local THL and apply to DBMS. 
replicator.pipeline.slave=remote-to-thl,thl-to-dbms
replicator.pipeline.slave.stores=thl
replicator.pipeline.slave.syncTHLWithExtractor=false

replicator.stage.remote-to-thl=com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask
replicator.stage.remote-to-thl.extractor=thl-remote
replicator.stage.remote-to-thl.applier=thl-local

replicator.stage.thl-to-dbms=com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask
replicator.stage.thl-to-dbms.extractor=thl-local
replicator.stage.thl-to-dbms.applier=mysql
replicator.stage.thl-to-dbms.filters=mysqlsessions

The syntax is not beautiful but it is quite flexible. Here is what this definition means.

This replicator knows about three pipelines named master, slave, and direct.
The slave pipeline has two stages called remote-to-thl and thl-to-dbms and a store called thl. It has a property named syncTHLWithExtractor which must be set to false for slaves. (We need to change that name to something like 'isMaster'.)
The remote-to-thl stage extracts from thl-remote. This extractor reads events over the network from a remote replicator. The stage apples to thl-local, which is an applier that writes events to the local transaction history log.
The thl-to-dbms stage pulls events from the local log and applies them to the database. Note that in addition to an applier and extractor, there is also a filter named mysqlsessions. This filter looks at events and modifies them to generate a pseudo-session ID, which is necessary to avoid problems with temporary tables when applying transactions from multiple sessions. It is just one of a number of filters that Tungsten provides.

Components like appliers, filters, extractors, and stores have individual configuration elsewhere in the tungsten.properties file. Here's an example of configuration for a MySQL binlog extractor. (Note that Tungsten 1.3 can now read binlogs directly as files or relay them from a master server.)


# MySQL binlog extractor properties.  
replicator.extractor.mysql=com.continuent.tungsten.replicator.extractor.mysql.MySQLExtractor
replicator.extractor.mysql.binlog_dir=/var/log/mysql
replicator.extractor.mysql.binlog_file_pattern=mysql-bin
replicator.extractor.mysql.host=logos1-u1
replicator.extractor.mysql.port=3306
replicator.extractor.mysql.user=${replicator.global.db.user}
replicator.extractor.mysql.password=${replicator.global.db.password}
replicator.extractor.mysql.parseStatements=true

# When using relay logs we download from the master into binlog_dir.  This 
# is used for off-board replication. 
#replicator.extractor.mysql.useRelayLogs=false

The thing that makes pipelines really flexible is that the interfaces are completely symmetric. Components to extract events from MySQL binlog or from a transaction history log have identical APIs. Similarly, the APIs to apply events are the same whether storing events in a log or applying to a slave. Pipelines can tie together practically any sequence of extract, filter, and apply operations you can think of.

Here are diagrams of a couple of useful single-stage pipelines.

The "dummy" pipeline reads events directly from MySQL binlogs and just throws them away. This sounds useless but in fact it is rather convenient. You can use the dummy pipeline check whether your binlogs are good. If you add filters you can also use a dummy pipeline to report on what is in the binlog. Finally, you can use it as a quick and non-intrusive check to see if Tungsten can handle the data in your binlog--a nice way to ensure you can migrate smoothly.

Here's the dummy pipeline definition:

# Generic pipelines. 
replicator.pipelines=master,slave,direct, dummy
...
# Dummy pipeline has single stage that writes from binlog to bit-bucket. 
replicator.pipeline.dummy=binlog-to-dummy
replicator.pipeline.dummy.autoSync=true

replicator.stage.binlog-to-dummy=com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask
replicator.stage.binlog-to-dummy.extractor=mysql
replicator.stage.binlog-to-slave.applier=dummy

The "direct" pipeline fetches events directly from a master MySQL server using client log requests over the network and applies them immediately to a slave. I use this pipeline to test master-to-slave performance, but it's also very handy for transferring a set of SQL updates from the binlog of any master to any slave on the network. For instance, you can transfer upgrade commands very efficiently out of the binlog of a successfully upgraded MySQL server to other servers on the network. You can also use it to "rescue" transactions that are stuck in the binlog of a failed master. That is starting to be genuinely useful.

The definition of the direct pipeline is already in the default replicator.properties.mysql template that comes with Tungsten 1.3, so it is not necessary to repeat it here. You can just download the software (open source version is here) and have a look at it yourself. There's almost more documentation than people can bear--look here to find a full set. Version 1.3 docs will be posted shortly on the website and are already available for commercial customers. As usual you can also view the source code on SourceForge.net.

Pipelines belong to a set of major feature improvements to Tungsten to support SaaS and large enterprise deployments. Some of the other features include fast event logging directly to disk (no more posting events in InnoDB), low-latency WAN transfer, multi-master replication support, and parallel replication. Stay tuned!

New Tungsten Software Releases for MySQL and PostgreSQL

2010-03-28T22:24:00.000-07:00

I would like to announce a couple of new Tungsten versions available for your database clustering enjoyment. As most readers of this blog are aware, Tungsten allows users to create highly available data services that include replicated copies, distributed management, and application connectivity using unaltered open source databases. We are continually improving the software and have a raft of new features coming out this year.

First, there is a new Tungsten 1.2.3 maintenance release available in both commercial as well as open source editions. You can get access to the commercial version on the Continuent website, while the open source version is available on SourceForge.

The Tungsten 1.2.3 release focuses on improvements for MySQL users including the following:

Transparent session consistency for multi-tenant applications. This allows applications that follow some simple conventions like sharding tenant data by database to get automatic read scaling to slaves without making code changes.
A greatly improved script for purging history on Tungsten Replicator.
Fixes to binlog extraction to handle enum and set data types correctly.

By far the biggest improvement in this release is Tungsten product documentation, including major rewrites for the guides covering management and connectivity. Even the Release Notes are better. If you want to find out how Tungsten works, start with the new Tungsten Concepts and Administration Guide.

Second, there's a new Tungsten 1.3 release coming out soon. Commercial versions are already in use at selected customer sites, and you can build the open source version by downloading code from SVN on SourceForge.

The Tungsten 1.3 release sports major feature additions in the following areas:

A new replicator architecture that allows you to manage non-Tungsten replication and also to configure very flexible replication flows to use multi-core systems more effectively and implement complex replication topologies. The core processing loop for replication can now cycle through 700,000 events per second on my laptop--it's really quick.
Much improved support for PostgreSQL warm standby clustering as well as provisional management of new PostgreSQL 9 features like streaming replication and hot standby.
Replication support for just about everything in the MySQL binlog: large transactions, unsigned characters, session variables, various permutations of character sets and binary data, and ability to download binlog files through the MySQL client protocol. If you can put it in the binlog we can replicate it.

We also have provisional support for Drizzle thanks to Markus Ericsson, plus a raft of other improvements. This has been a huge amount of work all around, so I hope you'll enjoy the results.

P.s., Contact Continuent if you want to be a beta test site for Tungsten 1.3.

Replication and More Replication at 2010 MySQL Conference

2010-03-22T17:18:00.000-07:00

Database replication is still interesting after all these years. Two of my talks focused on replication technology were accepted for the upcoming MySQL 2010 Conference. Here are the summaries.

Clustering for the Masses - A Gentle Introduction to Tungsten for MySQL

Not Your Grandpa’s Replication-The New Wave of MySQL Replication and How It Helps Your Applications

The first talk is a solo presentation covering Tungsten, which creates highly available and scalable database clusters using vanilla MySQL databases linked by flexible replication. I'll describe how it works and some cool things you can do like zero-downtime upgrades and session-based performance scaling. If you want to know how Tungsten can help you, this is a good time to find out.

The second talk is a joint effort with Jay Pipes covering issues like big data that are driving replication technology and the solutions to these problems available to MySQL users. We'll lay out our vision of where things are going to try to help you pick the right technology for your next project. Jay and I are also soliciting input on this talk from the Drizzle community among others. If you are interested check out the thread on drizzle-discuss or post to this blog.

Finally, I'll be around for much of the MySQL conference, so if you are interested in Tungsten or data replication in general or just want to hang out, please look me up. See you in Santa Clara!

Tungsten and PostgreSQL 9 at PG-East Conference

2010-03-22T11:50:00.000-07:00

My Continuent colleagues Linas Virbalas and Alex Alexander will be giving a talk entitled Building Tungsten Clusters with PostgreSQL Hot Standby and Streaming Replication later this week at the PG-East Conference in Philadelphia. I saw the demo last week and it's quite impressive. You can flip the master and slaves for maintenance, open slaves for reads, failover automatically, etc. It's definitely worth attending if you are in Philly this week.

Looking beyond the conference, we plan to be ready to support Tungsten clusters on PostgreSQL 9 as soon as it goes production. Everything we have seen so far indicates that the new log streaming and hot standby features are going to be real hits. They not only help applications, but from a clustering perspective queryable slaves with minimal replication lag are also a lot easier to manage. Alex and Linas will have more to say about that during their presentation.

Meanwhile, I'm sorry to miss the PG-East conference but wish everyone who will be attending a great time. See you later this year at PG-West!

MariaDB is Thinking about Fixing MySQL Replication and You Can Help

2010-01-28T18:10:00.000-08:00

In case you have not noticed, MariaDB is joining the list of projects thinking about how to improve MySQL replication. The discussion thread starts here on the maria-developers mailing list.

This discussion was jointly started by Monty Program, Codership, and Continuent (my employer) in an effort to push the state of the art beyond features offered by the current MySQL replication. Now that things are starting to die down with the Oracle acquisition, we can get back to the job of making the MySQL code base substantially better. The first step in that effort is to get a discussion going to develop our understanding of the replication problems we think are most important and outline a strategy to solve them.

Speaking as a developer on Tungsten, my current preference would to be to improve the existing MySQL replication. I suspect this would also be the preference of most current MySQL users. However, there are also more radical approaches on the table, for example from our friends at Codership, who are developing an innovative form of multi-master replication based on group communications and transaction certification. That's a good thing, as we want a range of contrasting ideas that take full advantage of the creativity in the community on the topic of replication.

If you have interest in improving MySQL replication please join the MariaDB project and contribute your thoughts. It should be an interesting conversation.

Tungsten 1.2.2 Release is Out - Faster, More Stable, More Fun

2010-01-27T12:16:00.000-08:00

Release 1.2.2 of Tungsten Clustering is available on SourceForge as well as through the Continuent website. The release contains mostly bug fixes in the open source version but there are also two very significant improvements of interest to all users.

The manager and monitoring capabilities of Tungsten are completely integrated on the same group communications channel. This fixes a number of problems that caused data sources not to show up properly in older versions.
We are officially supporting a new Tungsten Connector capability for MySQL called pass-through mode, which allows us to proxy connections by transferring network blocks directly rather than translating native request protocol to JDBC calls. Our tests show that it speeds up throughput by as much as 200% in some cases.

The commercial version has additional features like PostgreSQL warm standby clustering, add-on rules to manage master virtual IP addresses and other niceties. If you are serious about replication and clustering it is worth a look.

This is a good time to give a couple of reminders for Tungsten users. First, Tungsten is distributed as a single build that integrates replication, management, monitoring, and connectivity. The old Tungsten Replicator and Myosotis builds are going away. Second, we have a single set of docs on the Continuent website that covers both open source and commercial distributions.

With that, enjoy the new release. If you are using the open source edition, please post your experiences in the Tungsten community forums or write a blog article. We would love to hear from you.

P.s., We have added Drizzle support thanks to a patch from Marcus Eriksson but it's not in 1.2.2. For that you need to build directly from the SVN trunk. Drizzle support will be out in binary builds as part of Tungsten version 1.3.

What's in Your Binlog?

2010-01-17T00:58:00.000-08:00

Over the last couple of months I have run into a number of replication problems where I needed to run reports on MySQL binlogs to understand what sort of updates servers were processing as well as to compute peak and average throughput. It seems that not even Maatkit has a simple tool to report on binlog contents, so I wrote a quick Perl script called analyze-binlog.pl to summary output from mysqlbinlog, which is the standard tool to dump binlogs to text.

The script operates as a filter with the following syntax:

Usage: ./binlog-analyze.pl [-h] [-q] [-v]
Options:
  -h : Print help
  -q : Suppress excess output
  -v : Print verbosely for debugging

To get a report, you just run mysqlbinlog on a binlog file and pipe the results into analyze-binlog.pl. Here is typical invocation and output. The -q option keeps the output as short as possible.

$ mysqlbinlog /var/lib/mysql/mysql-bin.001430 | ./binlog-analyze.pl -q
===================================
| SUMMARY INFORMATION             |
===================================
Server Version    : 5.0.89
Binlog Version    : 4
Duration          : 1:03:37 (3817s)

===================================
| SUMMARY STATISTICS              |
===================================
Lines Read        :        17212685
Events            :         3106006
Bytes             :      1073741952
Queries           :         2235077
Xacts             :          817575
Max. Events/Second:            5871.00
Max. Bytes/Second :         1990077.00
Max. Event Bytes  :          524339
Avg. Events/Second:             813.73
Avg. Bytes/Second :          281305.20
Avg. Queries/Sec. :             585.56
Avg. Xacts/Sec.   :             214.19
Max. Events Time  :         9:01:02

===================================
| EVENT COUNTS                    |
===================================
Execute_load_query   :           10
Intvar               :        53160
Query                :      2235077
Rotate               :            1
Start                :            1
User_var             :          182
Xid                  :       817575

===================================
| SQL STATEMENT COUNTS            |
===================================
begin                :       817585
create temp table    :            0
delete               :        31781
insert               :           20
insert into          :       411266
select into          :            0
update               :       633857

There are lots of things to see in the report, so here are a few examples. For one thing, peak update rates generate 5871 events and close to 2Mb of log output per second. That's loaded but not enormously so--MySQL replication can easily dump over 10,000 events per second into the binlog using workhorse 4-core machines. The application(s) connected to the database execute a large number of fast, short transactions--typical of data logging operations, for example storing session data. We can also see from the Execute_load_query events that somebody executed MySQL LOAD DATA INFILE commands. That's interesting to me because we are just putting them into Tungsten and need to look out for them in user databases.

To interprete the binlog report most effectively, you need to understand MySQL binlog event types. MySQL replication developers have kindly provided a very helpful description of the MySQL binlog format that is not hard to read. You'll need to refer to it if you get very deeply into binlog analysis. It certainly beats reading the MySQL replication code, which is a bit of a thicket.

Anyway, I hope this script proves useful. As you may have noted from the URL the script is checked into the Tungsten project on SourceForge and will be part of future releases. I plan to keep tweaking it regularly to add features and fix bugs. Incidentally, if you see any bugs let me know. There are without doubt a couple left.

Exploring SaaS Architectures and Database Clustering

2010-01-02T18:32:00.000-08:00

Software-as-a-Service (Saas) is one of the main growth areas in modern database applications. This topic has become a correspondingly important focus for Tungsten, not least of all because new SaaS applications make heavy use of open source databases like MySQL and PostgreSQL that Tungsten supports.

This blog article introduces a series of essays on database architectures for SaaS and how we are adapting Tungsten to enable them more easily. I plan to focus especially on problems of replication and clustering relevant to SaaS—what are the problems, what are the common design patterns to solve them, and how to deploy and operate the solutions. I will also discuss how to make replication and clustering work better for these cases—either using Tungsten features that already exist or features we are designing.

I hope everything you read will be solid, useful stuff. However, I will also discuss problems where we are in effect thinking out loud about on-going design issues, so you may also see some ideas that are half-baked or flat-out wrong. Please do me the kindness of pointing out how they can be improved.

Now let's get started. The most important difference between SaaS applications and ordinary apps is multi-tenancy. SaaS applications are typically designed from the ground up to run multiple tenants (i.e., customers) on shared software and hardware. One popular design pattern is to have users share applications but keep each tenant's data stored in a separate database, spreading the tenant databases over multiple servers as the number of tenants grows.

Multi-tenancy has a number of important impacts on database architecture. I'm going to mention just three, but they are all significant. First of all, multi-tenant databases tend to evolve into complex topologies. Here's a simple example that shows how a successful SaaS application quickly grows from a single, harmless DBMS server to five servers linked by replication with rapid growth beyond.

In the beginning, the application has tenant data stored in separate databases plus an extra database for the list of tenants as well as data shared by every application. In accounting applications, for example, the shared information would include items like currency exchange and VAT rates that are identical for each tenant. Everything fits into a single DBMS server and life is good.

Now business booms and more tenants join, so soon we split the single server into three—a server for the shared data plus two tenant servers. We add replication to move the shared data into tenant databases.

Meanwhile business booms still more. Tenants want to run reports, which have a tendency to hammer the tenant servers. We set up separate analytics servers with optimized hardware and alternative indexing on the schema, plus more replication to load data dynamically from tenant databases.

And this is just the beginning of additional servers as the SaaS adds more customers and invents new services. It is not uncommon for successful SaaS vendors to run 20 or more DBMS servers, especially when you count slave copies maintained for failover and consider that many SaaS vendors also operate multiple sites. At some point in this evolution the topology, including replication as well as management of the databases, is no longer manually maintainable. As we say in baseball, Welcome to the Bigs.

Complex topologies with multiple DBMS servers lead to a second significant SaaS issue: failures. Just having a lot of servers already means failures are a bigger problem than when you run a single DBMS instance. To show why, let's say individual DBMS servers fail in a way that requires you do something about it on average once a year, a number that reliability engineers call Mean Time between Failures (MTBF). Here is a simple table that shows how often we can expect an individual failure to occur. (Supply your own numbers. These are just plausible samples.)

Number of DBMS Hosts			Days Between Failures
1			365
2			182.5
4			91.3
8			45.6
16			22.8
32			11.4

Failures are not just more common with more DBMS hosts, but more difficult to handle. Consider what happens in the example architecture when a tenant data server fails and has to be replaced with a standby copy. The replacement must not only replicate correctly from the shared data server, but the analytic server must also be reconfigured to replicate correctly as well. This is not a simple problem. There's currently no replication product for open source databases that handles failures in these topologies without sooner or later becoming confused and/or leading to extended downtime.

There is a third significant SaaS problem: operations on tenants. This includes provisioning new tenants or moving tenants from one database server to another without requiring extended downtime or application reconfiguration. Backing up and restoring individual tenants is another common problem. The one-database-per-tenant model is popular in part because it makes these operations much easier.

Tenant operations are tractable when you just have a few customers. In the same way that failures become more common with more hosts, tenant operations become more common as tenants multiply. It is therefore critical to automate them as well as make the impact on other tenants as small as possible.

Complex topologies, failures, and tenant operations are just three of the issues that make SaaS database architectures interesting as well as challenging to design and deploy. It is well worth thinking about how we can improve database clustering and replication to handle SaaS. That is exactly what we are working on with Tungsten. I hope you will follow me as we dive more deeply into SaaS problems and solutions over the next few months.

P.s., If you run a SaaS and are interested working with us on these features, please contact me at Continuent. I'm not hard to find.

Proving Master/Slave Clusters Work and Learning along the Way

2009-12-26T18:02:00.000-08:00

2009 has been a big year for Tungsten. In January we had (barely) working replication for MySQL. It had some neat features like global IDs and event filters, but to be frank you needed imagination to see the real value. Since then, Tungsten has grown into a full-blown database clustering solution capable of handling a wide range of user problems. Here are just a few of the features we completed over the course of the year:

Autonomic cluster management using business rules to implement auto-discovery of new databases, failover, and quick recovery from failures
Built-in broadcast monitoring of databases and replicators
Integrated backup and restore operations
Pluggable replication management, proven by clustering implementations based on PostgreSQL Warm Standby and Londiste
Multiple routing mechanisms to provide seamless failover and load balancing of SQL
Last, but not least, simple command line installation to configure and start Tungsten in minutes

You can see the results in our latest release, Tungsten 1.2.1, which comes in both open source and commercial flavors. (See our downloads page to get software as well as documentation.)

In the latter part of 2009 we also worked through our first round of customer deployments, which was an adventure but helped Tungsten grow enormously. Along the way, we confirmed a number of hunches and learned some completely new lessons.

Hardware is changing the database game. In particular, performance improvements are shifting clustering in the direction of loosely coupled master/slave replication rather than tightly coupled multi-master approaches. As I laid out in a previous article, the problem space is shifting from database performance to availability, data protection, and utilization.
Software as a Service (SaaS) is an important driver for replication technology. Not only is the SaaS sector growing, but even small SaaS applications can result in complex database topologies that need parallel, bi-directional, and cross-site replication, among other features. SaaS business economics tend to drive building these systems on open source databases like MySQL and PostgreSQL. By supporting SaaS, you support many other applications as well.
Cluster management is hard but worthwhile. Building distributed management with no single points-of-failure is a challenging problem and probably the place where Tungsten still has the most work to do. Once you get it working, though, it's like magic. We have been focused on trying to make management procedures not just simple but wherever possible to do away with them completely by making the cluster self-managing.
Business rules rock. We picked the DROOLS rule engine to help control Tungsten and make it automatically reconfigure itself when data sources appear or fail. The result has been an incredibly flexible system that is easy to diagnose and extend. Just one example: floating IP address support for master databases took 2 hours to implement using a couple of new rules that work alongside the existing rule set. If you are not familiar with rules technology, there is still time to make a New Year's resolution to learn it in 2010. It's powerful stuff.
Clustering has to be transparent. I mean really transparent. We were in denial on this subject before we started to work closely with ISPs, where you don't have the luxury of asking people to change code. Tungsten Replicator is now close to a drop-in replacement for MySQL replication as result. We also implemented proxying based on packet inspection rather than translation and re-execution to raise throughput and reduce incompatibilities visible to applications.
Ship integrated, easy-to-use solutions. We made the mistake of releasing Tungsten into open source as a set of components that users had to integrate themselves. We have since recanted. As penance we now ship fully integrated clusters with simple installation procedures even for open source editions and are steadily extending the installations to cover not just our own software but also database and network configuration.

Beyond the features and learning experiences the real accomplishment of 2009 was to prove that integrated master/slave clusters can solve a wide range of problems from data protection to HA to performance scaling. In fact, what we have implemented actually works a lot better than I expected when we began to design the system back in 2007. (In case this sounds like a lack of faith, plausible ideas do not not always work in the clustering field.) If you have not tried Tungsten, download it and see if you share my opinion.

Finally, keep watching Tungsten in 2010. We are a long way from running out of ideas for making Tungsten both more capable and easier to use. It's going to be a great year.

Replicating from MySQL to Drizzle and Beyond

2009-10-31T08:21:00.000-07:00

Drizzle is one of the really great pieces of technology to emerge from the MySQL diaspora--a lightweight, scalable, and pluggable database for web applications. I am therefore delighted that Marcus Erikkson has published a patch to Tungsten that allows replication from MySQL to Drizzle. He's also working on implementing Drizzle-to-Drizzle support, which will be very exciting.

Marcus has submitted the patch to us and I have reviewed the code. It's quite supportable, so I plan to integrate it as soon as we are done with our next Tungsten release, which will post around 5 November. You will be able to build and run it using our new community builds.

This brings up a question--what about replicating from MySQL to PostgreSQL? What about other databases? I get the PostgreSQL replication question fairly often but it may be a while before our in-house team can implement plug-in support for it. Anybody want to submit a patch in the meantime? Post in the Tungsten forums if you have ideas and need help to get the work done. Tungsten Replicator code is very modular and it is not hard to add new database support.

Meanwhile, go Marcus!!

Community Builds for Tungsten Clustering

2009-10-31T07:54:00.000-07:00

It's been almost two months since I have posted anything on the Scale-Out Blog, as our entire team has been heads-down working on Tungsten. We now have a number of accomplishments that are worth writing articles about. Item one on that list is community builds for Tungsten clusters.

Tungsten community builds offer a bone-simple process to check out and build Tungsten clustering software. The result is a fully integrated package that includes replication, management, monitoring, and SQL routing. The community builds work for MySQL 5.0 and 5.1 and also allow you to set up basic replication from MySQL to Oracle.

Community builds do not include much logic for autonomic management, including automated failover and sophisticated rules that keep databases up and running rain or shine. Those and other features like floating IP address support are part of the commercial Tungsten software. PostgreSQL and Oracle-to-Oracle support is also commercial only at least for the time being.

Community builds do include our standard installation process, which allows you to set up a working cluster a few minutes. You can back up and restore datebases, check liveness of cluster members, failover master databases for maintenance and a lot of other handy features. There is also full documentation, located here.

To get started, you need a host running Mac OS X, Linux, or Solaris that meets the following prerequisites. On Linux you can usually satisfy these requirements using Yum or Apt-get if the required software is not already there.

Java JDK 1.5 or higher.
Ant 1.7.0 or higher for builds
Subversion. We use version 1.6.1
MySQL 5.0 or 5.1 (only on hosts where cluster is installed)
Ruby 1.8.5 or greater (only on hosts where cluster is installed)

Now you can grab the software and do a build. Make a work directory, cd to it, and enter the following commands. (Due to truncation on the blog the SVN URL looks a little funny. Don't be fooled.)

svn checkout \
https://tungsten.svn.sourceforge.net/\
svnroot/tungsten/trunk/community

cd community
./release-community.sh    # (Press ENTER when prompted)

The release-community.sh script checks out most of the Tungsten code for you and does a build. IMPORTANT NOTE: The command shown above builds SVN HEAD, which means you may have a life of adventure. You can also build off branches which are more or less stable. Look at the available config files in the community directory.

After the build finishes, you have ready-to-install clustering software. You can scp the resulting tar.gz file out to another host or just cd directly into the build itself as shown below and run the configure script, which sets up Tungsten software on a single host.

cd build/tungsten-community-2009-1.2
./configure

You may need to read the manuals so you get all the answers right. The installation manual is posted here at www.continuent.com. You'll also need to look at the Replication Guide, Chapter 2 to see how to set up MySQL properly. We'll do that automatically in the future, but for now it's help yourself. (Don't worry: the database set-up is easy.)

To make the cluster interesting you should install on at least a couple of hosts. Here's what an installed cluster looks like using the Tungsten cluster control (cctrl) program.

[tungsten@centos5a tungsten-community-2009-1.2]$ tungsten-manager/bin/cctrl
[LOGICAL] /cluster/comm/> ls

COORDINATOR[centos5a:MANUAL]

ROUTERS:
+-----------------------------------------------------------------------+
|NONE                                                                   |
+-----------------------------------------------------------------------+

DATASOURCES:
+-----------------------------------------------------------------------+
|centos5a(master:ONLINE, progress=3)                                    |
+-----------------------------------------------------------------------+
|  REPLICATOR(role=master, state=ONLINE)                                |
|  DATASERVER(state=ONLINE)                                             |
+-----------------------------------------------------------------------+

+-----------------------------------------------------------------------+
|centos5b(slave:ONLINE, progress=3, latency=0.0)                        |
+-----------------------------------------------------------------------+
|  REPLICATOR(role=slave, master=centos5a, state=ONLINE)                |
|  DATASERVER(state=ONLINE)                                             |
+-----------------------------------------------------------------------+

Starting from scratch and pulling code from SourceForge, it takes me about 30 minutes to get to an installed cluster with two nodes. At this point you have access to a very powerful set of tools to protect data, keep your databases available, and scale performance. Look at the manuals. Try it out. If you have questions or feedback, post them in the Tungsten forums. In the meantime, have fun with your database cluster.

p.s., We will post binary builds next week. The current build is in final release checks, so you may notice a few problems--I hit a Ruby warning on configuration that will be fixed shortly.

Tungsten Replicator 1.0.3 Release

2009-09-05T09:46:00.000-07:00

Tungsten Replicator version 1.0.3 is now released and available as a download from Source Forge. Tungsten Replicator provides advanced, platform-independent replication for MySQL 5.0/5.1 with global transaction IDs, crash-safe slaves, flexible filtering, and built-in consistency checking. The 1.0.3 release adds backup and restore, which I described in a previous blog article.

In addition, there are numerous small feature editions and some great bug fixes that raise performance and stability for large-scale deployment. For example, the replicator now goes online in seconds even when there are millions of rows in the history table. This fixes our previous go-online performance which was, er, pretty slow. Thanks to our users in the Continuent forums for helping us to track down this problem as well as several others.

As of the 1.0.3 release we are also starting to offer the enterprise documentation for the open source replicator. I think this provides better documentation all around, not least of all because we can do a better job of maintaining a single copy. Get current replicator documentation here.

The Future of Database Clustering

2009-09-01T13:29:00.000-07:00

Baron Schwartz started a good discussion about MMM use cases that quickly veered into an argument about clustering in general. As Florian Haas put it on his blog, this is not just an issue of DRBD vs. MySQL Replication. Is a database cluster something you cobble together through bits and pieces like MMM? Or is it something integrated that we can really call a cluster? This is the core question that will determine the future of clustering for open source databases.

I have a strong personal interest in this question, because Tungsten clustering, which I designed, is betting that the answer is changing in two fundamental ways. First, the problems that clustering solves are evolving, which will in turn will lead to significant changes in off-the-shelf clusters. Second, for most users the new clusters will be far better than solutions built from a bunch of individual pieces.

To see why, let's start with some history of the people who use open source databases and why they have been interested in clustering over the last decade or so. Open source databases have a wide range of users, but there are a couple of particularly significant groups. Small- to medium-sized business applications like content management systems are a very large segment. Large web facing applications like Facebook or GameSpot are another. Then there are a lot of custom applications that are somewhere in between--too big to fit on a single database dual- or quad-core server but completely satisfied with the processing power of 2 to 4 servers.

For a long time all of these groups of users introduced clusters for two main reasons: ensuring availability and raising performance. Spreading processing across a cluster of smaller commodity machines was a good solution to both requirements and explains the enormous popularity of MySQL Replication as well as many less-than-successful attempts to implement multi-master clustering. However the state of the art has evolved in a big way in the last couple of years.

The reason for change is simple: hardware. Multi-core architectures, cheap DRAM, and flash memory are changing not just the cost of databases but the fundamental assumptions of database computing. Pull out your dog-eared copy of Transaction Processing by Gray and Reuter, and have a look at the 1991 price/performance trade-offs for memory inside the front cover. Then look at any recent graph of DRAM and flash memory prices (like this one). For example, within a couple of years it will be practical to have even relatively large databases on SSDs. Assuming reasonable software support random reads and writes to "disk" will approach main memory speeds. Dirt-cheap disk archives are already spread across the Internet. The old graph of costs down to off-line tape has collapsed.

Moreover, open source databases are also starting to catch up with the hardware. In the MySQL community both MySQL 5.4 and Drizzle are focused on multi-core scaling. PostgreSQL has been working on this problem for years as well. Commercial vendors like Schooner are pushing the boundaries with custom appliances that integrate new hardware better than most users can do it themselves and add substantial database performance improvements to boot.

With better multi-core utilization plus cheap memory and SSDs, the vast majority of users will be able to run applications with adequate performance on a single database host rather than the 2 to 4 nodes of yore. In other words, performance scaling is rapidly becoming a non-issue for a larger and larger group of users. These user don't need infinite performance any more than they need infinite features in a word processing program. What's already there is enough, or will be within the next year or two.

Performance is therefore receding as a motivation for clustering. Meanwhile, here are three needs that will drive database clustering of open source SQL databases over the next few years.

Availability. Keeping databases alive has always been the number one concern for open source database users, even back in the days when hosts and databases were less capable. This is not a guess. I have talked to hundreds of them since early 2006. Moreover most users just don't have the time to cover all the corner cases themselves and want something that just works without a lot of integration and configuration.
Data protection. Losing data is really bad. For most users nirvana is verified, up-to-the-minute copies of data without having to worry a great deal about how it happens. Off-site protection is pretty big too. Talk to any DBA if you don't believe how important this problem is.
Hardware utilization. With the dropping cost of hardware, concerns about up-front hardware investment are becoming somewhat outdated. Operational costs are a different matter. Let's look at power consumption and assume a dual CPU host drawing 250W, which we double to allow for cooling and other overhead. Using recent industrial electricity rates of 13.51 cents per kilowatt/hour in California you get an electric bill of around $600 per year. Electricity is just one part of operational expenses, which add up very quickly. (Thanks to an alert reader for correcting my math in the original post.)

We will continue to see database clusters in the future: in fact lots of them. But off-the-shelf clusters that meet the newer requirements in an efficient and cost-effective way for open source databases are going to look quite different from tightly coupled master/master or shared disk clusters like Postgres-R and RAC. Instead, we will see clusters based for the most part on far more scalable master/slave replication and with features that give them many of the same cluster benefits but cover a wider range of needs. To the extent that other approaches remain viable in the mass market, they will need to cover these needs as well.

Simple management and monitoring - The biggest complaint about clustering is that it's complicated. That's a solvable problem or should be once you can work with master/slave methods instead of more complex approaches. You can use group communications to auto-discover and auto-provision databases. You can control failover using simple, configurable policies based on business rules. You can schedule recurring tasks like backups using job management queues. You can have installations that pop up and just work.
Fast, flexible replication - Big servers create big update loads and overwhelm single-threaded slaves. We either need parallel database replication or disk-level approaches like the proposed PostgreSQL 8.5 log-streaming/hot standby or DRBD. Synchronous replication is a requirement for many users. Cross-site replication is increasingly common as well. Finally, replication methods will need to be pluggable, because different replication methods have different strengths; replication itself is just one part of the clustering solution, which for the most part is the same regardless of the replication type.
Top-to-bottom data protection - Simple backup integration is a good start, but the list of needs is far longer: off-site data storage, automatic data consistency checks, and data repair are on the short list of necessary features. Most clustering and replication frameworks offer little or nothing in this area even though replica provisioning is often closely tied to backups. Yet for many users integrated data protection will be the single biggest benefit of the new clustering approach.
Partition management - In the near future most applications will fit on a single database server, but most organizations have multiple applications while ISPs run many thousands of them. There need to be ways to assign specific databases to partitions and then allow applications to locate them transparently. This type of large-scale sharding is the problem that remains when single application databases can run on a single host.
Cloud and virtualized operation - In the long run virtualization is the simplest cure for hardware utilization problems--far easier and more transparent than other approaches. A large number of applications now run on virtual machines at ISPs or in cloud environments like Amazon for this reason. To operate in virtual environments, database clusters must be software only, have simple installation, and make very minimal assumptions about resources. Also, they need to support seamless database provisioning to as capacity needs rise and fall, for example adding new VMs or provisioning an existing 4 core VM to a larger 8-core VM with more memory as demand shifts.
Transparent application access - Applications need to be able to connect to clusters seamlessly using accustomed APIs and without SQL changes. This is actually easier to do on databases that use simple master/slave or disk block methods rather than more complex clustering implementations. (Case in point: porting existing applications to MySQL Cluster.) Also, the application access needs to be able to handle simple performance-based routing, such as directing reports or backups to a replica database. The performance scaling that most users now need is just not that complicated.
Open source - For a variety of reasons closed approaches to clustering are doomed to insignificance in the open source database markets. The base clustering components have to be open source as some of them will depend on extensions of existing open source technology down to the level of storage and database log changes. You also need the feedback loops and distribution that open source provides to create mass-market solutions.

What I have just described is exactly what we are building with Tungsten. Tungsten is aimed at the increasingly large number of applications that can run on a single database. We can help with database performance too, of course, but we recognize that over time other issues will loom larger for most users. The technical properties described above are tractable to implement and we have a number of them already with more on the way in the near future. Master/slave clustering is not just feasible--it works, and works well for a wide range of users.

Still, I don't want anyone to mistake my point. There are many applications for which performance is a very serious problem or whose other needs cannot possibly be met by off-the-shelf software. Facebook and other large sites will continue to use massive, custom-built MySQL clusters as well as non-SQL approaches that push the state of the art for scaling and availability. Analytics and reporting will continue to require ever larger databases with parallel query and automatic partitioning of data as Aster and GreenPlum do. There are specialized applications like Telco provisioning that really do require a tightly coupled cluster and where it's worth the effort to rewrite the application so it works well in such an environment. These are all special cases at the high end of the market.

Mainstream users need something that's a lot simpler and frankly more practical to deliver as an off-the-shelf cluster. Given the choice between combining a number of technologies like MMM, backups of various flavors, cron jobs, Maatkit, etc., a lot of people are just going to choose something that pops up and works. The hardware capability shift and corresponding database improvements are tilting the field to clustering solutions like Tungsten that are practical to implement, cover the real needs of users, and are fully integrated. I'm betting that for a sizable number of users this is the future of database clustering.

p.s., We have had a long summer of work on Tungsten, which is why this blog has not been as active as in some previous months. We are working on getting a full clustering solution out in open source during the week of September 7th. For more information check out full documentation of open source and commercial products here.

Tungsten Welcomes Your Contributions!

2009-08-16T00:14:00.000-07:00

Tungsten clustering and replication has been accessible as open source for almost a year, but it has taken us an amazingly long time to get our contribution policy set up. The dithering ended promptly after Monty Widenius wrote an excellent blog article on dual-licensed software from his experiences at AskMonty.org and previously at MySQL AB. One of the things I especially like is Monty's emphasis on contributor rights. Contributor rights create the sense of reciprocity that makes open source function effectively as a development model. Tungsten is henceforth adopting the AskMonty.org contribution model.

So, if you want to contribute code to Tungsten (I'll describe shortly why you might to do that), you first fill out our handy Code Contributor Agreement and send it to us. The CCA says that you grant us rights to use the code within Tungsten as if we had written it ourselves, which includes selling it in licensed versions of Tungsten. At the same time, you retain your rights to the code and can also use it for any purpose you please including donating it to other projects, selling it, licensing it commercially, etc. It is really a very simple agreement. We also plan to match further protections to contributors as Monty.org adopts them.

After you send us the CCA, you can send us patches. Why would you want to do so? Recall that Tungsten allows you to create database clusters that protect you from data loss, failover quickly to replicas, and scale performance by spreading work around multiple data copies. Here are just a few ways you can help Tungsten do that better:

New types of backups. Anybody want to add built-in backups using InnoDB Hot Backup? How about storing backup files on Amazon S3? Note: The second project is very high up my personal list so you'll need to hustle.
Replication event filtering. Our forums see regular discussions about filtering (like this one). What about a handy filter to remove specific databases, tables, or columns when moving data from masters to slaves?
Support for new databases. Anybody need to replicate from MySQL to Amazon SimpleDB? How about Drizzle or PostgreSQL?
Sharding. Anybody need to connect transparently to databases spread across multiple shards? You can do it by extending SQL Router load balancing.
Fixing things that are broken.

We will accept any patch that provides beneficial improvements to Tungsten and will make an effort to integrate it quickly. In some cases we may have to rewrite them, which will of course delay integration. The more you work with us the faster that integration will occur.

Speaking of patches, we got an initial contribution to implement replication from MySQL into Drizzle earlier in the summer. Now that we have all the licensing paperwork in order it's time to get that one properly integrated.

Building the Open Source Hackers Cooperative

2009-08-09T14:15:00.000-07:00

It is striking how much harder it is to make money from open source than to write it in the first place. Open source development is a sophisticated and well-understood social activity. However, the economic model is often laughably primitive: "if you build it, they will come." That applies to the question of turning your open source project into a real job. More interestingly, it applies to the question of how to make open source projects as valuable as possible to the largest number of people. In this post I would like to propose an answer to both questions.

To illustrate open source sophistication, just look how easy it has become to start and manage projects. It is almost a cookie-cutter procedure. You pick one of a number of well known licenses, manage the code on SourceForge.net or Launchpad, communicate with the project through skype and mailing lists, and tell the world about it using your blog plus Twitter. Within an afternoon you have set up infrastructure to support efficient collaborative development with team members from Seattle to Singapore. The number of projects itself makes the point: SourceForge.net alone has over 230,000 projects.

Things get much harder if you want to make a decent living from a successful project. It’s not that there is a lack of models to choose. You could form a commercial venture that offers closed source/licensed versions of your open source project. However, as many of us have seen with MySQL AB and other companies, commercial efforts have a tendency to conflict badly with broader community interests or those of other companies that might be useful partners. For this reason, they don’t even necessarily maximize the value of the original commercial effort.

You could of course try to create a foundation like Apache or Eclipse. However, these generally require established software and large commercial backers. The current experience of the fledging Open Database Alliance and other efforts shows that it can be quite complex to create such organizations from scratch even with well-established code and well-known players. This is not a model that can be repeated across a wide number of different projects large and small.

Finally, you could sell consulting and support for your project. The problem with the consulting model is that it is not scalable—in order to make a decent living you have to work on customer problems. The pro-bono work like extending the project or tending to a community tends to fall by the wayside unless you can get a customer to pay for it.

The fact that it is hard to make money off open source is a symptom of a larger problem: we are losing the wider social benefits that for many people are the real point of open source software. This is an economic problem: how do we allow hackers to make a reasonable living on open source projects while maximizing the long-term value of the software to the widest possible number of users? It turns out there’s a reasonable economic model that can do this: cooperatives, which are defined as follows in Wikipedia.

a legal entity owned and democratically controlled [equally] by its members

Cooperatives have existed for centuries in many different forms and have successfully solved problems ranging from providing student housing to delivering consumer goods like sporting equipment on a grand scale. We need a new form of cooperative that I propose to call the Open Source Hackers Cooperative, or Hackers Co-op for short. The Hackers Co-op has three different but co-equal types of members:

Hackers, who are the core committers to the project and stewards of the code
Sponsors, who supply funding and labor
Community members, who use the software, test it, and contribute patches

As we will see, some members are more equal than others, which is why I added brackets in the cooperative definition.

Here are the organizational principles for an Open Source Hackers Cooperative:

The Co-op is a non-profit. It’s not for sale and there is no exit strategy. Like all co-ops, it exists to maximize benefits for its members.
Hackers work directly for the Co-op. Their time is divided between implementing features that interest them, integrating patches as well as fixing bugs reported from the community, and implementing features for sponsors.
Sponsors provide long-term funds and/or labor to the Co-op. Sponsors build businesses on the open source software and kick back a percentage of the business value in return for support and new features. They can also contribute labor for specific co-op tasks. Sponsors need not be for-profit businesses. They could in some cases even be governments or NGOs. The point is that they fund the software based on its value, for example through grants.
Community members provide leverage to the development model. They use the software, provide basic support through forums, and contribute patches for bugs and small features. These activities leverage the hackers who can use community patches and feedback to evolve the software. This is a very efficient development model.
The Co-op is a democracy. Co-op members vote on allocation of hacker resources. The vote is structured to keep a single group of members from hijacking the entire Co-op by dividing hacker time into a pie with allocations for the interests of each member type. Sponsors vote for their section of the pie using a vote weighted by their relative funding contributions--sponsors are not equal to encourage competition to commit more funds. Hackers effectively vote their portion by doing whatever they want with the time they get for personal projects. Community members vote through surveys or some other reasonable mechanism.
The Co-op has elected officers. There is a chief economist who is in charge of the business model and plans finances, contracts with sponsors, arranges employment contracts, etc. There is also a chief technologist who ensures project infrastructure and moderates technical discussions. Co-op officers are elected or at least approved by the members at large.
The Co-op pays dividends. Some open source projects are quite valuable, so a well-run co-op could easily become very profitable. The excess profits are distributed to hackers in the form of retirement and other benefits, to sponsors in the form of cash rebates, and to community members in the form of conferences, hiring of new hackers to work on features, improved infrastructure, etc.

You can try to run through a number of scenarios for the hacker co-operative to see how well the model holds together. All you really need is software with enough intrinsic value that it can sustain an active, technically aware community and where sponsors are motivated to build businesses on it but do not need to own the engineering. It is helpful to have community members be programmers, but you can also design the software to allow even non-technical users to contribute effectively.

Such conditions hold for a variety of system software like database management systems, app servers, and communications packages. With a little thought they could apply broadly to user applications like accounting systems or voting software, which so far are relatively rare in open source. The Co-op is model is quite stable, because it aligns interests in such a way that everyone does better if they stick together.

Hackers earn a stable, comfortable living and public recognition for working on software that they enjoy. “Comfortable” in this context means salary and benefits equivalent to a typical EU country like Germany or Finland, which are the gold standard for employee compensation. This works economically because hackers are (a) very productive to begin with and (b) become more so by leveraging an active user community. Hackers are motivated to produce because the more viable the software is, the better the co-op does, and the more benefits they receive.
Sponsors get features that they need using a productive open source hacking development model. This is a replacement for models like trying to take the software private and farming it out to low-cost offshore locations, which experience has shown to be badly broken on a number of levels. More important, sponsors get stability in the sense that the software cannot be taken over by hostile corporate interests and is supported over the long term, which lowers the risk of building businesses on it. They are motivated to contribute more in order to vote more resources for tasks that help their businesses.
Community members get software that is continuously supported and evolving rapidly to add features they need. They get assurance that valuable patches will be integrated. They are motivated to use, test, and develop patches for the software as that further increases its value to them and leads to recognition as authorities on the project.

The Open Source Hackers Cooperative can be structured to create a number of virtuous feedback loops that will support and extend the software over a long period of time. The Hackers Co-op model could be standardized and even backed up with software as well as cookie-cutter legal documents so that it becomes very simple to set up and manage.

I don't know if there are projects already following this model. Selena Deckelman already used the term "Hacker's Cooperative" though from a somewhat different perspective. If you know of anyone who has worked through the cooperative economics fully I would appreciate hearing from you. Meanwhile, this is not a theoretical question for at least a couple of reasons.

First, I work for a for-profit company that is willing to sponsor projects in exactly the way described in this article. We are looking for (a) control, (b) stability, and (c) a development model that is cheaper than doing it ourselves. If such cooperatives existed we would be interested in them. I'm sure we are not alone, because this is how all for-profit businesses tend to think. The cooperative is a viable model because of the way it reinforces and maximizes member interests.

Second, the Hacker Co-op leads to some interesting questions about how to design software that promotes the leveraging effect of an open source community. It is possible to design even application software so the your users don't all have to be coders in order to contribute. If you want to create something really big out of your open source project (or just have a nice job) it helps to think these issues through before you write much code. Maybe it's the first thing on the list after you set up your new project.

Backups, Backups, Backups (and Restore)

2009-07-20T23:38:00.000-07:00

Tungsten Replicator has built-in backup and restore for MySQL! I checked in the final touches over the weekend. Here's how to run a backup on a database and store it so you can restore it later. If you leave off options, we use the default back-up procedure and storage that you select when setting up replication.

trepctl backup [-backup agent] [-storage agent] [-limit timeout]

And here's how to restore. If you leave off the options, we find the latest backup in your default storage and load that.

trepctl restore [-uri backup_uri] [-limit timeout]

That's the syntax. Now here's what happens behind the scenes. First Tungsten Replicator has a new BackupAgent plug-in that implements backup procedures. We have a backup agent for each the following types of backups for MySQL:

Mysqldump - Probably least useful but easy to set up.
LVM snapshot to tar.gz - Scalable with miminal database down time. Features are similar to Lenz Grimmer's excellent mylvmbackup script.
Script dump - Integrate your own script for backup and restore. The script has to follow some very simple conventions, for which there is an example. You can integrate practically any backup/restore package this way.

Speaking as an unbiased user of the system I love the LVM snapshots. LVM snapshot is overall the most convenient way to do backups for a wide range of databases, not just MySQL, though I have to admit I have not used either InnoDB Hot Backup or Percona's XtraBackup. I guess now would be a good time to try them since we can integrate them through the script dump mechanism.

Meanwhile, mysqldump is far and away my least favorite mechanism, not least of all because of this unusually heinous bug, which completely breaks the mysqldump --all-databases command. It's still not fixed as of at least MySQL 5.1.34. (How on earth did this one get in and why didn't it get corrected instantly?)

Second, there is a new StorageAgent plug-in to handle storing and retrieving backup files. There is one of these for each type of storage. Currently the choice is limited to shared disk but I expect we'll have an Amazon S3 storage plug-in in the near feature. That's just too useful to pass up for very long...Among other things we ourselves run all our company services on Amazon and I would like to use it for our own backups.

If you want to use the new backup capability you can either build Tungsten Replicator yourself using the instructions on our getting started page or wait until the next binary build comes out in a couple of weeks. Backup and restore are documented here in the Tungsten documentation.

We went through a lot of effort to make the backup and restore processes as simple as possible. It's down to one keyword for each operation, so I don't think it's going to get much simpler. Please try it out and provide your feedback. I love bug reports and want to hear what you think.

When SANs Go Bad

2009-06-20T10:20:00.000-07:00

They sometimes go bad in completely unpredictable ways. Here's a problem I have now seen twice in production situations. A host boots up nicely and mounts file systems from the SAN. At some point a SAN switch (e.g., through a Fibrechannel controller) fails in such a way that the SAN goes away but the file system still appears visible to applications.

This kind of problem is an example of a Byzantine fault where a system does not fail cleanly but instead starts to behave in a completely arbitrary manner. It seems that you can get into a state where the in-memory representation of the file system inodes is intact but the underlying storage is non-responsive. The non-responsive file system in turn can make operating system processes go a little crazy. They continue to operate but show bizarre failures or hang. The result is problems that may not be diagnosed or even detected for hours.

What to do about this type of failure? Here are some ideas.

Be careful what you put on the SAN. Log files and other local data should not go onto the SAN. Use local files with syslog instead. Think about it: your application is sick and trying to write a log message to tell you about it on a non-responsive file system. In fact, if you have a robust scale-out architecture, don't use a SAN at all. Use database replication and/or DRBD instead to protect your data.
Test the SAN configuration carefully, especially failover scenarios. What happens when the host fails from access one path to another? What happens when another host picks up the LUN from a "failed" host? Do you have fencing properly enabled?
Actively look for SAN failures. Write test files to each mounted file system and read them back as part of your regular monitoring. That way you know that the file system is fully "live."

The last idea gets at a core issue with SAN failures--they are rare, so it's not the first thing people think of when there is a problem. The first time this happened on one of my systems it was around 4am in the morning. It took a really long time to figure out what was going on. We didn't exactly feel like geniuses when we finally checked the file system.

SANs are great technology, but there is an increasingly large "literature" of SAN failures on the net, such as this overview from Arjen Lentz and this example of a typical failure. You need to design mission-critical systems with SAN failures in mind. Otherwise you may want to consider avoiding SAN use entirely.

Lots of New Tungsten Builds--Get 'Em While They're Hot

2009-06-17T17:11:00.000-07:00

There is a raft of new Tungsten open source builds available for your replication and clustering pleasure. Over the last couple of days we uploaded new binary builds for Tungsten Replicator, Tungsten Connector, Tungsten Monitor, and Tungsten SQL Router. These contain the features described in my previous blog article, including even more bug fixes (36 on Tungsten Replicator alone) than I had expected as we had a debugging fest over the last few days that knocked off a bunch of issues. You can pick up the builds on the Tungsten download page. Docs are posted on the Tungsten wiki.

If you have questions, see problems with the builds, or just want to tell us how great they are, please post on the community forums or on the tungsten-discuss mailing list.

Our next open source release will be the Tungsten Manager, which is long overdue to join the family of regular builds. We are doing some polishing work on the state machine processing and group communications, after which the Manager will go out along with documentation on how to use it.

Tungsten Development News - Lots of New Features!

2009-06-10T12:48:00.000-07:00

Articles on this blog have been pretty scanty of late for a simple reason--we have been 100% heads-down in Tungsten code since the recent MySQL Conference. The result has been a number of excellent improvements that are already in Subversion and will appear as open source builds over the next couple of weeks.

Tungsten has a simple goal: create highly available, performant database clusters using unaltered commodity databases that are simple to manage and look as close to a single database as possible for applications. Over the last two months we completed the integration of individual Tungsten components necessary to make this happen.

Full integration is a big step forward and finally gets us to the ease-of-use we were seeking. Imagine you want to add a slave database to the cluster. There's no management procedure any more--you just turn it on. Managers in the cluster automatically detect the new slave and add it as a data source. That's the way we want every component to work from top to bottom--either on or off, end of story. It was really nice to see it start to work a few weeks ago.

We are now ready to start pushing builds out to the Tungsten SourceForge.net project. Here is a selection of the features:

Tungsten Replicator -- API support for seamless failover, certification on Solaris, better Windows support, testing against MariaDB, and many other improvements like flush events for seamless failover. There are already 26 fixes in JIRA and I expect more before we post the build.

Tungsten SQL Router -- Pluggable load balancing with session consistency support. Session consistency means users see their own writes but can read changes by other users from a slave. It works using a single database connection, which is an important step toward eliminating application changes in order to scale on master/slave clusters.

Tungsten Manager -- Directory-based management model that allows you to view and manage both JMX-enabled services as well as regular operating system processes that follow the familiar LSB pattern of 'service name start/stop/restart'. The managers use group communications and can broadcast commands across multiple hosts, handle failures, and automatically detect new services as they come online.

Tungsten Monitor -- Improved monitoring of replicator status including slave latency, which is necessary to guide SQL Router load balancing features like session consistency.

There's a lot going on with Tungsten right now, in fact far too many things to mention even in a longish post like this one. One of my current code projects is to implement built-in backup and restore for Tungsten Replicator. I am planning on supporting slave auto-provisioning: a new slave comes up, restores the latest backup, and starts replicating. All you have to do is turn the slave on. (More of that on/off stuff--it's kind of an obsession for us at this point.)

Integrating backup/restore is the final big feature for Tungsten Replicator 1.0--after this we plan to turn attention to parallel replication and are already discussing how this might work with several potential customers. Feel free to contact me through this blog or better yet post on the community forums parallel replication topic to join the conversation.

One final bit of news, we are starting to work seriously on Tungsten PostgreSQL integration thanks to a new partnership between Continuent and 2nd Quadrant. This work is commercially focused for now but will lead to additional open source features in the not too distant future. Keep watching this space... :)

p.s., We also had a nice refit on the community website. Check it out.

The Scale-Out Blog

Tungsten on the Beach--LA MySQL Meetup on Jan 11, 2012

Replicating Data Now and Then with Tungsten

Why So Many Proprietary Rewrites of MySQL and InnoDB?

I Really Dislike Anonymous Attacks

Benchmarking Tungsten Parallel Replication

Open Source Hardware

Quick Installation of Replication from MySQL to MongoDB

What's Next for Tungsten Replicator

The Inimitable Mr. Steven Jobs

Practical Multi-Master Replication using Shard Filters

First the Blog, now the Webinar: Adding Parallel Replication to MySQL in a Hurry

The System of Record Approach to Multi-Master Database Applications

So Where's the Fall MySQL Community Conference?

Adding Parallel Replication to MySQL in a Hurry

Mobile Internet Access in Germany for Open Source Road Warriors

Introducing Tungsten On-Disk Queues for Parallel Replication

Introducing MySQL to MongoDB Replication

Time for a SQL/NoSQL Group Hug

Tungsten Supports Logical Replication on PostgreSQL

Belated Thanks to MySQL Community

Settling in at code.google.com

O'Reilly Conference Tungsten Talks and Some Welcome Open Source Progress

Slouching towards Multi-Master Conflict Resolution

Parallel Replication Using Shards Is the Only Workable Approach for SQL

Is Apple Good for Innovation?

Tuning Tungsten Parallel Replication Performance

Understanding Tungsten Replication Services

Virtual IP Addresses and Their Discontents for Database Availability

Tungsten Replicator Overview Webinar

Fixing Replication with Replication

Interested in Sponsoring Tungsten Open Source Features?

It's All about the Team

Parallel Replication on MySQL: Report from the Trenches

MySQL Disaster Recovery With Tungsten

MySQL Conference Slides and Thoughts on State of the Dolphin

Customized Data Movement with Tungsten Replicator Pipelines

New Tungsten Software Releases for MySQL and PostgreSQL

Replication and More Replication at 2010 MySQL Conference

Tungsten and PostgreSQL 9 at PG-East Conference

MariaDB is Thinking about Fixing MySQL Replication and You Can Help

Tungsten 1.2.2 Release is Out - Faster, More Stable, More Fun

What's in *Your* Binlog?

Exploring SaaS Architectures and Database Clustering

Proving Master/Slave Clusters Work and Learning along the Way

Replicating from MySQL to Drizzle and Beyond

Community Builds for Tungsten Clustering

Tungsten Replicator 1.0.3 Release

The Future of Database Clustering

Tungsten Welcomes Your Contributions!

Building the Open Source Hackers Cooperative

Backups, Backups, Backups (and Restore)

When SANs Go Bad

Lots of New Tungsten Builds--Get 'Em While They're Hot

Tungsten Development News - Lots of New Features!

What's in Your Binlog?