MySQL progress in a year

Usually people do this around New Year, I will do it in February. Actually, I was inspired to do this after reviewing all the talks for this year's MySQL Conference - what a snapshot into the state of where we are! It made me realize we've made important progress in the past year, worth taking a moment to celebrate it. So here we go...

Diversification

In the past few years there was a lot of fear and doubt about MySQL due to Oracle taking over the ownership. But if you ask me, I was more worried for MySQL because of MySQL itself. I've often said that if MySQL had been a healthy open source project - like the other 3 components in the LAMP stack - then most of the NoSQL technologies we've seen come about would never have been started as their own projects, because it would have been more natural to build those needs on top of MySQL. You could have had a key-value store (HandlerSocket, Memcache API...), sharding (Spider) and clustering (Galera). You could have had a graph storage engine (OQGraph engine isn't quite it, I understand it is internally an InnoDB table still?). There could even have been MapReduce functionality, although I do think the Hadoop ecosystem targets problems that actually are better solved without MySQL.

Instead these innovations have happened as their own projects and MySQL is playing catch-up (which is still good, I still want to see these features in a MySQL fork). If we go back to 2008, you'll remember that MySQL was even struggling to keep up with basic technical evolution in scaling to multiple cores. In fact the community (Google, Yasufumi, Percona) had already made great improvements in that area, but it took a while (until 2009) before MySQL got its act together and finally incorporated those patches.

It is with this background in mind I'm very happy to see how the community has diversified into 4 competing, yet collaborating forks (stable releases: MariaDB and Percona Server in 2010, Drizzle in 2011) plus some important supporting projects (see below). Diversity and letting all flowers bloom has always been the cornerstone of open source success and lack of it was always a weakness - if you ask me - for MySQL. Our blossoming ecosystem is perhaps still not the curated garden as is Linux, Apache and PHP, but there is diversity, and it works for us.

And yeah, I won't list performance separately below, but if you read Vadim on the MySQL Performance Blog, especially his tests on SSDs, you'll see that nowadays we are doing just fine thank you.

Xtrabackup

Going into specific technologies, let's start with something boring like backups. Today most of us use xtrabackup already, but do you remember only a while ago what we did for backups? Well you could use mysqldump, but if your DB is big it's not really online, and it's not even fast. You could shutdown your MySQL while you copy the files, and you could take LVM snapshots to avoid shutting down the DB for more than a second. And don't get me started on backup slaves... And there was Zmanda, but the open source version hasn't seen any updates for over 6 years now...

And of course, you could do all of the above, which is what one heroic DBA team I know well was still doing less than a year ago. It's the MySQL way you know, you get to choose which trade offs to make, but you have to choose one.

But not anymore! The first stable release of Xtrabackup was almost 2 years ago now. Early 2011 it was perhaps usable, but many of the newer features like per-table backups, streaming backups and incremental backups, especially when combined together, did still have some blocking bugs. But today... we finally have an open source tool providing us the right way to do backups, with no tradeoffs.

Xtrabackup Manager

Given that there is now this new backup tool we are using, there was an opening for a great backup management tool to schedule your backups with. This space used to be occupied by Zmanda, and they even had a GUI-less open source "community" version too, but as it hasn't been updated for years now, it obviously doesn't support Xtrabackup. (Nor would I wan't to use 6 year old software for backups anyway...)

Enter Xtrabackup Manager! Written almost completely by Lachlan Mulcahy, just a DBA like you and me, and former MySQL support guy, I really like this tool. It only recently got a usable command line interface (I used it back when the only interface was SQL :-) and is still in the pre-release series, but I really like this tool and I'm already willing to bet it will become a household name alongside Xtrabackup itself.

Btw, Xtrabackup Manager, or XBM as we call it between friends, is written in PHP, so it is a great opportunity for someone not proficient in C++ to get involved in an important MySQL project that is likely to be used for years to come. (And hey, it's PHP. There is a browser based GUI in there just waiting to happen... It could be you.)

Galera

When talking about replication, we should always remember that the Internet was built on MySQL replication. Clearly, it isn't that bad.

Yet, in the cloud era, when even database servers have to be deployed and taken down on a daily basis, you get a bit annoyed at managing it. And at some point you realize it doesn't really protect you from data loss anyway... Hence there has long been talk about the need for something better. Indeed, painless clustering is what all the new NoSQL solutions do by default (or at least they promise, what do I know...).

What can I say? Galera really rocks! It is synchronous: data is committed to all nodes at the same time. All nodes are guaranteed to be in sync, they are aborted if they can't. Deployment? Just connect a slave to a cluster and it will fetch a copy of the database automatically. The best part: There is no performance penalty like you are used to seeing with DRBD or SAN based HA, if you're lucky the performance will be better than on a single node! And the best part: no failovers: Just write to whichever nodes are there.

Congratulations to Percona to being the first MySQL fork to launch a product on this great technology. As Galera needs to patch a few hooks into InnoDB, it is great to have it integrated into the major fork that you are likely to use anyway, so that it comes in the same package.

Etc...

I think the above 4 points are the major advancements of the past year. There are others like the PAM based authentication all forks now support. (And yes, such features will also be covered at the user conference). There's the Online Schema Change script, although Galera 2.0 will now also support online alter table in a rolling upgrade fashion, so maybe those scripts will become just a phase in the history of MySQL.

Performance of course is a very broad topic - in fact possibly the single most awesome patch of all of these is fixing the group commit bug by Kristian Nielsen. I worked with Kristian while he developed this solution and watching him work has left an unforgettable impact on me, things I hope I can bring with me to many development teams to come. Group Commit is included today in Percona Server and the MariaDB 5.3 Release Candidate. (Drizzle had long ago refactored this problem away by rewriting replication, and MySQL has a different fix coming up for 5.6.) There's yet another trade off we don't have to make anymore!

What next?

Of course, there are also things where we have more to do.

HandlerSocket was a great breakthrough, bringing NoSQL performance to MySQL. (...in fact, InnoDB now gives you way more performance than any NoSQL solution out there!). But it seems we ended up in a situation where people are mostly waiting for MySQL to reimplement this idea as a Memcache API instead, including the creators of HandlerSocket also advocating for that solution. Hence, while the problem is kind of solved, the solution is not yet widely adopted and won't be until MySQL 5.6 is released.

After Galera and HandlerSocket the next (and last?) NoSQL-inspired problem to be solved is transparent sharding. There's not much there yet. Drizzle had a Google Summer of Code project to implement this, but that one wasn't completed and it won't be in the upcoming 7.1 release.

I haven't seen anybody writing about this yet, but a nice side effect of MySQL going with the Memcache API you could actually now use the sharding algorithms available in Memcache clients to get transparent sharding - but then only for the Memcache API.

More commonly I look at the Spider Engine to solve this problem in a generic, SQL compatible way. It hasn't really gotten much traction and has been standing still for years now. (Consequently, it also isn't covered at the MySQL conference this year, but was presented at Percona Live UK.) I know a few European companies have used it in production with much scale-out success and I think it's just a matter of time until people start picking up on it.

Cloud and devops

Finally, let me finish with another trend that will need work to keep MySQL users happy. Cloud, Database as a Service and DevOps. To me, DevOps really is just a consequence of the fact that having access to a cloud, and developers working with agile processes, we now need to deploy and upgrade our databases much faster and in larger quantities than ever before. Which simply means one thing: automation.

At my work I've seen a lot of Apache httpd, Tomcat and whatever installations nicely automated with puppet or chef. But not MySQL. Why not? Because it is easy to write a script that starts 5 httpd processes. It is much harder to start 2 or 3 MySQL servers, containing a 100 GB database, setting up replication between them and then doing a rolling upgrade without downtime. That's why I haven't seen many such scripts.

There's a pretty cool talk about Scalr in this area, they do automatic scale-out and scale-down on EC2, including for MySQL. I'm quite impressed, even if using EBS it is a bit easier than before. We are also looking into having one more DevOps inspired talk. And who knows, there could be one or two mentions about DBaaS too, I know things are happening...

Which is all to say, I believe these problems will be solved soon, script by script.

And you can hear all about it at the conference!

Andy (not verified)

Sun, 2012-02-12 08:33

> And the best part: no failovers: Just write to whichever nodes are there.

But how do you cope with a Galera node failure?

1) Say you're writing to a Galera cluster, a write only commits when it is written to all nodes. So what happens when a node fails? Does the write time-out and abort? How do you handle that in the application code?

2) When you read from a Galera node, what happens if it fails? Does the Galera cluster automatically re-route the query to a different node?

Thanks

Hi Andy

See my previous post for the second question at least: http://openlife.cc/blogs/2011/july/ultimate-mysql-high-availability-sol…

1) If some other node in the cluster fails, it is kicked out and the majority of nodes proceed to commit normally (there is a timeout during which they wait for the failed node to respond). If the node you're writing to fails, then you get whatever error code is appropriate. If the node crashed, then you'll find out it's gone :-)

Add new comment

The content of this field is kept private and will not be shown publicly. Cookie & Privacy Policy
  • No HTML tags allowed.
  • External and mailto links in content links have an icon.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
  • Use [fn]...[/fn] (or <fn>...</fn>) to insert automatically numbered footnotes.
  • Each email address will be obfuscated in a human readable fashion or, if JavaScript is enabled, replaced with a spam resistent clickable link. Email addresses will get the default web form unless specified. If replacement text (a persons name) is required a webform is also required. Separate each part with the "|" pipe symbol. Replace spaces in names with "_".
About the bookAbout this siteAcademicAccordAmazonBeginnersBooksBuildBotBusiness modelsbzrCassandraCloudcloud computingclsCommunitycommunityleadershipsummitConsistencycoodiaryCopyrightCreative CommonscssDatabasesdataminingDatastaxDevOpsDistributed ConsensusDrizzleDrupalEconomyelectronEthicsEurovisionFacebookFrosconFunnyGaleraGISgithubGnomeGovernanceHandlerSocketHigh AvailabilityimpressionistimpressjsInkscapeInternetJavaScriptjsonKDEKubuntuLicensingLinuxMaidanMaker cultureMariaDBmarkdownMEAN stackMepSQLMicrosoftMobileMongoDBMontyProgramMusicMySQLMySQL ClusterNerdsNodeNoSQLodbaOpen ContentOpen SourceOpenSQLCampOracleOSConPAMPPatentsPerconaperformancePersonalPhilosophyPHPPiratesPlanetDrupalPoliticsPostgreSQLPresalespresentationsPress releasesProgrammingRed HatReplicationSeveralninesSillySkySQLSolonStartupsSunSybaseSymbiansysbenchtalksTechnicalTechnologyThe making ofTransactionsTungstenTwitterUbuntuvolcanoWeb2.0WikipediaWork from HomexmlYouTube

Search

Recent blog posts

Recent comments