The Project Formally Known as Golden Gate

[Chris]

So I was watching the discovery channel about the maintenance of the Golden Gate bridge. I was surprised to find out that it takes two years to paint the whole thing, then, when they are "finished" the job starts all over again. Basically, this is a never ending project! I started to think about the larger, several thousand server, MySQL installations I've worked on and how to improved performance. Oddly enough The Project Formally Known as Golden Gate was born! Thought I would change the name given the recent threat from Big Brother!

-- The fore thought
Take an environment with several hundred or, in this case, several thousand servers and figure out a way to increase performance from 30% - 80% or more.

-- Back to basics
De-fragmentation of the data set is a simple way to make sure that your throughput at its best. Over time, social networking sites, or any site that runs heavy writes with deletes on its data set will get fragmented. To help minimize the fragmentation MySQL offers Optimize Table. This, of course, is NOT an optimal way to defragment any large data set on a server. Taking a queue from our "What's faster than Alter" post I figured that dumping out the entire data set with the MAA parallel dump tool would be a fast start toward de-fragmentation. You can see where this is headed... that's right, a full dump out, destroy and reload!

-- Implementation
In every fagmented data set I've seen this process has gained anywhere from 30% - 80% increase in throughput. I would post graphs but the companies I have done this for might not be too happy about that. Anyway, there are many ways you can implement this technque into your environment and automation is definatly NOT out of the question; however, with many servers, having patience with your environment is key this project's success!

-- Benefits
1. For MyISAM and in most INNODB setups you will regain disk space
2. You will see performance gains from this technque
3. Upper management will be pleased with the sudden increase in database performance
4. Your customers will also be pleased
-- NOTE: just make sure you figure (EDITOR NEEDED) out how to do this with minimal downtime (there are many ways NOT detailed here)
5. Depending on your server count and setup you might only have to do this twice a year or less
-- I've done it

Just try it on one of your systems... Start with a slave, fail-over then run it on the old master. Check the performance gains yourself and let me if it worked for you!

Original Post was September 14, 2009 10PM MST.
Updated 2010-02-19 9:15AM PST

Glad to see my ideas helping others!

[Kevin Knapp]

Ideally run the dump and load on a slave then failover. Downside: Extra hardware needed. Upside: Depending on how the db host is reached by the app, can result in extremely minimal downtime.