数据库连接池的大小你真的设置对了吗

左手的ㄟ右手 2021-11-01 08:08 468阅读 0赞

#### 问题 ####

真实环境prod中的系统，我们该如何设置数据库连接池的大小呢？

一些所谓的开发老鸟可能会肯定的告诉你:没关系，尽量设置的大些，比如设置成200,这样数据库性能会高些，吞吐量也会大些！

对于菜鸟的你，也许认为好像似乎说的有道理，真的是这样吗？接下来的分析，也许颠覆你的认知哦！

![aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85MDMzMDg1LWUxNzI0MzBlZGRjMDEzZmUuanBlZw][]

#### 数据库连接池的设置分析——测试数据 ####

<table> 
 <thead> 
  <tr> 
   <th>条件</th> 
   <th>线程池设置大小</th> 
   <th>每个请求在连接池队列里平均等待时间</th> 
   <th>执行SQL耗时</th> 
   <th>总耗时</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Oracle、9600并发线程、每次数据库操作sleep 550ms</td> 
   <td>2048</td> 
   <td>33ms</td> 
   <td>77ms</td> 
   <td>110ms</td> 
  </tr> 
  <tr> 
   <td>Oracle、9600并发线程、每次数据库操作sleep 550ms</td> 
   <td>1024</td> 
   <td>33ms</td> 
   <td>56ms</td> 
   <td>89ms</td> 
  </tr> 
  <tr> 
   <td>Oracle、9600并发线程、每次数据库操作sleep 550ms</td> 
   <td>96</td> 
   <td>1ms</td> 
   <td>2ms</td> 
   <td>3ms</td> 
  </tr> 
 </tbody> 
</table>

该测试数据是Oracle 性能小组测试&发布的，视频内容在[https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing][https_github.com_brettwooldridge_HikariCP_wiki_About-Pool-Sizing]  
我把视频描述的实验条件和结果汇总成此表了。

**连接池设置数量越小、总耗时越少？**

测试结果:只是将数据库连接池的大小降低了一下，这样，就能把之前平均100ms响应时间缩短到了3ms。吞吐量指数级上升啊!!!神奇!!!

为啥有这种效果呢?  
看到结果，感到震惊。那我们坐下来想想：  
为啥Nginx内部仅仅使用了4个线程，其性能就大大超越了100个进程的Apache HTTPD呢?

要能弄懂这么难的问题，我们得补习下基础：

**程序、进程、线程的区别?**

程序(Program) 是指令和数据的有序集合，其本身没有任何运行的含义，是一个静态的概念——它是在磁盘上

进程(Process) 是程序在处理机上的一次执行过程，它是一个动态的概念。——是在内存中

线程(Thread) 是程序中一个单一的顺序控制流程。进程内一个相对独立的、可调度的执行单元，是系统独立调度和分派CPU的基本单位，指运行中的程序的调度单位。

进程：作为资源分配的单元  
线程：作为调度和执行的单位

这里有个小结论：在一核 CPU 的机器上，顺序执行A和B永远比通过时间分片切换“同时”执行A和B要快。

###### 时间片的概念是什么? ######

时间片即CPU分配给各个程序的时间，每个线程被分配一个时间段， 称作它的时间片，即该进程允许运行的时间，使各个程序从表面上看是同时进行的。

如果在时间片结束时进程还在运行，则CPU将被剥夺并分配给另一个进程。如果进程在时间片结束前阻塞或结束，则CPU当即进行切换。而不会造成CPU资源浪费。

在宏观上：我们可以同时打开多个应用程序，每个程序并行不悖，同时运行。

但在微观上：由于只有一个CPU,一次只能处理程序运行的一部分，如何处理公平，一种方法就是引入时间片，每个程序轮流执行。

结论：当线程数大于CPU的核心数，需要通过算法及时间分片切换该哪个线程获得CPU的使用权，这个是需要耗时的。因为这里涉及到上下文切换耗费的额外的性能。

如果计算机是4核，机器值运行四个线程，处理速度相当快。但这只是理想状态。

#### 限制因素 ####

实际上，限制线程发挥的，不止有CPU，还有磁盘IO和网络IO。  
下面分别来看CPU 磁盘IO 网络IO

###### 只考虑CPU ######

结论1：如果只考虑CPU,不考虑磁盘I/O和网络I/O，在一个8核的服务器上，数据库连接数/线程数设置为8能够提供最优的性能，如果再增加连接数,反而会因为上下文切换导致性能下降。(这是理想情况)  
数据库连接数/线程数= CPU的核心数

###### 考虑磁盘I/O ######

耗时因素：寻址的耗时+旋转耗时；

当你的线程处理的是IO密集型业务时，便可以让 `线程/连接数` 设置的比CPU核心数大一些，这样就能够在同样的时间内，完成更多的工作，提升吞吐量。因为磁盘寻址的时候，CPU是空闲的，可以让CPU做点其他的。

**问题:设置成多大合适呢?**  
取决于磁盘：SSD固态硬盘、普通机械硬盘  
SSD固态硬盘，它不需要寻址，也不需要旋转碟片!是不是可以设置更大些?

结论正好相反! 无需寻址和没有旋回耗时的确意味着更少的阻塞，所以更少的线程( 更接近于CPU核心数)会发挥出更高的性能。只有当阻塞密集时，更多的线程数才能发挥出更好的性能。

###### 考虑网络IO ######

网络是我们应用程序最难把控了，因为网络拥塞、超时 由不得我们控制；通常网络瓶颈业务我们系统优化放在最后的。

![aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85MDMzMDg1LWFhMjk0OWIxNTNmOTk4MTQucG5n][]

如图，随着客户端连接数的增加，每秒传输速度影响还是很大的。  
该图是 PostgreSQL 的基准性能测试数据，从图中我们可以看到，TPS 在连接数达到 50 时开始变缓。回过头来想下，在上面 Oracle 的性能测试视频中，测试人员们将连接数从 2048 降到了 96，实际上 96 还是太高了，除非你的服务器 CPU 核心数有 16 或 32。

#### 数据库连接池计算公式 ####

下面公式由PostgreSQL 提供，不过底层原理是不变的，它适用于市面上绝大部分数据库产品。还有，你应该模拟预期的访问量，并通过下面的公式先设置一个偏合理的值，然后在实际的测试中，通过微调，来寻找最合适的连接数大小。

`连接数=((CPU核心数*2)+有效磁盘数)`

注意：核心数不应包含超线程(hyper thread)，即使打开了超线程也是如此，如果热点数据全被缓存了，那么有效磁盘数实际是0，随着缓存命中率的下降，有效磁盘数也逐渐趋近于实际的磁盘数。另外需要注意，这一公式作用于SSD的效果如何，尚未明了。

**举个栗子**  
按照这个公式，如果说你的服务器CPU是4核i7的，连接池大小应该为((4\*2)+1)=9。  
取个整，我们就设置为10吧。你这个行不行啊? 10 也太小了吧！  
测试结果表明：这个设置能轻松支撑3000用户以6000 TPS的速率并发执行简单查询的场景。你还可以将连接池大小超过10,那时，你会看到响应时长开始增加，TPS开始下降。

结论：你需要的是一个小连接池，和一个等待连接的线程队列！  
补充：连接池中的连接数量大小应该设置成：数据库能够有效同时进行的查询任务数(通常情况下来说不会高于2\*CPU核心数)。

###### 补充 ######

(公式并不是适用所有场景的，具体还需看场景)

实际上，连接池的大小的设置还是要结合实际的业务场景来说事。  
比如说，你的系统同时混合了`长事务`和`短事务`，这时，根据上面的公式来计算就很难办了。正确的做法应该是`创建两个连接池`，一个服务于长事务，一个服务于"实时"查询，也就是短事务。  
还有一种情况，比方说一个系统执行一个任务队列，业务上要求同一时间内只允许执行一定数量的任务，这时，我们就应该让并发任务数去适配连接池连接数，而不是连接数大小去适配并发任务数。  
记住：让业务并发数，去适配数据库连接数。

#### 回顾数据库连接池的实现原理 ####

JDBC访问数据库的问题总结  
(1)数据库连接资源很稀缺，每次创建和关闭都非常耗时。  
(2)每次数据库连接使用完后要及时关闭回收，如果程序运行中间出现异常，导致连接没有及时关闭，将有可能导致内存泄漏，服务器崩溃。  
(3)这种方式，不能控制被创建的连接对象的数量，也就是说，你可以创建无数个连接对象。没有谁去约束限制，对象太多，导致内存泄漏，服务器崩溃。

###### 后记 ######

到这里，是否颠覆了你的认知呢？

今天为什么要探究数据库连接池大小呢？是因为本人所在的公司(某大厂)，负责的某服务数据库连接池最大配置的是20，但系统平时限流设置的是100并发/s，当时看到时愣住了~  
后来查看了另外一个系统，日调用量8k万，分库分表了，QPS 900多，数据库连接池最大配置是64。  
怎么样，有点惊讶吧。  
还有同学测试的：“我试过600连接数和200连接数，压测性能相差不大”。  
因此数据库连接池大小，绝非越大越好，也不是max pool size一定要跟随系统并发量的增加而增加。我们需要做的是让业务并发数，去适配数据库连接数；让我们的数据库能够有效的同时进行并发执行、处于高效处理的状态。

[aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85MDMzMDg1LWUxNzI0MzBlZGRjMDEzZmUuanBlZw]: /images/20211101/ac878f9f021f4551976840ffe50a7f5f.png
[https_github.com_brettwooldridge_HikariCP_wiki_About-Pool-Sizing]: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
[aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85MDMzMDg1LWFhMjk0OWIxNTNmOTk4MTQucG5n]: /images/20211101/31fb68c9f84042f3ab6f78efe1a53356.png