您如何设置连接池？
设置池的最佳方法是什么：-
- 何时创建连接？
- 你什么时候关闭连接，你会关闭所有连接吗？
- 您测试连接是否仍然良好。 何时以及如何？
- 如何计算出最大连接数的最佳数字？
- 您采取了哪些监控措施来确保矿池用户行为良好？ 你能阻止一段坏代码毁掉一切吗？
- 您编写了自己的池，还是使用了第三方库？
我相信这是一个不可知的问题，但欢迎对特定数据库/语言的“功能”发表评论。 例如，连接某些数据库可能比其他数据库更慢或更昂贵。
澄清一下，我不打算从头开始编写一个池，这个问题更多的是关于如何配置进行池化的现有库。
What is the best way to setup your pool with respect to:-
- When do you create connections?
- When do you close connections, and would you close all of them?
- Do you test connections are still good. When and how?
- How do you figure out a good number for the maximum number of connections?
- What sort of monitoring do you have in place to ensure users of the pool are well behaved? Can you stop one bad piece of code from taking out everything?
- Have you written your own pool, or used a third-party library?
I believe this is an agnostic question, but comments about "features" of particular databases/languages are welcome. For example, it might be slower or more expensive to connect on some databases than others.
To clarify, I do not intend to write a pool from scratch, this question is more about how to configure an existing library that does pooling.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我用Java写了一个数据库连接池，当时它只是一个设计模式而不是一个通用库。 现在我用的是Tomcat内置的。
我使用一个线程来监视池的几个方面，并使用几个参数来控制其行为...
几年来，这对我很有帮助。 我粗略地观察了一下，该池的最高连接数是 151 个。 通常，在繁忙时段，泳池的水位大约为 12 米，而在清晨时分，水池的水位会降至最低 3 米。
我使用 Oracle 的 JDBC 瘦驱动程序并连接到 Oracle 数据库。
I wrote a connection pool for the database in Java when it was just a design pattern and not a common library. Now I use the one built into Tomcat.
I used a thread to monitor several aspects of the pool and several parameters to control its behavior...
This served me very well for a couple of years. The highest I ever saw the pool was 151 connections during a wild peek. Usually the pool was at about a dozen during heavy usage and idled down to the minimum three in the early morning hours.
I used Oracle's JDBC thin drivers and connected to an Oracle database.
这是我最近实施的基本原理。
连接池中有两种连接。 第一个已准备就绪，意味着已打开但未被客户端使用。 第二种是活动的，意味着正在被客户端使用。
让您的连接池维护少量的就绪连接，最小为 N，最大为 M。N 可以根据客户端请求连接的峰值速度进行调整。 如果就绪连接数降至零，则需要更大的 N。如果该数字始终很高（例如高于 10），则需要较低的 N。
当客户端需要连接时，为他们提供以下之一：准备就绪（使其处于活动状态），然后如果现在准备就绪的数量少于 N，则立即打开一个新的（但不要让客户端等待此完成，否则您将失去池化的优势）。 这可确保始终至少有 N 个就绪连接。 如果当客户端需要连接时没有准备好，他们将不得不等待您创建一个新连接。
当客户端完成活动连接时，如果就绪连接数少于 M，则将其返回到就绪状态。 否则关闭它。 这可以防止您拥有超过 M 个就绪连接。
定期回收就绪的连接以防止过时的连接。 如果有超过 N 个就绪连接，则关闭最旧的连接。 否则关闭它并重新打开另一个。
这样做的优点是可以在连接池中拥有足够的就绪连接和年轻连接，而不会导致服务器过载。
Here is the rationale I used for a recent implementation.
Have two sorts of connections in your connection pool. The first is ready, meaning open but not in use by a client. The second is active, meaning in use by a client.
Have your connection pooling maintain a small number of ready connections, minimum of N and maximum of M. N can be adjusted depending on the peak speed at which your clients request connections. If the number of ready connections ever drops to zero, you need a bigger N. If the number is consistently high (say above 10), you need a lower N.
When a client wants a connection, give them one of the ready ones (making it active), then immediately open a new one if there's now less than N ready (but don't make the client wait for this to complete, or you'll lose the advantage of pooling). This ensures there will always be at least N ready connections. If none are ready when the client wants one, they will have to wait around while you create a new one.
When the client finishes with an active connection, return it to the ready state if there's less than M ready connections. Otherwise close it. This prevents you from having more than M ready connections.
Periodically recycle the ready connections to prevent stale connections. If there's more than N ready connections, just close the oldest connection. Otherwise close it and re-open another.
This has the advantage of having enough ready and youthful connections available in your connection pool without overloading the server.
Jakarta Commons DBCP 已经完成了您列出的所有操作：
您可以通过设置最小连接数、要创建的最大连接数和超时来微调池。 较长的超时将允许您拥有较低的连接限制，而较短的超时可能需要更大的连接数。 这在很大程度上取决于您的应用程序的功能以及它如何使用连接。
Jakarta Commons DBCP already does all the stuff you listed:
You can fine tune your pool by playing with the minimum number of connections, the max number of connections to be created, and the timeout. A longer timeout will allow you to have a lower limit of connections, while a shorter timeout will probably required a larger number. This depends heavily on what your application does and how it uses the connections.
我同意 Matt b 的观点，我们不应该重新发明轮子。
然而，根据 this 和这个问题。
那里提到了更好的替代方案，例如 c3po 或 proxool.
或者您可以使用 RDBMS 依赖的连接池机制。
I agree with matt b that we should not reinvent the wheel.
However using Commons DBCP is arguable based on the answers of this and this questions.
There are better alternatives mentioned there like c3po or proxool.
Or you may use rdbms dependent connection pooling mechanism.
我不确定您使用联系的环境是什么，但我可以分享对我有用的内容。
我使用 SQL Server 作为后端，并结合使用缓存来获得更好的性能。
我的做法是仅在实际需要时才保持连接打开，并且不池化连接，以便它们立即清理，并且我可以在 SQL 活动监视器中准确地看到哪些活动，哪些不活动。 每个连接都会占用内存，因此在不需要它们时最好将其保持在沉闷的状态。
在回答连接打开和关闭问题之前，我要说的是缓存非常重要。 从缓存中获取对象将为您节省大量时间。 在我的一些 asp.net 应用程序中，当在开发中启用缓存时，我发现我几乎无法测量延迟，而使用数据库调用可能需要 15 毫秒到 45 毫秒才能完成调用，而且这甚至没有考虑其他延迟因素或负载。 我使用的另一种方法是为我的数据提供良好的对象结构，以便我仅在发生变化时才进行数据库更新。 我已经在我的对象上实现了一些方法，以确保我执行的 IO 操作尽可能少。
话虽这么说，我们都知道我们需要在某个时候访问和写入数据库，所以我遵循两个原则：
保持门窗关闭以节省能源。 一个地方打开的连接意味着它在另一个地方不可用（或者内存和其他资源更加有限）。 我们关闭了池化，因为它可以为我们带来更好的性能。
当连接打开时，我会尽可能批量或一次执行尽可能多的操作。 这有点复杂，所以让我解释一下。
回到我第一次开发 ASP 应用程序的那一天，我实际上会在页面开始加载后立即打开连接，然后关闭它。 我不建议再这样做了。 现在，这些类型的抽象和层有很大的好处，我建议任何新手程序员都仔细注意。
我的两分钱：
缓存您的数据！ 缓存您的数据！ 缓存您的数据！ 当无法缓存然后缓存数据时，尽可能少地进行数据库访问！
I’m not sure what the context in which you are using your connections but I can share what seems to work for me.
I use SQL server as my back end and use a combination of caching with it to get better performance.
My practice is to keep the connection open only if I actually need it and to not pool connections so that they clean up right away and I can see in SQL Activity monitor exactly what is active and what’s not. Each connection takes up memory so it’s nice to keep it to a dull roar when they aren’t needed.
Before I answer the connection open and close question let me say that caching is really important. Getting an object out of the cache is going to save you a ton of time. In some of my asp.net apps when caching is on in dev I have found that I can hardly measure the latency whereas with a DB call it might take anywhere from 15ms to 45ms to complete the call and this isn’t even considering other latency factors or load. The other method that I use is a good object structure for my data so that I only make a DB update if something changes. I’ve implemented some methods on my object o make sure that I’ve doing as little IO as possible.
That being said we all know that we need to access and write to our DB at some point so I follow two principles:
Keep the doors and windows closed to save on energy. An open connection in one place means that it’s not available in another (or the memory and other resources are more limited). We have turned pooling off because it has resulted in better performance for us.
I do as much in batch or at once as I can when the connection is open. This is a bit more complicated so let me explain.
Back in the day when I first was developing asp apps I’d actually open the connection as soon as the page started to load and then close it after. I don’t recommend doing that anymore. Now a day there is a large benefit to these sorts of abstractions and layers that I would recommend any novice programmer take careful attention to.
My two cents:
Cache your data! Cache your data! Cache your data! Do as little DB access as possible when you can’t cache and then cache your data!
为什么要重新发明轮子？
有人可能已经解决了这个问题，而且更好。
如果您在 Java 世界中，则可以使用 Commons DBCP。
Why re-invent the wheel?
Someone has already probably solved the problem, and better.
If you're in the Java world, you can use Commons DBCP.