News Archive
PhpRiot Newsletter
Your Email Address:

More information

Some sharding support and cache locality optimization support for PHP MySQL driver

Note: This article was originally published at Planet PHP on 19 December 2012.
Planet PHP

It is time for christmas presents: some sharding support and cache locality optimizations are coming with PECL/mysqlnd_ms 1.5. PECL/mysqlnd_ms is a plugin for the mysqlnd library. The plugin adds replication and load balancing support to any PHP MySQL API (mysql, mysqli, PDO_MySQL) if compiled to use the mysqlnd library.

As a MySQL user you can choose between a wide variety of clustering solutions to scale-out. Your options range from eventual consistent solutions to strong consistent ones, from built-in (MySQL Replication, MySQL Cluster) to third party or home-grown. PECL/mysqlnd_ms is a client side load balancer that aims to serve all.

Background: cluster-wide partitioning and sharding

Divide-and-conquer is a true classic. If you are lucky, your business requires you to spread load and data over many MySQL nodes using some sort of cluster. In many cases read scale-out offered by MySQL Replication is all you need. Any step further means digging deeper into the world of distributed systems.

Writes MySQL Master | | | Slave 1 Slave 2 Slave 3 Reads

Possibly, you need more than the above and look into schema-based partitioning for distributing data over many nodes. MySQL Replication cluster topologies are hardly limited by anything but your fantasy. Please, mind the writes because MySQL Replication is using lazy primary copy but who do I tell this?

Writes for DB1, DB2 Writes for DB3, DB4 Reads covering DB1aDB4 Reads covering DB1aDB4 MySQL Master 1 replicate DB1, DB2 - MySQL Master 2 DB1 (rw), DB2 (rw), DB3 (ro), DB4 (ro) DB1 (ro), DB2 (ro), DB3 (rw), DB4 (rw) | | A | A | A | Slave 1 Slave 2 A Slave 3 A Slave 4 A Slave 5 DB1 DB1 A DB2 A DB3 A DB4 Reads DB1 A Reads DB2 A Reads DB3 A Reads DB4

Finally, further growth gets you into sharding. Again, you split up a big task into small manageable units. This time the split is not done "vertical" along columns, tables or schemata but horizontally along rows. For example, you distribute all rows of the table T1 from the database DB1 based on their ID column value over multiple nodes.

Writes for DB1.T1 (ID 0a1e9) A Writes for DB1.T1 (ID 1e9) A Writes for DB1.T2 a Reads

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