Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Galera features beyond just HA

DZone's Guide to

Galera features beyond just HA

· Performance Zone
Free Resource

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

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:
CREATE TABLE `tab1` (
  `c1` int(11) NOT NULL,
  `c2` char(100) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
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!

/Karlsson

Learn tips and best practices for optimizing your capacity management strategy with the Market Guide for Capacity Management, brought to you in partnership with BMC.

Topics:

Published at DZone with permission of Anders Karlsson, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}