Connection Management in ActiveRecord

OMG! Happy Thursday! I am trying to be totally enthusiastic, but the truth is that I have a cold, so there will be fewer uppercase letters and exclamation points than usual. 

Anyway, I want to talk about database connection management in ActiveRecord. I am not too pleased with its current state of affairs. I would like to describe how ActiveRecord connection management works today, how I think it should work, and steps towards fixing the current system.

Posted by Aaron Patterson – October 20, 2011
http://tenderlovemaking.com/2011/10/20/connection-management-in-activerecord

Non-blocking ActiveRecord & Rails

Beyond 'gem install MySQL’ in Ruby

View more presentations from Ilya Grigorik.

Rails and MySQL go hand in hand. ActiveRecord is perfectly capable of using a number of different databases but MySQL is by far the most popular choice for production deployments. And therein lies a dirty secret: when it comes to performance and 'scalability' of the framework, the Ruby MySQL gem is a serious offender. The presence of the GIL means that concurrency is already somewhat of a myth in the Ruby VM, but the architecture of the driver makes the problem even worse. Let's take a look under the hood.

Dissecting Ruby MySQL drivers

The native mysql gem many of us use in production was designed to expose a blocking API: you issue a SQL query, and the library blocks until the server returns a response. So far so good, but unfortunately it also introduces a nasty side effect. Because it blocks inside of the native code (inside mysql_real_query() C function), the entire Ruby VM is frozen while we wait for the response. So, if you query happens to have taken several seconds, it means that no other block, fiber, or thread will be executed by the Ruby VM. Ever wondered why your threaded Mongrel server never really flexed its threaded muscle? Well, now you know.
Fortunately, the little known mysqlplus gem addresses the immediate problem. Instead of using a single blocking call, it forwards the query to the server, and then starts polling for the response. For the curious, there are also two implementations, one in pure Ruby with a select loop, and a native (C) one which uses rb_thread_select. The benefit? Well, now you can have multiple threads execute database queries without blocking the entire VM! In fact, with a little extra work, we can even get some concurrency out of ActiveRecord.

http://www.igvita.com/2010/04/15/non-blocking-activerecord-rails/?utm_source=feedburner&utm_campaign=Feed%3A+igvita+%28igvita.com%29&utm_content=feed

Always turn on ActiveRecord logging in the console

Remember that you can turn on ActiveRecord logging for the console and see what SQL-query is executed?
Here’s a statement you can put in your environment.rb file (or in one specific environment config file - such as development.rb) to have it turned on permanently:

if "irb" == $0
 ActiveRecord::Base.logger = Logger.new(STDOUT)
end

http://weblog.jamisbuck.org/2007/1/8/watching-activerecord-do-it-s-thing#comment-1266

nested relations in ActiveRecord

class State < ActiveRecord::Base
 has_many :cities
end

class City < ActiveRecord::Base
 has_many :streets
 belongs_to :state
end

class Street < ActiveRecord::Base
 has_many :houses
 belongs_to :city
end

class House < ActiveRecord::Base
 belongs_to :street
end

How do you get all of the Houses in virginia?

Well, you could do this:

virginia.cities.collect{|c| c.streets}.flatten.uniq.collect{|s| s.houses}.flatten.uniq

... but that's epically lame. It looks like shit and produces an metric ton of queries and as a result is highly inefficient.

How about this:

House.find(
 :all,
 :include => {:street => {:city => :state}},
 :conditions => {'states.id' => virginia.id}
)

This is a single query with joins where appropriate, and it's a finder on House which is what you're getting anyways. Makes sense, no?

http://joshsharpe.com/archives/56

DB Charmer – ActiveRecord Connection Magic Plugin

DbCharmer is a simple yet powerful plugin for ActiveRecord that does a few things:

1. Allows you to easily manage AR models’ connections (switch_connection_to method)
2. Allows you to switch AR models’ default connections to a separate servers/databases
3. Allows you to easily choose where your query should go (on_* methods family)
4. Allows you to automatically send read queries to your slaves while masters would handle all the updates.
5. Adds multiple databases migrations to ActiveRecord

Master slave adapter

This simple plugin acts as a common ActiveRecord adapter and allows you to setup a master-slave environment using any database you like (and is supported by ActiveRecord).

This plugin works by handling two connections, one to a master database, that will receive all non-"SELECT" statements, and another to a slave database that that is going to receive all SELECT statements. It also tries to do as little black magic as possible, it works just like any other ActiveRecord database adapter and performs no monkeypatching at all, so it’s easy and simple to use and understand.

http://agilewebdevelopment.com/plugins/master_slave_adapter