Posted about 1 month ago. Visible to the public. Linked content. Auto-destruct in 3 days

Updated: Using ActiveRecord with threads might use more database connections than you think

Rewrote the entire card.

Changes

  • Database connections are not thread-safe. That's why ActiveRecord uses a separate database connection for each thread.
  • -
  • -
  • -
  • -For instance, the following code requires three database connections:
  • +For instance, the following code uses 3 database connections:
  • ```ruby
  • 3.times do
  • Thread.new do
  • User.first # first database access makes a new connection
  • end
  • end
  • ```
  • -Unfortunately ActiveRecord never returns these connections after the thread finishes, unless you tell it to. Since the number of connections is limited, **you will eventually run out of connections**. In that case, the next thread trying to connect will raise `ActiveRecord::ConnectionTimeoutError`.
  • +These three connections will remain connected to the database server after the threads terminate. This only affects threads that use ActiveRecord.
  • -The number of connections is limited by both your database server and your ActiveRecord pool size configuration, which defaults to `5`. The ActiveRecord connection pool holds a list of all connections your worker process has made so far. With your help, the pool can re-use connections that are no longer in use by threads that have terminated.
  • +You can rely on Rails' various clean-up mechanisms to release connections, as outlined below. This *may* cause your application to open more connections than your database server allows. If you don't want to risk that, you can also manually release connections once your thread is done.
  • -Explicitily return the connection when you're done
  • +How can I exceed connection limits?
  • +-----------------------------------
  • -We already learned that this code leaks a database connection:
  • +There are two limits to consider:
  • +
  • +- Your database server has a limit how many open connections it will allow in total.
  • +- You can configure the maximum number of connections for each Rails process. This is called the size of your connection pool.
  • +- The default pool size is `5`. You can configure the pool size with a key like `pool: 3` in your `database.yml`.
  • +
  • +Every Rails process has its own connection pool, allowing the configured maximum number of connections. Note that an application worker process, a Rails console or a Sidekiq process each counts as its own process.
  • +
  • +You can calculate the maximum number that your application could theoretically use at the same time. For example, let's say we hae:
  • +
  • +- 2 application servers
  • +- 6 Rails worker processes in each application server
  • +- 1 background job server with Sidekiq ([will spawn 10 threads by default](https://github.com/mperham/sidekiq/wiki/Advanced-Options#concurrency))
  • +
  • +When your code does not use ActiveRecord in threads you may use the following number of connections:
  • -```ruby
  • -Thread.new do
  • - User.first # first database access makes a new connection
  • -end
  • ```
  • +(2 app servers * 6 app workers) + (1 job server * 10 sidekiq workers) = 12 + 20 = 32 connections
  • +```
  • +
  • +Your your code does use ActiveRecord from up to 4 threads in parallel, you may reach the following number of connections:
  • +
  • +```
  • +((2 app servers * 6 app workers) + (1 job server * 10 sidekiq workers)) * 4 threads = (12 + 20) * 4 = 128 connections
  • +```
  • +
  • +This calculation is a maximum. In practice the number will often be lower, as there are various automatic clean-up mechanisms.
  • +
  • +
  • +When will inactive threads release their connections?
  • +------------------------------------------------------
  • +
  • +Database connections will be released when one of the following events occur:
  • +
  • -To fix this, you can call `clear_active_connections!` to release the database connection back into the pool when you're done:
  • +### After every request
  • +
  • +When Action Pack handles a HTTP request it will automatically release connections of inactive threads.
  • +
  • +
  • +### When another thread needs a new connection and the pool is empty
  • +
  • +When a thread tries to check out a new connection and the connection pool is exhausted, Rails will release connections from inactive threads before giving up.
  • +
  • +If there are no connections from inactive threads, `ActiveRecord::ConnectionTimeoutError` is raised.
  • +
  • +
  • +### Every 60 seconds
  • +
  • +The connection pool has a "reaper thread" that periodically looks for connections from inactive threads and returns them to the pool.
  • +
  • +By default the reaper runs once every 60 seconds. You can configure a different interval by setting the `reaping_frequency` key in your `database.yml`
  • +
  • +
  • +### When you manually release the connection.
  • +
  • +If you don't want to rely on any of the automatic clean-up mechanisms above, you can explicitely release the connection from your thread.
  • +
  • +To do so, call `clear_active_connections!` before your thread terminates:
  • ```ruby
  • Thread.new do
  • begin
  • User.first # first database access makes a new connection
  • ensure
  • ActiveRecord::Base.clear_active_connections!
  • end
  • end
  • ```
  • -The `clear_active_connections!` is available in Rails 4 and 5. There are similar methods with [slightly different semantics](https://bibwild.wordpress.com/2014/07/17/activerecord-concurrency-in-rails4-avoid-leaked-connections/) in earlier versions of Rails.
  • -
  • -After the connection was returned to the pool, it can be **re-used** by the next thread.
  • -
  • -Rails does this automatically after every request, for the one thread used to process the request. So **you only need to do this for any threads you spawn yourself**. Rails does not know about any additional threads from your code, so it cannot release the connections for you.
  • -As an alternative, ActiveRecord also has a `with_connection` method that you can wrap your code in:
  • -
  • -```ruby
  • -Thread.new do
  • - ActiveRecord::Base.with_connection do
  • - # your code here
  • - end
  • -end
  • -```
  • +### When the Rails process terminates
  • -The one drawback is that this will *always* check out a connection from the pool, regardless whether or not your code will actually need a connection. E.g. [rendering partials in parallel](/makandra/45222) will not need a connection if the rendered data is already preloaded (which it should be).
  • +E.g. when you stop the server or console.
  • -Dealing with unfixable code
  • -Sometimes you need to work with code that is out of your control. E.g. convoluted legacy code or code from external gems. If that code makes threads and leaks database connections, you might not be able to fix it with the methods above.
  • +Will the pool ever close an unused connection?
  • +----------------------------------------------
  • -For these cases you can set a `reaping_frequency` option in your `database.yml`. This will schedule a thread that will release unused connections every `x` seconds.
  • +A released connection will be returned to the pool, but *not* disconnect from the database server. The connection will remain connected in the pool, waiting for a new thread to request a connection. Hence the connection *will* count against your database server's maximum number of connections.
  • -For code that is in your own control, I recommend to not rely on the reaper and make sure your code doesn't leak instead.
  • +Only when a connection in the pool remains unused for more than 5 minutes, ActiveRecord will actually *close* the connection. You can configure this by setting the `idle_timeout` in your `database.yml`.

By refactoring problematic code and creating automated tests, makandra can vastly improve the maintainability of your Rails application.

Owner of this card:

Avatar
Henning Koch
Last edit:
about 1 month ago
by Henning Koch
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more