Scaling out mysql (or any other database for that matter) means creating a master slave setup where the slaves replicate information from the master. This naturally creates replication lag. Depending on the load of the systems the slaves will be behind master anything between seconds or even minutes and more. This of course creates problems in the application tier. When data consistency in the user interaction is required the application needs to fall back to the master. When the user changes a setting the subsequent request should not show stale data from a slave that is not up to speed yet.
For Rails multiple master slave adapters are available that act as transparent delegates to connect to the master and slave setup. By wrapping the database access ActiveRecord can be forced to go to the master when full consistency for the user interaction is required.
ActiveRecord::Base.with_master do ... end
This can lead to many more requests to the master than what is really needed. Taking a step back and having only the user experience in mind it becomes clear that what usually is required is only a per-user consistency. The user should see what he just changed - not necessarily what other users have changed. This loosens the contract and is the basis for an idea.
The master server has a binlog position that increases with with every write. Slaves are fully replicated when their binlog position matches the one from master. It's a race of an ever increasing clock. This means that if the clock on the slave matches (or is larger than) the clock of the last write of the user, the query can safely use the slave without risking inconsistencies to be presented to the user.
To express this contract we introduced a new construct into the adapter
new_clock = ActiveRecord::Base.with_consistency(old_clock) do ... end
Whenever there is a write the clock is increased. The 'with_consistency' is a contract that the block is executed on database that has reached the given clock. When entering such a block the master slave adapter checks the slave replication status and makes the right choice whether the query can be served from the slave or whether it requires to go to the master. It shaves off all the unnecessary requests to master and concentrates on the user's view of the database.
By introducing this change at SoundCloud we managed to reduce the query load on the master server by roughly 50% and make better use of our slaves. The master no longer is the bottleneck. The per-user clocks are stored in memcache. If the clocks are gone we fall back to master.
The fork of the master slave adapter is available on github and can be used from Rails as a gem.
gem install master_slave_adapter_tcurdt
Because it makes use of
SHOW MASTER STATUS SHOW SLAVE STATUS
it's mysql only at this stage.
There still is some refactoring work to do but it is in production since June and we are more than happy with the results so far. If you have Rails and a mysql cluster you should probably give it a try.