How to get core to leverage a MySQL master/slave configuration?

Question

I read this question MySQL master/slave replication not working and its answer:

Using slave databases is barely implemented in Drupal core. If you are developing your own modules then calls to db_query need to specify that they want to use the slave database using the $options array. See DatabaseConnection::defaultOptions for how to set this array.

Is there a way without killing kittens hacking the core to get db_query() and db_select() to make more slave SELECT queries ?

By default, these functions will query the master unless specifically told to query the slave (see their API). You have to write db_query($query, $args, array('target' => 'slave')) in order to query the slave and the core (and all the modules) aren't written to achieve this.

Only search (see the slave part) and aggregator seems to leverage this.

Edit: Oct, 25
I saw pressflow 7 is out but I'm not sure if it helps a lot right now.
I haven't found something relevant so let's try a little bounty to help this getting an answer.

Edit: Oct, 31
I'm mainly worried about Crell's comments regarding this topic: What to do with slaves?.
Mainly, is there troubles if I send SELECT queries to the slave, what happens with delays in the replication and the fact that I may want to do a node_load() just after saving a new node.

Answer 1

Here's how I currently implement this.

First you need to set up a SelectQueryExtender class like this:

class SlaveTarget extends SelectQueryExtender {
  public function __construct(SelectQueryInterface $query, DatabaseConnection $connection) {
    if ($connection->getTarget() != 'slave') {
      $connection = Database::getConnection('slave', $connection->getKey());
    }
    parent::__construct($query, $connection);
    $this->addTag('SlaveTarget');
  }
}

Once you have that, then all you have to do is get all the other queries to extend the extender. :) if that makes sense. Here's the snippet.

/**
 * Implements hook_query_alter().
 */
function example_query_alter(QueryAlterableInterface $query) { 
  if (is_a($query, 'SelectQuery') && !$query->hasTag('SlaveTarget')) {
    $query->extend('SlaveTarget');
  }
}

And now all your SelectQuery hit the slave ;-) This is the only way I've been able to accomplish this. Anyways it works great.

Also if you have this on a custom module you can set up the SlaveTarget to be it's on file SlaveTarget.inc and add a files[] = SlaveTarget.inc to your module info file.

Answer 2

The AutoSlave module redirects SELECT queries to read-only replicant databases, and it takes into account replication lag.

According to the module docs, it only uses the read-only replicant when all of the following conditions are true:

1. The query is a select query
2. The tables in the select query have not been written to during the request and within the assumed replication lag
3. A transaction has not been started
4. The tables in the select query are not specified in the 'tables' option in the driver settings
5. A lock has not been started (core db-lock and memcache-lock supported)

Answer 3

from what I heard at the recent Drupal BADcamp Pressflow is the way to go if you want master/slave configurations. You'll be limited to Mysql as the DB. Also, checkout the "high performance group" on d.o.

Answer 4

Despite all of the amazing work done on the database abstraction layer in Drupal 7, this is still surprisingly difficult to do with Drupal core out of the box. As others have mentioned, AutoSlave is an option, although not one I've attempted due to my stubborn refusal to believe that it should be that difficult to do this.

A simpler solution I have found is the following. To route all SELECTs to the slave server, you create a file titled select.inc inside of the core includes/database/mysql directory with the following contents:

<?php

/**
 * @file
 * Select builder for MySQL database engine, routing all SELECTs to the slave.
 */

/**
 * @addtogroup database
 * @{
 */

class SelectQuery_mysql extends SelectQuery {
  public function __construct($table, $alias = NULL, DatabaseConnection $connection, $options = array()) {
    $key = $connection->getKey();
    $connection = Database::getConnection('slave', $key);
    $options['target'] = 'slave';
    parent::__construct($table, $alias, $connection, $options);
  }
}

/**
 * @} End of "addtogroup database".
 */

There are some risks with this method:

1. This method will hijack all SELECTs and direct them to the slave, which will undoubtedly cause issues if you have any lag in replication. Read that sentence again.
2. When you upgrade Drupal core, it is possible that this file will get deleted.
3. If Drupal core were ever to start shipping with its own includes/database/mysql/select.inc, your file would get overwritten during upgrade, and you would have to start maintaining your own patched version of the select.inc that ships with Drupal core.

If you don't have any slave servers specified in settings.php, the above code will not cause a problem. It will still gracefully degrade to using the master server.