Moving database servers is a nontrivial task that requires planning and forethought. This guide provides some tips for planning a migration of a small to midsize databases, but not yet data-warehousing in the 5Gb - 500Gb size. Of course the process will work for smaller databases, and even larger as well. Ultimately your mileage may vary. Depending on the software you’re running your database on, there may be alternate paths, such as replication, available to you. Spend time researching your options before embarking on this type of backup. They can save time and frustration.

This document will not help with sizing your systems, however, completing the “Prepwork” will get you thinking in the right direction as to what you’ll ultimately need to purchase. I highly recommend reading Refactoring SQL Applications by Stéphane Faroult before buying hardware for capacity reasons alone.

Prepwork

Possibly the most important thing you’ll do during the migration is the preparation work. Spend some time looking at what your moving and where you’re moving to up front, it will cause you to answer some questions earlier than later which results in not making rash decisions in the heat of the moment.

1. Inventory and Audit

It’s time to find all the dirty laundry. User’s with inappropriate access (like ’sa’ or ‘root’) for running queries are obvious examples. While it’s tempting to do all of your house cleaning up front, don’t. Document it all so it can be shopped around your organization.

• Look at your user accounts on the RDBMS. Are all of them still currently used? Can any be removed?
• Take a look at the schemas (databases). Are all of them still necessary? Can any be removed?
• Take a look at the tables within each schema. Make note of large objects, you’ll want to do further investigation on these to ensure they’re really serving their intended purpose.
• Make sure you’re backups are running with the intended frequency (and that they’re actually being generated).
• Understand the underlying sub-system design and architecture you currently have.
• Document the pain points you’re experiencing (assuming this is a capacity based move).
• Pick a downtime window for the cutover and ensure you build in some extra time. The length of your downtime can be estimated using your Soggy run.

Armed with this data, you can begin identifying garbage that doesn’t need to move. An example from my experience is a ColdFusion client storage database running on Microsoft SQL Server. This database holds 2 tables CDATA and CGLOBAL each with ~680K rows. With transactional logs etc, the footprint of the database swelled to 7.5Gb. Let alone for a minute that keeping transactional logs on this table was overkill, we were also making significant changes to the application servers. All of this was irrelevant because of other infrastructure changes that were being made concurrently and the database was able to be dropped. This is a singular example of how some research early on can save time later. Moving chunks of data, to disk or over the wire, still takes time - as does the restoration process.

2. Plan the actual steps required to move

This is a page out of David Allen’s GTD. Obvious right? Backup the data and move it to the new server, restore it… done! Well, sure that’s one way to do it, but make sure you’ve got details hashed out on the “move it” bit. If you’re doing this change in a major metro area at rush hour, you’re opening some huge risks into your timeline. But, forgetting traffic; is it best for your environment and your technology? Can you handle the downtime? Consider the following options when flushing out your plan.

• Replication, if available to you, is an ideal way to minimize the downtime. However, you’ll still need to push all data over the wire (or via disk) at some point to get the new equipment to the correct point in time for the log files to begin replication of data effectively.
• Sending up to 20Gb of binary data over the wire is pretty quick, but after that, it may be faster to consider other options for moving data, including moving data via USB/Firewire/eSATA/(insert your favorite external storage method here) or even a whole server.
• Physical data moving takes time and requires coordination, possibly at a datacenter (or multiple datacenters) and may require access be setup ahead of time.

Write down your execution plan, be optimized… you’ll run against this later to keep the noise out of your head.

When moving a large MySQL database between two geographically separated systems, you’re at the mercy of the public internet. Obviously, finding a time that the throughput for your route is best is ideal, but not always an option. After completing a full backup of an entire MySQL database, I pushed the 75Gb over the wire to the new equipment. That process took nearly 2 hours. Armed with the database footprint from your audit, you can use a download calculator to calculate a best case scenario for the transfer time. Keep in mind network congestion, route traffic and latency could increase this time - and those will all increase with distance.

Spring Cleaning

Grab a baseline of your performance. This is a great way to validate that the new solution will do what you expect it to. It’s always nice to see an increase in performance, but it’s better to have a quantified value of just how much increase you get per dollar spent. If you’ll be doing any re-factoring as part of the spring cleaning, be sure to benchmark after you finish that process again.

Begin your house cleaning list by truncating or dropping unnecessary data, tables and schemas. Clean out all of those old user accounts and get your house in order. Ensure you backup proccess has been generating good files and your ready to use those for a Soggy run.

If all has gone well with your process so far, you may be considering just keeping the equipment you’ve got! But if not, and a move is still in your future… get ready to do a Soggy run - note quite the real thing, but not a “dry” run either, because we want to time our process.

Soggy Run

Make note of the time… and get moving - stick to the script. Should you deviate (in order or adding additional steps) be sure to note it on the script so you’ll be prepared for the final run. Also note time deltas at intervals so you’ll know if somethings taking too long and your window might need to be extended. It’s possible if you’ll be using replication to manage the transfer, that you won’t need to execute this again, but if you do, it’ll be nice to have the actual steps you’ve taken.

• Make your backups
• Begin your plan for transfering the data
• Ensure you’ve got all the tools, scripts, and equipment you need in the right place in the right order.
• Restore the data
• Validate that EVERYTHING worked. Don’t just look at numbers of databases, but spot check a few (if there are too many to check them all) and ensure the data you’re expecting to be there is there!
  If you are using replication, can you start it, does it work. Trying validating a database or two to ensure everything is right - after the migration is no time to realize you’ve missed data.
• Try your environment against the database and ensure it performs as expected, not just from an accuracy standpoint, but from a performance one as well.
• If your not using replication to handle the next steps, drop all databases from your server. The system will need to drop them when you re-create them and this takes time especially for large databases.
• Make note of the final time. Add some padding to it and set that as your downtime window. Doubling it is safest, but 20% may be sufficient.

D-Day

It’s been rehearsed, the pitfalls discovered, and the process documented - it’s now down to simple execution steps. Ensure you have access, either via phone, or in person to people who can assist with the migration should something go wrong. It’s nice to have resources available to call on. Gather necessary materials and get going.

• Make sure you’re well rested. Nothing is worse than trying to make good decisions when extremely tired.
• Begin your planned downtime window with a clear head. Disable the necessary systems and put up any necessary messaging. Now is the time to ensure your data is perfect and not being altered.
• Run the playbook you’ve created…
• Bring the systems back online!
• Once you’re done with the plan - validate that it’s all working and point your apps, users and the world at your newly completed creation.
• Benchmark! Quantify the improvements you’ve made with similar load to what you’d been running before if possible.

There is always a fine balance between K.I.S.S. and giving users the flexibility they really need. I’ve been working on configuring a number of Windows Server machines over the last few days getting ready for a major shift in architectural design and philosophy that will happen tonight beginning at 6pm. On numerous occasions when dialing in the configuration I’ve found myself overwhelmed by the un-necessary complexity of the windows wizard interfaces and underwhelmed by the advanced setting panes of many services.

More important to creating a step by step wizard handling users through a myriad of options would be to provide a context specific wizard that takes into account not only what the user has click just now, but what they’ve clicked and interacted with before. The wizard could be setup to solve problems instead of provide functionality. Then for the battle hardened users who no longer require the training wheels that the wizards offer, provide simple automateable tools which let them configure the server quickly and export the settings for duplication!

Let the software learn the user through prior interaction and adjust itself accordingly. Finally, if there’s an option that a single word can’t explain, place the contextual help right there so the user doesn’t have to search Google to find out what it does! Before anyone thinks this is a problem unique to Windows, it’s not. Almost every single digital device (BlackBerry, OS X, Linux can’t we agree on a pseudo consistent path structure guys?) I’ve ever used has had a UI that could be improved through careful thought and extensive testing.

RGB Venn Diagram

If you’re unfamiliar with the RGB color space, it works basically like this. You add a bit of a color component by increasing one of the values. R=0, G=0, B=0 (or 0,0,0) is black, and R=15, G=15, B=15 (or 15,15,15) is white. The examples below are all created using the data table format included below.

UNION

Union takes two (or more) query results and provides you with the unique result. For example, if I had two queries 3, queries that each returned a segment of the color set with the color value = 15 and the other color values > 14, I could write this as three select statements with a form similar to the following, which selects the red subset SELECT r, g, b FROM colors WHERE r = 15 AND g >= 14 AND b >= 14. That gives me 4 rows, Set 1 below.

Now I’ll include another collection of colors: SELECT r, g, b FROM colors WHERE r BETWEEN 10 AND 12 AND g = 11 AND b BETWEEN 12 AND 14, labeled Set 2. And one more: SELECT r, g, b FROM colors WHERE r BETWEEN 9 AND 10 AND g >= 14 AND b BETWEEN 14 AND 16 The result is Set 3.

And now all merged together using a UNION. Notice it removes the duplicate, highlighted in red above, saving us some time! The order by is just to make life easier when reading the newly merged results. As you can imagine, with a more complex dataset, this could be really handy!

SELECT r, g, b FROM colors WHERE r = 15 AND g >= 14 AND b >= 14
UNION
SELECT r, g, b FROM colors WHERE r BETWEEN 10 AND 12 AND g = 11 AND b BETWEEN 12 AND 14
UNION
SELECT r, g, b FROM colors WHERE r BETWEEN 9 AND 10 AND g = 11 AND b BETWEEN 14 AND 15
ORDER BY r,g,b

UNION ALL

As a quick example, using the same 3 queries and result sets from above, UNION ALL gives us the duplicate record.

SELECT r, g, b FROM colors WHERE r = 15 AND g >= 14 AND b >= 14
UNION ALL
SELECT r, g, b FROM colors WHERE r BETWEEN 10 AND 12 AND g = 11 AND b BETWEEN 12 AND 14
UNION ALL
SELECT r, g, b FROM colors WHERE r BETWEEN 9 AND 10 AND g = 11 AND b BETWEEN 14 AND 15
ORDER BY r,g,b

Tables

A table “colors” was created and into it I populated the full set of colors leveraging the ordinal int values of 0-15, resulting in 4,096 rows of colors. You can of course do this for all 256 values of RGB supported in the CSS color space, which would be more accurate for performance testing of your queries, but I digress…

CREATE TABLE `test`.`colors` (
  `r` INT(4),
  `g` INT(4),
  `b` INT(4),
  PRIMARY KEY (`r`, `g`, `b`)
)
CHARACTER SET utf8;

You can use this script to quickly populate your newly created table. There are lots of other ways to do it, but this was the fastest for me to write today.

$conn = mysqli_connect($server,$username,$password,$schema);
for($r=0; $r<16; $r++){
	for($g=0; $g<16; $g++){
		for($b=0;$b<16; $b++){
			$conn->query("INSERT INTO colors (r,g,b) VALUES ($r,$g,$b)");
		}
	}
}
$conn->close();

I’ve been thinking a lot about topic specific search lately. Google has a product that lets you search within a specific vertical, for example Linux, for content that’s evergreen. This used to be more open so you could define your own search, although the link eludes me right now. This of course updates with Google’s typical frequency, which can vary depending on how popular your site is. But what about time sensitive content. What about items like Twitter feeds?

Sure there’s search.twitter.com, formerly Summize, but what about including Plurk, Yammer or any of the other life streaming content into it? Friendfeed attempts to bring all this content to you, and does a good job, but what about other verticals? What if you knit and what you care about is yarn. A quick Google search for ‘yarn blog’ brings up some blogs; some are even about knitting, but generally lacking are good portals about all things yarn.

While portals have fallen out of fashion over the last 5 years, they’re purpose is not lost. Tech Crunch is certainly an important technology portal, even if it’s just called a blog. Mike Arrington has gathered one of the most comprehensive dataset about web startups and hardened internet companies around. So what about those knitters who are constantly looking for new information about yarn? Where can they turn for quick, one stop shopping, reliable information, industry trends and other bits about yarn? Nowhere! There are blogs, ecommerce stores, even industry news sites, but nothing that brings it all together as a good 1990’s .com would have.

At very least, I’m sure some tech-savvy knitter is out there putting together a nice search engine to leverage the Google (or perhaps Yahoo!) search results to create a single destination for knitting or yarn, or perhaps even needles. Is it possible to create a search engine, on the fly, for every possible vertical? Is it possible to create a portal that has content specific to every need, but only that need? The google search I ran for yarn was good, but there were people who were telling tales, often called yarns, on their blogs. Once I’ve selected the appropriate subset, I’d like to see the correct information, not all data - and the most important and most recent information at the top.

Photo by LollyKnit, courtesy of Flickr

I’ve been doing some research over the last few days and have found that niche search is far from solved. Many market verticals are lacking an effective aggregator for timely content. The tools everyone needs are known, but they haven’t been implemented. Instead of true solutions, we find scraping sites that just suck in content and spit it out un-validated, unverified and unorganized. Because of the high costs of building robust tools, what results is a poor, incomplete collection of information, which quickly becomes stale.

That coupled with inaccurate supporting information, primarily due to high data-set costs, provides a less than desirable experience for users. Mahalo saw this in the generalized search market and put into place human editors who comb the internet looking for the best information and vetting user submissions. The result are guides of information that are informative and helpful.

How long will it be before we see Mahalo style sites targeting niche verticals? Who out there is working on a white label Mahalo which can be reused in these smaller markets?

Two days ago, AF-Design’s blog moved to WordPress’ SVN access which brought version 2.7-hemorrhage up. For any who are comfortable and familiar withe the existing WordPress administrative screens, I suggest you take a look at the proposed wireframes. Much like anything, it takes some getting used to, but there are some really great usability things coming along. One feature I really like is the quick edit for posts. If you’re like me (and I suspect a number of others) I often mis-categorize or tag a post and it provides a nice quick interface for making the updates.

WordPress Administrator Screenshot

Of course running bleeding edge software right from the repo isn’t without cost. So far I’ve been bit by a few items that I know of:

1. Comments are disabled by default for new posts created with the QuickPress.
2. Pingbacks are disabled by default for new posts created with the QuickPress.
3. Saving changes to already published posts seems to create an invalid page reference which would throw an error in the admin. Not sure what the root cause was there. I’m hopeful r9078 takes care of it.
4. Images are automatically wrapped with a caption [ caption ]…[ /caption ] by default (see above screenshot) and the uploader (as of r9078) doesn’t have a way to turn that off, so it requires manual deletion of the comment code.
5. Alignment of the image doesn’t carry over from the “Insert Media” feature

I think the Automattic WordPress team is doing a fantastic job and am looking forward to a stable version release.

This interesting story about how one man was able to make a huge profit off of software that did nothing. TechCrunch humorously covered the story yesterday but it had been removed before I could even see it in the app directory. Of course, without $1,000 to buy it or an iPhone/iPod to run it on, I wasn’t going to be giving any hands on demos anyway!

Imagine how much people would pay for an application that actually did do something… the sky is the limit!

Recently I’ve been doing a lot of work with memcached using PHP and have been bitten a few times by different things with how things worked. I’m calling those items out here so anyone getting started with Memcache can learn from my mistakes. Memcached is an amazingly powerful caching layer with lots and lots of online documentation. It’s easy to get running on Linux and hooking PHP into it - I’ll save yet another post about how to do it since there are so many excellent resources already. The hard part is determining where you’ll implement it and in what way. For this post, I’ll leave the implementation strategy aside and walk you through a couple of examples of where I’ve been bit. I’ve created a layer to further abstract the memcache() object in PHP so I can ignore dealing with the add() vs. replace() vs. set() switches and allowing me to have one stop shopping for all of my configuration settings. The source for that basic class is included at the bottom of this post, feel free to use/distribute as you see fit.

One last item before we delve into the examples. Memcached and memcache are not quite the same thing. When I reference Memcached - I’m actually referring to the server instance of Memcache which you are running on the server, which is accessible using a variety of methods, on a variety of platforms including but not limited to PHP. When I use memcache in this post, I’m referring to the API hooks that have been created for PHP to interact with your Memcached server instance. Some (or possibly all) of these items ONLY APPLY TO PHP and shouldn’t be construed as feature-bugs with the Memcached server itself.

Compression and Object Sizes

It took me a while to track down this bug - but I finally read up on memcache a bit more and learned that memcache behaves oddly with small chunks of information if compression is turned on. I’m pretty sure this is memcache not memcached that’s causing the issue because the compression layer happens in PHP. The output for the following code is The value is not the same. However, if $compression is set to false, it will work as expected. Integers and character strings seem to be okay with compression on - as do complex objects. The setCompressThreshold method allows adjustment of this size but I’ve gotten in the habit of not caching simple values like true and false - instead opting to cache objects, classes, arrays and alternately JSON.

// create the cache
$cache = new Memcache();
$cache->addServer("localhost","11211");
$compression = true;
 
// create the value in the cache
$x = true;
$cache->add("x", $x, $compression, 10);
 
// access the value
$y = $cache->get("x", $compression);
 
// check what happened
if($x == $y){
   print "The value is the same";
} else {
   print "The value is not the same";
}

Caching of Class Objects

When caching complex objects like classes, memcache serializes the entire object and then caches it was as it was instantiated. So be warned if your class definition changes, you’ll need to flush your cache entirely of those objects or you might find it behaves a little differently than your expecting. Let’s say you have a class with a few properties that update multiple properties when certain methods are called and you wish to change one of those properties slightly. Any objects that are in the cache already will continue to work with the old values until they are flushed from the cache. It’s not sufficient to read it out and put it back in - the object really is your OLD class definition.

class Foo{
   protected $property = array();
   public function __construct($arr){
      if(count($arr) > 0){
         $this->properties = $arr;
      }
   }
   public function __get($key){
      return $this->property[$key];
   }
   public function __set($key, $value){
      $this->property[$key] = $value;
   }
}

So now you can create Foo objects all day and stuff all sorts of information into them and cache them. You can also get them back out willy nilly later (I’ll use my cache class to save time on the code below).

$foo1 = new Foo(array("apples"=>11,"orange"=>20));
$cache->set("foo1",$foo1, 60);
$foo2 = $cache->get("foo1");
echo $foo2->apples; // should be 11

So we see that all works but, what if we change the way the class works? For example adding a layer of math to calculate a tax or something along those lines.

class Foo{
   protected $property = array();
   public function __construct($arr){
      if(count($arr) > 0){
         $this->properties = $arr;
      }
   }
   public function __get($key){
      return round($this->property[$key] * .9); // calculate storage/depreciation loss
   }
   public function __set($key, $value){
      $this->property[$key] = $value;
      $this->property['num_items'] = count($this->properties) - 1;
   }
}

Our existing cached object doesn’t behave as expected. One of two things seems to happen, and I haven’t fully flushed it out what happens when. First, the object just comes back as it was initially instantiated or second, it silently dies without returning an error. This might be a good reason to create a version value for your cached objects so you can switch on the version to determine if the cached value is valid.

// let's access our existing cache object from before again...
$foo2 = $cache->get("foo1");
echo $foo2->apples; // we might hope for 10 but...

Database Results

Caching of resources doesn’t work. The data being cached needs to be able to be serialized by memcache so it can be inserted into memcached. Database handles are much like your memcached connection - they’re a socket you talk to and unfortunately, so are MySQL results. There are good reasons for this so don’t gripe about it. You’ll need to write a simple wrapper that does all of the result parsing for you prior to caching. Then you can easily create a cacheable MySQL object that can be inserted into memcached. It only takes a few minutes to do this and I may even post later describing the wrappers I’m now using to do just this. Until then - know that you can quickly create an array of your data using the following code and cache that result instead.

// create a cache object (using class from below)
$cache = new Cache();
 
// create an array to populate the data with
$data_array = array();
 
// run the query 
$mysqli_result = $mysqli->query("select * from table where condition=true");
 
// stuff all the data into the array
while($row = $mysqli_result->fetch_assoc()){
   $data_array[] = $row;
}
 
// cache the array
$cache->set("query_data",$data_array,90);

Cache Time to Live

Nothing too big here, but if you provide 0 (zero) or false for a time to live/expiration value, the item never expires, it just gets pushed out if needed later. This all happens on a LRU basis and is well documented.

Protect your Namespaces

This may seem trivial, but I’ve been bit here too. Often it’s sufficient to use one server for multiple tasks. Since Memcached is easy to run in one large pool and share it among multiple resources (much like you would with MySQL) it’s easy to share across multiple applications. There are some nice economies of scale this will afford you. But consider the following bug you could create for yourself in your logic.

Application 1 accessing it’s DB table.

 
// Application 1 fetching content about a user
$memcache = new Cache();
$query = "select * from users where userid = 12";
$result = $memcache->get(hash(md5,$query));
 
// The data wasn't in cache, so we run the query below and store the data
if(!$result){
     $result = $mysql->query($query);
     $memcache->set(hash(md5,$query), $result, 600);
}

Application 2 accessing it’s DB table.

// Application 2 fetching content about a user
 
$memcache = new Cache();
$query = "select * from users where userid = 12";
$result = $memcache->get(hash(md5,$query));
 
// the value existed in cache - so it skips the query and uses the cached value
if(!$result){
     $result = $mysql->query($query);
     $memcache->set(hash(md5,$query), $result, 600);
}

Application 2 and Application 1 are using the EXACT same key to reference their data. Unless this is intentional (because they share a common database) it can be a real pain to debug. The easiest way to correct this is to create a namespace for the cache layer and append it to any keys you may use. The example class provided below does just that with minimal fuss. The code above would be changed to reflect the correct namespace for each application and they could co-exist using the memcached server together.

// in app 1 - use that namespace
$cache = new Cache("app1");
 
// in app 2 - use that namespace
$cache = new Cache("app2");

Memcache Abstraction Class

This is the abstraction class I use to handle all memcache interaction. It’s little more than a thin veneer over the existing PHP object. You can see where it’s easy to expand this basic cache layer within the constructor and you can tune for your data, servers and other bits relevant to your implementation as needed.

class Cache{
 
   protected $cache = false;
   protected $namespace = "";
 
   public function __construct($namespace = ""){
      $this->cache = new Memcache();
      $this->cache->addServer("localhost","11211");
      $this->cache->setCompressThreshold(127,0.2);
      $this->namespace = $namespace;
   }
 
   public function __destruct(){
      $this->cache->close();
   }
 
   public function set($key, $value, $ttl = 600){
      $this->cache->set($key . $this->namespace, $value, true, $ttl);
   }
 
   public function get($key){
      return $this->cache->get($key . $this->namespace, true);
   }
 
}

I was struggling with MySQL Query Browser, trying to make some minor modifications to a schema recently when a friend, aptly named Erik, from WebCentrix.net turned me onto CocoaMySQL. I’ve only been playing with it for a short time but have found it to be far more stable, easier to use (after a short learning curve) and generally a more flexible product. MySQL should give up on their in house project and hire the developers or at least leverage the open source technology that’s up on SourceForge.

Mashable has a nice collection of 16 tools for designers and developers for plugging into Firefox. I’m personally a fan of Web Developer Toolbar and Y!Slow which are both great. The original Mashable article included the other two tools I find indispensable, ColorZilla and Firebug.