Correct setting of database connection pool database.yml for single-threaded Rails applications
Quite late to the party here, but I ran out of database connections today in production.
Like a lot of people, I use Sidekiq to perform asynchronous jobs like sending emails for example. It is important to note that Sidekiq runs as a multithread process.
So, I don't just have a single-threaded Rails application, therefore this answer does not directly apply to the question asked but I thought it was worth saying something here as I think multithreaded Rails apps are relatively normal nowadays.
This means you need to adjust your pool size in such a way as to create enough connections to handle all the jobs that can be enqueued and take longer than 5 seconds (the default timeout period to wait for a database connection before throwing an error).
Manage Connections
The major benefit of connection pooling for a single-thread server like Mongrel/Passenger/etc is that the connection is established/maintained in a Rack handler outside the main Rails request processing. This allows for a connection to be established once vs. many times as it's used in different ways. The goal is to re-use the established connection and minimize the number of connections. This should prevent having to reconnect within a given request processing cycle and possibly even between requests (if I recall correctly).
Multiple Concurrent Connections
Although most use cases (Mongrel/Passenger) are single threaded and can only use a single connection at a time - there is JRuby and environments/app servers that have full multi-threaded support. Rails has been thread safe since 2.2
Connection pooling is handled inside of ActiveRecord, so all application servers should behave basically the same.
The database connection pool starts out empty and creates connections over time according to demand. The maximum size of this pool defaults to 5 and is configured in database.yml.
Requests and users share connections from this pool. A request checks out a connection the first time it needs to access the database and then checks the connection back in at the end of the request.
If you use Rails.threadsafe! mode, then multiple threads might beaccessing multiple connections at the same time, so depending on therequest load you might have multiple threads contending for a fewconnections.
You can change accordingly, If you are using single threaded app. Default is 5 as per most user's need, as now a day its normal to have a multithreaded app.