4. Replication Lag

Thresholds

LudicrousDB accommodates replica lag by making decisions based on the defined lag thresholds of each database. If the lag threshold is not set it will simply ignore the replica lag. Otherwise, it will try to find a non-lagged replica before connecting to a lagged one.

A replica is considered lagged when its lag is bigger than the "lag threshold" you have defined in $wpdb->database_defaults['lag_threshold'] or in the per-database settings. You can also rewrite the lag threshold by returning the $server['lag_threshold'] variable inside of the dataset group callbacks.

LudicrousDB does not check the lag on the replicas; you'll have to define two callbacks to do that:

$wpdb->add_callback( $callback, 'get_lag_cache' );

and

$wpdb->add_callback( $callback, 'get_lag' );

The first one is called before connecting to a replica and should return the lag in seconds (or false if unknown) based on $wpdb->lag_cache_key.

The second callback happens after a connection to a replica is established. It should return its lag (or false if unknown) based on the connection in $wpdb->dbhs[ $wpdb->dbhname ].

Sample lag detection configuration.

To detect replication lag, try mk-heartbeat: (http://www.maatkit.org/doc/mk-heartbeat.html)

This implementation requires the database user to have read access to the heartbeat table.

The cache uses shared memory for portability. Can be modified to work with Memcached, APC and etc.

$wpdb->lag_cache_ttl = 30;
$wpdb->shmem_key     = ftok( __FILE__, "Y" );
$wpdb->shmem_size    = 128 * 1024;

$wpdb->add_callback( 'get_lag_cache', 'get_lag_cache' );
$wpdb->add_callback( 'get_lag',       'get_lag' );

function get_lag_cache( $wpdb ) {
	$segment  = shm_attach( $wpdb->shmem_key, $wpdb->shmem_size, 0600 );
	$lag_data = @shm_get_var( $segment, 0 );

	shm_detach( $segment );

	if ( !is_array( $lag_data ) || !is_array( $lag_data[ $wpdb->lag_cache_key ] ) ) {
		return false;
	}

	if ( $wpdb->lag_cache_ttl < time() - $lag_data[ $wpdb->lag_cache_key ][ 'timestamp' ] ) {
		return false;
	}

	return $lag_data[ $wpdb->lag_cache_key ][ 'lag' ];
}

function get_lag( $wpdb ) {
	$dbh = $wpdb->dbhs[ $wpdb->dbhname ];

	if ( !mysql_select_db( 'heartbeat', $dbh ) ) {
		return false;
	}

	$result = mysql_query( "SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) AS lag FROM heartbeat LIMIT 1", $dbh );

	if ( !$result || false === $row = mysql_fetch_assoc( $result ) ) {
		return false;
	}

	// Cache the result in shared memory with timestamp
	$sem_id = sem_get( $wpdb->shmem_key, 1, 0600, 1 ) ;
	sem_acquire( $sem_id );
	$segment = shm_attach( $wpdb->shmem_key, $wpdb->shmem_size, 0600 );
	$lag_data = @shm_get_var( $segment, 0 );

	if ( !is_array( $lag_data ) ) {
		$lag_data = array();
	}

	$lag_data[ $wpdb->lag_cache_key ] = array( 'timestamp' => time(), 'lag' => $row[ 'lag' ] );

	shm_put_var( $segment, 0, $lag_data );
	shm_detach( $segment );
	sem_release( $sem_id );

	return $row[ 'lag' ];
}