PG空闲连接对性能的影响
该系列的第一篇为：PG空闲连接的资源消耗：https://amazonaws-china.com/cn/blogs/database/resources-consumed-by-idle-postgresql-connections/讨论PG如何管理连接以及空闲连接如何消耗内存和CPU。本文讨论空闲连接对PG性能的影响。
事务率影响
PG获取数据的时候，首先看请求页在没在共享内存。如果共享内存没有请求页，则从操作系统缓存取，如果也没有，则需要请求磁盘上的数据页。共享内存最快，操作系统缓存次之，磁盘最慢。随着PG连接的增长，操作系统缓存的可用内存就会减小，从而从操作系统缓存中移除数据页。下次再进行数据页查询时就会从磁盘上请求，因此性能变得更慢。
如果PG实例的空闲内存处于低水位，就会使用swap。这也是位于磁盘上，因此也很慢。使用swap空间可帮助释放一些内存，但是如果swapped 页再次被OS请求时，会被读回，导致IO的增加。更多信息请查看swap管理：https://www.kernel.org/doc/gorman/html/understand/understand014.html
可用内存对性能的影响取决于工作负载、数据集、总共的可用内存。如果数据集比总可用内存小，空闲内存的减少不会有明显影响，若数据集比总可用内存还大，就会产生巨大影响。
性能测试
下面小节显示了通过pgbench进行的性能测试。测试中Amazon RDS for PG实例为db.m5.large，2vCPU，8GB内存。1个EBS的IO为3000IOPS。
每个测试都有两个阶段，第一阶段pgbench执行1个小时，没有其他工作负载。这个提供了一个基准事务率。
第二个阶段，再次执行pgbench前打开1000个连接，每个连接从information_schema表获取一行数据。下面是步骤：
1）打开一个连接
2）获取所有表名及information_schema视图：
SELECT table_schema||'.'||table_name as relname from information_schema.tables WHERE table_schema='information_schema';
3）循环执行select：
SELECT * FROM information_schema.columns LIMIT 1;
4）对于1000个连接重复以上步骤
5）事务提交后不进行断开，保持空闲状态
重启实例后，内存中没有缓存任何数据页。第一次执行pgbench会加载请求的数据页到内存，随后再次执行pgbench，cache中的数据页可以重用，此时不再需要从磁盘加载。
为了最小化页缓存的影响，在执行测试案例前执行一个初始步骤。下图显示了打开1000个连接时，实例内存时如何从4.88GB下降到90MB的。
正如前系列介绍，虽然连接是空闲的，他们也会消耗内存和CPU资源。这个结果显示空闲连接对性能的影响。
事务率测试1：标准pgbench
第一个测试中，使用标准配置执行100个客户端连接，结果：
代码语言：javascript复制transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: simple
number of clients: 100
number of threads: 2
duration: 600 s
number of transactions actually processed: 749572
latency average = 80.058 ms
tps = 1249.096708 (including connections establishing)
tps = 1249.116996 (excluding connections establishing)1000个连接下，结果：
代码语言：javascript复制transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: simple
number of clients: 100
number of threads: 2
duration: 600 s
number of transactions actually processed: 684434
latency average = 87.686 ms
tps = 1140.430155 (including connections establishing)
tps = 1140.449899 (excluding connections establishing)结果表明，TPS从1249下降到1140，有8.7%的下降。
事务率测试2：select-only
因为空闲连接消耗了内存减小了页缓存可用内存，所以这些空闲连接对读的影响尤为明显。为测试这点，使用-S配置运行pgbench，使用内置的select only脚本。结果：
代码语言：javascript复制transaction type: <builtin: select only>
scaling factor: 1000
query mode: simple
number of clients: 100
number of threads: 2
duration: 600 s
number of transactions actually processed: 1181937
latency average = 50.778 ms
tps = 1969.344251 (including connections establishing)
tps = 1969.377751 (excluding connections establishing)1000个空闲连接下：
代码语言：javascript复制transaction type: <builtin: select only>
scaling factor: 1000
query mode: simple
number of clients: 100
number of threads: 2
duration: 600 s
number of transactions actually processed: 966656
latency average = 62.095 ms
tps = 1610.440842 (including connections establishing)
tps = 1610.470585 (excluding connections establishing)TPS从1969下降到1610，有18.2%的下降。
事务率测试3：custom pgbench
执行脚本：
代码语言：javascript复制set nbranches :scale
set naccounts 100000 * :scale
set aid random(1, :naccounts)
set bid random(1, :nbranches)
BEGIN;
SELECT * FROM pgbench_accounts WHERE aid >= :aid AND aid < (:aid 5000) AND bid=:bid LIMIT 1;
END;脚本中每个事物从pgbench_accounts表读取5000行数据，然后仅返回1条。结果：
代码语言：javascript复制transaction type: pgbench_script.sql
scaling factor: 5000
query mode: simple
number of clients: 100
number of threads: 2
duration: 600 s
number of transactions actually processed: 227484
latency average = 264.140 ms
tps = 378.586790 (including connections establishing)
tps = 378.592772 (excluding connections establishing)1000个空闲连接下，结果为：
代码语言：javascript复制transaction type: pgbench_script.sql
scaling factor: 5000
query mode: simple
number of clients: 100
number of threads: 2
duration: 600 s
number of transactions actually processed: 124114
latency average = 484.485 ms
tps = 206.404854 (including connections establishing)
tps = 206.507645 (excluding connections establishing)结果显示TPS从378下降到206，有46%的下降。通过Amazon RDS Performance Insights可以看到引擎wait events详细信息。下面两个图显示了DataFileRead等待事件中耗费时间最多的。即等待从表数据文件中读取数据。
下图显示了Amazon CloudWatch指标中的读负载：
第一次执行时读为87MB/s，第二次1000个连接下，增长到117MB/s。空闲连接消耗了操作系统内存，导致OS cache变小。因此需要从磁盘读取更多数据页，从而导致性能的衰减。
连接池
连接池可帮助减小数据库连接带来的影响。可以使用pgbouncer或者Amazon RDS Proxy。这些连接池可以限制连接数量。
Pgbouncer
Pgbouncer是轻量级的连接池组件，支持下面三种模式：
Session mode：每个应用连接绑定到一个数据库连接上。如果连接处于空闲状态，pgbouncer不能将它给其他应用连接重用。
Transaction mode：一个事务完成后，该连接就可以重用
Statement mode：一个SQL语句完成后就可以将该连接给其他客户端重用。
大多数应用中，使用transaction mode可以提供最优结果。下面测试pgbouncer配置了最大5000客户端连接，但我们的测试中最大连接设置为200.pgbench运行在pgbouncer pool中。结果：
代码语言：javascript复制transaction type: pgbench_script.sql
scaling factor: 5000
query mode: simple
number of clients: 100
number of threads: 2
duration: 600 s
number of transactions actually processed: 227064
latency average = 264.600 ms
tps = 377.928241 (including connections establishing)
tps = 377.928476 (excluding connections establishing)运行过程中，可以查看连接状态：
代码语言：javascript复制pgbouncer=# show pools;
-[ RECORD 1 ]-----------
database | pgbench
user | postgres
cl_active | 100
cl_waiting | 0
sv_active | 100
sv_idle | 0
sv_used | 0
sv_tested | 0
sv_login | 0
maxwait | 0
maxwait_us | 0
pool_mode | transactionPool状态显示有100个客户端连接（cl_active）从而有100个活跃server连接（sv_active）。第二次执行，打开1000个连接，并处于空闲状态。Pooler不需要维护任何服务端连接：
代码语言：javascript复制pgbouncer=# show pools;
-[ RECORD 1 ]-----------
database | pgbench
user | postgres
cl_active | 1000
cl_waiting | 0
sv_active | 0
sv_idle | 1
sv_used | 0
sv_tested | 0
sv_login | 0
maxwait | 0
maxwait_us | 0
pool_mode | transaction1000个空闲连接下，执行pgbench：
代码语言：javascript复制transaction type: pgbench_script.sql
scaling factor: 5000
query mode: simple
number of clients: 100
number of threads: 2
duration: 600 s
number of transactions actually processed: 226827
latency average = 264.935 ms
tps = 377.451418 (including connections establishing)
tps = 377.451655 (excluding connections establishing)下面显示使用连接池是，性能没有影响：
代码语言：javascript复制pgbouncer=# show pools;
-[ RECORD 1 ]-----------
database | pgbench
user | postgres
cl_active | 1100
cl_waiting | 0
sv_active | 100
sv_idle | 0
sv_used | 0
sv_tested | 0
sv_login | 0
maxwait | 0
maxwait_us | 0
pool_mode | transaction总共有1100个客户端连接，但是仅有100个服务端连接活跃。
该测试，RDS实例有2个CPU，因此100个进程并行执行，导致大量上下文切换，从而造成性能衰减。Pgbouncer配置最多20个数据连接下性能：
代码语言：javascript复制transaction type: pgbench_script.sql
scaling factor: 5000
query mode: simple
number of clients: 100
number of threads: 2
duration: 600 s
number of transactions actually processed: 256267
latency average = 234.286 ms
tps = 426.828543 (including connections establishing)
tps = 426.828801 (excluding connections establishing)得到了个更高的TPS，状态：
代码语言：javascript复制pgbouncer=# show pools;
-[ RECORD 1 ]-----------
database | pgbench
user | postgres
cl_active | 20
cl_waiting | 80
sv_active | 20
sv_idle | 0
sv_used | 0
sv_tested | 0
sv_login | 0
maxwait | 0
maxwait_us | 125884
pool_mode | transaction只有20个客户端连接活跃。剩下的80个连接等待被分配。更多的连接并不意味着更多的吞吐量。较少的客户端连接有助于上下文切换和资源争用，从而提高总体性能。
总结
连接数多并不意味着高吞吐。增加连接数，会增加上下文切换和资源争用，从而影响性能。
PG连接即使空闲状态，也会消耗资源。空闲连接不会影响性能的假设不正确。
应用设计的时候需要考虑不要有太多连接。
原文
https://amazonaws-china.com/cn/blogs/database/performance-impact-of-idle-postgresql-connections/
