You have hit the big time with your WordPress blog, GigaOM and Nikki Finke think you are the most influential blogger on the planet.

Congratulations, your mysql database is about ready to crash or worse, it already has.

In this post I discuss what you need to do with your database to keep this beast running smoothly and your blogging empire humming along.

Besides your users reporting the blog is slow, what objective measures are there? I use the NewRelic Std feature that lets me see database latency. Here an example of a database that needs help.

Site latency is almost 700ms or .7 seconds, visitors to the site will notice some lag.

NewRelic Slow DB

The database latency between the application and database server is terrible, averaging about 500ms or 1/2 seconds.

NewRelic Slow DB CPM

Looking at the CPM(calls per minute) vs database response time, the database response seems reasonable.

This WordPress has a few forum message board plugins on it so we need to either shard the database or create a read/write database pool. Sharding is great I am opting for the latter to solve this.

Here is how to create a read/write database pool using the HyperDB plugin

The HyperDB plugin allows you set set up multiple slave mysql servers for read access and split write access to a master mysql servers.

Some additional features: Configurable priority for reading and writing, Different tables on different databases/hosts, Failover for downed host, and Advanced statistics for profiling.

Here is a sample of the config of 3 mysql servers, 1st one is a read/write and 2nd/3rd are read only:

$wpdb->add_database(array(
‘host’ => DB_HOST-1, // If port is other than 3306, use host:port.
‘user’ => DB_USER,
‘password’ => DB_PASSWORD,
‘name’ => DB_NAME,
‘write’ => 1,
‘read’ => 1,
‘dataset’ => ‘global’,
‘timeout’ => 0.2,
));

$wpdb->add_database(array(
‘host’ => DB_HOST-2, // If port is other than 3306, use host:port.
‘user’ => DB_USER,
‘password’ => DB_PASSWORD,
‘name’ => DB_NAME,
‘write’ => 0,
‘read’ => 1,
‘dataset’ => ‘global’,
‘timeout’ => 0.2,
));

$wpdb->add_database(array(
‘host’ => DB_HOST-3, // If port is other than 3306, use host:port.
‘user’ => DB_USER,
‘password’ => DB_PASSWORD,
‘name’ => DB_NAME,
‘write’ => 0,
‘read’ => 1,
‘dataset’ => ‘global’,
‘timeout’ => 0.2,
));

For those that are unfamiliar with read/write splitting, you can send read requests to a pool of mysql slave database servers while sending the write requests to the master. Additionally, if you have a failure of one of the slaves, HyperDB can detect and send the read request to another slave in the pool.

After adding a few more slaves to the pool latency was lowered by 300ms and added another 2000 cpm, Your blogging empire is back in business