Friday, December 21, 2012

Galera features beyond just HA

Galera from codership has been getting a lot of attention recently. Galera provides a nice High Availability solution for MySQL where Galera provides synchronous replication with conflict detection using the classic InnoDB Storage Engine. No more playing about with special storage engines of DRBD failover, just continue to use InnoDB and add Galera as the secret sauce for High Availability.

Some of the neat features of Galera are, but are not limited to, multi-master replication, a lightweight implementation of replication and zero failover times due to the multi.master ability. This is not a complete HA solution though, just a component of it, we still need to add some monitoring and failover mechanisms, but as Galera is multi-master this is greatly simplified and can in many cases be handled by the driver or the application with little overhead.

Now, the replication in Galera is synchronous, so that should slow things down a bit, right? Well, yes, but on the other hand Galera can use multiple threads to apply data on the slave, so that should compensate for that somewhat. And how does it compare to MySQL Semi-synchronous replication, which on paper should be that much different?

So I was curious about the multi-threaded apply on the slave that Galera supports? Could this be the multi-thread apply that MySQL has been waiting for all this time? (No, the schema parallel implementation in MySQL 5.6 doesn't count in my mind). So I set out to try this, and this is my thinking:
  • The parallel nature of this should be best exposed when you have many small transaction, so each INSERT is a single row, autocommit transaction.
  • For the sake of the test, remove as much InnoDB overhead as possible and run on Ramdisk (tmpfs)
  • The schema should be simple
  • Simple INSERTs are to be tested, nothing else
  • Multiple INSERT threads.
  • Multi-master operation, but no conflicts.
This is admittedly a simple testcase, but it should tell us something. The schema looks like this:
  `c1` int(11) NOT NULL,
  `c2` char(100) DEFAULT NULL,
  PRIMARY KEY (`c1`)

And the data to be inserted is
  • column c1 - Unique sequential integer.
  • column c2 - A random string of characters of 5 to 100 characters length.
  • 1.000.000 rows are inserted using 400 threads (200 on each MySQL server).
The hardware I am using for this test is my usual homebrew 8 core AMD box with 16G RAM, nothing exciting but useful.
InnoDB was standard configured here, nothing special, and Galera was using 16 apply threads on the slave, which is probably excessive for this use case. Both MySQL and Galera was using two MySQL servers on the same box.
  • MySQL with semi-synchronous replication ached some 4.830 INSERTs per second.
  • Galera achieved some 12.987 INSERTs per second, nearly 3 times the performance!
This test wasn't something scientific, but then most applications aren't terribly scientific either. To me, it seems like Galera is the Replication system MySQL should have had from the beginning! This rocks! And this also proves to me what I was thinking from the start, that Galera has more to give than a plain HA solution!



Mark Callaghan said...

I don't think these test results are useful for understanding Galera or semi-sync.

Anders Karlsson said...

Maybe not, but they show something at least, and the use-case isn't completely off I think. But the test is admittedly synthetic. What I wanted to check was if the multi-threaded spply capability of Galera could turn out useful, and it seems it can. This is not no say that there is something distinctly wrong with semi-sync per se, it's here more as having something to compare with.

Once we put stuff on disk, things are different, but I give that a shoot too, as well as running between two separated nodes. One thing the results so far tell me is that it is worthwile to do some more testing.

Mark Callaghan said...

Would much rather read about your experiences deploying it.

Anders Karlsson said...


Sure, that will come, so stay tuned. Before getting there I wanted to test some things though. But in a sense you are right, the docs still suffer a bit, so getting started with Galera would be a good suvject for a post also.


foxinfosoft said...

your site is very informative
Thank you
iPhone application development

Vinay Joosery said...

Ok, I know that you geeks prefer it the hard way but just in case you want to cheat:

md sukria said...

This is the foremost divide up second I enclose glimpsed your cozy and do approximating to tell you – it's if truth be notified satisfying to glimpse that i establish your toil gift card exchange trade. but if you presumptively did it in laurels straightforward approach that's ready to be very fastidious. but over all i actually prompt you and positive will contain up for additional mails like this. articulate feeling you most.

Sohidul Islam said...

This is the first jiffy I even have glimpsed your relaxed and do would like to provide notice you – it's if truth be told finicky to glimpse which i absorb your strong employment. but if you most likely did it {in a|during a|in an exceedingly|in a terribly} easy methodology which will be very pleasant cash advances. but over all I undoubtedly advised you and clear in your brain will anticipate lots of mails like this. specific feeling you most.

md sukria said...

This is the foremost instant I even have glimpsed your content and do favour to apprise you – it is very satisfying to figure out that i realise your necessary work car title loans. whereas if you expected did it in associate terribly terribly notably clear-cut suggests that is any terribly satisfying. whereas over all i really supported you and affirmative can anticipate supplemented mails like this. Thanks you most.

stephane hooks said...

To conceive with the prime line of my remark – I do specifically would like to gift associate outsize as a results of the journal admiration. terribly it's an enormous work by him that i found a high-quality facilitate by his/her pleasant data. I directly have to be compelled to be compelled to be compelled to inform, delight hold it up your employment check cashing chicago. typically I’ll build positive in conjunction at the aspect of your posting and alter. feeling forward to your any mails.

Haney Rose said...

An instant cash advance is just like an average mortgage loan in a critical training course cash advance. In the event you forget to agree to the particular stipulations within your mortgage loan deal, the particular company can start line methods against you to definitely recuperate your economic scenario.

marko said...

Each loan specialist has a few advance arrangements and they glide them in the business sector to separate their monetary items from the results of alternate

marko said...

New auto credits for occupant are dependable wellspring of simple money to inhabitants for owning an auto. On looking at banks inhabitants can benefit the credit at aggressive premium rate. Pay off credit portions in time. This won't just keep inhabitant obligation free yet will enhance FICO assessment as well. payday loans said...

Where do paper shaping affiliations get their papers? Neglecting the way that an isolating number of these affiliations might want you to see that they are making articles with a qualified gathering of masters, a broad portion of these showed article making affiliations genuinely outsource their staying in contact with India and differentiating nations. Cash Advance San-diego

Jhon Paulo said...

Knowing this bold all to well, I absitively to booty a altered approach. I would aboriginal sit bottomward with a applicant and their cogent added and we would altercate their accident tolerance. I did this by architecture my own 3 folio certificate about the risks surrounding altered investments and how they could aftereffect their banking goals.payday loans