Java 连接池中的最佳连接数

Optimal number of connections in connection pool

提问by Silent Warrior

Currently we are using 4 cpu windows box with 8gb RAM with MySQL 5.x installed on same box. We are using Weblogic application server for our application. We are targeting for 200 concurrent users for our application (Obviously not for same module/screen). So what is optimal number of connections should we configured in connection pool (min and max number) (We are using weblogic AS' connection pooling mechanism) ?

目前我们正在使用 4 cpu windows box 和 8gb RAM，MySQL 5.x 安装在同一个盒子上。我们正在为我们的应用程序使用 Weblogic 应用程序服务器。我们的应用程序的目标是 200 个并发用户（显然不是同一模块/屏幕）。那么我们应该在连接池中配置的最佳连接数是多少（最小和最大数量）（我们使用的是 weblogic AS 的连接池机制）？

采纳答案by Slava Imeshev

There is a very simple answer to this question:

这个问题有一个非常简单的答案：

The number of connections in the connection pool should be equal the number of the exec threads configured in WebLogic.

连接池中的连接数应等于 WebLogic 中配置的 exec 线程数。

The rationale is very simple: If the number of the connections is less than the number of threads, some of the thread maybe waiting for a connection thus making the connection pool a bottleneck. So, it should be equal at least the number the exec threads (thread pool size).

原理很简单：如果连接数小于线程数，则某些线程可能会等待连接，从而使连接池成为瓶颈。因此，它应该至少等于 exec 线程的数量（线程池大小）。

回答by Greg D

You should profile the different expected workflows to find out. Ideally, your connection pool will also dynamically adjust the number of live connections based on recent usage, as it's pretty common for load to be a function of the current time of day in your target geographical area.

您应该分析不同的预期工作流程以找出答案。理想情况下，您的连接池还将根据最近的使用情况动态调整实时连接的数量，因为负载是目标地理区域中当前时间的函数是很常见的。

Start with a small number and try to reach a reasonable number of concurrent users, then crank it up. I think it's likely that you'll find that your connection pooling mechanism is not nearly as instrumental in your scalability as the rest of the software.

从少量开始，尝试达到合理数量的并发用户，然后再增加。我认为您很可能会发现您的连接池机制在您的可扩展性方面并不像软件的其余部分那样重要。

回答by matt b

This is something that needs to be tested and determined on an individual basis - it's pretty much impossible to give an accurate answer for your circumstanceswithout intimately being familiar with them.

这是需要根据个人情况进行测试和确定的事情 - 如果不密切熟悉情况，几乎不可能针对您的情况给出准确的答案。

回答by Steve B.

It's difficult to get hard data for this. It's also dependent on a number of factors you don't mention -

很难为此获得硬数据。这还取决于您未提及的许多因素-

• 200 concurrent users, but how much of their activity will generate database queries? 10 queries per page load? 1 query just on login? etc. etc.

• Size of the queries and the db obviously. Some queries run in milliseconds, some in minutes.

• 200 个并发用户，但他们的活动中有多少会生成数据库查询？每页加载 10 个查询？1 只在登录时查询？等等等等

• 查询和数据库的大小很明显。有些查询以毫秒为单位运行，有些以分钟为单位。

You can monitor mysql to watch the current active queries with "show processlist". This could give you a better sense of how much activity is actually going on in the db under peak load.

您可以使用“show processlist”监视 mysql 以查看当前的活动查询。这可以让您更好地了解峰值负载下数据库中实际发生的活动量。

回答by Thorbj?rn Ravn Andersen

The connection pool should be able to grow and shink based on actual needs. Log the numbers needed to do analysis on the running system, either through logging statements or through JMX surveillance. Consider setting up alerts for scenarios like "peak detected: more than X new entries had to be allocated in Y seconds", "connection was out of pool for more than X seconds" which will allow you to give attention to performance issues before they get real problems.

连接池应该可以根据实际需要增减。通过日志语句或通过 JMX 监视记录对正在运行的系统进行分析所需的数字。考虑为“检测到峰值：必须在 Y 秒内分配 X 个以上的新条目”、“连接超出池超过 X 秒”等场景设置警报，这将使您能够在性能问题出现之前关注它们真正的问题。

回答by AngerClown

Did you really mean 200 concurrentusers or just 200 logged in users? In most cases, a browser user is not going to be able to do more than 1 page request per second. So, 200 users translates into 200 transactions per second. That is a pretty high number for most applications.

您的意思是 200 个并发用户还是 200 个登录用户？在大多数情况下，浏览器用户每秒最多只能执行 1 个页面请求。因此，200 个用户每秒转化为 200 个事务。对于大多数应用程序来说，这是一个相当高的数字。

Regardless, as an example, let's go with 200 transactions per second. Say each front end (browser) tx takes 0.5 seconds to complete and of the 0.5 seconds, 0.25 are spent in the database. So, you would need 0.5 * 200, or 100 connections in the WebLogic thead pool and 0.25 * 200 = 50 connections in the DB connection pool.

无论如何，作为一个例子，让我们以每秒 200 个事务为例。假设每个前端（浏览器）tx 需要 0.5 秒才能完成，而在这 0.5 秒中，数据库中花费了 0.25 秒。因此，您需要 0.5 * 200，即 WebLogic 主池中的 100 个连接，而 DB 连接池中需要 0.25 * 200 = 50 个连接。

To be safe, I would set the max thread pool sizes to at least 25% larger than you expect to allow for spikes in load. The minimums can be a small fraction of the max, but the tradeoff is that it could take longer for some users because a new connection would have to be created. In this case, 50 - 100 connections is not that many for a DB so that's probably a good starting number.

为安全起见，我会将最大线程池大小设置为至少比您预期的大 25%，以允许负载峰值。最小值可能是最大值的一小部分，但权衡是某些用户可能需要更长的时间，因为必须创建新连接。在这种情况下，50 - 100 个连接对于数据库来说并不多，因此这可能是一个很好的起始数字。

Note, that to figure out what your average transaction response times are, along with your average DB query time, you are going to have to do a performance test because your times at load are probably not going to be the times you see with a single user.

请注意，要确定您的平均事务响应时间是多少，以及您的平均数据库查询时间，您将不得不进行性能测试，因为您的负载时间可能不会是您看到的时间用户。

回答by Vlad Mihalcea

Sizing connection pool is not a trivial thing to do. You basically need:

调整连接池的大小并不是一件容易的事。你基本上需要：

• metrics to investigate the connection usage
• failover mechanisms for when there is no connection available

• 用于调查连接使用情况的指标
• 没有可用连接时的故障转移机制

FlexyPoolaims to aid you figuring our the right connection pool size.

FlexyPool旨在帮助您确定我们正确的连接池大小。

You might check the following articles:

您可以查看以下文章：

• FlexyPool, reactive connection pooling

• Professional Connection Pool Sizing

• The simple scalability equation

• FlexyPool，反应式连接池

• 专业的连接池尺寸

• 简单的可扩展性方程

回答by Farsheed

Based on my experience on high transaction financial systems, if you want to handle for example 1K requests per seconds, and you have 32CPU's, You need to have 1000/32open connection polls to your database.

根据我在高交易金融系统上的经验，如果您想处理例如每秒 1K 的请求，并且您有32CPU，则需要对1000/32数据库进行开放连接轮询。

Here is my formula:

这是我的公式：

RPS / CPU_COUNT

RPS / CPU_COUNT

If most cases, your database engine will be able to handle your requests even in much lower numbers, but your connections will be in waiting mode if the number is low.

在大多数情况下，即使数量少得多，您的数据库引擎也能够处理您的请求，但如果数量少，您的连接将处于等待模式。

I think it's pretty important to mention that your database should be able to handle those transactions (based on your disk speed, database configuration and server power).

我认为值得一提的是您的数据库应该能够处理这些事务（基于您的磁盘速度、数据库配置和服务器能力）。

Good luck.

祝你好运。