About pool management in ActiveRecord

rubyonrails-core
1

Yesterday someone commented in my article on Sequel, where I compare it with AR in some aspects, including pool management:

To answer his comments I decided to first take a glance at the

current (4.1.1) implementation of how the connections’ pool work in Rails with AR. After our discussion in the comments, when I was about to sleep, I was thinking more about this subject and decided it might worth bringing some ideas to you, in case you’d be interested on them… Basically, ActiveRecord currently relies on delegating the connection pool management to the user. Most users don’t realize it because they don’t usually spawn new threads from the main request thread and there’s an AR middleware that’s automatically integrated to Rails that will checkin the connection back to the pool in the end of the request. Since the connection id is set in a thread local that means the Rack middleware can only checkin the connection used in the main request thread. Here’s some example to illustrate: Assuming the default pool size (5), running this action 6 times will fail currently: ab -n 6 -c 1 This is not anything new and Aaron Patterson has already touched this subject long ago, in 2011: In a side note, yesterday I learned about an interesting project to set a common API for job libraries that is intended to be merged to Rails at some point: The default adapter (inline) implements an “enqueue_at” method that will spawn a new thread: So, calling enqueue_at for a job using the default adapter will share the same problems of the implementation above. Then I was thinking that most of AR API could be implemented in a smarter way, so that this wouldn’t be a problem. That means calling “with_connection” behind the scenes whenever they need a connection. Also, even “execute” could be implemented this way. Instead of checking out a connection by calling AR::Base.connection, it could simply return a proxy. If you really want to checkout and reserve that connection you could call “connection.lock” for instance and then the user would know that they must ensure “unlock” is called after it’s done. But otherwise, calling “execute” would perform the query under a “with_connection” block, checking the connection in back to the pool after running the SQL statement. I’m just suggesting the idea in case someone might be interested in coming up with a PoC for this in case the core team agrees with the suggested approach (it introduces a bit of backward incompatibilities). I don’t plan to work on this, specially because I don’t use AR myself, but maybe a better automatic handling of connections in the pool might be of interest to most AR users… Cheers, Rodrigo.

2

Specifically regarding a Thread outside of the main request thread holding onto a connection, you might be interested in https://github.com/rails/rails/pull/14360 about more intelligently reaping connections held onto by dead threads.

Unfortunately, the comments on that PR say it will not be backported to 4.1 and will only be included in 4.2 :frowning:

3

Hi Schuck, that was indeed a smart trick to associate each connection with is owner thread and check whether they are alive when there's apparently no available connection. In that case the reaper is run and could free up those connections when the thread is dead. I liked it :slight_smile: Simple and effective for most use cases.

In theory even the middleware would no longer be strictly required, although it could add a bit of performance by avoiding the reaper to run everytime...

Congrats to Mathew :slight_smile:

But there's still another use case where a smarter implementation would help.

Consider you spawn a new thread and this thread will perform a request to the database. After it's done it will start some processing that could take quite a lot of time but that will no longer need to perform any statements in the database until the long processing is finished. Something like this:

def my_action    Thread.start do      posts = Post.all.to_a      do_long_time_processing_with posts      Post.update processed: true    end end

In this case, if someone needs a connection while the thread is processing some data the connection would still be marked as in use even though it's idle most of the time. You could certainly work around it by issuing a close in the connection after Post.all.to_a, but it would be better if this was handled in a transparent way.

Specially because it's not always obvious that this is the case. A better management of the connections could decrease the need for a bigger pool size.

That's why I think that it would be a good thing if the connections were not collected only in the end of the request (or thread) cycle but rather just after the statement is run. Unless the user explicitly wants to lock the connection in case they are creating temporary tables valid for the duration of the connection, or something like this...

But it's certainly good to know about this change for the upcoming Rails 4.2.

Thanks for sharing.

Rodrigo.