如何确定数据库的连接限制应该是多少？
在我的组织中，根据政策，PostgreSQL 数据库的创建连接数限制为 20 个。当多个应用程序使用连接池时，这往往会导致交互效果很差，因为其中许多应用程序打开了全套连接并使它们保持空闲状态。
正如您所期望的，一旦有多个应用程序与数据库联系，我们就会耗尽连接。
池化行为在这里是一个新事物；到目前为止，我们通过基于 Web 的数据库网关（？！）序列化对池连接的访问或根本不池化任何内容来管理池连接。因此，我不得不一遍又一遍地解释（实际上是一个人在项目过程中发出的 5 张故障单）池化的工作原理。
我想要的是以下之一：
增加数据库可用连接数量以与池良好配合的可靠、无可争议的理由。
如果是这样，安全限度是多少？有什么理由将限制保持在 20？我错了的原因，我们应该缩小池的大小或完全消除它们。
就其价值而言，以下是正在发挥作用的组件。如果与其中之一的配置方式相关，请权衡：
DB：PostgreSQL 8.2。不，我们不会将其升级作为此计划的一部分。
Web 服务器：Python 2.7、Pylons 1.0、SQLAlchemy 0.6.5、psycopg2
- 由于系统的某些方面使用手动配置的引擎使用 SQLAlchemy ORM 访问数据，而其他方面使用不同的引擎工厂访问数据（仍然是 sqlalchemy），因此情况变得复杂）由我的一位同事编写，它将连接包装在与旧 PHP API 匹配的对象中。
任务运行器：Python 2.7、celery 2.1.4、SQLAlchemy 0.6.5、psycopg2
At my organization, PostgreSQL databases are created with a 20-connection limit as a matter of policy. This tends to interact poorly when multiple applications are in play that use connection pools, since many of those open up their full suite of connections and hold them idle.
As soon as there are more than a couple of applications in contact with the DB, we run out of connections, as you'd expect.
Pooling behaviour is a new thing here; until now we've managed pooled connections by serializing access to them through a web-based DB gateway (?!) or by not pooling anything at all. As a consequence, I'm having to explain (literally, 5 trouble tickets from one person over the course of the project) over and over again how the pooling works.
What I want is one of the following:
A solid, inarguable rationale for increasing the number of available connections to the database in order to play nice with pools.
If so, what's a safe limit? Is there any reason to keep the limit to 20?A reason why I'm wrong and we should cut the size of the pools down or eliminate them altogether.
For what it's worth, here are the components in play. If it's relevant how one of these is configured, please weigh in:
DB: PostgreSQL 8.2. No, we won't be upgrading it as part of this.
Web server: Python 2.7, Pylons 1.0, SQLAlchemy 0.6.5, psycopg2
- This is complicated by the fact that some aspects of the system access data using SQLAlchemy ORM using a manually configured engine, while others access data using a different engine factory (Still sqlalchemy) written by one of my associates that wraps the connection in an object that matches an old PHP API.
Task runner: Python 2.7, celery 2.1.4, SQLAlchemy 0.6.5, psycopg2
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为每个并发活动需要一个连接是合理的，并且假设并发 HTTP 请求同时执行也是合理的。
现在，您想要处理的并发 HTTP 请求的数量应该随着 a) 服务器上的负载和 b) 可用的 CPU 数量而变化。如果一切顺利，每个请求都会消耗某个地方的 CPU 时间（在 Web 服务器、应用程序服务器或数据库服务器中），这意味着您无法同时处理比 CPU 数量更多的请求。在实践中，并不是一切顺利：某些请求会在某个时刻等待 IO，并且不会消耗任何 CPU。因此，并发处理比 CPU 数量更多的请求是可以的。
尽管如此，假设您有 4 个 CPU，允许 20 个并发请求已经是相当大的负载了。我宁愿限制 HTTP 请求，也不愿增加可以同时处理的请求数量。如果您发现单个请求需要多个连接，则您的应用程序存在缺陷。
因此，我的建议是应对限制，并确保没有太多空闲连接（与您实际并发处理的请求数量相比）。
I think it's reasonable to require one connection per concurrent activity, and it's reasonable to assume that concurrent HTTP requests are concurrently executed.
Now, the number of concurrent HTTP requests you want to process should scale with a) the load on your server, and b) the number of CPUs you have available. If all goes well, each request will consume CPU time somewhere (in the web server, in the application server, or in the database server), meaning that you couldn't process more requests concurrently than you have CPUs. In practice, it's not that all goes well: some requests will wait for IO at some point, and not consume any CPU. So it's ok to process some more requests concurrently than you have CPUs.
Still, assuming that you have, say, 4 CPUs, allowing 20 concurrent requests is already quite some load. I'd rather throttle HTTP requests than increasing the number of requests that can be processed concurrently. If you find that a single request needs more than one connection, you have a flaw in your application.
So my recommendation is to cope with the limit, and make sure that there are not too many idle connections (compared to the number of requests that you are actually processing concurrently).