本篇内容介绍了“PostgreSQL11 tpcb性能测试分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1、postgresql.auto.conf

listen_addresses='0.0.0.0'port=1921max_connections=2000superuser_reserved_connections=13unix_socket_directories='.,/var/run/postgresql,/tmp'tcp_keepalives_idle=60tcp_keepalives_interval=10tcp_keepalives_count=10shared_buffers=64GBmax_prepared_transactions=2000work_mem=8MBmaintenance_work_mem=2GBdynamic_shared_memory_type=posixvacuum_cost_delay=0bgwriter_delay=10msbgwriter_lru_maxpages=1000bgwriter_lru_multiplier=10.0effective_io_concurrency=0max_worker_processes=128max_parallel_maintenance_workers=24max_parallel_workers_per_gather=0parallel_leader_participation=onmin_parallel_table_scan_size=0min_parallel_index_scan_size=0parallel_setup_cost=0parallel_tuple_cost=0max_parallel_workers=64wal_level=minimalsynchronous_commit=offwal_writer_delay=10mscheckpoint_timeout=35minmax_wal_size=128GBmin_wal_size=32GBcheckpoint_completion_target=0.1max_wal_senders=0effective_cache_size=400GBlog_destination='csvlog'logging_collector=onlog_directory='log'log_filename='postgresql-%a.log'log_truncate_on_rotation=onlog_rotation_age=1dlog_rotation_size=0log_checkpoints=onlog_connections=onlog_disconnections=onlog_error_verbosity=verboselog_line_prefix='%m[%p]'log_timezone='PRC'log_autovacuum_min_duration=0autovacuum_max_workers=16autovacuum_freeze_max_age=1200000000autovacuum_multixact_freeze_max_age=1400000000autovacuum_vacuum_cost_delay=0msvacuum_freeze_table_age=1150000000vacuum_multixact_freeze_table_age=1150000000datestyle='iso,mdy'timezone='PRC'lc_messages='C'lc_monetary='C'lc_numeric='C'lc_time='C'default_text_search_config='pg_catalog.english'jit=offcpu_tuple_cost=0.00018884145574257426cpu_index_tuple_cost=0.00433497085216479990cpu_operator_cost=0.00216748542608239995seq_page_cost=0.014329random_page_cost=0.016测试初始100亿数据

使用pgbench 初始化100亿tpcb测试数据。

man pgbench

-i--initializeRequiredtoinvokeinitializationmode.-Iinit_steps--init-steps=init_stepsPerformjustaselectedsetofthenormalinitializationsteps.init_stepsspecifiestheinitializationstepstobeperformed,usingonecharacterperstep.Eachstepisinvokedinthespecifiedorder.Thedefaultisdtgvp.Theavailablestepsare:d(Drop)Dropanyexistingpgbenchtables.t(createTables)Createthetablesusedbythestandardpgbenchscenario,namelypgbench_accounts,pgbench_branches,pgbench_history,andpgbench_tellers.g(Generatedata)Generatedataandloaditintothestandardtables,replacinganydataalreadypresent.v(Vacuum)InvokeVACUUMonthestandardtables.p(createPrimarykeys)Createprimarykeyindexesonthestandardtables.f(createForeignkeys)Createforeignkeyconstraintsbetweenthestandardtables.(Notethatthisstepisnotperformedbydefault.)

初始化

pgbench-i-s100000-Idtg-n--tablespace=tbs2

耗时8385秒，约119万行/s。

9998500000of10000000000tuples(99%)done(elapsed8384.23s,remaining1.26s)9998600000of10000000000tuples(99%)done(elapsed8384.31s,remaining1.17s)9998700000of10000000000tuples(99%)done(elapsed8384.40s,remaining1.09s)9998800000of10000000000tuples(99%)done(elapsed8384.48s,remaining1.01s)9998900000of10000000000tuples(99%)done(elapsed8384.56s,remaining0.92s)9999000000of10000000000tuples(99%)done(elapsed8384.65s,remaining0.84s)9999100000of10000000000tuples(99%)done(elapsed8384.73s,remaining0.75s)9999200000of10000000000tuples(99%)done(elapsed8384.82s,remaining0.67s)9999300000of10000000000tuples(99%)done(elapsed8384.90s,remaining0.59s)9999400000of10000000000tuples(99%)done(elapsed8384.98s,remaining0.50s)9999500000of10000000000tuples(99%)done(elapsed8385.07s,remaining0.42s)9999600000of10000000000tuples(99%)done(elapsed8385.15s,remaining0.34s)9999700000of10000000000tuples(99%)done(elapsed8385.24s,remaining0.25s)9999800000of10000000000tuples(99%)done(elapsed8385.33s,remaining0.17s)9999900000of10000000000tuples(99%)done(elapsed8385.41s,remaining0.08s)10000000000of10000000000tuples(100%)done(elapsed8385.49s,remaining0.00s)创建索引

1、强制设置并行度为24，a,b,c,d取最小

altertablepgbench_accountsset(parallel_workers=32);#a

max_worker_processes=128#bmax_parallel_maintenance_workers=24#cmax_parallel_workers=64#dmax_parallel_workers_per_gather=0min_parallel_table_scan_size=0min_parallel_index_scan_size=0parallel_setup_cost=0parallel_tuple_cost=0

2、创建tpcb数据表索引，100亿记录，创建索引耗时44分钟。

pgbench-i-Ip--index-tablespace=tbs1

IO监测

TotalDISKREAD:2.77G/s|TotalDISKWRITE:2.86G/sActualDISKREAD:2.79G/s|ActualDISKWRITE:1795.02M/sTIDPRIOUSERDISKREADDISKWRITESWAPINIO>COMMAND13683be/4postgres142.87M/s141.55M/s0.00%1.47%postgres:parallelworkerforPID1365513684be/4postgres114.55M/s124.78M/s0.00%1.07%postgres:parallelworkerforPID1365513686be/4postgres163.22M/s165.50M/s0.00%0.64%postgres:parallelworkerforPID1365513671be/4postgres162.38M/s165.76M/s0.00%0.49%postgres:parallelworkerforPID1365513676be/4postgres169.80M/s169.31M/s0.00%0.45%postgres:parallelworkerforPID1365513672be/4postgres160.74M/s165.52M/s0.00%0.41%postgres:parallelworkerforPID1365513685be/4postgres174.74M/s177.10M/s0.00%0.37%postgres:parallelworkerforPID1365513687be/4postgres172.48M/s173.00M/s0.00%0.36%postgres:parallelworkerforPID1365513677be/4postgres116.31M/s124.28M/s0.00%0.35%postgres:parallelworkerforPID1365513680be/4postgres165.52M/s177.19M/s0.00%0.34%postgres:parallelworkerforPID1365513674be/4postgres167.31M/s169.44M/s0.00%0.12%postgres:parallelworkerforPID1365513655be/4postgres114.55M/s122.85M/s0.00%0.05%postgres:postgrespostgres[local]ALTERTABLE13678be/4postgres175.54M/s177.10M/s0.00%0.04%postgres:parallelworkerforPID1365513670be/4postgres127.29M/s126.61M/s0.00%0.01%postgres:parallelworkerforPID1365513682be/4postgres162.41M/s165.74M/s0.00%0.00%postgres:parallelworkerforPID1365513673be/4postgres145.02M/s152.94M/s0.00%0.00%postgres:parallelworkerforPID1365513675be/4postgres167.42M/s169.41M/s0.00%0.00%postgres:parallelworkerforPID1365513679be/4postgres117.00M/s128.97M/s0.00%0.00%postgres:parallelworkerforPID1365513681be/4postgres121.97M/s132.34M/s0.00%0.00%postgres:parallelworkerforPID1365513642be/4postgres0.00B/s0.00B/s0.00%0.00%postgres:logicalreplicationlauncher13634be/4postgres0.00B/s0.00B/s0.00%0.00%postgres13635be/4postgres0.00B/s0.00B/s0.00%0.00%postgres:logger13637be/4postgres0.00B/s0.00B/s0.00%0.00%postgres:checkpointer13638be/4postgres0.00B/s0.00B/s0.00%0.00%postgres:backgroundwriter13639be/4postgres0.00B/s0.00B/s0.00%0.00%postgres:walwriter13640be/4postgres0.00B/s0.00B/s0.00%0.00%postgres:autovacuumlauncher13641be/4postgres0.00B/s0.00B/s0.00%0.00%postgres:statscollector

耗时，43分50秒左右。

selectnow()-query_start,queryfrompg_stat_activitywherequery~'primarykey';\watch3Sun16Sep201805:53:13PMCST(every3s)?column?|query-----------------+----------------------------------------------------------------------------------00:43:49.994922|altertablepgbench_accountsaddprimarykey(aid)usingindextablespace"tbs1"00:00:00|selectnow()-query_start,queryfrompg_stat_activitywherequery~'tbs1';(2rows)Sun16Sep201805:53:16PMCST(every3s)?column?|query----------+----------------------------------------------------------------------------00:00:00|selectnow()-query_start,queryfrompg_stat_activitywherequery~'tbs1';(1row)tpcb 100亿，表、索引大小

postgres=#\l+postgresListofdatabasesName|Owner|Encoding|Collate|Ctype|Accessprivileges|Size|Tablespace|Description----------+----------+-----------+---------+-------+-------------------+---------+------------+--------------------------------------------postgres|postgres|SQL_ASCII|C|C||2599GB|pg_default|defaultadministrativeconnectiondatabase(1row)postgres=#\dt+pgbench*ListofrelationsSchema|Name|Type|Owner|Size|Description--------+------------------+-------+----------+---------+-------------public|pgbench_accounts|table|postgres|1251GB|public|pgbench_branches|table|postgres|3576kB|public|pgbench_history|table|postgres|0bytes|public|pgbench_tellers|table|postgres|42MB|(4rows)postgres=#\di+pgbench*ListofrelationsSchema|Name|Type|Owner|Table|Size|Description--------+-----------------------+-------+----------+------------------+---------+-------------public|pgbench_accounts_pkey|index|postgres|pgbench_accounts|209GB|public|pgbench_branches_pkey|index|postgres|pgbench_branches|2208kB|public|pgbench_tellers_pkey|index|postgres|pgbench_tellers|21MB|(3rows)

这里建议pgbench_accounts用 pg_pathman hash 分区。

测试

pgbench-Mprepared-v-r-P1-c64-j64-T3600-S

结果

.........progress:3550.0s,131287.4tps,lat0.492msstddev5.895progress:3551.0s,117855.0tps,lat0.543msstddev7.239progress:3552.0s,102975.0tps,lat0.527msstddev6.052progress:3553.0s,128299.1tps,lat0.575msstddev7.191progress:3554.0s,122215.5tps,lat0.522msstddev6.947progress:3555.0s,92727.5tps,lat0.689msstddev9.055progress:3556.0s,144550.4tps,lat0.433msstddev3.430progress:3557.0s,95982.1tps,lat0.565msstddev7.052progress:3558.0s,118369.9tps,lat0.638msstddev9.017progress:3559.0s,128757.4tps,lat0.497msstddev5.789progress:3560.0s,99634.5tps,lat0.529msstddev6.252progress:3561.0s,118071.4tps,lat0.635msstddev8.541progress:3562.0s,131764.3tps,lat0.488msstddev5.816progress:3563.0s,118408.9tps,lat0.540msstddev7.308progress:3564.0s,97566.1tps,lat0.538msstddev6.447progress:3565.0s,110077.0tps,lat0.686msstddev9.656progress:3566.0s,128585.3tps,lat0.498msstddev5.901progress:3567.0s,116302.4tps,lat0.514msstddev6.269progress:3568.0s,111013.7tps,lat0.615msstddev7.788progress:3569.0s,116588.9tps,lat0.549msstddev7.378progress:3570.0s,118875.0tps,lat0.538msstddev6.716progress:3571.0s,124222.9tps,lat0.514msstddev6.566progress:3572.0s,93014.1tps,lat0.551msstddev6.824progress:3573.0s,120148.7tps,lat0.639msstddev8.537progress:3574.0s,131078.1tps,lat0.488msstddev5.765progress:3575.0s,105341.2tps,lat0.509msstddev5.548progress:3576.0s,121529.8tps,lat0.612msstddev8.044progress:3577.0s,129391.4tps,lat0.479msstddev5.173progress:3578.0s,86950.4tps,lat0.612msstddev8.670progress:3579.0s,124735.8tps,lat0.616msstddev8.173progress:3580.0s,120284.6tps,lat0.532msstddev6.892progress:3581.0s,96491.2tps,lat0.559msstddev7.261progress:3582.0s,121068.8tps,lat0.611msstddev8.041progress:3583.0s,137346.3tps,lat0.466msstddev4.567progress:3584.0s,104130.5tps,lat0.526msstddev6.362progress:3585.0s,114540.8tps,lat0.639msstddev8.500progress:3586.0s,128416.6tps,lat0.499msstddev5.967progress:3587.0s,106762.0tps,lat0.527msstddev6.358progress:3588.0s,102988.7tps,lat0.697msstddev9.452progress:3589.0s,131513.3tps,lat0.487msstddev5.648progress:3590.0s,116013.7tps,lat0.508msstddev6.041progress:3591.0s,109295.3tps,lat0.632msstddev9.098progress:3592.0s,113581.1tps,lat0.493msstddev5.331progress:3593.0s,126594.3tps,lat0.569msstddev6.812progress:3594.0s,116212.3tps,lat0.551msstddev7.886progress:3595.0s,95710.3tps,lat0.532msstddev6.368progress:3596.0s,125784.2tps,lat0.613msstddev8.003progress:3597.0s,119773.1tps,lat0.534msstddev6.934progress:3598.0s,97862.0tps,lat0.560msstddev7.001progress:3599.0s,113585.9tps,lat0.644msstddev8.661progress:3600.0s,128750.5tps,lat0.496msstddev5.801transactiontype:<builtin:selectonly>scalingfactor:100000querymode:preparednumberofclients:64numberofthreads:64duration:3600snumberoftransactionsactuallyprocessed:424993812latencyaverage=0.542mslatencystddev=6.751mstps=118053.277351(includingconnectionsestablishing)tps=118054.214576(excludingconnectionsestablishing)statementlatenciesinmilliseconds:0.001\setaidrandom(1,100000*:scale)0.542SELECTabalanceFROMpgbench_accountsWHEREaid=:aid;

IO监测

TotalDISKREAD:4.90G/s|TotalDISKWRITE:0.00B/sActualDISKREAD:4.90G/s|ActualDISKWRITE:0.00B/sTIDPRIOUSERDISKREADDISKWRITESWAPINIO>COMMAND14357be/4postgres74.24M/s0.00B/s0.00%43.98%postgres:postgrespostgres[local]SELECT14257be/4postgres88.29M/s0.00B/s0.00%42.37%postgres:postgrespostgres[local]idle14318be/4postgres75.55M/s0.00B/s0.00%41.86%postgres:postgrespostgres[local]SELECT14322be/4postgres73.42M/s0.00B/s0.00%41.53%postgres:postgrespostgres[local]SELECT14356be/4postgres77.37M/s0.00B/s0.00%41.49%postgres:postgrespostgres[local]idle14319be/4postgres90.94M/s0.00B/s0.00%41.41%postgres:postgrespostgres[local]SELECT14307be/4postgres83.40M/s0.00B/s0.00%41.40%postgres:postgrespostgres[local]SELECT14327be/4postgres84.56M/s0.00B/s0.00%41.33%postgres:postgrespostgres[local]SELECT14351be/4postgres92.47M/s0.00B/s0.00%41.14%postgres:postgrespostgres[local]SELECT14333be/4postgres84.52M/s0.00B/s0.00%40.96%postgres:postgrespostgres[local]SELECT14352be/4postgres85.67M/s0.00B/s0.00%40.85%postgres:postgrespostgres[local]SELECT14301be/4postgres74.16M/s0.00B/s0.00%40.82%postgres:postgrespostgres[local]SELECT14348be/4postgres78.53M/s0.00B/s0.00%40.52%postgres:postgrespostgres[local]SELECT14358be/4postgres75.76M/s0.00B/s0.00%40.50%postgres:postgrespostgres[local]SELECT14353be/4postgres81.66M/s0.00B/s0.00%40.47%postgres:postgrespostgres[local]SELECT14317be/4postgres82.71M/s0.00B/s0.00%40.28%postgres:postgrespostgres[local]SELECT14347be/4postgres86.47M/s0.00B/s0.00%40.25%postgres:postgrespostgres[local]SELECT14312be/4postgres82.92M/s0.00B/s0.00%40.20%postgres:postgrespostgres[local]SELECT14334be/4postgres77.82M/s0.00B/s0.00%40.19%postgres:postgrespostgres[local]SELECT14310be/4postgres72.51M/s0.00B/s0.00%40.08%postgres:postgrespostgres[local]SELECT14335be/4postgres75.72M/s0.00B/s0.00%39.64%postgres:postgrespostgres[local]SELECT14359be/4postgres77.37M/s0.00B/s0.00%39.57%postgres:postgrespostgres[local]SELECT14341be/4postgres81.28M/s0.00B/s0.00%39.49%postgres:postgrespostgres[local]SELECT14314be/4postgres99.25M/s0.00B/s0.00%39.06%postgres:postgrespostgres[local]SELECT14325be/4postgres92.97M/s0.00B/s0.00%38.86%postgres:postgrespostgres[local]SELECT14272be/4postgres102.70M/s0.00B/s0.00%38.53%postgres:postgrespostgres[local]SELECT14306be/4postgres73.62M/s0.00B/s0.00%38.52%postgres:postgrespostgres[local]SELECT14321be/4postgres84.34M/s0.00B/s0.00%37.38%postgres:postgrespostgres[local]SELECT14344be/4postgres73.31M/s0.00B/s0.00%37.33%postgres:postgrespostgres[local]SELECT14349be/4postgres82.12M/s0.00B/s0.00%36.34%postgres:postgrespostgres[local]SELECT14350be/4postgres84.84M/s0.00B/s0.00%36.25%postgres:postgrespostgres[local]SELECT14354be/4postgres88.81M/s0.00B/s0.00%36.24%postgres:postgrespostgres[local]SELECT14329be/4postgres66.78M/s0.00B/s0.00%34.95%postgres:postgrespostgres[local]idle14332be/4postgres77.94M/s0.00B/s0.00%34.89%postgres:postgrespostgres[local]SELECT14326be/4postgres73.88M/s0.00B/s0.00%34.81%postgres:postgrespostgres[local]SELECT14324be/4postgres72.59M/s0.00B/s0.00%34.74%postgres:postgrespostgres[local]SELECT14309be/4postgres70.08M/s0.00B/s0.00%34.62%postgres:postgrespostgres[local]SELECT14336be/4postgres76.49M/s0.00B/s0.00%34.60%postgres:postgrespostgres[local]SELECT14340be/4postgres78.11M/s0.00B/s0.00%34.54%postgres:postgrespostgres[local]SELECT14308be/4postgres67.45M/s0.00B/s0.00%34.48%postgres:postgrespostgres[local]SELECT14337be/4postgres70.05M/s0.00B/s0.00%34.35%postgres:postgrespostgres[local]idle14305be/4postgres67.95M/s0.00B/s0.00%34.33%postgres:postgrespostgres[local]SELECT14311be/4postgres64.25M/s0.00B/s0.00%34.16%postgres:postgrespostgres[local]SELECT14313be/4postgres79.62M/s0.00B/s0.00%34.09%postgres:postgrespostgres[local]SELECT14316be/4postgres84.10M/s0.00B/s0.00%34.06%postgres:postgrespostgres[local]SELECT14355be/4postgres66.73M/s0.00B/s0.00%34.01%postgres:postgrespostgres[local]SELECT14345be/4postgres83.61M/s0.00B/s0.00%33.96%postgres:postgrespostgres[local]SELECT14343be/4postgres83.96M/s0.00B/s0.00%33.88%postgres:postgrespostgres[local]SELECT14323be/4postgres58.07M/s0.00B/s0.00%33.86%postgres:postgrespostgres[local]SELECT14282be/4postgres72.93M/s0.00B/s0.00%33.80%postgres:postgrespostgres[local]SELECT14320be/4postgres71.65M/s0.00B/s0.00%33.70%postgres:postgrespostgres[local]SELECT14328be/4postgres71.67M/s0.00B/s0.00%33.60%postgres:postgrespostgres[local]SELECTtpc-b 读写性能 (tps: 42058)

测试

pgbench-Mprepared-v-r-P1-c32-j32-T3600

结果

...............progress:3585.0s,38970.6tps,lat0.821msstddev2.700progress:3586.0s,37586.5tps,lat0.851msstddev3.968progress:3587.0s,42752.9tps,lat0.748msstddev0.505progress:3588.0s,43598.0tps,lat0.734msstddev0.459progress:3589.0s,43036.5tps,lat0.744msstddev0.502progress:3590.0s,43520.4tps,lat0.735msstddev0.483progress:3591.0s,37351.6tps,lat0.857msstddev4.399progress:3592.0s,36243.5tps,lat0.883msstddev4.897progress:3593.0s,43355.3tps,lat0.738msstddev0.487progress:3594.0s,43592.2tps,lat0.734msstddev0.486progress:3595.0s,44001.3tps,lat0.727msstddev0.454progress:3596.0s,40706.8tps,lat0.786msstddev2.690progress:3597.0s,38404.0tps,lat0.833msstddev3.124progress:3598.0s,43741.3tps,lat0.732msstddev0.470progress:3599.0s,39648.9tps,lat0.807msstddev3.401progress:3600.0s,43696.8tps,lat0.731msstddev0.458transactiontype:<builtin:TPC-B(sortof)>scalingfactor:100000querymode:preparednumberofclients:32numberofthreads:32duration:3600snumberoftransactionsactuallyprocessed:151412741latencyaverage=0.761mslatencystddev=1.873mstps=42058.973764(includingconnectionsestablishing)tps=42059.153928(excludingconnectionsestablishing)statementlatenciesinmilliseconds:0.002\setaidrandom(1,100000*:scale)0.001\setbidrandom(1,1*:scale)0.001\settidrandom(1,10*:scale)0.001\setdeltarandom(-5000,5000)0.029BEGIN;0.425UPDATEpgbench_accountsSETabalance=abalance+:deltaWHEREaid=:aid;0.062SELECTabalanceFROMpgbench_accountsWHEREaid=:aid;0.075UPDATEpgbench_tellersSETtbalance=tbalance+:deltaWHEREtid=:tid;0.065UPDATEpgbench_branchesSETbbalance=bbalance+:deltaWHEREbid=:bid;0.052INSERTINTOpgbench_history(tid,bid,aid,delta,mtime)VALUES(:tid,:bid,:aid,:delta,CURRENT_TIMESTAMP);0.049END;

IO监测

TotalDISKREAD:2027.55M/s|TotalDISKWRITE:661.01M/sActualDISKREAD:2028.33M/s|ActualDISKWRITE:653.42M/sTIDPRIOUSERDISKREADDISKWRITESWAPINIO>COMMAND14545be/4postgres59.30M/s9.82M/s0.00%26.14%postgres:postgrespostgres[local]UPDATE14588be/4postgres58.05M/s11.29M/s0.00%26.07%postgres:postgrespostgres[local]BINDTE14574be/4postgres62.22M/s10.33M/s0.00%25.95%postgres:postgrespostgres[local]UPDATE14582be/4postgres60.69M/s10.70M/s0.00%25.94%postgres:postgrespostgres[local]UPDATE14561be/4postgres56.00M/s9.60M/s0.00%25.77%postgres:postgrespostgres[local]idle14567be/4postgres70.98M/s9.82M/s0.00%25.70%postgres:postgrespostgres[local]UPDATE14589be/4postgres67.12M/s9.80M/s0.00%25.68%postgres:postgrespostgres[local]INSERT14563be/4postgres60.19M/s9.95M/s0.00%25.68%postgres:postgrespostgres[local]UPDATE14583be/4postgres69.52M/s9.87M/s0.00%25.60%postgres:postgrespostgres[local]UPDATE14566be/4postgres58.28M/s9.92M/s0.00%25.59%postgres:postgrespostgres[local]UPDATE14587be/4postgres54.09M/s9.86M/s0.00%25.57%postgres:postgrespostgres[local]UPDATE14565be/4postgres59.45M/s9.79M/s0.00%25.43%postgres:postgrespostgres[local]BINDRT14580be/4postgres71.12M/s9.96M/s0.00%25.32%postgres:postgrespostgres[local]SELECTntransaction14576be/4postgres64.08M/s9.54M/s0.00%25.20%postgres:postgrespostgres[local]UPDATE14568be/4postgres57.60M/s10.07M/s0.00%25.16%postgres:postgrespostgres[local]UPDATE14584be/4postgres63.87M/s10.05M/s0.00%25.13%postgres:postgrespostgres[local]UPDATE14564be/4postgres68.96M/s9.78M/s0.00%25.00%postgres:postgrespostgres[local]UPDATE14550be/4postgres68.82M/s9.85M/s0.00%24.94%postgres:postgrespostgres[local]UPDATE14562be/4postgres80.99M/s10.59M/s0.00%24.88%postgres:postgrespostgres[local]UPDATE14581be/4postgres59.95M/s11.24M/s0.00%24.85%postgres:postgrespostgres[local]UPDATE14578be/4postgres65.43M/s10.52M/s0.00%24.83%postgres:postgrespostgres[local]UPDATE14570be/4postgres61.71M/s10.06M/s0.00%24.51%postgres:postgrespostgres[local]UPDATE14573be/4postgres64.77M/s10.00M/s0.00%24.50%postgres:postgrespostgres[local]UPDATE14575be/4postgres61.35M/s10.01M/s0.00%24.40%postgres:postgrespostgres[local]UPDATE14585be/4postgres59.80M/s10.36M/s0.00%24.29%postgres:postgrespostgres[local]UPDATE14560be/4postgres74.76M/s11.13M/s0.00%24.09%postgres:postgrespostgres[local]idleintransaction14577be/4postgres58.25M/s9.75M/s0.00%23.98%postgres:postgrespostgres[local]idleintransaction14579be/4postgres72.02M/s10.09M/s0.00%23.94%postgres:postgrespostgres[local]UPDATE14571be/4postgres57.67M/s10.01M/s0.00%23.63%postgres:postgrespostgres[local]UPDATE14572be/4postgres55.61M/s10.20M/s0.00%23.36%postgres:postgrespostgres[local]UPDATE14569be/4postgres64.92M/s12.51M/s0.00%23.32%postgres:postgrespostgres[local]UPDATE14586be/4postgres59.95M/s9.79M/s0.00%23.29%postgres:postgrespostgres[local]UPDATE13639be/4postgres27.33K/s23.99M/s0.00%1.14%postgres:walwriter13638be/4postgres0.00B/s214.99M/s0.00%0.00%postgres:backgroundwriter13637be/4postgres0.00B/s95.76M/s0.00%0.00%postgres:checkpointer写测试后的数据大小

postgres=#\dt+pgbench*ListofrelationsSchema|Name|Type|Owner|Size|Description--------+------------------+-------+----------+---------+-------------public|pgbench_accounts|table|postgres|1264GB|public|pgbench_branches|table|postgres|16MB|public|pgbench_history|table|postgres|8701MB|public|pgbench_tellers|table|postgres|66MB|(4rows)postgres=#\di+pgbench*ListofrelationsSchema|Name|Type|Owner|Table|Size|Description--------+-----------------------+-------+----------+------------------+---------+-------------public|pgbench_accounts_pkey|index|postgres|pgbench_accounts|209GB|public|pgbench_branches_pkey|index|postgres|pgbench_branches|8776kB|public|pgbench_tellers_pkey|index|postgres|pgbench_tellers|43MB|(3rows)100亿 tpbc 性能数据小结1、100亿TPCB 初始化

耗时：8385秒。

速度：约119万行/s。

耗时：43分50秒。

速度：约380万行/s。

表：1.251 TB

索引：209 GB

TPS: 118053

QPS: 118053

TPS: 42058

QPS: 210290

“PostgreSQL11 tpcb性能测试分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！