Active Record Connection Management in Rails 5.1 or lower

file.md

Active Record Connection Management in Rails 5.1 or lower

This guide is for Rails 5.1 or lower. Starting in 5.2, all these issues have been made irrelevant.

• rails/rails#31173
• rails/rails#31221
• rails/rails#31241
• rails/rails#28742
• rails/rails#29807

---

In general, Active Record will automagically manage database connections in an efficient manner. See Configuring a Database and Database pooling for information about the basics of optimally configuring your application to use available resources.

Sometimes, you will need to manage Active Record connections and/or connection pools yourself. These situations are:

• Configuring a multi-process web server
• Spawning processes

In previous version of rails, you needed to think about managing connections when spawning threads, but this is no longer the case (I'm not sure in which version this was changed).

---

Configuring a multi-process web server

Each process in a multi-process web server needs its own database connection pool. If the server is also multi-threaded, the threads within each process will automatically share that processes' connection pool.

Here is how to configure a server to set up its pools while booting your app:

Before forking

Before the server forks, you need to disconnect the pool so that the forked processes don't use the same connections as the parent process. Here's how to do that in a puma config:

before_fork do
  ActiveRecord::Base.connection_pool.disconnect!
end

After forking

After a process forks and has no connections, it needs to establish connections. Here's how to do that in a puma config:

c.on_worker_boot do
  ActiveRecord::Base.establish_connection
end

Spawning processes

When spawning processes, you must do a small amount of manual database pool management.

Let's say you you have a script that you are going to run as a worker using rails runner, my_worker.rb. In this script, you are going to start two long-running processes. Here's how you would go about doing that:

ActiveRecord::Base.connection_pool.disconnect!
thing1_pid = Process.fork do
  ActiveRecord::Base.establish_connection

  Thing1.new.run
end

thing2_pid = Process.fork do
  ActiveRecord::Base.establish_connection

  Thing2.new.run
end

# optional: If you want to do other DB operations in the parent thread,
# you will need to now reconnect like this:
ActiveRecord::Base.establish_connection

Process.wait thing1_pid
Process.wait thing2_pid

Note that each of the forked processes will have their own connection pool with the number of connections configured in your app. So if database.yml specified a connection pool of 5, then running rails runner my_worker.rb will use up to 10 connections.

The relationship between parent and child pools

Now, one might imagine that, in the above example (which is exemplary of typical code), because...

1. A completely new pool of connections is established in each child process.
2. No DB operations are done in the children before the child pools are established.
3. No DB operations are done in the parent before the child pools are established.

...then maybe the disconnect in the parent isn't necessary, since no resources are attempted to be shared between processes at the same time?

However, this is not the case. There is no mechanism in Rails for "forgetting" the old pool without destroying it. The parent pool must always be disconnected in the parent process first.

Spawning threads

Rails will automatically allow threads to share connections from a connection pool. As long as your app is configured to have at least as many connections as there are threads running at the same time, you won't have to worry about managing connections.

You can experiment with this behavior in the console:

500.times{ Thread.new{print User.count}.join }
# succeeds

500.times{ Thread.new{print User.count; sleep 1} }
# after a few successful threads, raises "could not obtain a connection from the pool"

Further Reading

• http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/AbstractAdapter.html
• http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/ConnectionPool.html
• https://github.com/puma/puma-heroku/blob/master/lib/puma/plugin/heroku.rb
• https://github.com/grosser/parallel#activerecord
• https://devcenter.heroku.com/articles/deploying-rails-applications-with-the-puma-web-server#on-worker-boot
• https://devcenter.heroku.com/articles/concurrency-and-database-connections#threaded-servers
• https://tenderlovemaking.com/2011/10/20/connection-management-in-activerecord.html

Excelent guide!

It is not clear to me if in Rails 5.2+ it is still necessary to do disconnect! outside fork and establish_connection inside fork or if Rails is now smart enough to handle connections on forked processes.

I'm working in a Rails 6.1 project and we have some forked processes that needs ActiveRecord connections to work. We are experiencing issues with ActiveRecord connections, when our database is in scheduled maintenance, we loose the connections and Rails is not reconnecting by itself and some errors like ActiveRecord::StatementInvalid: PG::UnableToSend: no connection to the server are triggered.

Today, we are doing this:

ActiveRecord::Base.remove_connection

pid = Process.fork do
    Process.setproctitle("myprocesstitle")
    ActiveRecord::Base.establish_connection
    # ... My process code
end

Process.detach(pid)

The issue is that we have multiple databases and we are loosing the connection to these auxiliary databases. Maybe we should remove the connections outside fork and reestablish connection inside fork for each database and we are not doing that today.

We recently updated to Rails 6.1 and since then we didn't have database maintenances yet so I don't know if it happens with Rails 6.1.

Another thing to mention is that when the issues happened we was doing this auxiliary connections using establish_connection inside model instead of using new helper connects_to. We have already changed our code to use connects_to in the expectation that this solves our issues.

Basically my questions are:

• Do I still need to do these workarounds described in this guide for Rails 6.1? Is so, do I need to do that for every different database connection or doing it with ActiveRecord::Base should be enough?
• Changing from establish_connection at model to connects_to, even in forked processes, should make Rails be able to reconnect in case of connection loses?

Thank you!
@jjb

Another thing that just came to my mind is that maybe the problem is that we are doing the remove_connection outside fork and establish_connection inside fork manually. If I understood right, this is not necessary anymore and doing it manually maybe have some consequences, maybe Rails could not manage the connection pool to reconnect anymore and we need to do it manually too.

I'll try to remove this pieces of code to see what happens.

Hi @williamweckl! So, the top of this document says "This guide is for Rails 5.1 or lower. Starting in 5.2, all these issues have been made irrelevant".

Nothing in this guide is needed in rails 5.2 or higher.

I'm pretty sure you can remove all of your connection and reconnection code. I just double checked some daemons I have, which access multiple databases, and I no longer have any of the code described in this doc.

Was that not clear, or am I misunderstanding your question? Let me know!

Hi @jjb, thanks for the reply.

About this guide, I think it could be clearer. When you say that the issues have been made irrelevant and bring the links that you brought, my understanding was that maybe the issues that I have does not fit in the pull requests fixes listed. I saw all the PRs and this was the confusion to me, none of them had concrete examples that made me believe it fixed my specific issues. Some of them mention puma directly, other mention socket.io and mysql2. It is a little different from my scenarios.

What is still not clear to me is what happens if I keep the code fix from this guide in newer versions of Rails. It could improve something? It could be worse? In my case, I was experiencing issues similar to the fixes of the PRs listed (not exactly, as I said before) even using Rails 6.0+. So my hypothesis is that the fix for Rails 5.1 or lower from this guide, applied in Rails 5.2+, creates issues instead of solving them. That initializing the connection manually, makes ActiveRecord to loose the ability to reconnect and to manage pool connections. Does this make any sense to you? If so, maybe this information could be at the guide to help others.

What makes me think a lot is that Puma, even with the workarounds, was able to reconnect. My other processes that was forked manually, was not.

Anyway, I just removed all the workarounds from this guide in my applications and for now, none of the issues I had happened.

Thanks again for your time.

I saw all the PRs and this was the confusion to me, none of them had concrete examples that made me believe it fixed my specific issues.

The main thing to look at is this: rails/rails#29807

In particular this comment: rails/rails#29807 (comment)

"I believe I've eliminated the need for all of this guidance: it should finally all Just Work without anyone needing to think about it."

what happens if I keep the code fix from this guide in newer versions of Rails. It could improve something?

no

It could be worse?

probably not

I was experiencing issues similar to the fixes of the PRs listed (not exactly, as I said before) even using Rails 6.0+

maybe you found a bug. maybe you could try making a minimal rails app which reproduces the problem

my hypothesis is that the fix for Rails 5.1 or lower from this guide, applied in Rails 5.2+, creates issues instead of solving them

hm, interesting. seems unlikely. are you able to experiment?

My other processes that was forked manually, was not.

if you share your code here maybe i'll be able to recommend something

Anyway, I just removed all the workarounds from this guide in my applications and for now, none of the issues I had happened.

oh okay - seems like all good then? what a wild ride 🎢 😅