Welcome PECL/mysqlnd_ms 1.2.0-alpha with global transaction ID support

2011/12/14 by admin

Christmas time, time for presents! Version 1.2.0-alpha of the free and open source PHP mysqlnd replication and load balancing plugin has been made available on PECL. PECL/mysqlnd_ms makes using any kind of MySQL database cluster easier featuring:

Read-write splitting: automatic, SQL hints, can be disabled
Load balancing: random, round robin, user defined
Fail over
Global transaction ID support: client-side emulation
Service levels: eventual consistency, session consistency, strong consistency

The last two features are new. The motto/theme of the 1.2 series is: Global Transaction ID injection and quality-of-service concept.

For many years MySQL has adviced PHP developers to implement all kinds of logic needed to use MySQL Replication or MySQL Cluster in the application. PECL/mysqlnd_ms is changing this. Take any PHP MySQL application using any PHP MySQL extension (mysql, mysqli, PDO_MySQL), compile PHP to use the mysqlnd library, install the PECL module, drop it in and, be ready to migrate the application from a single database to a database cluster. At any time, it shall be possible to overrule all automatic decisions of the plugin, whenever needed.

Service levels

One of the biggest challenges when switching from a single database to an asynchronous database cluster, such as a MySQL Replication cluster, is the change in data consistency. A single server delivers strong consistency: all clients see each others changes. A MySQL replication cluster defaults to eventual consistency for a slave access: a client may or may not see its own changes, which is a significantly lower service level. However, in many cases its OK, why else would have an asynchronous approach have gained such popularity.

Version 1.2.0-alpha of mysqlnd_ms introduces a quality of service filter. The application says what service level it needs from the cluster and the plugin delivers:

Eventual consistency
- optional parameter: maximum age, don’t read from slaves that lag too far behind
Session consistency (read your writes)
- optional parameter: global transaction ID for reading from "up-to-date" slaves
Strong consistency

A simple function call – mysqlnd_ms_set_qos() – sets the service level.

Global Transaction ID support

In its most basic form a global transaction ID can be described as a transaction counter maintained on the master. If the master fails, the most current slave for master promotion can easily be found by searching the slave with highest transaction ID. The MySQL 5.6 Development Release contains this feature, however, it is neither production ready nor fully functional yet. And, so far, master fail over remains a manual task of the database administrator. Whenever the server solution is ready, mysqlnd_ms users are prepared to make the most out of it.

Beginning with version 1.2.0-alpha, PECL/mysqlnd_ms can to a client-side emulation of the feature and transparently maintain a global transaction ID table on the master. Client-side emulation is a second best solution but one of the few options one has in heterogenous environments with servers that do not have the functionality built-in.

A global transaction ID can also be used for improving session consistency (read your writes) load balancing for MySQL replication. Without a global transaction ID a client cannot say for sure in advance if a slave has already replicated a change. To read the own writes, the client must query the master. By help of a global transaction ID one can find if a certain write has been replicated already by a slave. Read your writes becomes possible on slaves. Read load is taken away from the master.