Thursday, February 12, 2009

MySQL Replication and Zend Framework

We recently switched to a replicated database system using MySQL and I had to modify our own framework (that is built on top of Zend Framework) to adapt to this new model.

In such model, you have one or many masters, which will be used for all write operations (insert, delete, update) and many slaves, which will be use for read-only operations (select).

The idea is to have a database adapter, or any class, that will handle this for us. We didn't want to have to use two adapters all the time, and use the 'master' adapter for writes, 'slave' adapter for reads, etc, on the fly manually. Maybe in your project such technique would work, but we have a more complicated framework, with business objects (which are derived from Zend_Db_Table, a Table Data Gateway pattern) which require a db adapter as parameter and will work on this adapter for database operations.

Since we wanted to make master/slave transparent for our classes that inherit from Zend_Db_Table, we needed a class that would make all the branching for the programmer and underlying Zend Framework classes.

At first, my idea was to make a class that would instantiate two adapters internally, one for the master, one for the slave. This class wouldn't derive from anything, it would be some kind of MyCompany_Replicated_Db_Manager with methods similar to Zend_Db_Adapter. But nope, this solution is impossible. Zend Framework sadly doesn't use interfaces intensively, and a class like Zend_Db_Table does validate the db adapter you're passing it against Zend_Db_Adapter class, not some Zend_Db_Adapter_Interface. I need to derive from Zend_Db_Adapter, there's no other way around with Zend Framework implementation of Zend_Db_Table.

Allright, I then jumped to solution #2. I created a MyCompany_Db_Adapter_Pdo_Mysql which, when used for write operations, uses the master connection, which is the main connection of the adapter... but dispatches all read calls to a db adapter I create internally which is mapped to the slave.

To create this adapter, you can pass it an extra 'slave' parameter which will give connection information for the slave server. If no 'slave' paramater is passed, this adapter will work like the normal one, everything will be done on your 'master' which is the default connection.

So basically, you get something like:

$myDbAdapter->insert(); // will insert automatically on the master
$myDbAdapter->fetchAll(); // will get data automatically from the slave

Sometimes, you need to make sure all read operations are also made on the master. Let's say a new user registers to your site, and the next page, you select the newly inserted user data from the database and display it to the user. By default, the fetch will be done on the slave... if the replication wasnt fast enough, you won't have the user information ready in time! You simply need to 'force' the adapter to stay on the master.


Once you're done and want the adapter to act normally, you reset the forced adapter:


There's more to it, but hopefully you get the main idea. By the way, this adapter doesn't support multiple slaves or masters. It's not its job to 'pool' connections. If you want to pool many servers, slaves and /or masters, I suggest making a class that does only that: pools different connections and gives you one randomly (or any other algo you might want to use). It's often a mistake newcomers to OOP will make: they try to make a class do much more than the thing it's supposed to do. An adapter is a single connection, it's not a pool. If you make an adapter become a pool, you have a problem with your design.

I have to admit I'm walking a fine line myself. In theory, an adapter should represent one connection, not two. But I will blame Zend Framework for this forced "OOP prostitution" ;-) Give me some Table Data Gateway interface and I'll make a connection manager, not a derived adapter...

I've seen other solutions on the internet that involved making a change to Zend_Db_Table, and not Zend_Db_Adapter. The problem with this is that it will only work when you use Zend_Db_Table. But sometimes you can't use this class, when the query is too complicated and/or when the query involves more than one table and doesn't fit in a Table Data Gateway pattern. When you extend Zend_Db_Adapter, you get your master/slave functionality at the root/top, not at the bottom.

1 comment:

  1. It would be great to see how you realize work with Mater/Slave replication. Could you share your code somewhere please?