Rails 3 Slave Databases: Compare Octopus to SDP

In the crazy wild days of Rails 2.x…

In the pre-Rails 3 ecosystem, there were a number of confusingly similar choices for getting master/slave database functionality established. These options included Masochism, DB Charmer, master_slave_adapter, and seamless_database_pool, amongst others. When it came time from Bonanza to make its choice on which slave plugin to use, I made my best effort to assess the velocity and functionality of each of the prominent slave database solutions, and wrote what went on to become a fairly popular post comparing the relative strengths of each choice.

Octopus

Fast forward to Rails 3, and the field has narrowed considerably. Most all of the top Google results for Rails slave database options these days point to Octopus, and with good reason. Its documentation is sound, and its github project has maintained good velocity for the better part of the past year. Reading between the lines of the Octopus documentation, it would seem that it was built first and foremost as a tool to make it stupidly easy to shard databases; secondarily, it also supports using slave databases in a non-sharding format, but the implementation here gets a little more sketchy, as the examples show users needing to explicitly declare a given slave database for a particular query. In the documentation, this is done at query time, e.g.,

User.where(:name => "Thiago").limit(3).using(:slave_one)

or

Octopus.using(:slave_two) do
  User.create(:name => "Mike")
end

Seamless Database Pool

Upon learning about octopus, my natural inclination was to compare it to our current solution, seamless_database_pool. Admittedly, when we got to the Rails 3 party, SDP was running a bit behind. The author had been kind enough to do much of the legwork to get it compliant with AR3, but we still encountered errors actually trying to use the plugin within controllers and views the way we had been able to with the previous version.

So I fixed it.

What Seamless Database Pool now represents is a slave database plugin that is specifically built with the purpose of making it as easy as possible to A) connect to one or more weighted slave databases B) declare whether a particular Rails action should attempt to use slaves, masters or both (automatically defaulting to the master when write operations occur) and C) gracefully handle failover if one or more of the slave databases declared should become unavailable for whatever reason.

SDP does not have any built in support for sharding, so if that is what your DB needs, Octopus is your best bet. But if what you need is specifically a Rails 3 supported solution that will allow you to connect mix and match your main database and N number of slaves, in a weighted way and with failover automatically baked in, this is where seamless_database_pool really shines.

Bonanza has been using SDP in production for more than a year now, and in the meantime have experience failures of our slave database every few months, which at one point what have brought down the entire site. Now, within seconds, Rails figure out that it needs to re-route requests and finds a database it can use that is still available. The still-good SDP documenation describes how to make it happen.

Bottom line

Prior to writing this blog, if you Google master/slave database you would probably come away thinking there was only one solution, and that solution was only secondarily focused on allowing N slaves to be configured. I may be wrong about the level of support that Octopus already had for setting up multiple weighted failover slaves (and being able to declare usage of these on a per-action vs. per-query basis), but the documentation makes me think that this is at best a future roadmap feature. In the meantime, if it’s specifically database support you need, try the drag-and-droppable SDP gem. I will continue linking my fork of the project until the original author decides what he wants to do with my pull request (which fixes fundamental issues with Rails 3 controller integration, plus adds more robust slave failover).

Installation

Is as easy as possible. In your bundler Gemfile:

gem “seamless_database_pool”, :git => “git://github.com/wbharding/seamless_database_pool.git”

Your database.yml file will then look something like:

production:
  adapter: seamless_database_pool
  port: 3306
  username: app_user
  password: app_pass
  pool_adapter: mysql
  master:
    host: 1.2.3.4
    pool_weight: 0 # 0 means we only use master for writes if the controller action has been setup to use slaves
  read_pool:
    - host: 2.3.4.5
      username: slave_login
      password: slave_pass

Do drop a line in the comments with any questions or feedback if you have experience with either SDP or Octopus as solutions for Rails slave database support!

Free Lock on Single Mysql Table Row

We ran into a problem today where a single row in one of our tables seemed to get stuck in a state where any query that tried to update it would hit our lock wait timeout of 50 seconds. I googled and googled to try to figure out a straightforward way to release this lock, but the closest thing I could find was a big assed Mysql page on table locks that lacked any specific solutions and this Stack Overflow post that suggests fixing a similar problem by dropping the entire table and re-importing it (uh, no thanks).

After some trial and error, I came up with two viable ways to track down and fix this problem.

The first way is to actually look at Mysql’s innodb status by logging into your Mysql server and running

show innodb status\G

This will list any known locks by Mysql and what it’s trying to do about them. In our case, the locked row did not show up in the innodb pool status, so instead I executed

show processlist;

This listed everything that currently had a connection open to Mysql, and how long it’s connection has been open for. In Rails it is a bit hard to spot which connection might be the one to blame, since every Rails instance leaves its connection open whether it is waiting for a transaction to complete or it is doing nothing. In today’s case, I happened to have a good hunch about which of the 50 connections might be the problem one (even though it was listed as being in the “sleep” state…strangely), so I killed it by restarting the server, and all was well. However, I could have also killed it using:

kill process [process id];

If you don’t happen to know which of your processes has done the lock, the only recourse I know would be to restart your servers and see which Mysql processes remain open after the servers have reset their connections. If a process stays connected when it’s parent has left, then it is your enemy, and it must be put down. Hope this methodology helps someone and/or my future self.