Database - Using the slave databases

Database - Using the slave databases

Overview

All INSERTs, UPDATEs and DELETEs should always be executed against the Master database. These queries affect the data in the database. When these data changes are made, they are automatically replicated to the Slave databases. So the Slave databases contain an near-realtime copy of all the data on the Master. This usually happens within a few seconds but we've seen the Slaves fall behind by several minutes at times. By running SELECTs against the slave, we free up available connections to the Master database meaning that more INSERTs, UPDATEs and DELETEs can be executed.

Drupal settings

The settings.php file now defines the slave database as an array rather than a string. When running queries against the slave database, one of the slaves in the pool will be randomly selected.

$db_url['default'] = 'mysqli://phoenix:xxx@192.168.100.65:3306/phoenix'; // db1
$db_url['slave'] = array(
  'mysqli://phoenix:xxx@192.168.100.66:3306/phoenix', // db2
  'mysqli://phoenix:xxx@192.168.100.150:3306/phoenix' // db3
);
$db_slave_url = $db_url['slave']; // Required for the Pressflow slave db functions

Database servers

The latest list of our servers can be found on the Servers page.

Running queries against the slave database

Method one - db_set_active()

// Run a query against the slave database:
db_set_active('slave'); // Will choose a random slave from the pool defined in settings.php
db_query("SELECT ...");


// Set active database back to default:
db_set_active();

Method two - db_query_slave() / db_query_range_slave()

Pressflow introduces the following alternative query options:

// Instead of db_query();
db_query_slave(); // Will choose a random slave from the pool defined in settings.php
 
// Instead of db_query_range();
db_query_range_slave();

Silex API

The Silex part of our Phoenix API automatically uses the Master for all writes and a random Slave for all reads.

In cases where a read query needs read something immediately after it was written, the useWritesDbForAllQueries() method can be called to ensure that subsequent reads are performed against the Master database rather than the Slave (which might not have the latest data yet).

Key files

  • sites/default/settings.php - where to define the Master database and Slave database pool.
  • includes/database.inc - random slave selection logic is in db_set_active() and the Pressflow functions db_query_slave() and db_query_range_slave() can be found here too.
  • custom/api/src/atd/api/base.php - random slave selection logic is in initDb().

Useful links

Related JIRA issues

blog tag: