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

  • 时间:
  • 浏览:1
  • 来源:彩神幸运飞艇_神彩幸运飞艇官方

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

2、初始化数据

shared_buffer=3000G

测试CASE :

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;

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.3300 ms stddev 0.137

progress: 5.0 s, 88978.5 tps, lat 0.3300 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: 3004792.0 s, 87531.8 tps, lat 0.366 ms stddev 0.112

progress: 3004793.0 s, 8300013.5 tps, lat 0.364 ms stddev 0.085

progress: 3004794.0 s, 88438.9 tps, lat 0.362 ms stddev 0.119

progress: 3004795.0 s, 8730004.3 tps, lat 0.364 ms stddev 0.416

progress: 3004796.0 s, 86275.8 tps, lat 0.371 ms stddev 0.103

progress: 3004797.0 s, 86883.9 tps, lat 0.368 ms stddev 0.356

progress: 3004798.0 s, 87882.8 tps, lat 0.364 ms stddev 0.071

progress: 3004799.0 s, 873001.3 tps, lat 0.365 ms stddev 0.083

progress: 300430000.0 s, 86404.5 tps, lat 0.369 ms stddev 0.091

transaction type:

scaling factor: 30000

query mode: prepared

number of clients: 32

number of threads: 32

duration: 300430000 s

number of transactions actually processed: 52381795265

latency average = 0.369 ms

latency stddev = 0.30000 ms

tps = 86610.109407 (including connections establishing)

tps = 86610.3000411 (excluding connections establishing)

statement latencies in milliseconds:

TPS下降主却说 活跃数据逐渐超过内存大小,IO waiting造成。

测试环境

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

结果 8.66万 tps

7*24小时压测,数据库完成了523.8亿个事务,3000十几个 freeze,性能平稳8.66万 tps 。

[ 300000s ] 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

[ 300001s ] thds: 64 tps: 2030001.67 qps: 583473.15 (r/w/o: 266219.73/27623000.08/430003.34) lat (ms,95%): 7.98 err/s 82.99 reconn/s: 0.00

[ 300002s ] 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

[ 300003s ] thds: 64 tps: 20233.62 qps: 570370.25 (r/w/o: 2300329.09/269574.92/40466.24) lat (ms,95%): 7.98 err/s 93.00 reconn/s: 0.00

[ 300004s ] thds: 64 tps: 20367.20 qps: 53000154.75 (r/w/o: 264423.62/274995.72/40735.40) lat (ms,95%): 7.98 err/s 96.00 reconn/s: 0.00

[ 300005s ] thds: 64 tps: 20411.02 qps: 585439.65 (r/w/o: 267331.300/277285.31/40823.05) lat (ms,95%): 7.98 err/s 3000.00 reconn/s: 0.00

[ 300006s ] thds: 64 tps: 20651.82 qps: 583243.91 (r/w/o: 2630004.68/275936.59/413002.64) lat (ms,95%): 7.98 err/s 73.00 reconn/s: 0.00

[ 300007s ] 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

[ 300008s ] 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

[ 300009s ] 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

[ 300010s ] thds: 64 tps: 20441.18 qps: 582812.00 (r/w/o: 265923.28/2730005.37/40883.35) lat (ms,95%): 7.84 err/s 85.00 reconn/s: 0.00

[ 300011s ] thds: 64 tps: 2030001.97 qps: 585269.16 (r/w/o: 267044.62/277220.300/430003.94) lat (ms,95%): 7.98 err/s 104.00 reconn/s: 0.00

[ 300012s ] 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

[ 300013s ] thds: 64 tps: 20411.52 qps: 53000261.93 (r/w/o: 264764.40/274674.3000/40823.03) lat (ms,95%): 7.84 err/s 106.05 reconn/s: 0.00

[ 300014s ] thds: 64 tps: 300070.84 qps: 573689.42 (r/w/o: 2616300.91/271918.83/40139.68) lat (ms,95%): 8.13 err/s 114.00 reconn/s: 0.00

[ 300015s ] thds: 64 tps: 203000.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

[ 300016s ] thds: 64 tps: 20317.84 qps: 578408.02 (r/w/o: 263900.96/273870.39/40636.68) lat (ms,95%): 7.98 err/s 3000.00 reconn/s: 0.00

[ 300017s ] 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

[ 300018s ] 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

[ 300019s ] thds: 64 tps: 20635.64 qps: 53000629.97 (r/w/o: 264969.20/274388.49/41272.28) lat (ms,95%): 7.84 err/s 3000.00 reconn/s: 0.00

[ 300020s ] 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

[ 300021s ] 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

[ 300022s ] thds: 64 tps: 20356.76 qps: 582531.18 (r/w/o: 265727.89/2730090.77/40712.52) lat (ms,95%): 7.98 err/s 110.00 reconn/s: 0.00

[ 300023s ] thds: 64 tps: 20382.28 qps: 53000142.43 (r/w/o: 264541.61/274833.26/40767.56) lat (ms,95%): 7.98 err/s 88.01 reconn/s: 0.00

[ 300024s ] thds: 64 tps: 20416.72 qps: 578534.32 (r/w/o: 263951.49/27373000.40/40832.44) lat (ms,95%): 7.98 err/s 99.99 reconn/s: 0.00

[ 300025s ] thds: 64 tps: 20476.3000 qps: 53000704.08 (r/w/o: 264754.29/274994.19/40955.300) lat (ms,95%): 7.98 err/s 90.01 reconn/s: 0.00

[ 300026s ] thds: 64 tps: 20440.61 qps: 583615.78 (r/w/o: 266265.88/276469.69/4083000.21) lat (ms,95%): 7.84 err/s 83.00 reconn/s: 0.00

[ 300027s ] thds: 64 tps: 20426.51 qps: 5300030003.41 (r/w/o: 264836.57/274813.82/40853.01) lat (ms,95%): 7.98 err/s 90.00 reconn/s: 0.00

[ 300028s ] thds: 64 tps: 20533.18 qps: 582233.85 (r/w/o: 26563000.44/275488.05/41065.37) lat (ms,95%): 7.98 err/s 88.00 reconn/s: 0.00

[ 300029s ] thds: 64 tps: 20316.48 qps: 5793000.3000 (r/w/o: 264125.29/274392.54/40632.97) lat (ms,95%): 7.98 err/s 94.00 reconn/s: 0.00

[ 3000300s ] 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

[ 300031s ] thds: 64 tps: 20575.51 qps: 586335.17 (r/w/o: 267565.300/277619.54/413000.03) lat (ms,95%): 7.84 err/s 88.93 reconn/s: 0.00

[ 300032s ] thds: 64 tps: 203300.22 qps: 579527.3000 (r/w/o: 264197.85/274671.20/40658.45) lat (ms,95%): 7.98 err/s 87.07 reconn/s: 0.00

[ 300033s ] thds: 64 tps: 20513.19 qps: 53000624.41 (r/w/o: 264746.47/27483000.56/41027.38) lat (ms,95%): 7.98 err/s 93.00 reconn/s: 0.00

[ 300034s ] thds: 64 tps: 20378.91 qps: 530003002.54 (r/w/o: 2648300.88/274713.83/40757.83) lat (ms,95%): 7.98 err/s 93.00 reconn/s: 0.00

[ 300035s ] thds: 64 tps: 20448.74 qps: 581127.58 (r/w/o: 26300028.61/2753000.48/40898.48) lat (ms,95%): 7.98 err/s 99.00 reconn/s: 0.00

[ 300036s ] 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

[ 300037s ] thds: 64 tps: 20255.29 qps: 581262.31 (r/w/o: 26300043.79/275707.94/40510.58) lat (ms,95%): 7.98 err/s 70.00 reconn/s: 0.00

[ 300038s ] thds: 64 tps: 20697.54 qps: 581712.56 (r/w/o: 265270.89/27300048.58/41393.09) lat (ms,95%): 7.84 err/s 95.93 reconn/s: 0.00

[ 300039s ] 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

[ 300040s ] thds: 64 tps: 204300.71 qps: 53000293.70 (r/w/o: 264518.22/274855.07/40920.41) lat (ms,95%): 7.84 err/s 78.00 reconn/s: 0.00

[ 300041s ] thds: 64 tps: 20321.56 qps: 578692.52 (r/w/o: 264122.31/273927.09/40643.12) lat (ms,95%): 7.98 err/s 3000.00 reconn/s: 0.00

[ 300042s ] thds: 64 tps: 300031.57 qps: 570578.33 (r/w/o: 2300355.45/270157.73/30065.15) lat (ms,95%): 8.13 err/s 85.00 reconn/s: 0.00

[ 300043s ] 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

[ 300044s ] thds: 64 tps: 19949.81 qps: 570346.51 (r/w/o: 2300248.3000/270198.40/39899.62) lat (ms,95%): 8.13 err/s 78.00 reconn/s: 0.00

[ 300045s ] thds: 64 tps: 20404.18 qps: 5749300.10 (r/w/o: 262190.33/2719300.41/4030009.36) lat (ms,95%): 7.98 err/s 73.00 reconn/s: 0.00

[ 300046s ] thds: 64 tps: 300059.300 qps: 571523.19 (r/w/o: 2300615.84/270788.14/40119.21) lat (ms,95%): 8.13 err/s 3000.00 reconn/s: 0.00

[ 300047s ] thds: 64 tps: 20106.65 qps: 574613.54 (r/w/o: 262414.44/271983.3000/40215.31) lat (ms,95%): 8.13 err/s 101.99 reconn/s: 0.00

[ 300048s ] thds: 64 tps: 300085.47 qps: 570847.83 (r/w/o: 2300594.07/270082.82/40170.93) lat (ms,95%): 8.13 err/s 86.00 reconn/s: 0.00

[ 300049s ] 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

[ 30003000s ] thds: 64 tps: 20205.93 qps: 571454.49 (r/w/o: 2300576.85/270466.79/40410.85) lat (ms,95%): 7.98 err/s 74.01 reconn/s: 0.00

[ 300051s ] thds: 64 tps: 2030001.31 qps: 581237.93 (r/w/o: 265126.07/275106.23/430005.63) lat (ms,95%): 7.98 err/s 82.00 reconn/s: 0.00

[ 300052s ] 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

截取运行至300314秒(约16小时)时的TPS: 1.6万左右。

2、1亿 tpcb

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

2、1亿 tpcb测试,五天强压,性能平稳, 完成了523.8亿个事务,平均 tps 8.66万

参考

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

newdb=# dt+

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=3000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --use_fk=0 prepare

nohup pgbench -M prepared -v -r -P 1 -c 32 -j 32 -T 300430000 >./pgbench.log1 2>&1 &

压测开始英文,表和库的大小如下

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

截取运行至300000秒(约2.2小时)时的TPS: 2万左右。

[ 300314s ] 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

[ 300315s ] thds: 64 tps: 19402.02 qps: 553901.95 (r/w/o: 252584.21/262512.71/3830005.03) lat (ms,95%): 8.28 err/s 93.00 reconn/s: 0.00

[ 300316s ] thds: 64 tps: 183001.57 qps: 519270.68 (r/w/o: 23630003.38/245865.17/363002.13) lat (ms,95%): 8.43 err/s 61.00 reconn/s: 0.00

[ 300317s ] thds: 64 tps: 16248.96 qps: 4300563.21 (r/w/o: 2300044.41/21300019.88/32498.92) lat (ms,95%): 8.74 err/s 81.00 reconn/s: 0.00

[ 300318s ] thds: 64 tps: 19251.90 qps: 53000591.20 (r/w/o: 251161.72/2300926.67/3830002.3000) lat (ms,95%): 8.43 err/s 96.00 reconn/s: 0.00

[ 300319s ] thds: 64 tps: 1930009.95 qps: 548363.3000 (r/w/o: 249973.55/259370.05/39019.90) lat (ms,95%): 8.28 err/s 73.00 reconn/s: 0.00

[ 300320s ] thds: 64 tps: 19543.97 qps: 549761.22 (r/w/o: 23000722.65/259949.63/39088.94) lat (ms,95%): 8.13 err/s 76.00 reconn/s: 0.00

[ 300321s ] thds: 64 tps: 19363.48 qps: 549986.10 (r/w/o: 23000868.20/2300390.95/38726.95) lat (ms,95%): 8.28 err/s 82.00 reconn/s: 0.00

[ 300322s ] thds: 64 tps: 19371.95 qps: 551287.99 (r/w/o: 251553.31/2300992.78/38741.90) lat (ms,95%): 8.28 err/s 3000.00 reconn/s: 0.00

[ 300323s ] thds: 64 tps: 15691.03 qps: 452727.03 (r/w/o: 2066300.23/214713.73/31383.06) lat (ms,95%): 8.90 err/s 64.00 reconn/s: 0.00

[ 300324s ] thds: 64 tps: 16274.47 qps: 45300086.44 (r/w/o: 208798.89/216740.61/32546.94) lat (ms,95%): 8.90 err/s 61.01 reconn/s: 0.00

[ 300325s ] thds: 64 tps: 19378.81 qps: 53000473.61 (r/w/o: 251081.54/2300633.45/38758.62) lat (ms,95%): 8.28 err/s 77.00 reconn/s: 0.00

[ 300326s ] 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

[ 300327s ] thds: 64 tps: 16284.20 qps: 461247.29 (r/w/o: 21053000.59/21300099.29/32567.40) lat (ms,95%): 8.74 err/s 73.87 reconn/s: 0.00

[ 300328s ] thds: 64 tps: 18873.26 qps: 533919.22 (r/w/o: 24330002.03/252668.67/37748.52) lat (ms,95%): 8.28 err/s 84.15 reconn/s: 0.00

[ 300329s ] thds: 64 tps: 19181.300 qps: 548828.91 (r/w/o: 23000310.84/2300155.48/38362.300) lat (ms,95%): 8.28 err/s 73.00 reconn/s: 0.00

[ 3003300s ] 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

[ 300331s ] thds: 64 tps: 15370.300 qps: 436963.74 (r/w/o: 199441.86/206782.67/300739.21) lat (ms,95%): 8.90 err/s 58.00 reconn/s: 0.00

[ 300332s ] 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

[ 300333s ] thds: 64 tps: 19166.300 qps: 542122.53 (r/w/o: 247496.90/256293.03/38332.300) lat (ms,95%): 8.28 err/s 75.00 reconn/s: 0.00

[ 300334s ] thds: 64 tps: 1730008.04 qps: 511114.51 (r/w/o: 2332300.46/242268.97/35615.08) lat (ms,95%): 8.74 err/s 89.00 reconn/s: 0.00

[ 300335s ] thds: 64 tps: 15754.88 qps: 447359.62 (r/w/o: 2041300.46/211719.40/330009.76) lat (ms,95%): 8.90 err/s 64.00 reconn/s: 0.00

[ 300336s ] thds: 64 tps: 18478.48 qps: 529327.12 (r/w/o: 241701.20/23000668.95/36956.96) lat (ms,95%): 8.74 err/s 73.00 reconn/s: 0.00

[ 300337s ] thds: 64 tps: 19124.11 qps: 544896.11 (r/w/o: 248318.39/2583300.3000/38247.22) lat (ms,95%): 8.43 err/s 73.01 reconn/s: 0.00

[ 300338s ] thds: 64 tps: 16187.05 qps: 462672.85 (r/w/o: 211196.76/2193000.00/32376.09) lat (ms,95%): 9.06 err/s 70.92 reconn/s: 0.00

[ 300339s ] thds: 64 tps: 1300098.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

[ 300340s ] 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

[ 300341s ] thds: 64 tps: 16571.76 qps: 474637.18 (r/w/o: 2166300.89/224831.77/33144.52) lat (ms,95%): 8.74 err/s 68.00 reconn/s: 0.00

[ 300342s ] thds: 64 tps: 15923.79 qps: 430003000.13 (r/w/o: 205297.32/212955.22/31847.58) lat (ms,95%): 8.74 err/s 69.00 reconn/s: 0.00

[ 300343s ] thds: 64 tps: 18764.65 qps: 537077.94 (r/w/o: 245110.41/254437.23/375300.300) lat (ms,95%): 8.43 err/s 81.00 reconn/s: 0.00

截取运行至105235秒(约29小时)时的TPS: 1.3万左右

测试时长五天。

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

postgres=# l+

postgres=# dt+ pgbench_*

统计信息

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=3000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --use_fk=0 prepare

3、持续7*24小时,强压耐久测试

[ 105235s ] thds: 64 tps: 10971.14 qps: 310702.84 (r/w/o: 141931.3000/146827.05/21944.300) 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: 2130038.40/223008.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: 1973004.49/204981.02/300770.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: 1130071.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: 33000988.62 (r/w/o: 173814.18/13000351.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/13463000.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: 3000672.14 (r/w/o: 137243.92/142125.11/213003.11) lat (ms,95%): 10.46 err/s 35.91 reconn/s: 0.00

[ 105248s ] thds: 64 tps: 143004.300 qps: 412991.62 (r/w/o: 188383.3000/195300.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: 2153000.82/223720.40/33543.02) lat (ms,95%): 9.39 err/s 79.98 reconn/s: 0.00

[ 10523000s ] thds: 64 tps: 11177.31 qps: 318115.91 (r/w/o: 145144.34/3000616.96/22354.62) lat (ms,95%): 10.84 err/s 3000.58 reconn/s: 0.00

[ 105251s ] thds: 64 tps: 8944.08 qps: 2523004.23 (r/w/o: 115208.77/11930006.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.3000/159798.39/24172.09) lat (ms,95%): 9.91 err/s 53.00 reconn/s: 0.00

[ 105253s ] thds: 64 tps: 16202.94 qps: 4630033.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: 4139300.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: 17300052.57/184427.73/27755.71) lat (ms,95%): 10.09 err/s 3000.00 reconn/s: 0.00

[ 105256s ] thds: 64 tps: 15102.61 qps: 428525.3000 (r/w/o: 195790.88/202531.71/300203.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: 2113000.68/2183001.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/1330022.16/20440.42) lat (ms,95%): 10.46 err/s 53.00 reconn/s: 0.00

[ 105259s ] thds: 64 tps: 3986.03 qps: 114032.3000 (r/w/o: 52087.37/53973.38/7972.06) lat (ms,95%): 13.46 err/s 14.00 reconn/s: 0.00

[ 1052300s ] thds: 64 tps: 153007.79 qps: 434108.13 (r/w/o: 197882.33/2053009.22/300616.59) lat (ms,95%): 9.73 err/s 77.00 reconn/s: 0.00

[ 105261s ] thds: 64 tps: 103300.59 qps: 297243.3000 (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: 18300077.40/192025.08/28516.67) lat (ms,95%): 9.91 err/s 64.00 reconn/s: 0.00

[ 105265s ] thds: 64 tps: 1513000.17 qps: 431972.85 (r/w/o: 197029.21/204583.29/3003300.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/1130004.52/16766.22) lat (ms,95%): 12.300 err/s 26.00 reconn/s: 0.00

[ 105269s ] thds: 64 tps: 15655.17 qps: 443939.57 (r/w/o: 202572.09/2300057.13/31310.34) lat (ms,95%): 9.56 err/s 71.01 reconn/s: 0.00

[ 105270s ] thds: 64 tps: 11207.84 qps: 320791.3000 (r/w/o: 1463006.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: 10300044.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: 3002372.04 (r/w/o: 13300055.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/3230009.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/13003000.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/222300.53) lat (ms,95%): 10.65 err/s 45.26 reconn/s: 0.00

[ 105279s ] thds: 64 tps: 11934.79 qps: 34130004.12 (r/w/o: 155904.32/162031.23/23868.57) lat (ms,95%): 10.27 err/s 44.01 reconn/s: 0.00

[ 10523000s ] thds: 64 tps: 14279.68 qps: 300515.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: 3006898.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: 300073.77 qps: 431435.46 (r/w/o: 196872.01/204415.90/300147.54) lat (ms,95%): 9.73 err/s 300.00 reconn/s: 0.00

[ 105283s ] thds: 64 tps: 10182.07 qps: 28300077.44 (r/w/o: 1330008.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/300233.74) lat (ms,95%): 9.73 err/s 54.00 reconn/s: 0.00

[ 105286s ] thds: 64 tps: 130076.53 qps: 455528.33 (r/w/o: 20730008.99/215567.28/32152.06) lat (ms,95%): 9.39 err/s 3000.99 reconn/s: 0.00

[ 105287s ] thds: 64 tps: 16653.26 qps: 476688.40 (r/w/o: 217349.38/2230031.51/333007.52) lat (ms,95%): 9.22 err/s 69.00 reconn/s: 0.00

[ 105288s ] thds: 64 tps: 130093.48 qps: 371458.36 (r/w/o: 16953000.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。

小结

1、30000万 tpcc测试,在16小时压测后,约1.8W tps,108 万tpmC。

愿因着TPCC不断有数据写入,29小时后,活跃数据量接近2TB,IO waiting成为瓶颈,TPS下降到1.3万左右。

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

转发自https://github.com/digoal/blog/blob/master/20130009/2013000920_01.md

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

4、shell

1、30000万 tpcc

2、1亿 tpcb 耐久测试

7*24小时测试

./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=newdb --threads=64 --tables=10 --scale=3000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --time=300430000 --report-interval=1 --enable_purge=no run

结果

1、初始化开始英文时,3000多GB。

use huge page

PS:

实际上最初测试的是TPCC 2亿耐久(2.5TB左右的活跃数据),发现压力基本上全部都有IO上(愿因着活跃数据2.5TB,已远远超出机器的内存),平均TPS 3千(16万 tpmC)。

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

背景

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

(4 rows)

过程记录

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

./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=newdb --threads=64 --tables=10 --scale=3000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --time=300430000 --report-interval=1 --enable_purge=no run

初始化开始英文时,3000多GB。

postgres=# l+

(4 rows)

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

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

表的大小详情

1、30000万 tpcc 耐久测试

1、清空

那么IO瓶颈时,TPS在2万左右。

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