Monday I made a big mistake.

We deployed the new member's area and had messed up the static page generation so that only a few of the pages were actually being generated and properly served back while everything else, including all the heavy hitting pages such as the home page were all dynamic. The mysql server couldn't handle the load and crashed. All our systems run on the same mysql box so while it was rebooting everything was down and we swapped the old member's area back in so that everything wouldn't go down again. We then spent monday fixing up the static page generation and it worked. Everything was ready to be deployed for tuesday morning but then we decided we might as well go all out and really start scaling our systems.

This whole problem could've been avoided had everything not been running on a single mysql box. That's a single point of failure and those are very very bad. Luckily we have an ORM layer so it's easy to make database access changes. Our apps only use objects and make calls to Mappers to get these objects. The Mappers call TDGs to get the information they need from the database. All sql is contained in the TDGs. (So Controllers say can has object? to Mappers and Mappers say can has sql result set? to TDGs.) We used to use MDB2 to access the db but not anymore. Yesterday we wrote a wrapper class for memached and overall db access. I'm going to describe our API but first, a bit about memcached.

Memcached was thought up by the LiveJournal guys as a way of saving your database. It seems to basically be a big cache of db results (probably a bit more complex than that but overall it gives a good picture). You have to build your own key to store results with (we picked an md5 hash of the querystring itself) and then you just look in the cache to see if you have anything fresh for that key (there's a timeout that you can specify for when a cache entry goes stale that we set at 3 minutes). If you find something then you can just use that, otherwise you do the db lookup, store the results in the cache and then return them. We now have memcached daemons running on all servers.

The way we used to access the db was that we would request a new db connection through MDB2 (a PHP database abstraction package) and use that. Something like:

$db = MDB2::singleton(AFramework::getConfig("dsn"));

$query = "Something though probably not built as a string, probably use something like autoPrepare and autoExecute instead";

$result = $db->exec($query);

if (PEAR::isError($result)) 

	throw new FrameworkTDGSQLErrorException($result->getMessage()." - ".$result->getUserInfo());

Now, we instead do:

$query = "Something";

$result = DBConn::exec($query);

DBConn has 3 methods we're interested in: insert, select and exec.

Calling insert will return the last auto_increment id and calling exec will return the number of affected rows but all methods are quite flexible. If you give it just a query it will execute it on the default db. (Which, in the case of insert or exec is the default write db but, in the case of select looks up in memcached and then selects one of the read dbs through a balancing method.) However, you can give it an array with one or more options.

Array options:

'query' => $query : The query to be executed

'old' => true|false : Whether to access the new database or the legacy database for postbacks (default: false)

(This next one for select only)

'type' => "all"|"row"|"one"|"query" : Whether to return the results of a queryAll, queryRow, queryOne or query (default: "all")

Also, all methods do the error checking and throw exceptions as in the first example if there's an sql error. Instead of being copy/pasted all over the codebase like before it's now just in one spot. Copy/paste is the devil.

So now it's not only much more flexible but a whole lot more reliable and scalable. This would've been a nightmare to implement if the sql was just scattered randomly across the application though. Yay for good design. Now we can just keep executing our queries nearly the same way as before and it's all balanced across multiple reads with a write that replicates to them along with memcached access to save db hits. In the end we've wrapped MDB2 database abstraction, memcached lookup and storage along with balanced read db access and selective write db access into a single abstraction. Pretty sweet.