CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresqldatabase-connectionpuma
Jurr
Jurr

Reputation: 41

What happens when the possible amount of database connections is larger than the PostgreSQL allowed max_connections?

Background:

On production we have a poorly understood error that occurs sporadically (more frequently by the week) and may take down our whole application at times – or sometimes just all of our background processes. Unfortunately, I am not certain what causes the issue, below is my working theory – could you please validate its logic?

The error preceding the downtime (occurring a couple of hundred times in matters of seconds) is the PostgreSQL error FATAL: sorry, too many clients already.

Working theory:

Various parts of an API can request connections with the database. In our Ruby on Rails application for example, we have 12 puma workers with 16 threads (12 * 16 = 192 possible db connections). Also, we have 10 background workers, each being allowed a single db connection. If we also account for a single SSH session with 1 database connection, the maximum amount of db connections we would have to anticipate is 192 + 10 + 1 = 203 PostgreSQL connections, set with the max_connections in the postgresql.conf config file.

Our max_connections however is still set to the PostgreSQL default of 100. My understanding is that this is problematic: when the application thinks more db connections are possible (looking at the application side settings for puma and our background workers) it allows for new db connections to be made. But when those connections with PostgreSQL are initiated, PostgreSQL looks at its own set maximum of 100 connections and breaks the connection.

When instead the amount of "requestable" connections (in this case 203) would either be lower than or equal to the PostgreSQL max_connections, it would utilise the pool timeout to queue to requested db connection until a db socket becomes available.

This is desirable since too many connections could be resolved within the pool timeout. Thus the solution to our problem is to make the "requestable" database connections =< possible database connections. If that is still not enough, I should increase the 100 possible connections.

Does this make sense...?

Any ideas or criticism would be very much appreciated!

Upvotes: 0

Views: 488

Answers (1)

dentarg
dentarg

Reputation: 1764

Your app threads does not need to map 1-1 to database connections. You can use a connection pool for the database connections. See https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/ConnectionPool.html

There is also lots of good info on this subject at https://devcenter.heroku.com/articles/concurrency-and-database-connections

Upvotes: 1

Related Questions