Database Replication

Galera 2.0beta - node joining with incremental DB synchronization

2012-01-02T15:26:00.000-08:00

Galera replication 2.0 beta has been released, get your download in https://launchpad.net/galera/2.x/22.2.0beta

This release comes only with Galera library build, there are 64bit DEB and RPM packages available for download. To test Galera 2.0 replication, install the beta library in Galera cluster 1.1 and you are ready to go (wsrep API version #22 is required).

Galera 2.0 has as a new feature incremental state transfer (IST). Earlier Galera cluster behavior was to always copy full database to new joining node. With IST, the previous state of joining node is taken in consideration, and only the delta of missing transactions will be transferred and applied in joining node. Applications having big database, will experience a great performance boost with node join operations.

The beta cycle is expected to continue for a few weeks and is then followed by 2,0 GA release, stay tuned.

Galera Cluster Release 1.1 - Out She Sails

2011-12-17T08:00:00.000-08:00

Galera release 1.1 has been published and rolled out in the open!

This release has a bunch of bug fixes and one prominent new feature: rolling schema upgrade. With rolling schema upgrade, it is possible to apply schema changes (DDL statements e.g. CREATE, DROP, ALTER, etc...) for each cluster node separately. The node to be upgraded is a member of cluster but is not applying replication events as long as the DDL statement is processing. With this, the cluster can stay in production due out the schema upgrade process. I will write a more detailed description how it works in a separate blog.

The 1.1 release uses new version of replication API: wsrep API #22. This means that both MySQL and Galera components must be upgraded with same go.

Galera release 1.1 is available for downloads: here. There are separate versions both for MySQL 5.5.17 and MySQL 5.1.59.

Oli wrote an excellent blog about howto upgrade Galera from 1.0 to 1.1 level in: FromDual blog. While landing on FromDual site, check out Oli's other blogs as well, plenty of Galera related stuff there.

And, don't forget Severalnine's ClusterControl, it offers benefits like:

Hassle free installer for Galera Cluster
Excellent cluster aware GUI for Galera cluster monitoring and management

You can start your ClusterControl journey here: ClusterControl™ for MySQL Galera

Galera Presentation in PerconaLive

2011-10-27T14:43:00.000-07:00

Here is the slide set for the Galera release 1.0 presentation given in the PerconaLive London conference: Galera Replication Release 1.0, Getting There. Many thanks for Percona for all the effort in organizing this fabulous event.

And special thanks for Clustrix for the Revolution Party, and congratulations as well for the awesome benchmark results. Clustrix sure runs at the "ludicrous speed".

MySQL/Galera Release 1.0 - Replication Redefined

2011-10-25T09:43:00.000-07:00

MySQL/Galera Release 1.0 has slipped from our hands, we could not hold back it anymore. The pressure was just too immense from the community, our partners, and our new sales guy (specials thanks to Sakari for kicking some engineer ass).

So, the infection is underway, and there will be no return to the steam-age with async replication. Galera is replication redefined - and this is permanent. Biggest news in this release is that we now support both MySQL 5.1 and 5.5 series. Other nice features (since 0.8.1 level) include:

replication over SSL
garbd - the lightweight arbitator
causal read support
persistent write set buffering

Not all that can be expressed with one sentence. but believe, me you are dying to to get it, because you cannot live without it.

So, there it is: www.codership.com/downloads/download-mysqlgalera/ .

And there is even more to it. Our beloved western neighbors, brave dudes from Severalnines have worked, if possible, even harder than us and are releasing Severalnines ClusterControl for MySQL Galera. ClusterControl provides three utilities:

configurator
monitor
management

...which pretty much cover all the tasks you need to do with your cluster.

Check out: www.severalnines.com/galera-configurator to get started. With the configurator, you just fill in your system information, and you will receive a tarball with deployment script, which will install and configure your MySQL/Galera cluster automatically.

So there goes, now back to PerconaLive - it is intense here!

Galera Presentation in PerconaLive, London Conference

2011-10-18T01:12:00.000-07:00

Team Codership will give a Galera presentation in the next event of the distinguished PerconaLive conference series in London (Oct 25).

We will send a three person mini-delegation in the conference, and here we follow the guidelines we have been preaching to the Galera community for long: always have at least three members in the cluster for high availability. With the presence of conference buffets, evening reception, London attractions, pubs & night life, Chelsea stadium etc..., our team may have to do a few internal failovers. But, three member staffing guarantees that at least one of us is available at all times for any discussion you may want to get us involved with, preferably HA, replication or clustering related, as these topics make our world tick.

Our presentation will focus in Galera replication new features developed during summer time. Since the MySQL User Conference in April, we have pushed three new 0.8 series releases, and, now the Galera 1.0 release is just about sliding out of the shipyard. A wealth of new features altogether, well worth 30 min talk session.

Ohoj! hope to see you there!

Era of Galera - Release 0.8.0 Published

2011-06-22T14:41:00.000-07:00

MySQL/Galera 0.8.0 starts a new major release series of Galera clustering. It features significant improvements in reliability, usability and performance over 0.7 series and is not backward compatible with it. However its principles of operation (synchronous multi-master replication) remain the same as well as most of configuration settings. 0.7 users can reuse their old configuration files with it.

Most of the changes relate to the internal workings of Galera replicator. Improvements noticeable to end users include:

better configuration system

scriptable state transfer methods

scriptable notifications for cluster and node state changes

better status reporting for troubleshooting

better performance through optimized flow control and parallel
applying

higher transparency (many compatibility bugs fixed)

For more information on 0.8 series see Galera Wiki

Download from Codership Downloads

Many thanks for early feedback from Vadim (Percona) and Oli (FromDual)

Codership Presentations in Collaborate11 & MySQL User Conference

2011-04-22T22:38:00.000-07:00

Codership team did a major two week US tour in April visiting both Collaborate11 and MySQL User Conferences. Three presentations were given during the trip:

Collaborate11: Galera Replication
MySQL UC: Galera Replication
MySQL UC: A State of Multi-Master Replication

The Galera presentations are more or less with same content and show the current state of the Galera project. There is also some real fresh data about recent benchmark runs in Amazon EC2 environment.

The multi master speak explores various multi-master solutions putting them under common classification.

Collaborate is a huge conference with mostly Oracle content. We had time to check out Oracle's MySQL sessions, and were impressed by the progress in the 5.6 release, .e.g. the parallel applying will be available for early testing. This will work in database granularity level, but there should be no obstacles in making parallel applying on table level as well. This looks promising, good work O'mighty Oracle.

MySQL User Conference is as active and vivid as before. However, the expo side is still in recession. Maybe it is high time for us to straighten up and bring our Galera booth there next year. We saw many outstanding presentations in the conference and there were interesting talks in the hallways as well, this conference is sure worth a visit.

Synchronous Replication Loves You Again

2011-04-09T16:08:00.000-07:00

So, the other day I posted the performance benchmarks for the multi-master MariaDB/Galera cluster. Spectacular performance. But some of you may justifiably say:

— Well, we were born into a master/slave world. We, like, adapted to it. We have invested so much brain power to make our applications to work in master/slave environment. What do we do now with all these read/write splitting voodoo Lua scripts and slave lag battling techniques? And master failover... There's a whole industry there. Thousands of jobs!

— But of course! — I say, — Galera likes read/write splitting like the other guy. If you want a node as a slave - just don't use it as a master, simple as that. (Slave lag and master failover will have to go though. Sorry about that.)

Also about a year ago I was so fed up with the expert opinions about how synchronous replication is "slow" (Why "slow"? The word "synchronous" does not even have 'l' in it!) and does not work over WAN, that I ran a quick ad-hoc benchmark with 0.7pre (collector's edition) to see about it. Well, I saw it pretty well, but promised to get back to it later, with a more scientific approach and a more configurable Galera.

So in this installment I'll benchmark synchronous master/slave Galera 0.8pre performance, both in LAN and between Ireland, EU, and Virginia, US. In a scientific way. Yes, with standard deviations and stuff. Amazon EC2, despite its dismal IO performance will help us with that.

The configuration is the same as in the previous article. To make Galera more WAN-ready I added the following options (this must be put on a single line in my.cnf):


wsrep_provider_options="gcs.fc_factor=0.95; gcs.fc_limit=1024; evs.send_window=512; evs.user_send_window=256; evs.suspect_timeout=PT30S; evs.inactive_timeout=PT60S; evs.consensus_timeout=PT90S"

Among other things it allows to have up to 256 transactions in replication at a time. But it in no way compromises synchronous guarantee. For reference see Galera wiki

What do we have here, in order of appearance:

Stock MariaDB with innodb_flush_log_on_trx_commit=1 as an alternative to synchronous replication.
Single MariaDB/Galera node, which normally should not be run alone, but it serves as a reference point for master/slave configuration performance. I.e. "how much slower do we get?".
2-node master/slave cluster in the same eu-west accessibility zone.
2-node master/slave cluster between eu-west (Ireland) and us-east (Virginia) zones.
3-node master/salve cluster with 1 slave in eu-west and another in us-east.
2-node multi-master cluster in the eu-west zone for reference.
2 eu-west master nodes + 1 us-east slave — how does adding a transcontinental slave affect multi-master performance?
eu-west client connects to standalone us-east server directly. Just to see if we need this WAN replication at all.

A blowup of the most interesting part:

And since we're especially concerned with transaction latencies here, here's the latency profile at 32 threads. Just to see it tad clearer how bad it could be:

Well, what to say here? Synchronous replication in LAN does not really make a difference to a standalone server. Move along, nothing to see here. Except that it is so much faster than flushing logs after each commit. As for the WAN, it does add latency to transaction. Guilty as charged. Up to 90 milliseconds!

Of course we have an alternative to this slow synchronous replication: direct connection to central server across the world. The proponents of this approach are in for an exquisite fun of having several seconds latencies even on idle servers.

We can also notice an interesting property of replication latency contribution — it is only noticeable until transaction execution latency takes over. That is when master becomes saturated. However the same is not true for IO latency, because, unlike data replication, data flushing tends to monopolize the resource. That's where group commit should come in.

...with special thanks to Google docs for visual effects.
Original...

Scaling-out OLTP load on Amazon EC2 revisited.

2011-04-07T16:18:00.000-07:00

It's been long known that Galera optimistic replication and enterprise-size databases are a match made in heaven. Today we're going to get a little closer to testing this statement.We'll have look at how Galera can scale out Sysbench OLTP complex 60 million rows workload in EC2. This is a first proper benchmark for 0.8 series and also the first benchmark of MariaDB/Galera port, so I'll start modest, just to see how it goes. I chose m1.large instances with 7.8Gb of RAM for cluster nodes and c1.xlarge instance for a client — I don't want the client to be a bottleneck.

For comparison I have also measured performance of a stock standalone MariaDB 5.1.55 server. I used the standard my.cnf that comes with MariaDB Debian package with the following alterations:

max_connections=1024
innodb_buffer_pool_size=6G
innodb_log_file_size=512M

Galera nodes also add

innodb_flush_log_at_trx_commit=0
innodb_locks_unsafe_for_binlog=1
wsrep_slave_threads=16

That's right. One of the purposes if this study is to see how synchronous replication can be an alternative to on-disk durability.

EC2 environment is known for its inconsistent performance, so in order to get reliable figures each measurement (a 20-minute sysbench run) was performed 3 times with an interval of ~2 hours between each. Overall the main part of the benchmark ran non-stop for over 30 hours. Surprisingly consistency was very good. Most of transaction rate scores had standard deviations well below 1%, and latency figures — below 2%. (This essentially means that every shape you see on the charts is statistically significant.) Most inconsistent measurements came from standalone MariaDB server and in a short while we'll discuss possible causes of this.

60 million sysbench rows is more than 14Gb (UPDATE: in the course of benchmarking it grew up to 20Gb) in the table space and with a 6Gb buffer pool this makes the benchmark quite IO-bound. I suppose this is a fairly realistic setup. My initial guess was that it should be favorable for Galera, as any heavy load should, but IO-bound is not the same as CPU-bound, so I was not certain how exactly it would affect slave threads blocks lookup, for example.

Ok, enough with the chatter, here are the charts:

Three things (besides striking performance and scalability) are readily noticeable on that chart:

Stock MariaDB performance is rather poor. 2-node MariaDB/Galera cluster more than doubles it and 4-node — triples. It also does not display a characteristic performance peak at a certain number of connections. Instead its throughput seem to be slowly, but growing with the number of threads, meaning that context switch penalty is negligible in this case. Something else is severely limiting its performance.

The answer to it seems to be that the load is really IO-bound and the need to flush log after each transaction commit does not help it in any way. Well, that's the price one has to pay for not using synchronous replication when he can't afford losing any transactions. (Yeah, I know, rather than to implement synchronous replication, people learned to live with losing data.)
Unsaturated part of 5-node curve lies below 4-node curve. This is most easily explained by a growing replication overhead. Indeed, with 8-7 connections per node, there is still plenty of CPU and IO resources. However, we get less direct connections per node (lower efficiency) plus 25% increase in replication latency (even lower efficiency) which requires more connections to compensate for it. In fact, if we look at a 4-node curve, we can see that it almost goes to under the 3-node curve at 32 connections.
Scalability that took off so wonderfully somehow hits a ceiling at around 1100 trx/sec. What is it? Well, there are several suspects:
1. Replication overhead has finally caught up with us. See above item.
2. Slave workload has caught up with us, see this article about multi-master arithmetics. Indeed, sysbench OLTP load has about 25% of write queries, so 1/0.25 + 1 = 5 — pretty close to what we have. However, first, RBR event applying is far cheaper than query processing in the first place, so this estimate is faulty. Second, why so abrupt?
3. Slave threads cannot handle all that replication traffic from 4 peer nodes (slave thread pool saturated). I've been using a pool of 16 slave threads and while in 2-node cluster that could be more than enough, in 5-node cluster we have 4 times more of slave traffic.
Of course all three of the above reasons combined could (and no doubt will at some cluster size) end the scalability. However I did not expect it so soon and so firm. Just look at them red, orange, green and magenta curves - doesn't it tell you that there is a space for at least one more node?

Replication overhead, while well pronounced with small number of threads per server, is clearly negligible when saturation is reached. This can be easily seen from the latencies graph where bigger clusters actually display lower latencies due to the distribution of workload. To put it another way, in saturated server transaction latency is dominated by the progressive deficit of internal resources, rather than the constant replication overhead.

Slave workload saturation can be ruled out by a simple observation of CPU usage on the nodes. In 4 and 5 node configurations idle CPU time was considerably above 50% (in a 2-node case it was ~20%). The nodes clearly hadn't run out of CPU power. Something else was holding a performance. Normally that would be IO waits.

But IO waits come in two forms:

IO waits caused by buffer pool misses, which are normally dealt with by increasing concurrency: while one thread is waiting for a block, another is performing useful work.
IO waits like "cannot perform more than this amount of IO operations per second" - and that is what we see in case of a single MariaDB server.

So, which one we're dealing with here?

In addition to low CPU usage, another interesting observation was made there. Galera 0.8 has a status variable 'wsrep_flow_control_paused' which shows the fraction of time replication was paused due to waiting for other nodes. While I could not devise a method to chart it here, it was very easy to see that this fraction grows with the number of nodes and in the 5-node cluster is routinely around 60-70%. Essentially this means that slave writesets cannot be applied as fast as they arrive. But not because of lack of CPU power!

Slave thread pool saturation seems like a most likely smoking gun here, so I'm running the same benchmark in 4, 5, and 8-node configurations with 64 slave threads. It should put 5-node cluster on the equal footing with 2 nodes.

Another possible trick to try is Out-Of-Order-Committing (OOOC): even with several concurrent slave appliers we can encounter a transaction that takes especially long to apply and thus hold all other slave threads from committing. In this case OOOC would allow other slave threads to continue their work.

So I've rerun the benchmark with the aforementioned tricks - but on different set of EC2 instances (only the first node that was used for plain MariaDB testing was consistently present in all setups) and that yielded somewhat incomparable results... Which is in a sense good, because it explains some things.

First of all, overall throughput is considerably lower than in the initial benchmark (gray curves). Well, this can be explained by that not all m1.large instances in EC2 are equal, and they certainly are not. That it is not a random fluke is corroborated by the amazing consistency that, say, 4-node curves show in this benchmark.

And so, since m1.large instances can be so different in performance, a natural explanation for a lack of scalability when going from 4 to 5 nodes in the initial benchmark is that the 5th node simply happened to be too weak to add any extra throughput.

Moreover, it looks like we happened to have a more even set of instances here and we see a healthy 12% increase in throughput for 25% increase of nodes. Scalability is still there! Notice, that we're talking about scaling-out of sysbench OLTP complex benchmark which nobody else ever dared to do. And, surprise, even 8 nodes show some increase in performance. Maybe quite marginal, but it still does what it is supposed to do: more nodes can handle more clients. Isn't it what "scale-out" is about?

Now, the other thing is that apparently neither expansion of slave thread pool to 64 threads, nor OOOC resulted in any throughput improvement.

The first thing is easy to explain. From the first chart we see that single node is pretty much saturated at 16 connections. This is with client-server communication latency in play. Slave threads have no client-server communication, so they saturate the server even sooner. So adding more slave threads will more likely to harm performance than to improve it.

OOOC on the other hand sets existing threads totally loose and with practically no collisions between the writesets they should be able to apply them in a virtually tight loop. Still, this doesn't happen. And this last observation forces us to make the only possible conclusion: m1.large instance simply cannot handle more IO operations.

This can be further explained by that if the load is IO-bound and if reads and writes on average cause the same amount of buffer pool misses (on master end this probably does not hold, due to reads prefetching pages that might be used by writes, but on slave end it most probably does), then we arrive to the same old formula that places practical scalability limit at 4-5 nodes.

Conclusions and further work.

Well, m1.large instance IO seriously disappointed. In fact, measuring 8Gb file creation time has shown that /dev/sdb there is slower than my laptop hard drive, which is kinda hard to justify. Several things can be tried to improve it:

Use EBS volumes for data and innodb log storage. They were said to be faster, however this is an extra expense and complexity. I was trying to show how you can have a highly-available MySQL on EC2 without resorting to non-volatile storage. Seriously, how hard is it to make local drive fast enough?
Try a higher-end instances (yeah, cluster compute!). Well, as soon as we get budget for that I'll surely give it a try. Although I have little doubts that result will be nothing short of stellar.
Try with an xfs/ext4 file system on /mnt instead. ext3 is as adequate a choice as fat16 these days.

Replication overhead. Yes, it is small, but we can see it and I'd prefer that we can't. The problem here is that EC2 does not support multicast and probably never will, so no luck here. Some experiments with multicast though have suggested that even using UDP unicast could considerably improve latencies compared to TCP. This could be out next optimization project.

As for the rest, we saw great performance, great scalability and a good reason to use Galera replication instead of innodb_flush_log_at_trx_commit=1.

Downloads

MariaDB-wsrep port is available from lp:codership-maria.
Galera wsrep provider is available from lp:galera.

To be continued with master/slave and WAN benchmarks.

Original.

Codershippers in FOSDEM

2011-02-09T02:00:00.000-08:00

Codership team spent this weekend in Brussels, in and around the ever famous FOSDEM 2011 conference. This time there was quite a lot of the *around* part, as we were traveling with families, and trip scheduling was mostly not within our control. We regrettably missed .e.g. the MySQL dinner, which we later heard, was fueled by Monty's black bottle (TM, hallelujah).
In the conference, when we finally got in there, we gave a presentation which explores various Multi-Master replication technologies, classifying, comparing and sorting them, (and in the end, boiling in deep oil). This presentation will grow both in length and content and will be given in longer version in the O'Reilly MySQL User Conference, in Santa Clara, Apr 14. For this, we welcome any status updates you might have of your favorite Multi-Master replication solution. I know that at least the Tungsten dudes are working hard developing Multi-Master Tungsten 2.0 replicator. I hope you can send your project status before the conference.

The FOSDEM presentation slides are available for download in: http://www.codership.com/files/presentations/MMRep_FOSDEM_2011.pdf

From Behind The Ash Curtain

2010-04-27T23:44:00.000-07:00

Like many other MySQL conference visitors, also Codership team was stranded in bay area due to the volcano eruption in Iceland. For us however, the extra time in silicon valley was not an issue, as we were working on local assignments anyways, and staying local enabled us to work really focused during this period.

Also the "evacuation" from SFO worked out really well for us, thanks to KLM and Air France. We just visited SFO on Thursday (22) and got flights for the next morning. I actually got two bookings, which was a little embarrassing. There were many empty seats and especially the CDG-HEL leg was practically empty. Ash refugees had already left by train, I guess.
The return from SFO happened somewhat too early for us, as we had holiday plans for the forthcoming weekend, and we had to cancel the fun part. So this trip ended up as all work and no joy for us...

The conference itself, was fun and very interesting. We were busy first to prepare our presentation, then giving the presentation and finally sorting out mis-conceptions caused by the presentation. But it was fun altogether, and Galera got a lot attention there. We had many interesting conversations of Galera and replication strategies in general.
To my disappointment, the expo hall was half empty, I'm not sure if this can be profitable and it makes me wonder of the future of this conference.

On Fri 16th, there were both Drizzle and MariaDB conferences, which we planned to visit but were too busy to catch. We wanted to sort out the differences between the Drizzle replication API and wsrep API in very detail, but this work must be postponed a little.
We, however, entered MariaDB conference just when they were closing, and hooked into short replication discussion with Kristian Nielsen, Sergei Golubchik and Paul McCullagh. MontyProgram is driving the Replication API design and implementation and we don't need to work inside MariaDB code base. Codership will just provide Galera plugin for the end solution.

Paul brought to my attention an interesting fact about PBXT: rollback is low effort operation with PBXT. This is very favorable for Galera replication (and optimistic concurrency control in general). Odds are that PBXT will scale better in Galera cluster, even with hot spot work loads.

Slides of our presentation are available here: http://en.oreilly.com/mysql2010/public/schedule/detail/13286

Codership Visit in O'Reilly MySQL Conference

2010-03-20T23:34:00.000-07:00

I will be presenting Galera replication in O'Reilly MySQL Conference & Expo on April 14. Here is the link to the presentation abstract:
Galera - Synchronous Multi-master Replication For InnoDB.
The presentation will be run jointly with Alexey Yurchenko and will focus mostly in the practicalities of managing Galera cluster, like:

Howto download and install MySQL/Galera cluster

Configuration options, clustering use cases and topologies

Managing Galera cluster, joining node(s) in cluster, backups etc...

Application connectivity options

Monitoring Galera cluster, troubleshooting best practices

This presentation will give in a nutshell all you need to know to start using Galera cluster as your application's data redundancy solution.
We plan to arrange a somewhat extended trip in Bay area, so we will have spare time for ad-hoc meetings. If you would like to have a f2f meeting with Codership team, just get it touch. We can arrange Galera demonstrations / presentations, or .e.g. analyze your use case in detail showing how Galera works for your application load.

See you in Santa Clara!

For Those About to Galera - We Salute You!

2010-02-11T23:46:00.000-08:00

Two new Galera presentations are available for downloads. First is from the ever famous
FOSDEM 2010 conference in Brussels, where I visited the MySQL Developers' room and
presented a 20 minute overview of Galera project. This presentation contains new 100% insert rate benchmark and synchronous WAN replication test results. Get your copy from here.

Yesterday, we presented Galera replication in MySQL University session. The focus of this webinar is to describe our replication API (wsrep API) and our patch in MySQL source code to support the API. This is MySQL oriented and quite technical presentation. The session was recorded and you can play it in MySQL University site: Galera presentation. The plain presentation slides (without disturbing narration), are also available in Codership site

Thanks for all the feedback! If your comment/question looks to have public interest, don't hesitate to post in our mailing list
We are working now for 0.7.3 release, and will publish in very near future (we look to squeeze MySQL 5.1.43 merge and "LOAD DATA LOCAL..." support still in the package). To ease the installation, we have also debian and RPM packages coming soon. They are needed for our very first cloud image, we are working on.

MySQL/Galera 0.7.1 Released

2010-01-14T02:39:00.000-08:00

MySQL/Galera release 0.7.1 ships out.

This is a maintenance release, which has fixes for 9 issues, listed in launchpad release page:

https://launchpad.net/codership-mysql/0.7/0.7.1

It makes sense to upgrade, if you suffer from any of the above.

Most notable changes are perhaps fixes for running concurrently DDL and DML queries. The MySQL version has also been bumped two notches up to 5.1.41

Prebuilt binary downloads are available, as usual, in the launchpad site:
https://launchpad.net/codership-mysql/+download.
Pay attention to pick the latest 0.7.1 version, as launchpad seems to give precedence for the old 0.7 release (no matter how hard I try to configure LP...).

Galera Author Interviewed by Himself

2009-12-11T05:05:00.000-08:00

We just made a major software release, but I still don't see journalists queuing outside our office. Looks like I have to do the hard work and interview myself. In the following, I'll give rough reporter treatment to me:

So, what are we talking about?
MySQL/Galera release 0.7 - synchronous multi-master clustering solution for InnoDB.

Downloads? Where?
.e.g. here: https://launchpad.net/codership-mysql

Support?
Sure, here: www.codership.com/services/consulting
But, can't you ask any longer questions?

Oh, sorry, assumed that you geek people prefer not to talk with natural language. But, what is this Galera thingie good for? For whom would you suggest this release?
Practically any innodb user can potentially benefit of MySQL/Galera. There are no unnecessary tweaks in the MySQL behavior. Odds are good that your application will notice no difference when compared with vanilla MySQL.

If high availability is your need, Galera provides that out of the box, due to synchronous replication. After committing, the data is safe in every active cluster node, simple as that.

And, if more performance is needed, Galera can boost your data access considerably. Note that, Galera scales even write intensive workloads. However, hot spots are poison for this replication method. If workload contains focused hot spots, the number of write-accepting masters should be reduced.

Is it good for production, anything to worry about?
We have tested this during a focused test session after 0.7pre release, and we are quite happy with the stability. Two issues were postponed for future maintenance release. There is obvious issue when running DDL and DML concurrently in the cluster. That should be avoided, if it ever were in your plans.

But no matter how much we can test in laboratory, for production use, it is anyway essential to evaluate with the real application and with test load that closely simulates production use.

How stable is it, can I go in engine room and pull out cables wildly?
yes! 0.7 release was designed to be fault tolerant and can recover from most of the expected and un-expected situations. It tolerates even ad-hoc engine room visits.

Does it support innodb plugin?
This build is over MySQL 5.1.39 and innodb plugin is in there. We have enabled innodb plugin in the build and did also some compatibility tests with it. No issues surfaced, but our testing was quite minimal. .e.g. no performance testing has been run with plugin version.
MySQL/Galera will start by default with builtin innobase engine.There is configuration sample in the distribution showing how innodb plugin can be loaded, if you want to play with it.

Everybody is talking of this emerging MariaDB, any plans on supporting that?
Yes, plans and even actions. MariaDB version will be available here: https://launchpad.net/codership-maria

Everybody is talking of PostgreSQL, any plans on supporting that?
PostgreSQL has been in our roadmap from the very beginning. However, reality bites, and in practice MySQL development has eaten all our resources so far. We plan to get PostgreSQL development rolling in near future, but it sure would help if some experienced PostgeSQL partner would join in this development.

Is this a cry for help, or what?
Yes

So, what's next?
Next in schedule is maintenance release 0.7.1, ETA before end of the year. It will mostly address issues in running DDL and DML concurrently. In general, the maintenance release cycle will be kept as short as possible.
Next major release will be 0.8, which has features for considerably faster node join operation. (currently we are limited by mysqldump speed...).
Also MariaDB porting will continue with added effort. One more cup of coffee, and I will promise MariaDB port during December time frame.

Thanks! What are we?
You are welcome

And what was it?
It was a pleasure

MySQL/Galera Release 0.6

2009-05-15T04:21:00.000-07:00

MySQL/Galera release 0.6 shipped out today.

MySQL/Galera is synchronous multi-master clustering solution for innodb storage engine, offering un-compromised performance and thanks to certification based replication model, scalability even with write intensive work loads.

We have tested MySQL/Galera 0.6 with a number of benchmarks. Here is a summary of sysbench oltp benchmark run on clusters of 1-4 nodes of Amazon EC2 large instances: sysbench results Scalability is remarkable here and many other benchmarks show similar performance gain.

The 0.6 release adds following new features over the earlier Demo-2 release:

Merged with MySQL 5.1.33
Full DDL replication using "total order isolation" mode
Workaround for drupal issue #282555. The fix is simply about retrying the failed autoinc insert query
...and some bug fixes to go

The MySQL/galera 0.6 is binary linux release (both 32 and 64 builds available) and is available in: Codership Downloads. This release has passed a number of feature and performance tests .e.g. with Drupal benchmarks.

You can evaluate MySQL/Galera 0.6 with minimal effort. Just install and configure MySQL/Galera in each node in your cluster. Then start group communication daemon and all MySQL servers. MySQL/Galera cluster is functional at this point and you can load your data in one cluster node, data will replicate to whole cluster. Then start your application and connect to any node(s). You can also use load balancer in front to balance connections between nodes. We have good experience with Galera Load Balancer (glb: Codership Downloads), but in practice, any TCP level load balancer will do as well.

Next Galera release will be 0.7 and it is under R&D effort having deadline at the end of June. The 0.7 release will be open sourced and is functionally quite complete, offering.e.g. node join capabilities for the cluster. Galera is cooking good at the moment.

Clustering Drupal

2009-04-15T02:38:00.000-07:00

We have been testing MySQL/Galera cluster with various benchmarks and one exercise in our test plan is to try clustering performance in web application level. We picked Drupal as our first target application and composed cluster from identical Drupal instances. Each Drupal node has local MySQL database and we cluster the databases with Galera synchronous multi-master replication system. As a result, the effects of http requests hitting any Drupal node, will be synchronously replicated to the whole cluster.

Alex wrote a detailed article about the benchmarking session. I present here just an executive summary and go directly to the final results.

Test Platform
We tested the Drupal cluster with Amazon EC2 small instances. Small instance is not particularly suitable for web platform due to long latencies, but we got our baseline figures from this setup, and decided to run more high end tests later on.

Running the test, shows strong unbalance between Apache/Drupal and MySQL CPU usages. MySQL consumes just 5-10% of the CPU and rest goes for Apache. Resource-wise, it would make sense to create separate farm for web servers and have a small MySQL cluster serving the farm. However, our test configuration has some advantages as well:

It is easy to setup, each node is identical
Local MySQL gives faster response to Drupal
It is possible to fallback on one node only

For the test session, we created clusters from 1-4 Drupal instances.

The Test
For testing, we used a jmeter test, which runs three thread groups:

Posters - create new pages in the system
Commenters - read pages and add comments to the stories
Browsers - just keep on reading pages in the system

The jmeter http load goes through glb load balancer to the Drupal cluster. Each http request can hit any cluster node at will.

Results

Final results show quite linear scalability:


Nodes   Users   Request rate   Latency   Error rate
        (req/min)      (ms)      (%)
---------------------------------------------------
1       40         129         3950      0.07
2       80         259         3960      0.06
3       120        387         3700      0.05
4       160        514         3490      0.12

Scaling continues linearly up to four nodes and we did not try with larger cluster sizes.

Near Future
Alex promised to continue with Drupal testing and run the tests with EC2 large instances to get reasonable latencies. Results from these experiments should appear in the near future.

We are also presenting Galera clustering in the Percona Performance Conference and can provide ad-hoc demonstrations for anybody interested there.

Managing Auto Increments with Multi Masters

2009-02-26T12:37:00.000-08:00

MySQL has system variables auto_increment_increment and auto_increment_offset for managing auto increment 'sequences' in multi master environment. Using these variables, it is possible to set up a multi master replication, where auto increment sequences in each master node interleave, and no conflicts should happen in the cluster. No matter which master(s) get the INSERTs.

Logically auto increment sequence is a shared resource, which would require distributed locking to deal with. However, auto increment sequence interleaving circumvents the need to lock, it sort of splits the auto increment sequence to several node specific sequences, making it "not a shared resource" anymore.

auto_increment_increment and auto_increment_offset have been implemented as session variables, as opposed to being global. We felt at first a bit uncomfortable with this, as there is obvious risk of misconfiguration resulting in conflicts. Apparently, the idea is to let user make separation with tables which are shared in the cluster and tables which are local to each node. If some dedicated session(s) touch only local tables, they can have session specific increment and offset values set to 1. And for sessions needing access to cluster wide shared tables, proper cluster-aware auto increment settings should be used.

These auto increment controlling variables are suitable for our Galera replication model as well. We however, wanted to go one step further and prevent any possibility of auto increment conflicts in the cluster. Galera runs on top of group communication system, which has real time view of cluster memberships. It is therefore possible to adjust increment and offset variables on the fly, triggered by any changes in cluster configuration. We implemented cluster view handler, which the group communication calls whenever somebody joins or leaves the group. The handler code is passed the number of members in the group and group ID of the processing node. These translate nicely to increment and offset values, cluster size will be the auto_increment_increment and auto_increment_offset can be calculated from the node ID.

We wanted to play even more safe to avoid any possibilities for conflicts, and therefore decided to restrict user's access to auto increment variables to read only. And, in order to be transparent, we also added one more variable: wsrep_auto_increment_control to define if the automatic auto increment controlling is enabled or not. With auto increment controlling enabled, cluster will take fully care of setting the increment and offset variables and this will guarantee no conflicts. If auto increment controlling is disabled, system will behave as default MySQL, and user must specify increment and offset globally or by session to suitable values.

Our implementation is based to MySQL 5.1.30, which happens to suffer from a known problem with slave side applying: http://bugs.mysql.com/bug.php?id=41986. This bug is so severe, that we backported the fix for this issue. So current wsrep integration code is actually 5.1.30 + 41986 patch + wsrep related changes.

The auto increment controlling among other new features are present in next Galera Demo-2 Release. The release is under testing and will be released as soon as our paranoid QA manager is done with all his remaining manoeuvres.

Replicating Locking Sessions

2009-02-04T03:14:00.000-08:00

We were running Drupal benchmarks to measure the performance of Drupal/Galera cluster and were surprised to find locking sessions (LOCK TABLES...UNLOCK) in the SQL profile. Locking sessions were originally left out of Galera supported feature set, but now we need to re-consider our policy a bit. Apparently, we are going to encounter more applications, which were originally written for MYISAM usage, but were later migrated to INNODB. As a rule of thumb, it seems that if application can be configured to both MyISAM and INNODB usage, it quite probably uses locking sessions as well.

Eager Replication
We have in the past, implemented one pretty effective method for replicating locking sessions in synchronous cluster. This, "eager replication" method, used transaction sequencing from group communication level to order the table locks. However, the implementation required eventually complete re-write of thr locking (thr_lock.c) and this effort was sure not a joy ride. thr_lock.c module contains adult only content.

We are now looking for a more light weight way to support locking sessions, instead. Galera is a replication system for transaction processing applications and anything we implement beyond that will be a hack (or add-on feature, to translate it to sales-talk). Locking sessions require up-front locking of resources and that makes them complicated to synchronize.

Managing Read Locks
First observation is that read and write locks can be treated differently in Galera cluster. Read locks do not replicate anything, because they are pure read only sessions by definition. Therefore we can leave them processing un-interrupted in local state. Slave applier must acknowledge read locking sessions and wait for them to complete. If application has lengthy read locking sessions, it will obviously delay cluster processing. But that is more or less application's problem, and it could be helped with a bit of re-design in application side.

Converting Locking Sessions to Transactions
Transactions and locking sessions have different semantics, but some applications might, nevertheless, work well with (write) locking sessions replaced by transactions. We can implement this quite simply in parsing level and no application changes are needed for this. For the application, this change in processing, means that "locking sessions" could be aborted due to deadlocks. If deadlocks will happen with application's work load and there is no comprehensive exception management in the application code, then this approach is not viable anymore.

We will add new MySQL option variable for defining if (write) locking sessions should be converted to transactions, and try how Drupal benchmarks works with this method.
Un-interrupted read locks and optional write lock session to transaction conversion will be the first attempts in supporting locking sessions. Hopefully, we don't need to go any further in this path. Otherwise, we are quite close to eager replication model again.

Experimenting with Write Set Replication

2009-01-28T12:32:00.000-08:00

During the past year, we have been developing a write set replication system for MySQL/innodb engine, called Galera. Now, our project has reached milestone where we can run benchmarks to get performance metrics and also give out releases for public evaluation. In this blog, I'll give a short introduction to Galera and related projects.

Some Technology
Galera is generic multi-master synchronous replication system, which replicates transaction write sets at the commit time and resolves conflicts by comparing keys in write sets. Replication happens through group communication system, which (among other tasks) defines the order of committing transactions in the cluster. The write sets can carry original SQL statements or for best performance: row based replication events, available in MySQL 5.1 and onwards.

Galera replication method leaves the actual SQL statement processing to happen uninterrupted, and quite close to the native MySQL way. This makes client interaction with the cluster fast and for the application, Galera cluster looks just like any native MySQL server. Only difference is commit processing, where certain delay is caused by synchronization with the cluster.

Galera replication has been integrated with MySQL/Innodb 5.1.30 providing a full fledged multi-master MySQL database cluster. We call this first version "demo release" and it is available for downloads in our website.

Some Benchmarking
We have benchmarked Galera with different benchmarks (sysbench, dbt2, DOTS, osdb, sqlgen) using different load profiles to find out the constraints for the feasibility of Galera replication. Our observation is, that Galera cluster provides good performance and scalability even with write intensive work loads.

Here is one summary gained with dbt2 benchmark (resembles TPC-C), run in amazon EC2 environment. The graph shows how 1-4 node Galera cluster compares against pristine MySQL 5.1.30 server.
Dbt2 load contains hot spots and is not favorable for clustering. You can see the deadlock rate growing when more cluster nodes are added. However, total performance still gets better even with 4 nodes.

One Roadmap
We just released MySQL/Galera demo release. It should be stable enough for evaluating with real applications. You can download the demo release from here: Galera demo.

Our next task will be to implement all missing features, we plan to have in beta release. The major task there is providing a way to bring a new node in the cluster. In essence, this means implementing DB snapshot transfer for joining nodes. We assume, that feature complete version is possible during Q2 this year.

And All Those Projects
Galera communicates with DBMS engine through an API, which we call: wsrep API (wsrep as: "write set replication"). We started one open source project just for defining this API and another project for implementing the API integration in MySQL/innodb engine.

Here's our current project list:

wsrep API defines the wsrep API only.
mysql patches by Codership is open source wsrep integration in MySQL code base.
openrep will be open source implementation of wsrep API replication system. We just started working on this, no deliverables yet.
galera is wsrep API implementation, optimized for best performance

We have investigated postgres source code quite a bit, and wish to be able to start "wsrep integration patches for postgres" project as well. But, we don't have enough hands and heads to go ahead with this plan in the near future. Technically however, postgres integration should be within easy reach.

This is the state of Galera development in a nutshell. Feel free to visit our website, there is plenty of more information available, for the interested reader.