postgresql 正确设置单线程Rails应用的数据库连接池database.yml

Correct setting of database connection pool database.yml for single-threaded Rails applications

提问by chris_b

I was wondering about the following setting in the Rails database.yml:

我想知道 Rails database.yml 中的以下设置：

By default the number of database connections for the connection pool of ActiveRecord is set to 5:

默认情况下，ActiveRecord 的连接池的数据库连接数设置为 5：

development:
  ...
  pool: 5

But by default, Rails 3 is single threaded. Why would you need 5 connections by default?

但默认情况下，Rails 3 是单线程的。为什么默认情况下需要 5 个连接？

As far as I understand, a single threaded Rails app can't trigger multiple database operations at once, why would do you need to keep more connection open?

据我了解，一个单线程的 Rails 应用程序不能同时触发多个数据库操作，为什么你需要保持更多的连接打开？

I would assume that 2 connections would make sense, so you always have one active connection even if the other one times out, but holding five connections seems a little odd to me.

我认为 2 个连接是有意义的，因此即使另一个连接超时，您也始终有一个活动连接，但保持五个连接对我来说似乎有点奇怪。

Am I missing something?

我错过了什么吗？

UPDATE If anyone else is curious, I just found a commit that explains it: https://github.com/rails/rails/commit/b700153507b7d539a57a6e3bcf03c84776795051

更新如果其他人很好奇，我刚刚找到了一个解释它的提交：https: //github.com/rails/rails/commit/b700153507b7d539a57a6e3bcf03c84776795051

In fact these default settings don't make any sense, it was fixed but then temporarily reverted (one year ago) because of the test suite.

事实上，这些默认设置没有任何意义，它是固定的，但由于测试套件而暂时恢复（一年前）。

回答by djb

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.

像很多人一样，我使用 Sidekiq 来执行异步工作，例如发送电子邮件。需要注意的是，Sidekiq 作为多线程进程运行。

So, I don't justhave 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.

所以，我不只是一个单线程的 Rails 应用程序，因此这个答案并不直接适用于所提出的问题，但我认为值得在这里说点什么，因为我认为现在多线程的 Rails 应用程序相对正常。

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).

这意味着您需要以这样一种方式调整池大小，以创建足够的连接来处理所有可以排队并花费超过 5 秒（在引发错误之前等待数据库连接的默认超时时间）的作业。

回答by Gopal S Rathore

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).

对于像 Mongrel/Passenger/etc 这样的单线程服务器，连接池的主要好处是连接是在主 Rails 请求处理之外的 Rack 处理程序中建立/维护的。这允许一次或多次建立连接，因为它以不同的方式使用。目标是重用已建立的连接并最小化连接数。这应该可以防止在给定的请求处理周期内甚至可能在请求之间重新连接（如果我没记错的话）。

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

尽管大多数用例（Mongrel/Passenger）都是单线程的，并且一次只能使用一个连接——但 JRuby 和环境/应用程序服务器具有完整的多线程支持。Rails 从 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 be accessing multiple connections at the same time, so depending on the request load you might have multiple threads contending for a few connections.

• 连接池在 ActiveRecord 内部处理，因此所有应用程序服务器的行为应该基本相同。

• 数据库连接池开始时为空，并根据需要随着时间的推移创建连接。此池的最大大小默认为 5，并在 database.yml 中配置。

• 请求和用户共享来自该池的连接。请求在第一次需要访问数据库时签出连接，然后在请求结束时重新签入连接。

• 如果你使用 Rails.threadsafe！模式，那么多个线程可能同时访问多个连接，因此根据请求负载，您可能有多个线程争用几个连接。

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.

如果您使用的是单线程应用程序，您可以相应地进行更改。根据大多数用户的需要，默认值为 5，因为现在拥有多线程应用程序是正常的。