Alas poor Mysql, I knew thee well.

My work often requires me to squeeze blood out of stones so to speak, on the Amazon cloud. I’ve been doing AWS with Rightscale for over a year now and it seems a good fit. We’re pushing massive amounts of data and are always looking for ways trim things down.

One area in particular where I was discussing with the Rightscale folks was MySQL. Apparently, they rolled out some alpha 5.0 and 5.1 templates with Percona patches. This was great since it was a definite speed boost. But they had no 32bit version, and that was a real pain. I spoke to Ryan over at Percona and he suggested I give the OurDelta MariaDB builds a try.

For those of you who don’t really know what it is, head over to http://askmonty.org/wiki/MariaDB and read up on it. But all you really need to know is that it’s a direct, drop-in replacement for mysql (including binary compatibility with innodb aka XtraDB). It’s like mysql on crack, but without all the side effects, stigma and jail time.

To make a long story short.. we’re almost 100% on MariaDB now and haven’t looked back. I grabbed one of the 5.1 percona templates from Rightscale. Tore out their mysql version, put in MariaDB (both 64bit and 32bit), added our own optimizations and viola! We went from running our db’s on Extra Large Memory instances, to Large Memory instances, with snaps being on Computational mediums instead of Large Memory instances.

The main issue with the XL/L memory instances is that you’re pretty much stuck with the same io anyway. With LVM all the ephemeral stores are striped and dm0 will get io saturated on an XL waaay before the ephemeral volumes. This is why with i/o bound mysql issues, it won’t help you to move up instances. You need to reduce io and shard. Which brings me to MariaDB. By reducing down to a Large, it’s now more economical to shard. With 2 large instances you’re getting slightly more ram then an XL and twice the i/o if you shard your data.

Now the OurDelta folks are pretty awesome, and the fact that Percona supports their builds is pretty awesome as well, considering MariaDB is being worked on by Monty. I predict a steady decline of mysql in Oracle’s fine hands and MariaDB filling that gap quite nicely.

So you want to try MariaDB you say? Perfect! My Linux of choice is CentOS on the server. Ubu.. what, where, how? 😉
You’ll want to head on over to http://ourdelta.org/centos. If you want the latest MariaDB though, don’t follow their instructions. It’s been months and they haven’t updated their links yet. That’s cool though, because you’ve found my blog, and I’ll tell you where to get the goods from:

32bit MariaDB 5.1.42 can be found here:
http://master.ourdelta.org/yum/CentOS-MariaDB51/5/i386/RPMS/

and 64bit here:
http://master.ourdelta.org/yum/CentOS-MariaDB51/5/x86_64/RPMS/

Their latest builds should be direct replacements of mysql, but you may want to remove your mysql install first nevertheless.

You can even drop a mariadb 5.1.42 on top of a mysql 5.0 binary tree for a quick upgrade. (Make damn sure you backup first, and make sure the ibdata file sizes match). run mysql_upgrade, restart, and you’re ready to roll.

If you peruse the Percona websites, you’ll find optimization goodies and other info. You’ll definitely want to read this http://www.percona.com/docs/wiki/percona-xtradb:start, and afterwards head on over to http://www.mysqlperformanceblog.com. Percona people and users post a lot of details on http://www.mysqlperformanceblog.com and optimization options. It’s an invaluable resource.

Being able to halve our instance costs is pretty sweet. MariaDB’s performance is out of this world.

Oh Maria!

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: