Best implementation for MySQL replication with Rails 3?

Question

We're looking at potentially setting up replication for our primary MySQL database, and while setting up the replication seems pretty straight-forward, the application implementation seems a bit murkier.

My first idea would be to set up a master-slave configuration and RW-splitting, with all write queries (CREATE, INSERT, UPDATE) going to master, and all read queries (SELECT) going to slave. Having read up on it, it seems that there are essentially two options for how to implement this with our app:

1. Using an independent middleware layer for all MySQL connections, such as MySQL proxy or DBSlayer. However, the former is in Alpha and the latter has limited documentation.
2. Using a Ruby-based gem/plugin, such as Octopus to achieve RW-splitting in the framework.

If we wanted to go with a master-slave setup, what you recommend moving forward?

The other thought I've had was to use a master-master configuration, but am unsure about the implementation of such a setup.

Thoughts?

Answer 1

Generally you should do your R/W splitting in the framework because only it can understand the context. In PHP I do this by maintaining two connections - one for writes and one for reads and decide which you want explicitly in your code. The reason for this is that it's not as simple as splitting by query type. For example, if you start a transaction on the write connection, you want all the reads inside it to go through that as well, otherwise they will be outside the transaction and will probably get old data or get hung up with locks.

Unless your workload is really read-heavy, replication is not a scaling solution as replication lag will cause you to get out of date results. Master-master is not that special - it's just two instances of master-slave, but you should not make the mistake of trying to write to both masters as you're asking for split-brain nightmares.

The config I really like is using mmm with master-master pair. This makes failover and redundancy really easy and transparent to applications, and it works beautifully.