Automatic database slave selection

This database driver (called "autoslave") will automatically use the database slaves where applicable and otherwise use a master database.

where applicable means:

  1. The query is not a write query (and not a select query with locking)
  2. The tables in the 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 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)

The driver uses regular expressions to determine whether or not the query will perform a change to the DB. There might be a slight CPU overhead, but I've found this to be negligable, and it should also be weighed against the possibility for making all contrib modules use slave queries where possible.

Installation

Enable the module and copy the autoslave directory from the modules directory to includes/database/, e.g.

%> cd [my-drupal-installation]
%> cp -r sites/all/modules/autoslave/autoslave includes/database/


Common and simple configuration

(see full documentation)

$databases['default']['default'] = array (
  'driver' => 'autoslave',
);

$databases['default']['master'] = array (
  'database' => 'mydb',
  'username' => 'username',
  'password' => 'password',
  'host' => 'master.example.com',
  'port' => '',
  'driver' => 'mysql',
  'prefix' => '',
);

$databases['default']['autoslave'] = array (
  'database' => 'mydb',
  'username' => 'username',
  'password' => 'password',
  'host' => 'slave.example.com',
  'port' => '',
  'driver' => 'mysql',
  'prefix' => '',
);

// Use locking that supports force master
$conf['lock_inc'] = 'sites/all/modules/autoslave/memcache-lock.inc';

// Use AutoSlave transactional safe cache wrapper with a memcache backend
$conf['cache_backends'][] = 'sites/all/modules/autoslave/autoslave.cache.inc';
$conf['cache_default_class'] = 'AutoslaveCache';
$conf['autoslave_cache_default_class'] = 'MemCacheDrupal';


Recommended configuration

  • Change isolation to READ-COMMITTED (requires MySQL >= 5.1.37 and row based replication).
  • Disable the dblog module (and use something else, e.g. syslog)
  • Use memcache as cache backend
  • Apply patches/drush-pdo-7.x-5.8.patch (if using drush)
  • Apply patches/update-pdo-7.22.patch
  • Apply patches from the section "Known issues" on this page
  • Currently exploring: Change ENGINE on table autoslave_affected_tables to MEMORY and change primary key index type to HASH - may be faster, may not due to table level locking... tbc
$databases['default']['default'] = array (
  'driver' => 'autoslave',
  'master' => array('master', 'autoslave'), // Fallback to slaves if master fails (read-only mode)
  'slave' => array('autoslave', 'master'), // Fallback to master if slaves fail
  'affected tables backend' => 'autoslave.affected_tables.memcache-db-accurate.inc',
  'watchdog on shutdown' => TRUE,
  'replication lag' => 2,
);

$databases['default']['master'] = array (
  'database' => 'mydb',
  'username' => 'username',
  'password' => 'password',
  'host' => 'master.example.com',
  'port' => '',
  'driver' => 'mysql',
  'prefix' => '',
);

$databases['default']['autoslave'] = array (
  'database' => 'mydb',
  'username' => 'username',
  'password' => 'password',
  'host' => 'slave.example.com',
  'port' => '',
  'driver' => 'mysql',
  'prefix' => '',
  'readonly' => TRUE, // Required for system to enter "readonly" mode if master fails.
  'tables' => array('sessions', 'semaphore', 'watchdog', 'users', 'history'),
);

// Use locking that supports force master
$conf['lock_inc'] = 'sites/all/modules/autoslave/memcache-lock.inc';

// Use AutoSlave transactional safe cache wrapper with a memcache backend
$conf['cache_backends'][] = 'sites/all/modules/autoslave/autoslave.cache.inc';
$conf['cache_default_class'] = 'AutoslaveCache';
$conf['autoslave_cache_default_class'] = 'MemCacheDrupal';


Caveats

  1. This database driver is currently incompatible with Drush, as the driver is virtual, and Drush checks directly against PDO for validity. (See bundled patch for Drush or workaround in example above)
  2. Does not work with update.php (same reason as Drush, see bundled patch for update.inc or workaround in example above)


Known issues

  1. #1889328: Not all objects respect the query option "throw_exception" (required for read-only mode to work 100%)
  2. #1891728: Database schema methods like getComment() and findTables() always query the "default" target on MySQL (may be required if using table prefixes)
  3. #1893996: _drupal_bootstrap_database() does not respect the multiple slave db structure in $databases, when initializing prefixes for test (may be required for simpletest to work)
  4. #1905096: Wrong usage of database API (Date module incompatibility)


Known potential issues

  1. If a write-query is not recognized as a write-query, the driver will try to write to a slave db (have not experienced this). Remember to use readonly users on your slave db's, so an exception will occur... and provide me with feedback if you ever experience this.
  2. Can switch to "master" connection if a false positive write-query occurs (theoretically possible, but not very likely). The result should be "harmless", as it will then just read from the master instead of a slave.

Drupal 8

The configuration for Drupal 8 is similar to that of Drupal 7, except for the lock part, which is:

<?php
$conf['lock_class'] = 'Drupal\autoslave\Lock\AutoSlaveLockBackend';
$conf['autoslave_lock_class'] = 'Drupal\Core\Lock\DatabaseLockBackend';
?>

The above may very well be subject to change, as it requires a not-approved-yet patch: #1897806: How to register a custom lock handler

Sponsors

Project Information

Downloads