How to Master/Slave switch, load balancing in CakePHP

I need to implement master/slave/load balancing into an existing site.

Does anyone use these (or other) implementations for master/slave switching?

The resources I found on how to implement master/slave into Cake:.

• (preferable) gamephase.net/posts/view/master-slave-datasource-behavior-cakephp
• http://bakery.cakephp.org/articles/view/master-slave-support-also-with-multiple-slave-support
• http://bakery.cakephp.org/articles/view/load-balancing-and-mysql-master-and-slaves-2

I'm getting number 1) to work most of the times but it has trouble with some of the joins.

I welcome new sources, hacks or mods for master/slave implementation as for now I can't get my head around it.

(Cake version I am using atm is 1.2) (I'm cross posting this on CakePHP's google groups http://groups.google.co.uk/group/cake-php/browse_thread/thread/4b77af429759e08f)

Take a look at this tutorial in regards to Master/Slave over several nodes.

http://www.howtoforge.com/setting-up-master-master-replication-on-four-nodes-with-mysql-5-on-debian-etch

This may help you understand better.

As far as I can tell this happens if your model has relationships with models that do not use the same behaviour. Please correct me if this assumption is wrong.

All models have meta-data, which CakePHP accumulates using a DESCRIBE query on the database, if this data is not present your joins will be broken. This meta-data is database config specific.

CakePHP uses this meta-data to populate the $this->_schema property. SQL joins are built with data from the $this->_schema property and I guess this is where your issue lies, the database introduced by this MasterSlave switch behaviour do not have any model meta-data for tables associated with the model.

A solution would be to update your behaviour so that it only switches selectively on read and writes. Add this behaviour to all related models. i.e Any model that is related using hasOne, hasMany etc should also use the same behaviour.

In essence all models that are related should write to the same database and read from the same database.

The bonus of this solution is you will share the same database connections.

Your web app seems to be multi tier, you need to scale each tier individually:

• The web layer, i.e. tha CakePHP app can be spread across multiple web servers. This is easy to do, as the code itself is idempotent. You should look into how to load balance apache servers, it is not a big deal. Webservers have quite high throughput though, so if you have a bottleneck here, you might improve your code/caching strategy instead. (Use memcache instead of file caches for example.) If you depend on the file system (uploads for example) this becomes a bit more complex, as it must become distributed or separated.

• The data layer. There are various tutorials how to scale/load balance MySQL already linked by others.

Albeit first I would suggest to make benchmarks. (Premature optimization is the root of all evil.) You must know first where the bottlenecks are, where the throughput should scale. Often you can optimize queries, caching, or make thing cacheable in the first place. You must also be clear in your goals: scalability? fault tolerance?