你如何设置连接池？

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.

为了澄清,我不打算从头开始编写池,这个问题更多的是关于如何配置实现池的现有库。

6 个解决方案

#1

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.

我在Java中为数据库编写了一个连接池,它只是一个设计模式,而不是一个公共库。现在我使用Tomcat内置的那个。

I used a thread to monitor several aspects of the pool and several parameters to control its behavior...

我使用一个线程来监视池的几个方面以及控制其行为的几个参数......

minimumInPool="3"... These first three are created upon launch. The pool is never allowed to drop below three.

minimumInPool =“3”......前三个是在发布时创建的。游泳池永远不会低于三。

maximumIdleTimeBeforeRemoval="60"... If a connect is idle for an hour, then drop it and create a new one. Idle time probably means there is only the minimum of three in the pool.

maximumIdleTimeBeforeRemoval =“60”...如果连接空闲一小时,则将其删除并创建一个新连接。空闲时间可能意味着池中只有至少三个。

maximumInUseTimeBeforeRemoval="30"... If a given connection has been checked out for over 30 minutes, then something is probably wrong. Recall it, and kill the connection.

maximumInUseTimeBeforeRemoval =“30”...如果一个给定的连接已被检出超过30分钟,那么可能是错误的。回想一下,并终止连接。

maximumTimeBeforeRemoval="60"... Remove it if it is over 60 minutes old.

maximumTimeBeforeRemoval =“60”...如果超过60分钟,请将其删除。

maximumUsageBeforeRemoval="1000"... Remove it if it has been checked out over 1000 times.

maximumUsageBeforeRemoval =“1000”...如果已经超过1000次检出,则删除它。

monitorInterval="15"... Check the above parameters every 15 minutes.

monitorInterval =“15”...每15分钟检查一次上述参数。

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.

几年来,这对我很有帮助。我见过的最高的游泳池是一个野外偷看的151个连接。通常情况下,在大量使用期间,游泳池大约有十几个,并且在凌晨时间闲置至最少三个。

I used Oracle's JDBC thin drivers and connected to an Oracle database.

我使用了Oracle的JDBC瘦驱动程序并连接到Oracle数据库。

#2

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.

让您的连接池保持少量就绪连接,最小N和最大M. N可以根据客户端请求连接的峰值速度进行调整。如果就绪连接数量下降到零,则需要更大的N.如果数字一直很高(例如大于10),则需要更低的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.

当客户想要连接时,给他们一个准备好的连接(使其活动),然后立即打开一个新的,如果现在已经准备好少于N(但不要让客户等待这个完成,或者你'将失去汇集的优势)。这可确保始终至少有N个就绪连接。如果客户想要一个没有准备好,他们将不得不等待你创建一个新的。
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.

当客户端使用活动连接完成时,如果少于M个就绪连接,则将其返回到就绪状态。否则关闭它。这可以防止您拥有超过M个就绪连接。
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.

定期回收就绪连接以防止过时连接。如果有超过N个就绪连接,则只需关闭最旧的连接。否则关闭它并重新打开另一个。

This has the advantage of having enough ready and youthful connections available in your connection pool without overloading the server.

这样做的好处是可以在连接池中提供足够的就绪和年轻连接,而不会使服务器过载。

#3

Jakarta Commons DBCP already does all the stuff you listed:

Jakarta Commons DBCP已经完成了你列出的所有内容:

it creates connections as needed and manages them in a pool

它根据需要创建连接并在池中管理它们

it can close connections if they haven't been used for a certain period of time

如果它们在一段时间内没有被使用,它可以关闭连接

it can execute a query on a connection before handing it out, and if there is an error, the connection is thrown away and a new one is created. Connections can also be tested periodically while idle.

它可以在分发之前对连接执行查询,如果有错误,则抛弃连接并创建新连接。也可以在空闲时定期测试连接。

you can set a limit on the connections that will be created and also on the minimum number of connections to have ready. The limit of course depends a lot on your application.

您可以对将要创建的连接以及准备好的最小连接数设置限制。当然,限制很大程度上取决于您的应用。

I don't know how but DBCP knows when a connection is not being closed and closes it for you, throwing an exception so that you know what happened when you see your log.

我不知道DBCP知道连接何时没有关闭并为你关闭它,抛出一个异常,以便你知道当你看到你的日志时发生了什么。

DBCP has a timeout parameter which is very useful. If all the connections in the pool are being used, it will wait for that period of time for a connection to be returned to the pool and if there aren't any available when the limit is reached, you get an error.

DBCP有一个超时参数,非常有用。如果正在使用池中的所有连接,它将等待连接返回池的那段时间,如果达到限制时没有可用,则会出现错误。

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.

您可以通过播放最小连接数,要创建的最大连接数以及超时来微调池。较长的超时将允许您具有较低的连接限制,而较短的超时可能需要较大的数量。这在很大程度上取决于您的应用程序的功能以及它如何使用连接。

#4

I agree with matt b that we should not reinvent the wheel.

我同意亚光b,我们不应该重新发明*。

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.

然而,使用Commons DBCP可以根据这个和这个问题的答案来论证。那里有更好的替代方案,如c3po或proxool。

Or you may use rdbms dependent connection pooling mechanism.

或者您可以使用rdbms相关的连接池机制。

#5

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.

我使用SQL服务器作为我的后端,并使用缓存的组合来获得更好的性能。我的做法是只有在我真正需要它时才保持连接打开,并且不要将连接池连接起来,以便它们立即清理,我可以在SQL Activity监视器中看到究竟什么是活动的,什么不是。每个连接都会占用内存,因此在不需要它时可以将它保持在沉闷的咆哮状态。

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.

在我回答连接打开和关闭问题之前,让我说缓存非常重要。将对象从缓存中取出将为您节省大量时间。在我的一些asp.net应用程序中,当我在开发中使用缓存时,我发现我几乎无法测量延迟,而使用数据库调用可能需要15毫秒到45毫秒来完成调用,这甚至不考虑其他延迟因素或负荷。我使用的另一种方法是我的数据的一个很好的对象结构,这样我只有在发生变化时才进行数据库更新。我已经在我的对象上实现了一些方法o确保我尽可能少地使用IO。

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.

当连接打开时,我可以批量或一次性地执行此操作。这有点复杂,所以让我解释一下。
- one method that I’ve used is to pass my connection objects down the pipe so that all the objects can use one connection object. This results in one connection being open and closed instead of maybe 10 or more depending on your app. A good example of this is one of our purchasing models that takes advantage of the power of SQL server for gathering statistics and hashing out complicated ordering patterns. It doesn’t make sense to keep opening and closing the connection when you’re making 200K+ DB lookup or whatever the apps is for. The other part to this is that when I use object I try to bundle my updates to reduce the time that I keep the connection open. So doing a scope_identity on the insert call let’s me take care of both my insert and a lookup for the unique ID to add to my object before caching it. 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!

缓存您的数据!缓存您的数据!缓存您的数据!当您无法缓存然后缓存数据时,尽可能少地访问数据库!

#6

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.

如果您在Java世界中,则可以使用Commons DBCP。

#1