I'm now working on a site with multiple database.
1 master(default) and another slave. The default are the primary database which handle all read/ write. And slave is a read-only.

From what I read on settings.php,

Drupal may try to connect to a slave server when appropriate and if one is not available will simply fall back to the single master server.

I tried to search how drupal should automatically chose the target database, but from what I read and test. Drupal will not choose slave unless ordered to. And I think this should be automatic.

The problem is that drupal always targeting default if no target specified. Now either we have to add an options to target slave on all of our modules, including all contributed modules we use. Or change the drupal core code.

So I prefer hacking the drupal code for db_query and db_query_range :

function db_query($query, array $args = array(), array $options = array()) {
  if (empty($options['target'])) {
    $options['target'] = 'slave';
  }

  return Database::getConnection($options['target'])->query($query, $args, $options);
}

And

function db_query_range($query, $from, $count, array $args = array(), array $options = array()) {
  if (empty($options['target'])) {
    $options['target'] = 'slave';
  }

  return Database::getConnection($options['target'])->queryRange($query, $from, $count, $args, $options);
}

So what happen if there is no slave database defined on settings.php? the getConnection function already have the fail-save, to silently move up to default if there is no slave database.

I'm also thinking about adding more properties on settings.php To configure which table should be written on default and which on slave. Eg :

$db_target = array(
     'default'   => 'default',
     'users'      => 'slave',
     'sessions'  => 'slave',
     'role'      => 'slave',
     'authmap'   => 'slave',
   );

That will require change on db_insert/ db_update/ db_delete.

So any thought guys?

Comments

LaurentAjdnik’s picture

Subscribing

Crell’s picture

We spent a great deal of time trying to figure out during the Drupal 6 cycle how to predict what queries will be slave-safe. The problem is that simply assuming SELECT == slave-safe is not true, and when it's not the bug will be subtle and not obvious until you're in production on a multi-DB setup.

See here for previous conversation: http://groups.drupal.org/node/27820

Unless someone with way more high-scale DB knowledge than I have can weigh in here, I'm for won't-fixing this. We simply don't have enough information to be sure that default-slave is safe.

Version: 7.0-alpha6 » 7.x-dev

Core issues are now filed against the dev versions where changes will be made. Document the specific release you are using in your issue comment. More information about choosing a version.