PostgreSQL 11 1Kw TPCC , 1亿 TPCB 7*24 强压耐久测试

29 minute read

背景

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小时,强压耐久测试

./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 --time=604800 --report-interval=1 --enable_purge=no run      

初始化结束时,100多GB。

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    
    
    
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    
    
./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 --time=604800 --report-interval=1 --enable_purge=no run      

结果

1、初始化结束时,100多GB。

截取运行至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   | Description     
--------+--------------+-------+----------+---------+-------------    
 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  
           |          |           |         |       | postgres=CTc/postgres |         |            |   
(4 rows)  
  
postgres=# \dt+ pgbench_*  
                          List of relations  
 Schema |       Name       | Type  |  Owner   |  Size   | Description   
--------+------------------+-------+----------+---------+-------------  
 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: <builtin: TPC-B (sort of)>  
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

Flag Counter

digoal’s 大量PostgreSQL文章入口