Advanced Read/Write Splitting with PHP’s MySQLnd

In part one in this series we took our first look at mysqlnd_ms, the the MySQLnd plugin for read/write splitting. In this article we’ll look at more advanced usage.

Multiple Configurations

Keen eyed readers of part one may have noticed that the mysqlnd_ms configuration has a top-level key named (in our examples) “appname” which contains all of our configuration. This allows you to specify multiple configurations in one configuration file.

So you have:

{
    "APP-SPECIFIC-KEY": {
        "master": {
            ...
        },
        "slave": {
            ...
        }
	}
}

To specify which configuration to use you use this key as the hostname to connect to.

Using mysqli:

$mysqli = new mysqli("appname", "user", "password", "dbname");

Or using PDO:

$pdo = new PDO("mysql:host=appname;dbname=dbname", "user", "password");

Note: You need to do this even if you only have one configuration, otherwise the mysqlnd_ms configuration will not be used at all.

Failover

The mysqlnd_ms plugin allows for automatic failover should a connection fail while trying to connect to a server (which happens transparently when issuing the first query to it) — it is disabled by default, as there are issues with state (e.g. SQL user variables). However, it is easy to set up, by setting the failover.strategy configuration option.

This option has three possible values:

• disabled: Never failover automatically. This is the default.
• master: Always failover to the master
• loop_before_master: If a slave request is being attempted, it will first loop through the slaves, before trying the master.

{
    "appname": {
        "master": {
            ...
        },
        "slave": {
            ...
        }
        "failover": {
             "strategy": "loop_before_master"
        }
    }
}

In addition to setting the strategy, you can additionally set two other settings:

• remember_failed: This setting will tell the plugin to remember failed servers for the remainder of the session. This defaults to false, but is recommended.
• max_retries: The number of retries that should be attempted on each server before considering it failed. Each server will only be tried once per iteration of the list, and will be removed only after failing N times. This defaults to 0 which means unlimited, so a server will never be removed; this conflicts with the remember_failed configuration option.

{
    "appname": {
        "master": {
            ...
        },
        "slave": {
            ...
        }
        "failover": {
             "strategy": "loop_before_master",
             "remember_failed": true,
             "max_retries": 1
        }
    }
}

Note: when in transactions (as discussed below), failover is implicitly disabled.

Transactions

One of the pitfalls of splitting queries, and load balancing between multiple read servers is that transactions could end up with different queries hitting different servers.

This is a particular issue when you call INSERT/UPDATE/DELETE and want to then SELECT on that modified data set before completing the transaction.

To solve this, you can either use the MYSQLND_MS_MASTER_SWITCH or MYSQLND_MS_LAST_USED_SWITCH SQL hints, or if you are using mysqli, or PDO, you can let the plugin automatically track transactions.

To do the latter, you must either turn auto-commit off at the start of your transaction, and on at the end, or it can hook into the transaction methods.

First you need to setup the plugin configuration to enable sticky transactions:

To use auto-commit you should do:

// MySQLi extension
$mysqli->autocommit(false); // disable autocommit, implicitly starts a transaction

$mysqli->query('BEGIN');
// queries
$mysqli->query('COMMIT'); // or ROLLBACK

$mysqli->autocommit(true); // enable autocommit

// PDO extension
$pdo->setAttribute('PDO::ATTR_AUTOCOMMIT', false); // disable autocommit, implicitly starts a transaction

$pdo->exec('BEGIN');
// queries
$pdo->exec('COMMIT'); // or ROLLBACK

$pdo->setAttribute('PDO::ATTR_AUTOCOMMIT', true); // disable autocommit

The plugin also tracks the begin transaction, commit, and rollback methods for both extensions, and will send all requests to the master when you call PDO->beginTransaction(), or mysqli->beginTransaction(), while PDO->commit(), PDO->rollback(), mysqli->commit(), or mysqli->rollback() will end the transaction.

Filters

mysqlnd_ms supports a filter chain for determining which server will ultimately be used to execute the query.

There are two types of filters:

• multi filters: these return a list of master, slave, or both servers from which one should ultimately be picked
• single filters: these return a single server

You can specify multiple multi filters, and multiple single filters, each one is passed the result of the previous filter.

Filters are executed in the order they are defined in the configuration file. You must end with a single filter, which will typically be one of the load balancing filters — random, random_once, or roundrobin — or user.

Replication Lag

The first filter we will look at is the quality of service (qos) multi filter.

One of the primary reasons to use the MYSQLND_MS_LAST_USED_SWITCH SQL hint is to send reads to the master once a write has occurred so that you are not affected by replication lag; but otherwise to use the slave to distribute the load.

This is also possible to achieve transparently by using the master_on_write configuration setting:

{
    "appname": {
        "master": {
            ...
        },
        "slave": {
            ...
        }
        "master_on_write": 1
    }
}

However, this would mean that all remaining queries in the request go to the master when this isn’t necessary for queries unaffected by the write that triggered the switch.

Newer versions of mysqlnd_ms have tried to solve this with the qos filter. This feature allows you to be more granular when it comes to handling replication lag in the write-then-read scenario.

There are three types of service level:

• eventual consistency: this is the default, all masters/slaves are considered for read queries. You can additionally automatically filter out slaves that lag N seconds behind the master.
• session consistency: this is the similar as master_on_write=1, however you can additionally automatically include servers that have already replicated the data by using Global Transaction IDs (GTIDs)
• strong consistency: this is only used when using a multi-master synchronous MySQL setup, for example MySQL Cluster.

To configure mysqlnd_ms you can either add it as a filter in the config:

{
    "appname": {
        "master": {
            ...
        },
        "slave": {
            ...
        }
        "filters": {
            "quality_of_service": {
                "TYPEOF_consistency": {
                    "OPTION": VALUE
                }
            },
            "random": 1
        },
    }
}

Or, you can also set this programmatically using:

mysqlnd_ms_set_qos($connection, MYSQLND_MS_QOS_CONSISTENCY_[TYPE], MYSQLND_MS_QOS_OPTION_[OPTION], $value);

Eventual Consistency

The simplest of these is eventual consistency, which will transparently check the Seconds_Behind_Master value on each slave and any that is less than or equal to the accepted value is considered as a read candidate. However, this value increments in whole seconds which means you can still be caught by replication lag issues. It is also dependant on faster networks.

With eventual consistency, we tell mysqlnd_ms to only consider slaves that are within N seconds of the master. To configure the plugin we either use the config:

{
    "appname": {
        "master": {
            ...
        },
        "slave": {
            ...
        }
        "filters": {
            "quality_of_service": {
                "eventual_consistency": {
                    "age": 1
                }
            }
        }
    }
}

Or:

mysqlnd_ms_set_qos($connection, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL, MYSQLND_MS_QOS_OPTION_AGE, 1);

This sets the the plugin to consider all nodes that are less than or equal to one second behind the master.

Session Consistency

Session consistency will ensure that we only use servers that are definitely in sync with the master, this can be slower, but provides a more robust solution that eventual consistency. Session consistency achives this by using Global Transaction IDs or GTIDs. In MySQL 5.6.5m8 and up, MySQL supports server-side GTIDs, which the plugin can use automatically — this is the preferred solution. If you are using older versions of MySQL, or GTIDs are not enabled, you will need to implement client-side GTIDs, which are discussed in the manual.

Because GTIDs may not be available in MySQL, the plugin will fallback to master only.

To configure the plugin for this we use:

{
    "appname": {
        "master": {
            ...
        },
        "slave": {
            ...
        }
        "filters": {
            "quality_of_service": {
                "session_consistency": 1
            }
        }
        "global_transaction_id_injection": {
            "select" => "SELECT @@GLOBAL.GTID_EXECUTED",
            "fetch_last_gtid" => "SELECT @@GLOBAL.GTID_EXECUTED AS trx_id FROM DUAL",
            "check_for_gtid" => "SELECT GTID_SUBSET('#GTID', @@GLOBAL.GTID_EXECUTED) AS trx_id FROM DUAL",
            "report_errors" => true,
        },
    }
}

In this case we add a new section to the configuration global_transaction_id_injection this tells the plugin the queries to use to check the GTID on the server.

Note: These queries only work from MySQL 5.6.17+

Now the plugin will check and consider all servers (for SELECTs) that are in-sync with the master. For other types of queries, it will still only use the master.

If you do not add this additional section to the configuration, it will function in the same was as setting the master_on_write earlier.

It is not possible to set this additional configuration programmatically. However you can enable the master_on_write behavior using:

mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION);

Partitioning and Sharding

Due to the fact that it is very hard to scale writes to multiple machines (e.g. multi-master replication) with traditional MySQL (i.e. not MySQL Cluster) it is fairly common to to use partitioning, or sharding, to enable you to scale across multiple masters by writing different data sets to each one.

For example, your user profile data might be on master A, while shopping cart transactions might be on master B.

Not all partitioning and sharding is done to scale writes however, sometimes replication topologies will choose to replicate subsets of the master database to specific slaves, this allows you to scale hardware more effectively around specific data sets.

The mysqlnd_ms plugin allows you to setup both multiple masters using the mysqlnd_ms.multi_master INI settings, and partitioning using the node_groups multi filter.

Unlike a lot of the other features in mysqlnd_ms partitioning is not an entirely transparent process, in that partitions must be determined either when writing the query in your editor, or programmatically when running the query.

If you require it, to enable multi-master support you should add the following to your PHP.ini:

mysqlns_ms.multi_master=1

Then to setup your node groups, you will add the following to your JSON configuration:

{
    "appname": {
        "master": {
            "master_0": {
                "host": "mysql.master.0",
	          "db": "dbname"
            },
            "master_1": {
                "host": "mysql.master.1",
	          "db": "dbname"
            },
        },
        "slave": {
            "slave_0": {
                "host": "mysql.slave.0",
	          "db": "dbname"
            },
            "slave_1": {
                "host": "mysql.slave.1",
	          "db": "dbname"
            },
        }
        "filters": {
             "node_groups": {
                  "Group_A" : {
                      "master": ["master_0"],
                      "slave": ["slave_0", "slave_1"]
                  },
                  "Group_B" : {
                      "master": ["master_1"],
                      "slave": ["slave_0", "slave_1"]
                  }
             }
         }
    }
}

This adds two node groups, Group_A and Group_B, with both using all of the slaves, but only one master.

You can then route queries to a specific node group by adding a SQL hint containing it’s name to the beginning of the query:

/*Group_A*/SELECT * FROM users;

Which you can chain with other SQL hints like so:

/*Group_B*//*ms=last_used*/SELECT * FROM transactions;

How you prepend the SQL hint is up to you. As mentioned, you can do that in the editor when writing the code, or you can do it programmatically when constructing the query.

Custom Routing

The final feature we will cover is custom routing, which allows you to write your own logic for determining which server the query should be routed to.

This is done by using the user, or user_multi filters. These filters must define a callback, which must return either a single server, or a list of both master and slave servers respectively.

This callback must be a function, it cannot be a static or object method call (or any other type of callable, such as a closure). However, it can be a wrapper around an object or any other code.

With this feature, we are able to programmatically detect some of the edge cases that might otherwise be missed by the default mechanism.

First, let’s setup the configuration:

{
    "appname": {
        "master": {
            ...
        },
        "slave": {
            ...
        }
        "filter": {
             "user": "select_db_server"
        }
    }
}

Next, we can define our callback function:

function select_db_server($connection, $sql, $masters, $slaves, $last_used, $in_transaction)
{
	// Always switch to master if it was last used:
	if (in_array($masters, $last_used)) {
		return $last_used;
	}
	
	// Always use the last used connection if we're in a transaction:
	if ($in_transaction === true) {
		return $last_used;
	}
	
	// Check for the SQL hints:
	if (strpos($sql, '/*' .MYSQLND_MS_MASTER_SWITCH. '*/') !== false) {
		// Force master: 
		return array_rand($masters); // Use array_rand just in case we have multiple masters
	} elseif (strpos($sql, '/*' .MYSQLND_MS_SLAVE_SWITCH. '*/') !== false) {
		// Force slave:
		return array_rand($slaves);
	} elseif (strpos($sql, '/*' .MYSQLND_MS_LAST_USED_SWITCH. '*/') !== false) {
		// Force last used:
		return $last_used;
	}
	
	// Check for some edge-cases:

	// Allow both queries that start with SELECT or (SELECT to go the slaves:
	if (stripos($sql, 'SELECT') === 0 || stripos($sql, '(SELECT') === 0) {
		// But not those with SELECT... INTO:
		// Remember, if we're already in a transaction then last_used is used
		if (pcre_match('/^\(?SELECT (.*) INTO (@|OUTFILE|DUMPFILE)(.*) FROM (.*)$/i', $sql) == 1) {
			return array_rand($masters);
		}
		
		return array_rand($slaves);
	}
	
	// Send CREATE TEMPORARY TABLE to last_used:
	if (stripos($sql, 'CREATE TEMPORARY TABLE') === 0) {
		return $last_used;
	}
	
	// We have to pick one server by the end of this, so default to master:
	return array_rand($masters);
}

Please note that this is an example and probably shouldn’t be used in production. However it illustrates the power of the user filter.

We do a number of checks to ultimately arrive at which server should be used:

1. Much like the master_on_write flag, and the session consistency qos filter, we switch to the master for all future queries once the master has been used.
2. Next, we ensure that we respect transactions and use the last used connection if we are currently in a transaction
3. Then we check and follow the SQL hints
4. Finally we get to our edge-cases: queries starting with (SELECT, and SELECT… INTO queries, and then CREATE TEMPORARY TABLE queries
5. If nothing else was returned, we simply default the master — this ensures we are less likely to send write queries to a slave by accident.

Now, you can do anything you like within this function, so long as you only return one server.

Alternatively, you can use the user_multi filter which is identical, except that you would return an array with two values, each of which is an array of master, or slave servers respectively.

Conclusion

The mysqlnd_ms plugin has a lot of advanced features, that can allow you to effectively scale your application much more easily using traditional MySQL replication topologies.

There is still other features which we haven’t covered, such as cache support, distributed transactions, and MySQL Fabric support.

In the next installment of this series we will take a look MySQL’s InnoDB Memcache Interface, as well as how we can easily (and transparently) utilize it with another MySQLnd plugin: mysqlnd_memcache.

P.S. Are you using any of these advanced techniques already? We’d love to hear your experiences with the mysqlnd_ms plugin via twitter or reddit.