First let me say this is only really useful if you are using ActiveRecord in a non-Rails, multi-threaded context. As in standalone Ruby app or maybe even in a different web framework.
The point of this patch is to allow you to take advantage of ActiveRecord’s connection pool without having to ever deal with it explicitly. The biggest thing you have to worry about with the connection pool is releasing connections when you’re done with them, so that is what this patch does for you. This allows you to have large numbers of threads, even long running ones, using ActiveRecord without requiring an equally sized connection pool.
For example, let’s say you’ve setup your connection pool to allow 3 connections. Try to run this code:
ts =  6.times do ts << Thread.new do User.find(:first) end end ts.each &:join
It’s going to take approximately 5 seconds to finish. Why? Because the first 3 threads take up all the connections from the pool. When the 4th thread tries to checkout a connection it waits up to
wait_timeout (defaults to 5) seconds for a connection to become available. If it can’t get any within that amount of time it will checkin connections for dead threads and then try to checkout again. If you tried to do this with 7 threads you would get a “cannot obtain database connection error” because even after 5 seconds there wasn’t an available connection for the last thread.
As I showed in my previous posts you could solve this relatively easily by calling a cleanup method periodically or at the end of every thread. But how about this example:
6.times do Thread.new do User.find(:first) sleep 100 # or do something non ActiveRecord related for 100 seconds end end
This one cannot be solved as easily. None of the threads die quickly so there are no connections to reclaim. This is where my patch comes in. Even though our threads are alive for 100 seconds we are really only using a connection for a small % of their lifespan. If your application fits this usage pattern then this patch is for you.
How it works
First we have the
cleanup_connection method. Wrap any code with this method and it will ensure that your connection is checked back into the pool when the block completes:
def cleanup_connection return yield if Thread.current[:__AR__cleanup_connection] begin Thread.current[:__AR__cleanup_connection] = true yield ensure release_connection Thread.current[:__AR__cleanup_connection] = false end end
The thread locals are used to make sure the connection won’t be released on nested calls. I will demonstrate the need for this protection in a second. But for now, let’s fix our previous example:
10.times do Thread.new do User.cleanup_connection do User.find(:first) end sleep 100 end end
Each thread will release its connection before the sleep and all the threads should complete their find statements very quickly. But who wants to call
cleanup_connections all over their code. Luckily, you don’t have to. My patch wraps all the necessary ActiveRecord internals with it instead of you having to wrap your code.
The following example illustrates the need for nesting protection. Keep in mind my patch has wrapped both
User.transaction do User.create(:name => '1') User.create(:name => '2') end
Without nesting protection the first
create call would have released our connection after it completed. Which means another thread could have checked it out in between or we could have gotten a different connection for the second call. Either way, our transaction would have been messed up. In short, nesting protection allows you to wrap the smallest amount of code necessary rather than requiring you wrap your code at a very high level. This allows you to keep the connections checked out for as short a time as possible.
There is of course a performance hit for all the added method calls and the cost of constantly checking out/in connections to the pool, but it’s not much. On a query which takes 0.15 seconds to run here are the numbers:
1000x queries ---------------- without patch: 19.23s with patch: 19.82s with patch where all 1000 calls are nested under a single cleanup_connection to prevent checkin/checkout for every call: 19.66s
So the penalty is about 3%. The actual overhead penalty is about 30% (query of 0.001 seconds) so the faster your queries the closer you move toward this.
How to use it
Get it here. It’s a monkey patch so require it anywhere after ActiveRecord has been loaded.
I’ve done my best to try to find all the ActiveRecord methods that need to be wrapped but it’s possible that I have missed some. Because of this I monkey patch the connection method to raise an alert whenever it is called from outside of a
cleanup_connection block. If you see one of these, you can look through the stack trace, determine which method needs to be wrapped and add it to the
methods_to_wrap hash. After you are confident all necessary methods are patched you can remove the
connection monkeypatch to speed things up a bit.
I’ve submitted a patch to Rails for just the
cleanup_connection portion. I think it would be a nice addition to the already existing but not very useful
with_connection method. Feel free to comment or +/-1 the patch.