News Archive
PhpRiot Newsletter
Your Email Address:

More information

Slides: MySQL 5.6 Global Transaction Identifier and PECL/mysqlnd_ms for session consistency

Note: This article was originally published at Planet PHP on 28 March 2012.
Planet PHP

Why do we have to bother about built-in GTID support in MySQL 5.6 at all? Sure, it is a tremendous step forward for a lazy primary copy system like MySQL Replication. Period. GTIDs make server-side failover easier (slides). And, load balancer, including PECL/mysqlnd_ms as an example of a driver integrated load balancer, can use them to provide session consistency. Please, see the slides. Buta

MySQL 5.6 Global Transaction IDs - Use case: (session) consistency
View more presentations on MySQL and mysqlnd

a the primary remains a single point of failure. GTIDs can be described as cluster-wide transaction counters generated on the master. In case of a master failure, the slave that has replicated the highest transaction counter shall be promoted to become the master. Its the most current slave. Failover made easy - no doubt! Adequately deployed, you should reach very reasonable availability.

Know the limits of replicated systems

A multi-master (update anywhere) design does not have a single point of failure. But among the biggest is scaling a multi-master solution. Jim Gray and Pat Helland concluded 1996 in "The Dangers of Replication and a Solution": Update anywhere-anytime-anyway transactional replication has unstable behavior as the workload scales up: a ten-fold increase in nodes and traffic gives a thousand fold increase in deadlocks or reconciliations.. N^3 - buuuhhhh, anything worse than linear scale is not appreciated. Guess what: Microsoft SQL Azure is using primary copy combined with partitioning.

In practice things are not that bad, particulary not for a small number of nodes and recent algorithms. For example, MySQL Cluster (related webinar on March 29) is a true multi-master solution - even eager/synchronous. To overcome the write-scale limitations it has built-in partitioning (sharding). The two classical scale-out solutions - replication and partitioning - are combined in one product. If you want extreme performance and are ready to pay for the costs of partitioninga try it.

Anything to learn from the NoSQL kids on the block?

Some other kids offer relaxed eventual consistency just as MySQL Replication does. Sometimes the CAP theorem is cited as an excuse for it . Some leave conflict resolution, even conflict detection to the application developer . A massively scalabale, high available, synchronous update anywhere solution with built-in conflict resolution - the big thing we all dream of - is hard to create.

In the meanwhilea - maybe custer-aware APIs

While we all wait for the one-fits-all solution, there is something we can do. We can start to tell our load balancers precisely what we need and request no higher level of service than needed. Consistency - as in CAP - is one aspect of service quality. We should start to have cluster-aware APIs abstracting the details of replication architectures. Then, our load balancers, including PECL/mysqlnd_ms can hide everything that makes working with a cluster complicated (connection pooling, request splitting and redirection, failover, node selection, load distribution, a). Also, vendors can start to play with consistency to improve performance without messing up application logic.

Below is how you use the PECL/mysqlnd_ms 1.2+ function mysqlnd_ms_set_qos() to switch between eventual consistency (stale data allowed) and session concistency (read-your-writes). MySQL Replication details hidden behind a function call.

$mysqli = new mysqli("myapp", "username", "password", "database"); if (!$mysqli) /* Of course, your error handling is

Truncated by Planet PHP, read more at the original (another 2457 bytes)