PostgreSQL 11 1Kw TPCC , 1亿 TPCB 7*24 强压耐久测试-优快云博客

本文详细记录了TPCC和TPCB两种数据库性能测试的案例，包括1000万TPCC和1亿TPCB的7*24小时耐久测试。展示了不同阶段的TPS变化、数据库资源消耗及最终性能指标。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景
TPCC, TPCB是工业标准的OLTP类型业务的数据库测试，包含大量的读、写、更新、删除操作。

7*24小时强压耐久测试，主要看数据库在长时间最大压力下的性能、稳定性、可靠性。

测试CASE ：

1、1000万 tpcc

2、1亿 tpcb

测试时长7天。

测试环境
与如下测试同一台ECS虚拟机环境。

《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab》

《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》

《PostgreSQL 11 100亿 tpcb 性能测试 on ECS》

https://github.com/digoal/sysbench-tpcc

shared_buffer=300G

use huge page
PS:
实际上最初测试的是TPCC 2亿耐久(2.5TB左右的活跃数据)，发现压力基本上都在IO上（因为活跃数据2.5TB，已远远超出机器的内存），平均TPS 3千(18万 tpmC)。

1、1000万 tpcc 耐久测试
1、清空

./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=newdb --threads=64 --tables=10 --scale=100 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql cleanup
2、初始化数据

export pgsql_table_options="tablespace tbs1"
export pgsql_index_options="tablespace tbs2"
./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=newdb --threads=64 --tables=10 --scale=100 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --use_fk=0 prepare
3、持续7*24小时，强压耐久测试

4、shell

!/bin/bash

./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=newdb --threads=64 --tables=10 --scale=100 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql cleanup

截取运行至8000秒（约2.2小时）时的TPS： 2万左右。

[ 8000s ] thds: 64 tps: 20485.43 qps: 581769.53 (r/w/o: 265359.48/275433.19/40976.85) lat (ms,95%): 7.98 err/s 89.01 reconn/s: 0.00
[ 8001s ] thds: 64 tps: 20501.67 qps: 583473.15 (r/w/o: 266219.73/276250.08/41003.34) lat (ms,95%): 7.98 err/s 82.99 reconn/s: 0.00
[ 8002s ] thds: 64 tps: 20494.29 qps: 579007.65 (r/w/o: 264151.49/273867.58/40988.58) lat (ms,95%): 7.84 err/s 84.01 reconn/s: 0.00
[ 8003s ] thds: 64 tps: 20233.62 qps: 570370.25 (r/w/o: 260329.09/269574.92/40466.24) lat (ms,95%): 7.98 err/s 93.00 reconn/s: 0.00
[ 8004s ] thds: 64 tps: 20367.20 qps: 580154.75 (r/w/o: 264423.62/274995.72/40735.40) lat (ms,95%): 7.98 err/s 96.00 reconn/s: 0.00
[ 8005s ] thds: 64 tps: 20411.02 qps: 585439.65 (r/w/o: 267331.30/277285.31/40823.05) lat (ms,95%): 7.98 err/s 100.00 reconn/s: 0.00
[ 8006s ] thds: 64 tps: 20651.82 qps: 583243.91 (r/w/o: 266004.68/275936.59/41302.64) lat (ms,95%): 7.98 err/s 73.00 reconn/s: 0.00
[ 8007s ] thds: 64 tps: 20324.62 qps: 579310.20 (r/w/o: 264444.07/274218.89/40647.24) lat (ms,95%): 7.98 err/s 111.00 reconn/s: 0.00
[ 8008s ] thds: 64 tps: 20419.62 qps: 585216.87 (r/w/o: 267119.16/277256.47/40841.25) lat (ms,95%): 7.84 err/s 93.00 reconn/s: 0.00
[ 8009s ] thds: 64 tps: 20563.73 qps: 583747.26 (r/w/o: 266217.47/276403.34/41126.45) lat (ms,95%): 7.84 err/s 78.00 reconn/s: 0.00
[ 8010s ] thds: 64 tps: 20441.18 qps: 582812.00 (r/w/o: 265923.28/276005.37/40883.35) lat (ms,95%): 7.84 err/s 85.00 reconn/s: 0.00
[ 8011s ] thds: 64 tps: 20501.97 qps: 585269.16 (r/w/o: 267044.62/277220.60/41003.94) lat (ms,95%): 7.98 err/s 104.00 reconn/s: 0.00
[ 8012s ] thds: 64 tps: 20511.63 qps: 579944.71 (r/w/o: 264265.36/274656.10/41023.25) lat (ms,95%): 7.84 err/s 94.95 reconn/s: 0.00
[ 8013s ] thds: 64 tps: 20411.52 qps: 580261.93 (r/w/o: 264764.40/274674.50/40823.03) lat (ms,95%): 7.84 err/s 106.05 reconn/s: 0.00
[ 8014s ] thds: 64 tps: 20070.84 qps: 573689.42 (r/w/o: 261630.91/271918.83/40139.68) lat (ms,95%): 8.13 err/s 114.00 reconn/s: 0.00
[ 8015s ] thds: 64 tps: 20600.03 qps: 584514.83 (r/w/o: 266629.38/276684.39/41201.06) lat (ms,95%): 7.84 err/s 79.00 reconn/s: 0.00
[ 8016s ] thds: 64 tps: 20317.84 qps: 578408.02 (r/w/o: 263900.96/273870.39/40636.68) lat (ms,95%): 7.98 err/s 80.00 reconn/s: 0.00
[ 8017s ] thds: 64 tps: 20468.49 qps: 581658.79 (r/w/o: 265438.29/275285.53/40934.97) lat (ms,95%): 7.98 err/s 88.00 reconn/s: 0.00
[ 8018s ] thds: 64 tps: 20420.97 qps: 581415.10 (r/w/o: 264919.59/275653.57/40841.94) lat (ms,95%): 7.98 err/s 91.00 reconn/s: 0.00
[ 8019s ] thds: 64 tps: 20635.64 qps: 580629.97 (r/w/o: 264969.20/274388.49/41272.28) lat (ms,95%): 7.84 err/s 100.00 reconn/s: 0.00
[ 8020s ] thds: 64 tps: 20333.19 qps: 581171.36 (r/w/o: 264986.46/275517.52/40667.39) lat (ms,95%): 7.98 err/s 77.99 reconn/s: 0.00
[ 8021s ] thds: 64 tps: 20413.23 qps: 584491.09 (r/w/o: 266667.01/276999.63/40824.45) lat (ms,95%): 7.84 err/s 93.01 reconn/s: 0.00
[ 8022s ] thds: 64 tps: 20356.76 qps: 582531.18 (r/w/o: 265727.89/276090.77/40712.52) lat (ms,95%): 7.98 err/s 110.00 reconn/s: 0.00
[ 8023s ] thds: 64 tps: 20382.28 qps: 580142.43 (r/w/o: 264541.61/274833.26/40767.56) lat (ms,95%): 7.98 err/s 88.01 reconn/s: 0.00
[ 8024s ] thds: 64 tps: 20416.72 qps: 578534.32 (r/w/o: 263951.49/273750.40/40832.44) lat (ms,95%): 7.98 err/s 99.99 reconn/s: 0.00
[ 8025s ] thds: 64 tps: 20476.80 qps: 580704.08 (r/w/o: 264754.29/274994.19/40955.60) lat (ms,95%): 7.98 err/s 90.01 reconn/s: 0.00
[ 8026s ] thds: 64 tps: 20440.61 qps: 583615.78 (r/w/o: 266265.88/276469.69/40880.21) lat (ms,95%): 7.84 err/s 83.00 reconn/s: 0.00
[ 8027s ] thds: 64 tps: 20426.51 qps: 580503.41 (r/w/o: 264836.57/274813.82/40853.01) lat (ms,95%): 7.98 err/s 90.00 reconn/s: 0.00
[ 8028s ] thds: 64 tps: 20533.18 qps: 582233.85 (r/w/o: 265680.44/275488.05/41065.37) lat (ms,95%): 7.98 err/s 88.00 reconn/s: 0.00
[ 8029s ] thds: 64 tps: 20316.48 qps: 579150.80 (r/w/o: 264125.29/274392.54/40632.97) lat (ms,95%): 7.98 err/s 94.00 reconn/s: 0.00
[ 8030s ] thds: 64 tps: 20477.27 qps: 581592.57 (r/w/o: 265383.46/275252.58/40956.53) lat (ms,95%): 7.98 err/s 94.00 reconn/s: 0.00
[ 8031s ] thds: 64 tps: 20575.51 qps: 586335.17 (r/w/o: 267565.60/277619.54/41150.03) lat (ms,95%): 7.84 err/s 88.93 reconn/s: 0.00
[ 8032s ] thds: 64 tps: 20330.22 qps: 579527.50 (r/w/o: 264197.85/274671.20/40658.45) lat (ms,95%): 7.98 err/s 87.07 reconn/s: 0.00
[ 8033s ] thds: 64 tps: 20513.19 qps: 580624.41 (r/w/o: 264746.47/274850.56/41027.38) lat (ms,95%): 7.98 err/s 93.00 reconn/s: 0.00
[ 8034s ] thds: 64 tps: 20378.91 qps: 580302.54 (r/w/o: 264830.88/274713.83/40757.83) lat (ms,95%): 7.98 err/s 93.00 reconn/s: 0.00
[ 8035s ] thds: 64 tps: 20448.74 qps: 581127.58 (r/w/o: 265028.61/275200.48/40898.48) lat (ms,95%): 7.98 err/s 99.00 reconn/s: 0.00
[ 8036s ] thds: 64 tps: 20344.99 qps: 582162.67 (r/w/o: 265618.85/275853.84/40689.98) lat (ms,95%): 7.98 err/s 89.00 reconn/s: 0.00
[ 8037s ] thds: 64 tps: 20255.29 qps: 581262.31 (r/w/o: 265043.79/275707.94/40510.58) lat (ms,95%): 7.98 err/s 70.00 reconn/s: 0.00
[ 8038s ] thds: 64 tps: 20697.54 qps: 581712.56 (r/w/o: 265270.89/275048.58/41393.09) lat (ms,95%): 7.84 err/s 95.93 reconn/s: 0.00
[ 8039s ] thds: 64 tps: 20382.69 qps: 579676.79 (r/w/o: 264292.48/274616.92/40767.38) lat (ms,95%): 7.98 err/s 96.07 reconn/s: 0.00
[ 8040s ] thds: 64 tps: 20460.71 qps: 580293.70 (r/w/o: 264518.22/274855.07/40920.41) lat (ms,95%): 7.84 err/s 78.00 reconn/s: 0.00
[ 8041s ] thds: 64 tps: 20321.56 qps: 578692.52 (r/w/o: 264122.31/273927.09/40643.12) lat (ms,95%): 7.98 err/s 80.00 reconn/s: 0.00
[ 8042s ] thds: 64 tps: 20031.57 qps: 570578.33 (r/w/o: 260355.45/270157.73/40065.15) lat (ms,95%): 8.13 err/s 85.00 reconn/s: 0.00
[ 8043s ] thds: 64 tps: 20412.66 qps: 578667.83 (r/w/o: 263881.59/273964.92/40821.33) lat (ms,95%): 7.98 err/s 92.00 reconn/s: 0.00
[ 8044s ] thds: 64 tps: 19949.81 qps: 570346.51 (r/w/o: 260248.50/270198.40/39899.62) lat (ms,95%): 8.13 err/s 78.00 reconn/s: 0.00
[ 8045s ] thds: 64 tps: 20404.18 qps: 574960.10 (r/w/o: 262190.33/271960.41/40809.36) lat (ms,95%): 7.98 err/s 73.00 reconn/s: 0.00
[ 8046s ] thds: 64 tps: 20059.60 qps: 571523.19 (r/w/o: 260615.84/270788.14/40119.21) lat (ms,95%): 8.13 err/s 80.00 reconn/s: 0.00
[ 8047s ] thds: 64 tps: 20106.65 qps: 574613.54 (r/w/o: 262414.44/271983.80/40215.31) lat (ms,95%): 8.13 err/s 101.99 reconn/s: 0.00
[ 8048s ] thds: 64 tps: 20085.47 qps: 570847.83 (r/w/o: 260594.07/270082.82/40170.93) lat (ms,95%): 8.13 err/s 86.00 reconn/s: 0.00
[ 8049s ] thds: 64 tps: 20229.09 qps: 573145.08 (r/w/o: 261418.18/271270.73/40456.17) lat (ms,95%): 7.98 err/s 68.00 reconn/s: 0.00
[ 8050s ] thds: 64 tps: 20205.93 qps: 571454.49 (r/w/o: 260576.85/270466.79/40410.85) lat (ms,95%): 7.98 err/s 74.01 reconn/s: 0.00
[ 8051s ] thds: 64 tps: 20501.31 qps: 581237.93 (r/w/o: 265126.07/275106.23/41005.63) lat (ms,95%): 7.98 err/s 82.00 reconn/s: 0.00
[ 8052s ] thds: 64 tps: 20178.41 qps: 576336.27 (r/w/o: 262996.37/272983.08/40356.83) lat (ms,95%): 8.13 err/s 93.00 reconn/s: 0.00
截取运行至60314秒（约16小时）时的TPS： 1.8万左右。

[ 60314s ] thds: 64 tps: 19341.74 qps: 543204.76 (r/w/o: 247957.48/256563.81/38683.48) lat (ms,95%): 8.43 err/s 93.00 reconn/s: 0.00
[ 60315s ] thds: 64 tps: 19402.02 qps: 553901.95 (r/w/o: 252584.21/262512.71/38805.03) lat (ms,95%): 8.28 err/s 93.00 reconn/s: 0.00
[ 60316s ] thds: 64 tps: 18301.57 qps: 519270.68 (r/w/o: 236803.38/245865.17/36602.13) lat (ms,95%): 8.43 err/s 61.00 reconn/s: 0.00
[ 60317s ] thds: 64 tps: 16248.96 qps: 460563.21 (r/w/o: 210044.41/218019.88/32498.92) lat (ms,95%): 8.74 err/s 81.00 reconn/s: 0.00
[ 60318s ] thds: 64 tps: 19251.90 qps: 550591.20 (r/w/o: 251161.72/260926.67/38502.80) lat (ms,95%): 8.43 err/s 96.00 reconn/s: 0.00
[ 60319s ] thds: 64 tps: 19509.95 qps: 548363.50 (r/w/o: 249973.55/259370.05/39019.90) lat (ms,95%): 8.28 err/s 73.00 reconn/s: 0.00
[ 60320s ] thds: 64 tps: 19543.97 qps: 549761.22 (r/w/o: 250722.65/259949.63/39088.94) lat (ms,95%): 8.13 err/s 76.00 reconn/s: 0.00
[ 60321s ] thds: 64 tps: 19363.48 qps: 549986.10 (r/w/o: 250868.20/260390.95/38726.95) lat (ms,95%): 8.28 err/s 82.00 reconn/s: 0.00
[ 60322s ] thds: 64 tps: 19371.95 qps: 551287.99 (r/w/o: 251553.31/260992.78/38741.90) lat (ms,95%): 8.28 err/s 80.00 reconn/s: 0.00
[ 60323s ] thds: 64 tps: 15691.03 qps: 452727.03 (r/w/o: 206630.23/214713.73/31383.06) lat (ms,95%): 8.90 err/s 64.00 reconn/s: 0.00
[ 60324s ] thds: 64 tps: 16274.47 qps: 458086.44 (r/w/o: 208798.89/216740.61/32546.94) lat (ms,95%): 8.90 err/s 61.01 reconn/s: 0.00
[ 60325s ] thds: 64 tps: 19378.81 qps: 550473.61 (r/w/o: 251081.54/260633.45/38758.62) lat (ms,95%): 8.28 err/s 77.00 reconn/s: 0.00
[ 60326s ] thds: 64 tps: 19208.78 qps: 546456.65 (r/w/o: 249474.10/258563.99/38418.55) lat (ms,95%): 8.43 err/s 68.00 reconn/s: 0.00
[ 60327s ] thds: 64 tps: 16284.20 qps: 461247.29 (r/w/o: 210580.59/218099.29/32567.40) lat (ms,95%): 8.74 err/s 73.87 reconn/s: 0.00
[ 60328s ] thds: 64 tps: 18873.26 qps: 533919.22 (r/w/o: 243502.03/252668.67/37748.52) lat (ms,95%): 8.28 err/s 84.15 reconn/s: 0.00
[ 60329s ] thds: 64 tps: 19181.30 qps: 548828.91 (r/w/o: 250310.84/260155.48/38362.60) lat (ms,95%): 8.28 err/s 73.00 reconn/s: 0.00
[ 60330s ] thds: 64 tps: 19163.09 qps: 544791.56 (r/w/o: 248654.17/257811.21/38326.18) lat (ms,95%): 8.43 err/s 84.00 reconn/s: 0.00
[ 60331s ] thds: 64 tps: 15370.60 qps: 436963.74 (r/w/o: 199441.86/206782.67/30739.21) lat (ms,95%): 8.90 err/s 58.00 reconn/s: 0.00
[ 60332s ] thds: 64 tps: 18666.11 qps: 535700.06 (r/w/o: 244396.39/253969.45/37334.21) lat (ms,95%): 8.58 err/s 81.00 reconn/s: 0.00
[ 60333s ] thds: 64 tps: 19166.30 qps: 542122.53 (r/w/o: 247496.90/256293.03/38332.60) lat (ms,95%): 8.28 err/s 75.00 reconn/s: 0.00
[ 60334s ] thds: 64 tps: 17808.04 qps: 511114.51 (r/w/o: 233230.46/242268.97/35615.08) lat (ms,95%): 8.74 err/s 89.00 reconn/s: 0.00
[ 60335s ] thds: 64 tps: 15754.88 qps: 447359.62 (r/w/o: 204130.46/211719.40/31509.76) lat (ms,95%): 8.90 err/s 64.00 reconn/s: 0.00
[ 60336s ] thds: 64 tps: 18478.48 qps: 529327.12 (r/w/o: 241701.20/250668.95/36956.96) lat (ms,95%): 8.74 err/s 73.00 reconn/s: 0.00
[ 60337s ] thds: 64 tps: 19124.11 qps: 544896.11 (r/w/o: 248318.39/258330.50/38247.22) lat (ms,95%): 8.43 err/s 73.01 reconn/s: 0.00
[ 60338s ] thds: 64 tps: 16187.05 qps: 462672.85 (r/w/o: 211196.76/219100.00/32376.09) lat (ms,95%): 9.06 err/s 70.92 reconn/s: 0.00
[ 60339s ] thds: 64 tps: 18098.52 qps: 517695.48 (r/w/o: 236153.76/245345.67/36196.05) lat (ms,95%): 8.74 err/s 81.09 reconn/s: 0.00
[ 60340s ] thds: 64 tps: 19052.64 qps: 545579.33 (r/w/o: 248832.36/258642.69/38104.28) lat (ms,95%): 8.58 err/s 91.00 reconn/s: 0.00
[ 60341s ] thds: 64 tps: 16571.76 qps: 474637.18 (r/w/o: 216660.89/224831.77/33144.52) lat (ms,95%): 8.74 err/s 68.00 reconn/s: 0.00
[ 60342s ] thds: 64 tps: 15923.79 qps: 450100.13 (r/w/o: 205297.32/212955.22/31847.58) lat (ms,95%): 8.74 err/s 69.00 reconn/s: 0.00
[ 60343s ] thds: 64 tps: 18764.65 qps: 537077.94 (r/w/o: 245110.41/254437.23/37530.30) lat (ms,95%): 8.43 err/s 81.00 reconn/s: 0.00
截取运行至105235秒（约29小时）时的TPS： 1.3万左右

[ 105235s ] thds: 64 tps: 10971.14 qps: 310702.84 (r/w/o: 141931.50/146827.05/21944.30) lat (ms,95%): 11.04 err/s 54.42 reconn/s: 0.00
[ 105236s ] thds: 64 tps: 16716.96 qps: 473482.17 (r/w/o: 216038.40/224008.86/33434.92) lat (ms,95%): 9.39 err/s 79.00 reconn/s: 0.00
[ 105237s ] thds: 64 tps: 12184.17 qps: 347403.15 (r/w/o: 158458.12/164578.69/24366.33) lat (ms,95%): 10.09 err/s 51.98 reconn/s: 0.00
[ 105238s ] thds: 64 tps: 13984.71 qps: 393431.48 (r/w/o: 179531.45/185928.61/27971.42) lat (ms,95%): 9.73 err/s 54.02 reconn/s: 0.00
[ 105239s ] thds: 64 tps: 15386.03 qps: 433355.56 (r/w/o: 197604.49/204981.02/30770.05) lat (ms,95%): 9.56 err/s 65.00 reconn/s: 0.00
[ 105240s ] thds: 64 tps: 8817.13 qps: 254406.00 (r/w/o: 116071.20/120699.54/17635.25) lat (ms,95%): 12.08 err/s 40.96 reconn/s: 0.00
[ 105241s ] thds: 64 tps: 7327.41 qps: 206762.43 (r/w/o: 94191.16/97917.44/14653.83) lat (ms,95%): 11.87 err/s 37.04 reconn/s: 0.00
[ 105242s ] thds: 64 tps: 16702.17 qps: 474897.45 (r/w/o: 216633.26/224857.86/33406.33) lat (ms,95%): 9.56 err/s 76.99 reconn/s: 0.00
[ 105243s ] thds: 64 tps: 13411.71 qps: 380988.62 (r/w/o: 173814.18/180351.02/26823.42) lat (ms,95%): 10.09 err/s 53.01 reconn/s: 0.00
[ 105244s ] thds: 64 tps: 9963.37 qps: 284381.14 (r/w/o: 129775.85/134680.54/19924.75) lat (ms,95%): 11.04 err/s 35.00 reconn/s: 0.00
[ 105245s ] thds: 64 tps: 14759.45 qps: 419263.48 (r/w/o: 191417.92/198324.66/29520.91) lat (ms,95%): 9.73 err/s 69.00 reconn/s: 0.00
[ 105246s ] thds: 64 tps: 12785.37 qps: 365241.33 (r/w/o: 166674.43/172997.16/25569.73) lat (ms,95%): 10.27 err/s 52.96 reconn/s: 0.00
[ 105247s ] thds: 64 tps: 10651.06 qps: 300672.14 (r/w/o: 137243.92/142125.11/21303.11) lat (ms,95%): 10.46 err/s 35.91 reconn/s: 0.00
[ 105248s ] thds: 64 tps: 14604.60 qps: 412991.62 (r/w/o: 188383.80/195400.63/29207.19) lat (ms,95%): 9.39 err/s 54.18 reconn/s: 0.00
[ 105249s ] thds: 64 tps: 16771.01 qps: 472864.24 (r/w/o: 215600.82/223720.40/33543.02) lat (ms,95%): 9.39 err/s 79.98 reconn/s: 0.00
[ 105250s ] thds: 64 tps: 11177.31 qps: 318115.91 (r/w/o: 145144.34/150616.96/22354.62) lat (ms,95%): 10.84 err/s 50.58 reconn/s: 0.00
[ 105251s ] thds: 64 tps: 8944.08 qps: 252604.23 (r/w/o: 115208.77/119506.29/17889.17) lat (ms,95%): 11.45 err/s 40.35 reconn/s: 0.00
[ 105252s ] thds: 64 tps: 12086.05 qps: 338581.29 (r/w/o: 154610.80/159798.39/24172.09) lat (ms,95%): 9.91 err/s 53.00 reconn/s: 0.00
[ 105253s ] thds: 64 tps: 16202.94 qps: 466033.79 (r/w/o: 212768.97/220859.94/32404.88) lat (ms,95%): 9.56 err/s 76.99 reconn/s: 0.00
[ 105254s ] thds: 64 tps: 14574.21 qps: 413960.09 (r/w/o: 188988.78/195822.88/29148.43) lat (ms,95%): 9.73 err/s 63.00 reconn/s: 0.00
[ 105255s ] thds: 64 tps: 13877.36 qps: 390236.01 (r/w/o: 178052.57/184427.73/27755.71) lat (ms,95%): 10.09 err/s 50.00 reconn/s: 0.00
[ 105256s ] thds: 64 tps: 15102.61 qps: 428525.80 (r/w/o: 195790.88/202531.71/30203.21) lat (ms,95%): 9.56 err/s 64.00 reconn/s: 0.00
[ 105257s ] thds: 64 tps: 16296.44 qps: 461997.43 (r/w/o: 211100.68/218301.87/32594.88) lat (ms,95%): 9.39 err/s 73.00 reconn/s: 0.00
[ 105258s ] thds: 64 tps: 10220.71 qps: 287978.86 (r/w/o: 131516.28/136022.16/20440.42) lat (ms,95%): 10.46 err/s 53.00 reconn/s: 0.00
[ 105259s ] thds: 64 tps: 3986.03 qps: 114032.80 (r/w/o: 52087.37/53973.38/7972.06) lat (ms,95%): 13.46 err/s 14.00 reconn/s: 0.00
[ 105260s ] thds: 64 tps: 15307.79 qps: 434108.13 (r/w/o: 197882.33/205609.22/30616.59) lat (ms,95%): 9.73 err/s 77.00 reconn/s: 0.00
[ 105261s ] thds: 64 tps: 10360.59 qps: 297243.50 (r/w/o: 135577.75/140944.58/20721.17) lat (ms,95%): 10.84 err/s 41.01 reconn/s: 0.00
[ 105262s ] thds: 64 tps: 9348.18 qps: 263222.94 (r/w/o: 120104.48/124424.10/18694.36) lat (ms,95%): 11.45 err/s 48.00 reconn/s: 0.00
[ 105263s ] thds: 64 tps: 10481.89 qps: 296577.98 (r/w/o: 135384.62/140227.57/20965.79) lat (ms,95%): 11.04 err/s 43.00 reconn/s: 0.00
[ 105264s ] thds: 64 tps: 14258.34 qps: 405619.15 (r/w/o: 185077.40/192025.08/28516.67) lat (ms,95%): 9.91 err/s 64.00 reconn/s: 0.00
[ 105265s ] thds: 64 tps: 15180.17 qps: 431972.85 (r/w/o: 197029.21/204583.29/30360.35) lat (ms,95%): 9.91 err/s 58.01 reconn/s: 0.00
[ 105266s ] thds: 64 tps: 16335.46 qps: 462913.83 (r/w/o: 211153.08/219089.82/32670.93) lat (ms,95%): 9.39 err/s 79.00 reconn/s: 0.00
[ 105267s ] thds: 64 tps: 14183.78 qps: 404733.81 (r/w/o: 184728.18/191639.07/28366.57) lat (ms,95%): 9.56 err/s 65.00 reconn/s: 0.00
[ 105268s ] thds: 64 tps: 8382.61 qps: 235797.93 (r/w/o: 107527.18/111504.52/16766.22) lat (ms,95%): 12.30 err/s 26.00 reconn/s: 0.00
[ 105269s ] thds: 64 tps: 15655.17 qps: 443939.57 (r/w/o: 202572.09/210057.13/31310.34) lat (ms,95%): 9.56 err/s 71.01 reconn/s: 0.00
[ 105270s ] thds: 64 tps: 11207.84 qps: 320791.50 (r/w/o: 146306.95/152070.87/22413.69) lat (ms,95%): 11.04 err/s 44.00 reconn/s: 0.00
[ 105271s ] thds: 64 tps: 13845.13 qps: 399042.68 (r/w/o: 181915.68/189434.75/27692.26) lat (ms,95%): 9.91 err/s 65.00 reconn/s: 0.00
[ 105272s ] thds: 64 tps: 8264.45 qps: 236754.99 (r/w/o: 108044.68/112181.41/16528.90) lat (ms,95%): 11.45 err/s 40.99 reconn/s: 0.00
[ 105273s ] thds: 64 tps: 10702.08 qps: 302372.04 (r/w/o: 138055.74/142912.14/21404.16) lat (ms,95%): 10.27 err/s 53.02 reconn/s: 0.00
[ 105274s ] thds: 64 tps: 16254.38 qps: 463970.25 (r/w/o: 211848.78/219611.72/32509.75) lat (ms,95%): 9.56 err/s 76.84 reconn/s: 0.00
[ 105275s ] thds: 64 tps: 13855.29 qps: 399107.99 (r/w/o: 182274.21/189125.20/27708.58) lat (ms,95%): 10.09 err/s 72.15 reconn/s: 0.00
[ 105276s ] thds: 64 tps: 11715.07 qps: 337701.73 (r/w/o: 154173.03/160100.57/23428.13) lat (ms,95%): 10.46 err/s 48.00 reconn/s: 0.00
[ 105277s ] thds: 64 tps: 13778.07 qps: 392465.82 (r/w/o: 179067.23/185841.44/27557.14) lat (ms,95%): 9.91 err/s 56.67 reconn/s: 0.00
[ 105278s ] thds: 64 tps: 11129.26 qps: 311401.06 (r/w/o: 141946.61/147193.91/22260.53) lat (ms,95%): 10.65 err/s 45.26 reconn/s: 0.00
[ 105279s ] thds: 64 tps: 11934.79 qps: 341804.12 (r/w/o: 155904.32/162031.23/23868.57) lat (ms,95%): 10.27 err/s 44.01 reconn/s: 0.00
[ 105280s ] thds: 64 tps: 14279.68 qps: 400515.10 (r/w/o: 182797.16/189158.57/28559.37) lat (ms,95%): 9.91 err/s 57.99 reconn/s: 0.00
[ 105281s ] thds: 64 tps: 10798.39 qps: 306898.05 (r/w/o: 139849.04/145452.24/21596.78) lat (ms,95%): 10.65 err/s 45.00 reconn/s: 0.00
[ 105282s ] thds: 64 tps: 15073.77 qps: 431435.46 (r/w/o: 196872.01/204415.90/30147.54) lat (ms,95%): 9.73 err/s 60.00 reconn/s: 0.00
[ 105283s ] thds: 64 tps: 10182.07 qps: 288077.44 (r/w/o: 131508.09/136204.20/20365.14) lat (ms,95%): 10.84 err/s 32.99 reconn/s: 0.00
[ 105284s ] thds: 64 tps: 12926.33 qps: 370825.74 (r/w/o: 169125.44/175848.65/25851.65) lat (ms,95%): 10.09 err/s 58.01 reconn/s: 0.00
[ 105285s ] thds: 64 tps: 15116.37 qps: 427026.63 (r/w/o: 194883.63/201909.27/30233.74) lat (ms,95%): 9.73 err/s 54.00 reconn/s: 0.00
[ 105286s ] thds: 64 tps: 16076.53 qps: 455528.33 (r/w/o: 207808.99/215567.28/32152.06) lat (ms,95%): 9.39 err/s 80.99 reconn/s: 0.00
[ 105287s ] thds: 64 tps: 16653.26 qps: 476688.40 (r/w/o: 217349.38/226031.51/33307.52) lat (ms,95%): 9.22 err/s 69.00 reconn/s: 0.00
[ 105288s ] thds: 64 tps: 13093.48 qps: 371458.36 (r/w/o: 169550.32/175721.08/26186.97) lat (ms,95%): 10.09 err/s 54.00 reconn/s: 0.00
[ 105289s ] thds: 64 tps: 9397.12 qps: 266228.12 (r/w/o: 121445.65/125989.23/18793.24) lat (ms,95%): 11.24 err/s 49.00 reconn/s: 0.00
运行至105235秒（约29小时）时，数据库写入量达到1.9TB。

postgres=# l+

                                                           List of databases

Name	Owner	Encoding	Collate	Ctype	Access privileges	Size	Tablespace	Description
newdb	postgres	SQL_ASCII	C	C		1945 GB	pg_default

TPS下降主要是活跃数据逐渐超过内存大小，IO waiting造成。

没有IO瓶颈时，TPS在2万左右。

统计信息

newdb=# select relname, n_tup_ins, n_tup_upd, n_tup_hot_upd,n_tup_del from pg_stat_all_tables order by (n_tup_ins+ n_tup_upd+ n_tup_del) desc;

     relname         | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_tup_del

order_line8	742854773	713469103	470117428	0
order_line1	742833097	713118634	468813423	0
order_line2	742814453	713125655	469244070	0
order_line6	742772114	713101727	468560015	0
order_line7	742806127	713043788	468913950	0
order_line4	742723229	713108643	468710015	0
order_line3	742737023	713059091	469035956	0
order_line10	742644699	713128483	468425378	0
order_line9	742687699	713048549	471041026	0
order_line5	742673162	713019192	469636425	0
stock8	10000000	712762996	711773249	0
stock1	10000000	712724019	711733972	0
stock7	10000000	712710454	711720727	0
stock2	10000000	712699712	711709720	0
stock6	10000000	712664471	711675060	0
stock3	10000000	712628267	711639239	0
stock4	10000000	712614959	711625378	0
stock9	10000000	712578521	711589735	0
stock5	10000000	712571539	711581883	0
stock10	10000000	712537577	711548476	0
order_line18	120222432	90592862	64624605	78896814
order_line17	120261447	90560878	64785230	78887855
order_line19	120202018	90466530	64834794	78751265
order_line16	120159770	90427602	64739869	78745237
order_line20	120198335	90395667	65068488	78700223
order_line13	120134220	90412599	64828782	78727933
order_line15	120211731	90378933	65101992	78664788
order_line14	120159274	90323656	64909828	78623449
order_line12	120146896	90345538	64738002	78593905
order_line11	120113947	90188119	64658216	78476210
customer8	3000000	142867720	142235485	0
customer2	3000000	142863783	142221419	0
customer10	3000000	142861001	142231849	0
customer1	3000000	142856118	142226469	0
customer3	3000000	142856100	142225914	0
customer6	3000000	142856066	142187400	0
customer7	3000000	142854615	142219529	0
customer4	3000000	142846513	142213297	0
customer9	3000000	142837654	142204790	0
customer5	3000000	142825641	142194765	0
orders8	74435847	71350501	66463303	0
orders1	74437273	71320879	66424847	0
orders2	74430364	71318489	66403037	0
orders4	74425266	71319415	66413399	0
orders7	74428137	71308212	66384341	0
orders6	74423999	71312104	66391791	0
orders5	74420941	71311488	66417836	0
orders9	74419829	71312456	66429273	0
orders10	74414375	71316975	66415194	0
orders3	74417964	71305524	66387134	0
new_orders8	72324035	0	0	71341012
new_orders1	72325453	0	0	71310992
new_orders2	72318824	0	0	71308036
new_orders4	72312623	0	0	71309388
new_orders6	72312188	0	0	71301214
new_orders7	72316346	0	0	71297056
new_orders9	72308709	0	0	71302880
new_orders5	72309479	0	0	71301185
new_orders10	72302706	0	0	71307616
new_orders3	72307185	0	0	71296566
district2	1000	142968966	142714582	0
district7	1000	142966656	142698327	0
district1	1000	142964804	142646240	0
district3	1000	142962034	142701972	0
district6	1000	142961021	142720706	0
district10	1000	142950841	142703853	0
district4	1000	142946915	142680283	0
district8	1000	142946805	142717357	0
district9	1000	142938663	142709335	0
district5	1000	142928459	142693541	0
stock17	10000000	89481481	88938366	0
stock15	10000000	89476911	88933546	0
stock12	10000000	89462637	88918517	0
stock11	10000000	89439475	88896285	0
stock14	10000000	89438138	88896221	0
stock18	10000000	89437799	88894243	0
stock13	10000000	89432484	88888980	0
stock16	10000000	89398767	88855039	0
stock20	10000000	89386421	88842973	0
stock19	10000000	89385085	88842093	0
history3	74424652	0	0	0
history7	74420955	0	0	0
history2	74420732	0	0	0
history10	74418428	0	0	0
history6	74417388	0	0	0
history1	74408236	0	0	0
history4	74402262	0	0	0
history9	74397395	0	0	0
history8	74393045	0	0	0
history5	74388763	0	0	0
warehouse3	100	71492200	71450379	0
warehouse2	100	71488430	71444047	0
warehouse7	100	71488084	71442103	0
warehouse6	100	71486603	71441165	0
warehouse10	100	71486293	71440442	0
warehouse1	100	71476931	71435941	0
warehouse4	100	71470721	71426193	0
warehouse9	100	71466903	71426798	0
warehouse8	100	71460796	71417622	0
warehouse5	100	71456483	71411916	0
orders18	12108589	9125510	8807138	7939496
orders17	12110637	9120269	8804006	7938378
orders19	12105891	9113110	8797260	7923660
orders20	12108671	9107010	8793572	7920437
orders16	12099771	9106790	8787831	7922956
orders13	12094924	9102890	8794151	7919890
orders15	12103020	9098450	8779376	7914356
orders14	12099048	9093850	8780012	7909755
orders12	12093362	9092400	8775385	7906052
orders11	12087640	9075160	8760871	7891505
history20	12036742	0	0	11726195
history19	12034802	0	0	11727724
history18	12032658	0	0	11724219
history16	12032243	0	0	11716928
history17	12029737	0	0	11718122
history13	12026358	0	0	11706908
history12	12023241	0	0	11705417
history14	12022479	0	0	11706108
history15	12022364	0	0	11705010
history11	12014446	0	0	11697237
customer18	3000000	18036170	17654943	0
customer17	3000000	18033330	17652039	0
customer13	3000000	18031178	17649365	0
customer16	3000000	18025381	17643831	0
customer12	3000000	18023919	17642613	0
customer19	3000000	18021666	17640586	0
customer20	3000000	18015621	17633319	0
customer14	3000000	18014509	17633309	0
customer15	3000000	18010869	17629605	0
customer11	3000000	18002119	17621502	0
new_orders18	10007127	0	0	9125290
new_orders17	10009015	0	0	9119249
new_orders19	10004860	0	0	9112640
new_orders20	10007841	0	0	9106570
new_orders16	9997658	0	0	9105470
new_orders15	10000794	0	0	9097660
new_orders13	9992490	0	0	9101210
new_orders14	9996854	0	0	9092360
new_orders12	9990837	0	0	9090990
new_orders11	9984771	0	0	9073380
district12	1000	18017077	18016085	0
district17	1000	18016760	18015911	0
district13	1000	18014365	18013375	0
district18	1000	18012427	18011288	0
district16	1000	18011460	18010443	0
district14	1000	18010505	18009558	0
district20	1000	18009697	18008717	0
district15	1000	18009694	18008741	0
district19	1000	18008130	18007262	0
district11	1000	18008011	18006927	0
warehouse16	100	9008348	9008124	0
warehouse13	100	9007670	9007509	0
warehouse12	100	9007018	9006817	0
warehouse19	100	9006228	9006066	0
warehouse20	100	9006071	9005942	0
warehouse17	100	9005111	9004914	0
warehouse18	100	9004644	9004459	0
warehouse14	100	9002354	9002220	0
warehouse11	100	9000592	9000473	0
warehouse15	100	8999848	8999714	0
item20	100000	0	0	0
item13	100000	0	0	0
item3	100000	0	0	0
item9	100000	0	0	0
item19	100000	0	0	0
item7	100000	0	0	0
item12	100000	0	0	0
item17	100000	0	0	0
item5	100000	0	0	0
item2	100000	0	0	0
item16	100000	0	0	0
item8	100000	0	0	0
item18	100000	0	0	0
item4	100000	0	0	0
item10	100000	0	0	0
item15	100000	0	0	0
item14	100000	0	0	0
item1	100000	0	0	0
item11	100000	0	0	0
item6	100000	0	0	0

表的大小详情

newdb=# dt+

                    List of relations

Schema	Name	Type	Owner	Size
public	customer1	table	postgres	2123 MB
public	customer2	table	postgres	2120 MB
public	customer3	table	postgres	2124 MB
public	customer4	table	postgres	2124 MB
public	customer5	table	postgres	2122 MB
public	customer6	table	postgres	2121 MB
public	customer7	table	postgres	2121 MB
public	customer8	table	postgres	2123 MB
public	customer9	table	postgres	2124 MB
public	district1	table	postgres	6616 kB
public	district10	table	postgres	6528 kB
public	district2	table	postgres	6944 kB
public	district3	table	postgres	8240 kB
public	district4	table	postgres	6728 kB
public	district5	table	postgres	6664 kB
public	district6	table	postgres	6272 kB
public	district7	table	postgres	5160 kB
public	district8	table	postgres	6376 kB
public	district9	table	postgres	6360 kB
public	history1	table	postgres	6698 MB
public	history10	table	postgres	6699 MB
public	history2	table	postgres	6700 MB
public	history3	table	postgres	6699 MB
public	history4	table	postgres	6698 MB
public	history5	table	postgres	6697 MB
public	history6	table	postgres	6699 MB
public	history7	table	postgres	6699 MB
public	history8	table	postgres	6697 MB
public	history9	table	postgres	6697 MB
public	item1	table	postgres	11 MB
public	item10	table	postgres	11 MB
public	item2	table	postgres	11 MB
public	item3	table	postgres	11 MB
public	item4	table	postgres	11 MB
public	item5	table	postgres	11 MB
public	item6	table	postgres	11 MB
public	item7	table	postgres	11 MB
public	item8	table	postgres	11 MB
public	item9	table	postgres	11 MB
public	new_orders1	table	postgres	13 MB
public	new_orders10	table	postgres	12 MB
public	new_orders2	table	postgres	13 MB
public	new_orders3	table	postgres	13 MB
public	new_orders4	table	postgres	12 MB
public	new_orders5	table	postgres	13 MB
public	new_orders6	table	postgres	13 MB
public	new_orders7	table	postgres	13 MB
public	new_orders8	table	postgres	12 MB
public	new_orders9	table	postgres	13 MB
public	order_line1	table	postgres	82 GB
public	order_line10	table	postgres	82 GB
public	order_line2	table	postgres	82 GB
public	order_line3	table	postgres	82 GB
public	order_line4	table	postgres	82 GB
public	order_line5	table	postgres	82 GB
public	order_line6	table	postgres	82 GB
public	order_line7	table	postgres	82 GB
public	order_line8	table	postgres	83 GB
public	order_line9	table	postgres	83 GB
public	orders1	table	postgres	4632 MB
public	orders10	table	postgres	4630 MB
public	orders2	table	postgres	4631 MB
public	orders3	table	postgres	4631 MB
public	orders4	table	postgres	4631 MB
public	orders5	table	postgres	4631 MB
public	orders6	table	postgres	4631 MB
public	orders7	table	postgres	4631 MB
public	orders8	table	postgres	4632 MB
public	orders9	table	postgres	4631 MB
public	stock1	table	postgres	3806 MB
public	stock10	table	postgres	3806 MB
public	stock2	table	postgres	3806 MB
public	stock3	table	postgres	3819 MB
public	stock4	table	postgres	3809 MB
public	stock5	table	postgres	3809 MB
public	stock6	table	postgres	3808 MB
public	stock7	table	postgres	3808 MB
public	stock8	table	postgres	3809 MB
public	stock9	table	postgres	3815 MB
public	warehouse1	table	postgres	1144 kB
public	warehouse10	table	postgres	1328 kB
public	warehouse2	table	postgres	1144 kB
public	warehouse3	table	postgres	1160 kB
public	warehouse4	table	postgres	1104 kB
public	warehouse5	table	postgres	936 kB
public	warehouse6	table	postgres	1048 kB
public	warehouse7	table	postgres	1080 kB
public	warehouse8	table	postgres	1056 kB
public	warehouse9	table	postgres	1120 kB

2、1亿 tpcb 耐久测试
7*24小时测试

pgbench -i -s 1000 --tablespace=tbs1 --index-tablespace=tbs2

nohup pgbench -M prepared -v -r -P 1 -c 32 -j 32 -T 604800 >./pgbench.log1 2>&1 &
压测结束，表和库的大小如下

postgres=# l+

                                                           List of databases

Name	Owner	Encoding	Collate	Ctype	Access privileges	Size	Tablespace	Description
newdb	postgres	SQL_ASCII	C	C		23 MB	pg_default
postgres	postgres	SQL_ASCII	C	C		3217 GB	pg_default	default administrative connection database
template0	postgres	SQL_ASCII	C	C	=c/postgres +	7947 kB	pg_default	unmodifiable empty database
				postgres=CTc/postgres
template1	postgres	SQL_ASCII	C	C	=c/postgres +	7947 kB	pg_default	default template for new databases

(4 rows)

postgres=# dt+ pgbench_*

                      List of relations

Schema	Name	Type	Owner	Size
public	pgbench_accounts	table	postgres	13 GB
public	pgbench_branches	table	postgres	17 MB
public	pgbench_history	table	postgres	2546 GB
public	pgbench_tellers	table	postgres	174 MB

(4 rows)
过程记录

head -n 10 pgbench.log1
nohup: ignoring input
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 1.0 s, 65992.7 tps, lat 0.345 ms stddev 0.186
progress: 2.0 s, 87864.7 tps, lat 0.364 ms stddev 0.166
progress: 3.0 s, 87709.0 tps, lat 0.365 ms stddev 0.155
progress: 4.0 s, 88942.7 tps, lat 0.360 ms stddev 0.137
progress: 5.0 s, 88978.5 tps, lat 0.360 ms stddev 0.125
progress: 6.0 s, 89881.9 tps, lat 0.356 ms stddev 0.134
progress: 7.0 s, 89717.4 tps, lat 0.357 ms stddev 0.144
........
progress: 604792.0 s, 87531.8 tps, lat 0.366 ms stddev 0.112
progress: 604793.0 s, 88013.5 tps, lat 0.364 ms stddev 0.085
progress: 604794.0 s, 88438.9 tps, lat 0.362 ms stddev 0.119
progress: 604795.0 s, 87804.3 tps, lat 0.364 ms stddev 0.416
progress: 604796.0 s, 86275.8 tps, lat 0.371 ms stddev 0.103
progress: 604797.0 s, 86883.9 tps, lat 0.368 ms stddev 0.356
progress: 604798.0 s, 87882.8 tps, lat 0.364 ms stddev 0.071
progress: 604799.0 s, 87601.3 tps, lat 0.365 ms stddev 0.083
progress: 604800.0 s, 86404.5 tps, lat 0.369 ms stddev 0.091
transaction type:
scaling factor: 1000
query mode: prepared
number of clients: 32
number of threads: 32
duration: 604800 s
number of transactions actually processed: 52381795265
latency average = 0.369 ms
latency stddev = 0.800 ms
tps = 86610.109407 (including connections establishing)
tps = 86610.150411 (excluding connections establishing)
statement latencies in milliseconds:

     0.001  \set aid random(1, 100000 * :scale)  
     0.001  \set bid random(1, 1 * :scale)  
     0.001  \set tid random(1, 10 * :scale)  
     0.000  \set delta random(-5000, 5000)  
     0.026  BEGIN;  
     0.086  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;  
     0.046  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;  
     0.058  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;  
     0.057  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;  
     0.046  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);  
     0.049  END;

结果 8.66万 tps
7*24小时压测，数据库完成了523.8亿个事务，50几次freeze，性能平稳8.66万 tps 。

小结
1、1000万 tpcc测试，在16小时压测后，约1.8W tps，108 万tpmC。
由于TPCC不断有数据写入，29小时后，活跃数据量接近2TB，IO waiting成为瓶颈，TPS下降到1.3万左右。

2、1亿 tpcb测试，7天强压，性能平稳，完成了523.8亿个事务，平均 tps 8.66万
参考
《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab》

《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》

《PostgreSQL 11 100亿 tpcb 性能测试 on ECS》

https://github.com/digoal/sysbench-tpcc
转发自https://github.com/digoal/blog/blob/master/201809/20180920_01.md