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:

The query is not a write query (and not a select query with locking)
The tables in the query have not been written to during the request and within the assumed replication lag
A transaction has not been started
The tables in the query are not specified in the 'tables' option in the driver settings
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.

Pre-Installation

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
(patch not needed when using drush 7.4.0 (see README.txt)
Apply patches from the section "Known issues" on this page
OR see list of patches in this patches/README.txt
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
For more details please refer to README.txt

$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

special settings.php config required to bypass autoslave with drush.

Example settings.php code snippet:

if (drupal_is_cli() || basename($_SERVER['PHP_SELF'] == 'update.php')) {
    $databases['default']['default'] = $databases['default']['master1'];
  }

This database driver is incompatible with Drush, as the driver is virtual, and Drush checks directly against PDO for validity. (See autoslave/README.txt or workaround in example above)

Does not work with update.php (same reason as Drush, see bundled patch for update.inc or workaround in example above)

Known issues

#1889328: Not all objects respect the query option "throw_exception" (required for read-only mode to work 100%)
#1891728: Database schema methods like getComment() and findTables() always query the "default" target on MySQL (may be required if using table prefixes)
#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)
#1905096: Wrong usage of database API (Date module incompatibility)

Known potential issues

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.
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';
?>

25/07/2017: A new released has been done which includes a working module and the configuration gets recognised, however there are some known issues.

Known issues

If master is down/not available, an error is thrown and it can't query database
Make sure that AutoSlave get's triggered before the default Drupal config.

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

Note

A newer version of Drupal 8 module is being develop. More information to come.

Sponsors

WebMediaBrands Inc.

RTBC issues for AutoSlave

Filters