Archive

Posts Tagged ‘MySQL’

MySQL and the Linux swap problem

Ever since Peter over at Percona wrote about MySQL and swap, I’ve been meaning to write this post. But after I saw Dathan Pattishall’s post on the subject, I knew I’d better actually do it. 🙂

There’s a nasty problem with Linux 2.6 even when you have a ton of RAM. No matter what you do, including setting /proc/sys/vm/swappiness = 0, your OS is going to prefer swapping stuff out rather than freeing up system cache. On a single-use machine, where the application is better at utilizing RAM than the system is, this is incredibly stupid. Our MySQL boxes are a perfect example – they run only MySQL and we want InnoDB to have a lot of RAM (32-64GB … and we’re testing 128GB).

You can’t just not have any swap partitions, though, or kswapd will literally dominate one of your CPU cores doing who-knows-what. But you can’t have it swapping to disk, or your performance goes into the toilet. So what to do?

Our solution is to make swap partitions out of RAM disks. Yes, I realize how insane that sounds, but the Linux kernel’s insanity drove us to it. Best part? It works. Here’s how:

mkdir /mnt/ram0
mkfs.ext3 -m 0 /dev/ram0
mount /dev/ram0 /mnt/ram0
dd bs=1024 count=14634 if=/dev/zero of=/mnt/ram0/swapfile
mkswap /mnt/ram0/swapfile
swapon /mnt/ram0/swapfile

That’ll give you a 14MB swap partition that’s actually in RAM, so it’s super-fast. This assumes your kernel is creating 16MB ramdisk partitions, but you can adjust your kernel paramenters and/or the ‘dd’ line above to suit whatever size you want.

We’ve found that anywhere from 20MB-40MB tends to be enough (so use /dev/ram1, /dev/ram2, etc), depending on load of the box. kswapd no longer uses any noticeable CPU, there’s always a few MB of free “swap”, and life is back in the fast lane. Just add those lines to your relevant startup file, like /etc/rc.d/rc.local, and it’ll persist after reboots.

Some Linux purists will probably hate this approach, others may have more efficient ways of achieving the same thing, but this works for us. Give it a shot. 🙂

Oh, and I hope it goes without saying, but make *darn* sure you know what you’re running on your box and what the maximum RAM footprint will be before you try running with only 20-40MB of swap. We’ve never OOMed (Out-Of-Memory) a production MySQL box – but that’s because we’re careful.

UPDATE: See what happens when I wait to blog? I forget that I read another related post over on Kevin Burton’s blog. Like Kevin, we’re using O_DIRECT, but unlike Kevin, this doesn’t solve the problem for us. Linux still swaps. We use the latest 2.6.18-53.1.14.el5 kernel from CentOS 5, btw. (Sorry, had posted 2.6.9 because I was dumb. We’re fully patched)

Categories: datacenter, MySQL Tags: , , , , , , ,

Death of MySQL read replication highly exaggerated

April 16, 2008 4 comments

I know I’m a little late to the discussion, but Brian Aker posted a thought-provoking piece on the imminent death of MySQL replication to scale reads.  His premise is that memcached is so cool and scales so much better, that read replication scaling is going to become a think of the past.  Other MySQL community people, like Arjen and Farhan, chimed in too.

Now, I love memcached.  We use it as a vital layer in our datacenters, and we couldn’t live without it.  But it’s not a total solution to all reads, so at least for our use case, it’s not going to kill our replica slaves that we use to scale reads.  

Why?  Because we still need to do index lookups to get the keys that we can extract from memcached.  And we have to do lots of those indexed queries.  Most of the row data lives inside of memcached, so this turns out to be a great solution, but we still need read slaves to provide the lists of keys.  Bottom line is that we still use read replication heavily – but we use it for different things that we did in years past.

And then, of course, there’s the issue of memcached failure.  For us, it’s very rare, and thanks to the way memcached works, it rarely hampers system performance, but when a node fails and needs to be re-filled, we have to go back to disk to get it.  And doing that efficiently means read slaves again.

For us, memcached plus MySQL replication is true magic.  Brian’s a very smart guy, and I realize he wrote the post to get people thinking and talking about the issue, but at least for us, read slaves are here to stay. 🙂

The Sky is Falling! MySQL charging for features!

April 16, 2008 10 comments

There’s quite a bit of buzz on the blogosphere from people I respect a great deal, like Jeremy Cole at Proven Scaling and Vadim at Percona, about MySQL’s new Enterprise backup plans.  

The big deal?  They’re releasing a Community version that doesn’t have all the same features as the Enterprise version of Online Backup, including compression and encryption.  The Community version is open-sourced under GPL, the Enterprise version is not.

Personally, I think this is awesome. Don’t get me wrong – I love open source.  We couldn’t have built our business without it, and we love it when we get a chance to contribute back to the community.

But let’s not forget that MySQL is a business.  And that business helps the community and improves the software.  They have customers (I’m one – we’re a paying MySQL Enterprise Platinum customer), and they have to solve those customers’ problems.  This is a virtuous cycle where the community benefits directly as MySQL thrives financially.  

Every time a business like us pays MySQL for a service or feature, MySQL can then invest in better software that benefits all.  The end result in MySQL’s case is more GPL’d code.   In a very real way, without companies like mine, there wouldn’t be a new backup tool at all – let alone the differences this debate is focused on.

Every day, I hear someone saying “Man, I love SmugMug so much!  It has [insert features here] which I love!  Why isn’t it free?”

The answer?  “It wouldn’t be SmugMug if it was free.”  MySQL’s situation is very similar.

I wish more open source projects would make it easier for this cycle to ignite.  Some of them, like Red Hat, refuse to even take our money.  Talk about stupid.  There are *lots* of businesses out there willing to pay for extra services and features, and the community can harness that revenue in amazing ways, including getting more (or better) GPL’d code.

Couple more thoughts:

  • I wouldn’t be surprised if future releases add new Enterprise-only features and some existing Enterprise-only features migrate down to Community.
  • The Community version is open-sourced, so I’m sure the community will develop their own compression and encryption features.
  • This is really no different from Enterprise Monitor, which has been only for Enterprise customers for awhile.
  • Lots of other projects do this (and I would argue this benefits those projects and their communities, too)
  • I’m 99% sure that this was the plan before Sun acquired MySQL.
In short, I view this as one of the ways we can both build our business and give back to the open source community.  Keep it up, MySQL!
%d bloggers like this: