Deploy Drupal with Master/Slave MySQL Databases

On the heals of one of my previous blog posts (Making Drupal Fly) I decided to expand upon that particular server configuration. Since the time I wrote that blog post, we've changed a few things on that server since the requirements on the CMS have changed. We've got a higher load on the server than expected so we needed a way to distribute the extra database traffic across the clustered machines. As you may or may not know, Drupal is a rather database heavy application. If a lot of users are reading while a bunch are writing at once, the site can slow down quite a bit. Now I've already covered the idea of caching database queries and their results, but that doesn't help us if the queries are mostly unique or are for authenticated users (in which case, most of your caching is no longer effective).

So we decided to deploy Drupal with a distributed database scheme. We would have one "Master" database that would handle all INSERT, UPDATE, and any other request requiring data to be written or modified in the database. We would then have several "Slave" databases that would be designed to read data and that's it. This keep things speedy for anonymous requests (since each server in the cluster would just query their local copy of the database) and allow authenticated users to do all their work no matter which server they get dropped onto by the load balancer.

Now this is all fine and dandy in theory. There is one problem however; Drupal 6's database layer doesn't support Master/Slave replication, at all. This means the golden rule must be broken, we're going to have to hack Drupal core. But it's not a huge hack and one that's simple enough to implement again if there are updates made to the files we need to modify.

This patch is thanks to apersaud on Drupal.org. She posted this patch and as of Drupal 6.13 it still works.

Here's how you get it working:

Go to your "settings.php" file and make the following changes to your database connection string

 $db_url['default'] = 'mysql://username:password@localhost/databasename';
 $db_url['readonly'] = 'mysql://username:password@localhost/databasename';

Open up /includes/database.mysql-common.inc and comment out the original db_query($query) function
(Check the CVS commit tag at the top of the file. If it matches // $Id: database.mysql-common.inc,v 1.17.2.1 2008/02/07 10:17:26 goba Exp $ you should be safe.)
Just below it, add the following

function db_query($query) {
  $args = func_get_args();
  array_shift($args);
  $query = db_prefix_tables($query);
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
  }
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
 
  /* Added on 5/21/2009 by apersaud
     Integrating multiple read/write databases
*/
    if(strpos(strtolower($query),"select") === 0){
        $commits = array('alter', 'insert', 'update', 'delete', 'flush', 'lock','create');
$is_commit = false;
foreach($commits as $type) {
  if((strpos(strtolower($query),$type))){
          $is_commit = true;
         }
       }
if($is_commit){
  db_set_active('default'); 
} else{
         db_set_active('readonly'); 
}
    }
    else {
      db_set_active('default');
    }
 
  return _db_query($query);
}

And that should do it. Your database reads will be sent to the slave databases, all other requests are handled by the master database. Obviously, if you have multiple copies of Drupal core running in this setup (and you more than likely do) you'll have to make similar modifications to them to get the master/slave to work there as well.

I have noticed on occasion that running updates (especially installing modules) takes a little longer at times. It's more than likely that I'm connected to one of the slave machines and I have to wait for the SQL query to makes its way over the network and return a result. Small price to pay for the overall performance boost you gain.

adobe cs3 professional serial

Submitted by Anonymous on 5 September, 2010 - 08:58.

you don t have adobe flash player drop cap in adobe pagemaker
adobe cs3 video tutorialmicrosoft windows installer repair

Pressflow

Submitted by mwinstone on 16 November, 2009 - 11:37.

After writing this article, I came across Pressflow from FourKitchens .com. We've since switched to it instead of maintaining our own modified Drupal core. Works perfectly with every module we've tried thus far and it is just a wee bit faster than Drupal core. Things to note is that you will need MySQL 5 and PHP 5 to use Pressflow.

--
Mathew Winstone
Director of Operations

Coldfront Labs