21

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.

17
+100

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.

  • Hi Eric, thanks for your answer, what mainly worries me is this thread: What to do with slaves? and Crell's comment regarding slave. So is you solution safe in any cases ? Do you restrict some SELECT queries ? How do you deal with delays in the replication and the fact that loading a node just after saved it may causes troubles ? – tostinni Oct 30 '11 at 20:23
  • This is changing the database to slave only on Select queries. This only happens when the query has been written with SelectQuery not db_query so no need to worry about and insert or update being targeted to the slave. We're running this on 3 huge production environments without any problems. I've haven't worried much about the mysql replication as its almost instant (in my case) but I can see how that might be a small issue issue in certain environments. – ericduran Oct 31 '11 at 15:07
  • Thanks for your answers, it's a great solution, I'll see if this is viable on our environment. – tostinni Nov 1 '11 at 3:11
  • Eric, is this code out there somewhere as a contrib or sandbox module? – paul-m Jun 9 '12 at 18:35
  • @paul-m: see drupal.org/project/autoslave. – smokris Dec 13 '12 at 16:43
5

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)
1

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.

  • 1
    Currently Pressflow 7 = D7, there's nothing (yet) that Pressflow do that D7 don't :( – tostinni Oct 27 '11 at 5:02
1

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.

  • Yes, it appears even though the connection can be set to "slave", if the query itself doesn't have the target => 'slave' option set, it will still run on the default connection. It's a pain it's not easier to set the connection target more easily at the query_alter level. – David Thomas Nov 5 '17 at 23:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.