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

  • 时间:
  • 浏览:0
  • 来源:5分6合APP下载_5分6合APP官方

1、2000万 tpcc 耐久测试

1、清空

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

2、1亿 tpcb 耐久测试

7*24小时测试

2、1亿 tpcb

小结

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

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

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

参考

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

[ 105235s ] thds: 64 tps: 10971.14 qps: 310702.84 (r/w/o: 141931.200/146827.05/21944.200) 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: 2120038.40/222008.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: 1972004.49/204981.02/200770.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: 1120071.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: 3200988.62 (r/w/o: 173814.18/1200351.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/1346200.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: 200672.14 (r/w/o: 137243.92/142125.11/212003.11) lat (ms,95%): 10.46 err/s 35.91 reconn/s: 0.00

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

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

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

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

[ 105256s ] thds: 64 tps: 15102.61 qps: 428525.200 (r/w/o: 195790.88/202531.71/200203.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: 211200.68/2182001.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/1320022.16/20440.42) lat (ms,95%): 10.46 err/s 53.00 reconn/s: 0.00

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

[ 1052200s ] thds: 64 tps: 152007.79 qps: 434108.13 (r/w/o: 197882.33/2052009.22/200616.59) lat (ms,95%): 9.73 err/s 77.00 reconn/s: 0.00

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

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

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

[ 105270s ] thds: 64 tps: 11207.84 qps: 320791.200 (r/w/o: 1462006.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: 1020044.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: 2002372.04 (r/w/o: 1320055.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/322009.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/1200200.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/222200.53) lat (ms,95%): 10.65 err/s 45.26 reconn/s: 0.00

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

[ 1052200s ] thds: 64 tps: 14279.68 qps: 200515.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: 2006898.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: 20073.77 qps: 431435.46 (r/w/o: 196872.01/204415.90/200147.54) lat (ms,95%): 9.73 err/s 200.00 reconn/s: 0.00

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

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

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

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

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

压测现在刚刚刚开使,表和库的大小如下

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

转发自https://github.com/digoal/blog/blob/master/2012009/201200920_01.md

测试环境

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

newdb=# dt+

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

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

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

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

progress: 2004793.0 s, 820013.5 tps, lat 0.364 ms stddev 0.085

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

progress: 2004795.0 s, 872004.3 tps, lat 0.364 ms stddev 0.416

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

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

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

progress: 2004799.0 s, 872001.3 tps, lat 0.365 ms stddev 0.083

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

transaction type:

scaling factor: 2000

query mode: prepared

number of clients: 32

number of threads: 32

duration: 20042000 s

number of transactions actually processed: 52381795265

latency average = 0.369 ms

latency stddev = 0.2000 ms

tps = 86610.109407 (including connections establishing)

tps = 86610.200411 (excluding connections establishing)

statement latencies in milliseconds:

(4 rows)

./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=newdb --threads=64 --tables=10 --scale=200 --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=200 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --use_fk=0 prepare

结果 8.615万 tps

7*24小时压测,数据库完成了523.8亿个事务,200好多个freeze,性能平稳8.615万 tps 。

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

结果

1、初始化现在刚刚刚开使时,200多GB。

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

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

背景

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

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

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

[ 200316s ] thds: 64 tps: 182001.57 qps: 519270.68 (r/w/o: 2362003.38/245865.17/362002.13) lat (ms,95%): 8.43 err/s 61.00 reconn/s: 0.00

[ 200317s ] thds: 64 tps: 16248.96 qps: 4200563.21 (r/w/o: 220044.41/2120019.88/32498.92) lat (ms,95%): 8.74 err/s 81.00 reconn/s: 0.00

[ 200318s ] thds: 64 tps: 19251.90 qps: 5200591.20 (r/w/o: 251161.72/2200926.67/382002.200) lat (ms,95%): 8.43 err/s 96.00 reconn/s: 0.00

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

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

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

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

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

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

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

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

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

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

[ 200329s ] thds: 64 tps: 19181.200 qps: 548828.91 (r/w/o: 2200310.84/2200155.48/38362.200) lat (ms,95%): 8.28 err/s 73.00 reconn/s: 0.00

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

统计信息

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

(4 rows)

过程记录

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

shared_buffer=200G

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

初始化现在刚刚刚开使时,200多GB。

use huge page

PS:

实际上最初测试的是TPCC 2亿耐久(2.5TB左右的活跃数据),发现压力基本上后会IO上(肯能活跃数据2.5TB,已远远超出机器的内存),平均TPS 3千(115万 tpmC)。

没法IO瓶颈时,TPS在2万左右。

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

2、初始化数据

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

postgres=# dt+ pgbench_*

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;

测试时长四天。

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

[ 20001s ] thds: 64 tps: 202001.67 qps: 583473.15 (r/w/o: 266219.73/2762200.08/42003.34) lat (ms,95%): 7.98 err/s 82.99 reconn/s: 0.00

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

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

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

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

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

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

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

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

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

[ 20011s ] thds: 64 tps: 202001.97 qps: 585269.16 (r/w/o: 267044.62/277220.200/42003.94) lat (ms,95%): 7.98 err/s 104.00 reconn/s: 0.00

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

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

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

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

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

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

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

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

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

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

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

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

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

[ 20025s ] thds: 64 tps: 20476.200 qps: 5200704.08 (r/w/o: 264754.29/274994.19/40955.200) lat (ms,95%): 7.98 err/s 90.01 reconn/s: 0.00

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[ 20042s ] thds: 64 tps: 20031.57 qps: 570578.33 (r/w/o: 2200355.45/270157.73/20065.15) lat (ms,95%): 8.13 err/s 85.00 reconn/s: 0.00

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

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

[ 20045s ] thds: 64 tps: 20404.18 qps: 5749200.10 (r/w/o: 262190.33/2719200.41/402009.36) lat (ms,95%): 7.98 err/s 73.00 reconn/s: 0.00

[ 20046s ] thds: 64 tps: 20059.200 qps: 571523.19 (r/w/o: 2200615.84/270788.14/40119.21) lat (ms,95%): 8.13 err/s 200.00 reconn/s: 0.00

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

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

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

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

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

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

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

4、shell

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

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

1、2000万 tpcc

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

postgres=# l+

postgres=# l+

表的大小详情

测试CASE :

猜你喜欢

日志服务在CDN流量指标中的异常检测实战(上)

企业级WebNginx服务优化(5)【CDN排查方案-1】认识CDN网络调优SLS机器学习最佳实战:时序异常检测和报警版权声明:本文内容由互联网用户自发贡献,版权归作者所有,本

2020-02-19

各类机器学习问题的最优结果合集!附论文及实现地址索引

论文:DYNAMICEVALUATIONOFNEURALSEQUENCEMODELS一、NLP该GitHub库提供了所有机器学习问题图片的当前最优结果,并尽最大努力保证该库是最

2020-02-19

游客7zcwupywj7vuy的主页

文章:15丨粉丝:62624丨话题:0阿里云计算Kubernetes、Docker、调度、上方件阿里云容器服务管控调度系统开发文章:0丨粉丝:62623丨话题:0云栖运营小编~

2020-02-19

与word2vector相关精彩内容

作者:revolution2u1872人浏览loggerxmlutf-8wikigensimword2vector说明源码github地址:https://github.com

2020-02-19

工商银行借记卡只能办一张么

前要注意的是,Ⅱ、Ⅲ类账户具有有限功能,只有账号而只有实体卡,且只有与Ⅰ类户绑定使用。展开删改Ⅰ类户是全功能账户,能只有办理存款、转账、消费缴费、购买投资理财产品等,使用范围和

2020-02-19