1. 前言

中启乘数科技是一家专业的PostgreSQL和Greenplum数据库服务提供商,专注于数据库极致的性能,当前PostgreSQL 14beta1已经发布,其中一个激动人心的特性就是当数据库有海量连接时,其它连接的性能基本不下降。下面我们通过实际的测试来说明。

2. 测试环境和方法

本着不详细介绍测试方法和测试环境的测试都是耍流氓的精神,我们把测试环境和方法详细介绍如下:

我们准备在一台物理机器上准备两套环境,一套是PostgreSQL 13.3,另一套是PostgreSQL 14beta1。
物理机器的硬件为2路服务器,CPU为:Intel(R) Xeon(R) Silver 4210R CPU @ 2.40GHz,内存为256GB。

我们使用initdb命令初始化数据库,然后修改最大连接数:

  1. max_connections = 11000

其它参数都不修改,默认shared_buffer为128MB。

然后用pgbench造2千万行数据:

  1. pgbench -i -s 200

PostgreSQL 14与PostgreSQL 13在造数据上花的时间差不多,稍微快一点:

PostgreSQL 14:

  1. [pg14@pg01 ~]$ time pgbench -i -s 200
  2. dropping old tables...
  3. NOTICE: table "pgbench_accounts" does not exist, skipping
  4. ...
  5. 20000000 of 20000000 tuples (100%) done (elapsed 15.45 s, remaining 0.00 s)
  6. vacuuming...
  7. creating primary keys...
  8. done in 23.67 s (drop tables 0.00 s, create tables 0.06 s, client-side generate 15.54 s, vacuum 2.70 s, primary keys 5.37 s).
  9. real 0m23.674s
  10. user 0m5.027s
  11. sys 0m0.138s

PostgreSQL 13:

  1. [pg13@pg01 ~]$ time pgbench -i -s 200
  2. dropping old tables...
  3. NOTICE: table "pgbench_accounts" does not exist, skipping
  4. ...
  5. ...
  6. ...
  7. 20000000 of 20000000 tuples (100%) done (elapsed 17.23 s, remaining 0.00 s)
  8. vacuuming...
  9. creating primary keys...
  10. done in 26.41 s (drop tables 0.00 s, create tables 0.06 s, client-side generate 17.32 s, vacuum 2.69 s, primary keys 6.34 s).
  11. real 0m26.409s
  12. user 0m5.022s
  13. sys 0m0.117s

测试方法的思路是我们给数据库建9000个执行pg_sleep(2000)的空闲连接,然后在用pgbench做只读压力测试。

建9000个pg_sleep(2000)连接的方法为:

我们建一个sleep.sql的文件,内容如下:

  1. select pg_sleep(2000);

然后执行下面的命令

  1. pgbench -c 9000 -f sleep.sql

而我们压力测试的命令为:

  1. pgbench -S -c 64 -j 96 -M prepared -T30 -P 2

3. 实际测试

3.1 开启9000个空闲连接的测试

测试PostgreSQL 13.3

先开9000个执行pg_sleep()的连接:

  1. [pg13@pg01 ~]$ pgbench -c 9000 -f sleep.sql
  2. starting vacuum...end.

然后压力测试:

  1. [pg13@pg01 ~]$ pgbench -S -c 128 -j 16 -M prepared -T30 -P 2
  2. starting vacuum...end.
  3. progress: 2.0 s, 259526.5 tps, lat 0.446 ms stddev 0.185
  4. progress: 4.0 s, 268625.8 tps, lat 0.437 ms stddev 0.131
  5. ...
  6. ...
  7. progress: 30.0 s, 272168.1 tps, lat 0.462 ms stddev 0.135
  8. transaction type: <builtin: select only>
  9. scaling factor: 200
  10. query mode: prepared
  11. number of clients: 128
  12. number of threads: 16
  13. duration: 30 s
  14. number of transactions actually processed: 8120358
  15. latency average = 0.454 ms
  16. latency stddev = 0.151 ms
  17. tps = 270446.442022 (including connections establishing)
  18. tps = 271185.174447 (excluding connections establishing)
测试PostgreSQL 14beta1

先开9000个执行pg_sleep()的连接:

  1. [pg14@pg01 ~]$ pgbench -c 9000 -f sleep.sql
  2. starting vacuum...end.

然后压力测试:

  1. [pg14@pg01 ~]$ pgbench -S -c 128 -j 16 -M prepared -T30 -P 2
  2. starting vacuum...end.
  3. progress: 2.0 s, 324460.7 tps, lat 0.335 ms stddev 0.475
  4. progress: 4.0 s, 384877.4 tps, lat 0.316 ms stddev 0.454
  5. progress: 6.0 s, 385626.7 tps, lat 0.316 ms stddev 0.467
  6. ...
  7. ...
  8. progress: 28.0 s, 557425.8 tps, lat 0.213 ms stddev 0.152
  9. progress: 30.0 s, 557706.0 tps, lat 0.212 ms stddev 0.145
  10. pgbench (PostgreSQL) 14.0
  11. transaction type: <builtin: select only>
  12. scaling factor: 200
  13. query mode: prepared
  14. number of clients: 128
  15. number of threads: 16
  16. duration: 30 s
  17. number of transactions actually processed: 15378932
  18. latency average = 0.231 ms
  19. latency stddev = 0.256 ms
  20. initial connection time = 216.714 ms
  21. tps = 515524.706846 (without initial connection time)

可以看到在PostgreSQL 13中只能到达27万,而在PostgreSQL 14中就可以达到51万了,有近翻倍的性能提升。

极端测试: 20000万个空闲连接

PostgreSQL 13.3
  1. [pg13@pg01 ~]$ pgbench -S -c 128 -j 16 -M prepared -T30 -P 2
  2. starting vacuum...end.
  3. progress: 30.0 s, 146343.3 tps, lat 0.447 ms stddev 0.176
  4. transaction type: <builtin: select only>
  5. scaling factor: 200
  6. query mode: prepared
  7. number of clients: 128
  8. number of threads: 16
  9. duration: 30 s
  10. number of transactions actually processed: 4392927
  11. latency average = 0.447 ms
  12. latency stddev = 0.176 ms
  13. tps = 146325.374803 (including connections establishing)
  14. tps = 155519.134507 (excluding connections establishing)
PostgreSQL 14beta1
  1. [pg14@pg01 ~]$ pgbench -S -c 128 -j 16 -M prepared -T30 -P 2
  2. starting vacuum...end.
  3. progress: 2.0 s, 390343.4 tps, lat 0.234 ms stddev 0.196
  4. progress: 4.0 s, 542178.6 tps, lat 0.219 ms stddev 0.152
  5. progress: 6.0 s, 550231.9 tps, lat 0.216 ms stddev 0.145
  6. progress: 8.0 s, 550509.8 tps, lat 0.215 ms stddev 0.153
  7. progress: 10.0 s, 554279.3 tps, lat 0.214 ms stddev 0.144
  8. progress: 12.0 s, 552410.2 tps, lat 0.215 ms stddev 0.148
  9. progress: 14.0 s, 554254.9 tps, lat 0.214 ms stddev 0.147
  10. progress: 16.0 s, 554077.1 tps, lat 0.214 ms stddev 0.149
  11. progress: 18.0 s, 552176.4 tps, lat 0.215 ms stddev 0.148
  12. progress: 20.0 s, 554539.0 tps, lat 0.214 ms stddev 0.144
  13. progress: 22.0 s, 550789.9 tps, lat 0.215 ms stddev 0.154
  14. progress: 24.0 s, 554287.8 tps, lat 0.214 ms stddev 0.144
  15. progress: 26.0 s, 552759.7 tps, lat 0.215 ms stddev 0.149
  16. progress: 28.0 s, 554362.7 tps, lat 0.214 ms stddev 0.148
  17. pgbench (PostgreSQL) 14.0
  18. transaction type: <builtin: select only>
  19. scaling factor: 200
  20. query mode: prepared
  21. number of clients: 128
  22. number of threads: 16
  23. duration: 30 s
  24. number of transactions actually processed: 16240811
  25. latency average = 0.216 ms
  26. latency stddev = 0.160 ms
  27. initial connection time = 460.811 ms
  28. tps = 548627.931145 (without initial connection time)

可以看到PostgreSQL 14还是可以达到50多万的TPS,而PostgreSQL 13.3就只能是14万了,差距巨大。

3.3 进一步的测试

我们不建空闲连接,直接不断的改变连接数,看看解结果是怎样的:

  1. pgbench -S -c <连接数> -j 16 -M prepared -T30 -P 2

压测时只改变上面的-c的连接数。测试的过程这里就不写出来,直接给出结果:

连接数 PostgreSQL 13.3 PostgreSQL 14beta1
64 496841 513140
128 528556 552778
256 475030 497435
1000 347863 402369
5000 205857 310090
1000 124371 248701

高连接下PostgtreSQL 14性能与PostgreSQL 13.3对比

4. 结论

PostgtreSQL 14可以支持大量的空闲连接,这些空闲连接对执行的SQL的性能影响很小,但在PostgreSQL 13,会有很大的影响。所以从PostgreSQL 14之后,数据库可以支持上万个空闲连接,以前为了支持很多连接需要上连接池,现在可以不用了。

5. 原理简介

PostgreSQL 14主要是优化了GetSnapshotData()的性能,原先是随连接数增加,这个函数的性能是线性下降,从PostgreSQL 14之后就不是了。这个优化总共提交了7个PATCH,如果想进一步了解的同学可以见:

  1. snapshot scalability: Don’t compute global horizons while building snapshots.
  2. snapshot scalability: Move PGXACT->xmin back to PGPROC.
  3. snapshot scalability: Move PGXACT->vacuumFlags to ProcGlobal->vacuumFlags.
  4. snapshot scalability: Move subxact info to ProcGlobal, remove PGXACT.
  5. snapshot scalability: Introduce dense array of in-progress xids.
  6. snapshot scalability: cache snapshots using a xact completion counter.
  7. Fix race condition in snapshot caching when 2PC is used.
本站文章,未经作者同意,请勿转载,如需转载,请邮件customer@csudata.com.
0 评论  
添加一条新评论