The following is a continuation of our series on scaling WordPress and a repost of Peter Yorke’s original at peteryorke.net. Peter is a Solution Architect at Joyent with a passion for performance and scalability — he’s the man who knows how to make websites go to "11."

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,
));