Ulf Wendel

Replication and load balancing mysqlnd plugin for all PHP MySQL extensions released

Happy Easter! Replication is as old as life on earth. No life without replication. MySQL replication is as old as MySQL, almost. No MySQL without replication. The PECL/mysqlnd_ms 1.0.0 alpha release is brand new. My dream, no PHP without the mysqlnd library replication and load balancing plugin, which works with all the PHP MySQL extensions (mysql, mysqli, PDO_MySQL)!

PECL/mysqlnd_ms (download alpha release) is a transparent plugin for the mysqlnd library. It inspects statements executed by any of the three PHP MySQL extensions, if they are compiled to use mysqlnd. Read-only statements are load balanced and send to one of the configured MySQL slave servers. All other statements are executed on the configured MySQL master server. Connection pooling and connection switches are done automatically. The plugin does not change the user API. Depending on the usage scenario very little, if any, application changes may be needed.

All automatic decisions of the plugin can be monitored and overruled, if need be.

Any PHP MySQL application
|
PHP 5.3
mysql mysqli PDO_MySQL
mysqlnd library
PECL/mysqlnd_ms mysqlnd plugin
automatic R/W split and load balancing (manual override)
| | |
MySQL master MySQL slave 1 MySQL slave n

Usage example

Basic usage of the plugin is straight-forward. The plugin deploys its own configuration file. Among others, the configuration file instructs the plugin which MySQL master server and MySQL slave servers to use. The configuration file is divided into sections. Each section has a name. For creating a load balanced connection open a MySQL handle and use the section name as a host name.

[myapp]
master[]=localhost:/tmp/mysql.sock
slave[]=192.168.2.27:3306

/* Load balanced following "myapp" section rules from the plugins config file */
$mysqli = new mysqli("myapp", "username", "password", "database");
$pdo = new PDO('mysql:host=myapp;dbname=database', 'username', 'password');
$mysql = mysql_connect("myapp", "username", "password");

Any non-prepared statement run on any of the three MySQL handles opened in the previous example will be load balanced. Every statement which begins with SELECT is considered read-only and sent to a slave. All other statements are run on the master server. SQL hints can be used to force using a slave, the master or the last used server. A user defined callback can be installed to replace the built-in read write split mechanism.

/* Statements will be run on the master */
if (!$mysqli->query("DROP TABLE IF EXISTS test")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
if (!$mysqli->query("CREATE TABLE test(id INT)")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
if (!$mysqli->query("INSERT INTO test(id) VALUES (1)")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}

/* read-only: statement will be run on a slave */
if (!($res = $mysqli->query("SELECT id FROM test")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
} else {
 $row = $res->fetch_assoc(); 
 $res->close();
 printf("Slave returns id = '%s'\n", $row['id'];
}
$mysqli->close();

Three load balancing policies are available: random, random once (sticky), round robin. Additionally a user defined callback can be installed to pick the server for running a statement.

Key Features at a glance

  • Transparent and therefore easy to use
    • supports all PHP MySQL extensions
    • no API changes
    • very little, if any, application changes required, dependent on the usage scenario required
  • Featured read-write split strategies
    • automatic detection of SELECT, supports SQL hints to overrule automatism
    • user-defined
  • Featured load balancing strategies
    • round robin: choose different slave in round robin fashion for every slave request
    • random: choose random slave for every slave request
    • random once (sticky): choose random slave once to run all slave requests for the duration of a web request
    • user-defined. The application can register callbacks with mysqlnd_ms

Current status and Limitations

The plugin is released as alpha. The release contains all features planned for 1.0. It shows the final API. The release does pass all tests. There are no known bugs. However, as an alpha release it cannot be recommended for mission critical use.

The 1.0 alpha does not support multi-statements. Also, it does not support native prepared statements. Please, do not confuse the PDO prepared statement emulation used by PDO_MySQL by default with native prepared statements. By default, PDO_MySQL works with the plugin. Further limitations are documented in the manual.

Happy Easter, happy hacking!

Feedback and comments are welcome! Happy Easter, happy hacking. Enjoy replication.

Download, Documentation, Blog postings

Happy Easter!

6 Comments

  1. Does this module perform straight load balancing without read/write splitting with a blacklist and timeout for failed nodes in the same way Connecotr/J does for NDBCLUSTER?

  2. Havent wondered why can’t you use HTTPs request method for determining which type of server (master or slave) to connect to.

    HTTP protocol states that GET/HEAD should be a safe method, and therefore it can use a slave, whereas a POST/PUT/DELETE isn’t and probably needs a master.

  3. Henrik,

    thanks :-) !

    Matthew,

    no, the 1.0 does not offer straight load balancing without read/write splitting. One could hack that using a user-defined callback for server select. Cluster has not been our focus for 1.0. Plain old MySQL replication is the focus.

    You are not the first to ask for a blacklist and timeout of failed nodes/servers. I haven’t though much about 1.1 but such a blacklist seem a very valuable and important feature – even for plain old MySQL replication. It is probably a must for 1.1.

    Andrey once proposed to check if SHOW SLAVE HOSTS and/or SHOW SLAVE STATUS provides any information which helps with automatic reconfiguration for the case that a server becomes unavailable or falls behind the master in an unacceptable way.

    However, this is 1.0 alpha. There are severe limitations (multi-statement, prepared statements, transaction awareness) to lift. We are no on pair with C/J, yet…

    Thanks for the hint on blacklist and timeout!
    Ulf

  4. Ren,

    I do not understand. How does the HTTP request type tell you what your PHP script does?

    When GETting a web page the web server may start a PHP script to create the web page. That PHP script may run an INSERT and that’s something which should go to the master.

    *tilt* Give me a hand.

    Ulf

  5. Section 9.1 of Hypertext Transfer Protocol — HTTP/1.1 RFC 2616

    http://www.w3.org/Protocols/rfc2616/rfc2616-sec9.html

    Taking the RFC’s suggestions as rules, seems a good idea. Yes a HTTP GET can have side effects, but generally should be avoided. One example of it going wrong would be the problems that Google’s Web Accelerator caused with phpMyAdmin. All sue to the phpMyAdmin devs not paying attention to the HTTP spec.