灵魂一问：数据库连接池到底该怎么配？

来自公众号：新世界杂货铺



好家伙，我直接好家伙！GitHub不愧是全球最大的同性交友网站，资源丰富且质量高！

连接池的配置应该按照什么原则来？这个问题在笔者心中疑惑良久，直到在GitHub上发现了About Pool Sizing这篇文章。看完之后一扫笔者心中阴霾，神清气爽。妈妈再也不用担心我在项目中瞎配连接池啦！

以下内容为笔者根据原文翻译总结所得。



原文提到开发人员经常会将连接池配置错误，而要想正确配置连接池需要理解一些原则，即使这些原则可能违反人类直觉。

1万并发用户访问

假设你有一个需要每秒处理2万事务的网站，你的连接池应该配置多大？令人惊讶的是，这个问题不应该是连接池配置多大而是应该配置多小。



下面这个视频是Oracle Real-World Performance组发布的（笔者建议各位读者架个梯子亲自看看）：

https://www.youtube.com/watch?v=xNDnVOCdvQ0



下面笔者对视频中的内容进行简单的概括。



注：视频中是对Oracle数据库进行测试，笔者在开发/线上环境均使用Mysql数据库，但触类旁通，该测试结果仍具有很高的参考价值。



压测初始配置如下：



笔者随机截取视频中第一次压测结果如下：





Sessions达到2048时，Queue-ms（每个请求在队列中的等待时间）为30ms，Run-ms（SQL执行时间）为71ms，同时还有很多buffer busy waits。



接下来其他条件不动，仅将初始线程池大小设置为1024得到如下结果：





Sessions达到1024时，Queue-ms（38ms）和第一次相比可以认为几乎没有变化，Run-ms（30ms）和第一次相比明显减少，buffer busy waits和第一次相比也明显减少。



最后，将线程池大小设置为96，其他条件不变得到如下结果：





此时，Queue-ms和Run-ms耗时极短并且看不到任何的buffer busy waits。好家伙，原作者对此时的结果直接打了一个✅。



最后，一起对比一下三次压测结果的吞吐量：





图中上半部分为耗时因子，下半部分为吞吐量。红框、蓝框和黄框分别代表着链接池大小为2048、1024和96的吞吐量。由折线图知当连接池大小为96时吞吐量明显上升。



没有做任何其他调整，仅减小连接池大小就可将应用程序的性能提升近50倍！

But why?



为什么nginx只用4个线程发挥出的性能就大大超越了100个进程的Apache Web服务器？回想一下计算机科学的基础知识，答案其实是很明显的。



即使是单核的计算机也可以“同时”支持数十个或数百个线程。但是我们都应该知道，这仅仅是操作系统通过时间分片交替执行的一个小把戏，实际上，单个内核一次只能执行一个线程，然后操作系统切换上下文执行另一个线程的代码，依此类推。给定一颗CPU核心，其顺序执行A和*B*永远比通过时间分片“同时”执行A和B要快，这是一条计算机科学的基本法则。一旦线程的数量超过了CPU核心的数量，再增加线程数系统就只会更慢，而不是更快。



笔者认为上述A,B在没有I/O阻塞时，顺序执行才比同时执行更快。

有限的资源



当我们排查数据库的性能瓶颈时，它们可以概括为三个类别：CPU，磁盘，网络。内存和磁盘、网络相比，带宽高出好几个数量级故忽略此排查方向。



如果我们忽略磁盘和网络，那就更加简单了。在一个8核的服务器上，将连接数设置为8将提供最佳性能，再增加连接数就会因上下文切换的损耗导致性能下降。但是我们不能忽视磁盘和网络。



数据库通常将数据存储在磁盘上，对于老式的机械硬盘存在寻址时间成本和旋转时间成本。在这段时间内（ I/O等待），连接/查询/线程被阻塞以等待磁盘，此时操作系统控制CPU执行其他线程代码以更好地利用CPU资源。所以，由于线程在I/O上阻塞，我们可以让线程/连接数比CPU核心多一些，这样能够在同样的时间内完成更多的工作。



那连接数具体应该设置多少呢？这取决于磁盘。因为新型的SSD不需要寻址和旋转开销。此时不要想当然地认为“SSD速度更快，所以我们应该有更多的线程数”，恰好相反，更快意味着更少的阻塞，因此越接近核心数量的线程将会发挥最优的性能。只有当阻塞创造了更多的执行机会时，更多的线程数才能发挥出更好的性能。



网络类似于磁盘。当发送/接收缓冲区填满并停止时，通过以太网接口在线路写入数据也会导致阻塞。 10G宽带延迟小于1G宽带，而1G宽带的延迟又小于100M宽带。就阻塞而言，网络通常是放在第三位考虑的，但是仍然有人会在性能计算中忽略它。



说实话，下面这张图笔者研究了半天也不明白它的意义，不过既然原文贴出来了，笔者只好照搬不误。





在上述PostgreSQL基准测试中可以看到，TPS速率在大约50个连接处开始趋于平稳。 在上面的Oracle视频中，将连接数从2048下调至96。但实际上，96也很高了，除非服务器使用的是16或32核的处理器。

计算公式

虽然下面的公式是PostgreSQL提供的，不过我们认为该公式可以广泛地适用于不同的数据库。你可以利用该公式计算一个初始值，以此初始值为基准模拟负载并调整连接数大小从而找到一个合适的连接数。



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



在多年的基准测试中，保持最优吞吐量的活跃连接数都是接近该公式的计算结果。 核心数不应包含超线程，即使启用了超线程也是如此。如果活跃数据全部被缓存了，那么有效磁盘数是0，随着缓存命中率的下降，有效磁盘数将逐渐趋近于实际的磁盘数。

特别注意：目前为止，还没有任何关于该公式作用于SSD的效果分析。



这个公式意味着什么？假如你有一个服务器，该服务器具有一块磁盘和一个4核的i7处理器，那么此服务器的连接池大小应该是：9 =（（4 * 2）+1），取个整数的话就是10。是不是看起来很小？但是请尝试一下，我们敢打赌在这样的设置下，它可以轻松搞定3000个前端用户以6000TPS的速率执行简单查询。如果你增加连接池的大小并运行负载测试，你会发现前端响应时间增加的同时TPS速率开始下降。

公理：你需要一个小的充满了等待连接的线程队列

如果你有10000个用户，设置一个10000的连接池基本等于疯了，1000仍然很恐怖，即使是100也太多了。你最多需要一个十几个连接的小型池，其余的业务线程则被阻塞直到有可用连接。连接池中的连接数量应该等于你的数据库能够有效同时进行的查询任务数（通常不会高于2*CPU核心数）。



我们经常见到一些小规模的web应用，应付着大约十来个的并发用户，却使用着一个100连接数的连接池。不要过度配置数据库。



笔者想到一个案例：曾经有一个读者将HTTP连接池的最大空闲连接设置为300，后来发现经常出问题，最后在笔者的劝说下使用了默认配置之后就再也没找过我。

避免死锁的连接池大小



单个线程同时需要多个连接可能会造成死锁。这在很大程度上是一个业务上的问题，该问题可以通过增加连接池大小来解决。但是在增加连接池大小之前，我们还是强烈建议您首先检查在业务方面可以做什么。

为避免死锁，计算连接池有一个简单的计算公式：



连接数 = 最大线程数 （单线程需要的最大连接数 - 1） + 1



假如你有3个线程，每个线程需要4个连接来执行某些任务。确保永不发生死锁所需的池大小为：

3 x (4-1) + 1 = 10

👉这不一定是最佳的连接池大小，而是避免死锁所需的最小的连接池大小。



笔者根据自己的开发经验在此特意提醒：

1. Go中单个协程尽量不要同时使用多个连接进行操作。

2. 执行事务期间不要通过非当前事务连接获取数据， 请使用当前事务连接直接获取数据（如果在执行事务期间使用非当前事务连接获取数据相当于同时使用两个连接）。

忠告

连接池的大小最终与系统特性有关。



例如，一个混合了长时事务和短事务的系统是非常难以使用连接池进行调优的。通常做法是， 使用两个连接池，一个用于长时事务，一个用于实时查询。



在主要运行长时事务的系统中，连接池大小通常存在外部约束。例如，一个任务执行队列只允许固定数量的任务同时运行。此时，任务队列的大小应该去适应连接池的大小，而不是反过来。



最后，衷心希望笔者的翻译能够对各位读者有一定的帮助。