« Protect Your Intellectual Assets with a Reconstitution Plan | Main | RubyNation 2011 is a Wrap »

March 09, 2011

TrackBack

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a01156fc2ebf9970c014e869b9fbe970d

Listed below are links to weblogs that reference Tuning MySQL:

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

Charles Calvert

Nice post. I like the fact that your advice is "measure, then cut" rather than "eyeball it" or "just apply this rule of thumb."

Scottestubbs

I found your post from listening to the Ruby5 podcast. Thank you for posting this information about MySQL and InnoDB, there is some good stuff here. Database tuning is often neglected until it becomes an extreme pain-point. These items you have outlined are quick wins that go a long way. I would like to point out something you mentioned about your own database configuration:

"we use a battery backed-up raid card running RAID-0 for our mysql instances at CodeSherpas"

Battery-backed RAID cards: +1. RAID-0 configuration: -1. Some readers may take that as a good recommendation for RAID configuration but really it's not. RAID-0 may be fast and cheap it is also a dangerous choice for master database configurations. Even though it's part of RAID it offers zero redundancy. Recovery from a failure is a pain in the patootie and you are at greater risk of losing valuable data.

What you should be using is RAID-10; it is just as fast but puts the "R" back in the name. It is more expensive so if you are on a tight budget, a good compromise is RAID-5 or even RAID-1 if you only have two disks. RAID-0 would only be a good candidate for slaves or throw-away servers were recovery isn't a concern--but never use it on your master database servers.

David Westerink

Have you ever looked into MySQLtuner.pl ? It's an perl script to suggest tuning on MySQL. It helped me out when we were tuning an MySQL server. It also reports some other things, like accounts without password and such.

Maybe it can make your life even easier ;-)

David Bock

Scott, *doh* I'd call it a typo, but it was ore like a brain-o... I meant Raid1, not raid0. Raid1 gives you two identical drives. Useful for a quick recovery if one fails, but *not* a backup. First, mistakes are immediately written to both drives. Second, both drives will have similar wear patterns, and if one fails, the other may be well on its way. Third, if the raid card fails, it can fail in ways that take out both drives. and Fourth, it doesn't give you any off-site backup.

I have used Raid5 in the past, and with the size of today's drives, I think you are just inviting failure with it. It takes so long to rebuild after a drive failure in the array that you risk losing another drive.

David Bock

David,

I think automated tools are fine for some stuff (like looking for accounts without passwords), but for tuning, I think they are worthless. No tool accounts for all the variants I see beween real hardware and virtualization, understanding how the application is using the database, knowing the read to write ratio, knowing if memory in the server is better used for innodb table caching, passenger instances, or memcache.

Tools like that create a 'culture of ignorance' - you are following advice, you don't know why, and that script may have been written for a completely different situation than you find yourself in today.

The comments to this entry are closed.