本篇内容介绍了“PostgreSQL11 tpcb性能测试分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
参数
1、postgresql.auto.conf
listen_addresses = '0.0.0.0' port = 1921 max_connections = 2000 superuser_reserved_connections = 13 unix_socket_directories = '., /var/run/postgresql, /tmp' tcp_keepalives_idle = 60 tcp_keepalives_interval = 10 tcp_keepalives_count = 10 shared_buffers = 64GB max_prepared_transactions = 2000 work_mem = 8MB maintenance_work_mem = 2GB dynamic_shared_memory_type = posix vacuum_cost_delay = 0 bgwriter_delay = 10ms bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 10.0 effective_io_concurrency = 0 max_worker_processes = 128 max_parallel_maintenance_workers = 24 max_parallel_workers_per_gather = 0 parallel_leader_participation = on min_parallel_table_scan_size=0 min_parallel_index_scan_size=0 parallel_setup_cost=0 parallel_tuple_cost=0 max_parallel_workers = 64 wal_level = minimal synchronous_commit = off wal_writer_delay = 10ms checkpoint_timeout = 35min max_wal_size = 128GB min_wal_size = 32GB checkpoint_completion_target = 0.1 max_wal_senders = 0 effective_cache_size = 400GB log_destination = 'csvlog' logging_collector = on log_directory = 'log' log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 log_checkpoints = on log_connections = on log_disconnections = on log_error_verbosity = verbose log_line_prefix = '%m [%p] ' log_timezone = 'PRC' log_autovacuum_min_duration = 0 autovacuum_max_workers = 16 autovacuum_freeze_max_age = 1200000000 autovacuum_multixact_freeze_max_age = 1400000000 autovacuum_vacuum_cost_delay = 0ms vacuum_freeze_table_age = 1150000000 vacuum_multixact_freeze_table_age = 1150000000 datestyle = 'iso, mdy' timezone = 'PRC' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english' jit = off cpu_tuple_cost=0.00018884145574257426 cpu_index_tuple_cost = 0.00433497085216479990 cpu_operator_cost = 0.00216748542608239995 seq_page_cost=0.014329 random_page_cost = 0.016
测试
初始100亿数据
使用pgbench 初始化100亿tpcb测试数据。
man pgbench
-i --initialize Required to invoke initialization mode. -I init_steps --init-steps=init_steps Perform just a selected set of the normal initialization steps. init_steps specifies the initialization steps to be performed, using one character per step. Each step is invoked in the specified order. The default is dtgvp. The available steps are: d (Drop) Drop any existing pgbench tables. t (create Tables) Create the tables used by the standard pgbench scenario, namely pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers. g (Generate data) Generate data and load it into the standard tables, replacing any data already present. v (Vacuum) Invoke VACUUM on the standard tables. p (create Primary keys) Create primary key indexes on the standard tables. f (create Foreign keys) Create foreign key constraints between the standard tables. (Note that this step is not performed by default.)
初始化
pgbench -i -s 100000 -I dtg -n --tablespace=tbs2
耗时8385秒，约119万行/s。
9998500000 of 10000000000 tuples (99%) done (elapsed 8384.23 s, remaining 1.26 s) 9998600000 of 10000000000 tuples (99%) done (elapsed 8384.31 s, remaining 1.17 s) 9998700000 of 10000000000 tuples (99%) done (elapsed 8384.40 s, remaining 1.09 s) 9998800000 of 10000000000 tuples (99%) done (elapsed 8384.48 s, remaining 1.01 s) 9998900000 of 10000000000 tuples (99%) done (elapsed 8384.56 s, remaining 0.92 s) 9999000000 of 10000000000 tuples (99%) done (elapsed 8384.65 s, remaining 0.84 s) 9999100000 of 10000000000 tuples (99%) done (elapsed 8384.73 s, remaining 0.75 s) 9999200000 of 10000000000 tuples (99%) done (elapsed 8384.82 s, remaining 0.67 s) 9999300000 of 10000000000 tuples (99%) done (elapsed 8384.90 s, remaining 0.59 s) 9999400000 of 10000000000 tuples (99%) done (elapsed 8384.98 s, remaining 0.50 s) 9999500000 of 10000000000 tuples (99%) done (elapsed 8385.07 s, remaining 0.42 s) 9999600000 of 10000000000 tuples (99%) done (elapsed 8385.15 s, remaining 0.34 s) 9999700000 of 10000000000 tuples (99%) done (elapsed 8385.24 s, remaining 0.25 s) 9999800000 of 10000000000 tuples (99%) done (elapsed 8385.33 s, remaining 0.17 s) 9999900000 of 10000000000 tuples (99%) done (elapsed 8385.41 s, remaining 0.08 s) 10000000000 of 10000000000 tuples (100%) done (elapsed 8385.49 s, remaining 0.00 s)
创建索引
1、强制设置并行度为24，a,b,c,d取最小
alter table pgbench_accounts set (parallel_workers =32); # a
max_worker_processes = 128 # b max_parallel_maintenance_workers = 24 # c max_parallel_workers = 64 # d max_parallel_workers_per_gather = 0 min_parallel_table_scan_size=0 min_parallel_index_scan_size=0 parallel_setup_cost=0 parallel_tuple_cost=0
2、创建tpcb数据表索引，100亿记录，创建索引耗时44分钟。
pgbench -i -I p --index-tablespace=tbs1
IO监测
Total DISK READ : 2.77 G/s | Total DISK WRITE : 2.86 G/s Actual DISK READ: 2.79 G/s | Actual DISK WRITE: 1795.02 M/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 13683 be/4 postgres 142.87 M/s 141.55 M/s 0.00 % 1.47 % postgres: parallel worker for PID 13655 13684 be/4 postgres 114.55 M/s 124.78 M/s 0.00 % 1.07 % postgres: parallel worker for PID 13655 13686 be/4 postgres 163.22 M/s 165.50 M/s 0.00 % 0.64 % postgres: parallel worker for PID 13655 13671 be/4 postgres 162.38 M/s 165.76 M/s 0.00 % 0.49 % postgres: parallel worker for PID 13655 13676 be/4 postgres 169.80 M/s 169.31 M/s 0.00 % 0.45 % postgres: parallel worker for PID 13655 13672 be/4 postgres 160.74 M/s 165.52 M/s 0.00 % 0.41 % postgres: parallel worker for PID 13655 13685 be/4 postgres 174.74 M/s 177.10 M/s 0.00 % 0.37 % postgres: parallel worker for PID 13655 13687 be/4 postgres 172.48 M/s 173.00 M/s 0.00 % 0.36 % postgres: parallel worker for PID 13655 13677 be/4 postgres 116.31 M/s 124.28 M/s 0.00 % 0.35 % postgres: parallel worker for PID 13655 13680 be/4 postgres 165.52 M/s 177.19 M/s 0.00 % 0.34 % postgres: parallel worker for PID 13655 13674 be/4 postgres 167.31 M/s 169.44 M/s 0.00 % 0.12 % postgres: parallel worker for PID 13655 13655 be/4 postgres 114.55 M/s 122.85 M/s 0.00 % 0.05 % postgres: postgres postgres [local] ALTER TABLE 13678 be/4 postgres 175.54 M/s 177.10 M/s 0.00 % 0.04 % postgres: parallel worker for PID 13655 13670 be/4 postgres 127.29 M/s 126.61 M/s 0.00 % 0.01 % postgres: parallel worker for PID 13655 13682 be/4 postgres 162.41 M/s 165.74 M/s 0.00 % 0.00 % postgres: parallel worker for PID 13655 13673 be/4 postgres 145.02 M/s 152.94 M/s 0.00 % 0.00 % postgres: parallel worker for PID 13655 13675 be/4 postgres 167.42 M/s 169.41 M/s 0.00 % 0.00 % postgres: parallel worker for PID 13655 13679 be/4 postgres 117.00 M/s 128.97 M/s 0.00 % 0.00 % postgres: parallel worker for PID 13655 13681 be/4 postgres 121.97 M/s 132.34 M/s 0.00 % 0.00 % postgres: parallel worker for PID 13655 13642 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: logical replication launcher 13634 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres 13635 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: logger 13637 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: checkpointer 13638 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: background writer 13639 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: walwriter 13640 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: autovacuum launcher 13641 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres: stats collector
耗时，43分50秒左右。
select now()-query_start, query from pg_stat_activity where query ~ 'primary key'; /watch 3 Sun 16 Sep 2018 05:53:13 PM CST (every 3s) ?column? | query -----------------+---------------------------------------------------------------------------------- 00:43:49.994922 | alter table pgbench_accounts add primary key (aid) using index tablespace "tbs1" 00:00:00 | select now()-query_start,query from pg_stat_activity where query ~ 'tbs1'; (2 rows) Sun 16 Sep 2018 05:53:16 PM CST (every 3s) ?column? | query ----------+---------------------------------------------------------------------------- 00:00:00 | select now()-query_start,query from pg_stat_activity where query ~ 'tbs1'; (1 row)
tpcb 100亿，表、索引大小
postgres=# /l+ postgres List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ----------+----------+-----------+---------+-------+-------------------+---------+------------+-------------------------------------------- postgres | postgres | SQL_ASCII | C | C | | 2599 GB | pg_default | default administrative connection database (1 row) postgres=# /dt+ pgbench* List of relations Schema | Name | Type | Owner | Size | Description --------+------------------+-------+----------+---------+------------- public | pgbench_accounts | table | postgres | 1251 GB | public | pgbench_branches | table | postgres | 3576 kB | public | pgbench_history | table | postgres | 0 bytes | public | pgbench_tellers | table | postgres | 42 MB | (4 rows) postgres=# /di+ pgbench* List of relations Schema | Name | Type | Owner | Table | Size | Description --------+-----------------------+-------+----------+------------------+---------+------------- public | pgbench_accounts_pkey | index | postgres | pgbench_accounts | 209 GB | public | pgbench_branches_pkey | index | postgres | pgbench_branches | 2208 kB | public | pgbench_tellers_pkey | index | postgres | pgbench_tellers | 21 MB | (3 rows)
这里建议pgbench_accounts用 pg_pathman hash 分区。
tpc-b 只读性能 (tps: 118053)
测试
pgbench -M prepared -v -r -P 1 -c 64 -j 64 -T 3600 -S
结果
......... progress: 3550.0 s, 131287.4 tps, lat 0.492 ms stddev 5.895 progress: 3551.0 s, 117855.0 tps, lat 0.543 ms stddev 7.239 progress: 3552.0 s, 102975.0 tps, lat 0.527 ms stddev 6.052 progress: 3553.0 s, 128299.1 tps, lat 0.575 ms stddev 7.191 progress: 3554.0 s, 122215.5 tps, lat 0.522 ms stddev 6.947 progress: 3555.0 s, 92727.5 tps, lat 0.689 ms stddev 9.055 progress: 3556.0 s, 144550.4 tps, lat 0.433 ms stddev 3.430 progress: 3557.0 s, 95982.1 tps, lat 0.565 ms stddev 7.052 progress: 3558.0 s, 118369.9 tps, lat 0.638 ms stddev 9.017 progress: 3559.0 s, 128757.4 tps, lat 0.497 ms stddev 5.789 progress: 3560.0 s, 99634.5 tps, lat 0.529 ms stddev 6.252 progress: 3561.0 s, 118071.4 tps, lat 0.635 ms stddev 8.541 progress: 3562.0 s, 131764.3 tps, lat 0.488 ms stddev 5.816 progress: 3563.0 s, 118408.9 tps, lat 0.540 ms stddev 7.308 progress: 3564.0 s, 97566.1 tps, lat 0.538 ms stddev 6.447 progress: 3565.0 s, 110077.0 tps, lat 0.686 ms stddev 9.656 progress: 3566.0 s, 128585.3 tps, lat 0.498 ms stddev 5.901 progress: 3567.0 s, 116302.4 tps, lat 0.514 ms stddev 6.269 progress: 3568.0 s, 111013.7 tps, lat 0.615 ms stddev 7.788 progress: 3569.0 s, 116588.9 tps, lat 0.549 ms stddev 7.378 progress: 3570.0 s, 118875.0 tps, lat 0.538 ms stddev 6.716 progress: 3571.0 s, 124222.9 tps, lat 0.514 ms stddev 6.566 progress: 3572.0 s, 93014.1 tps, lat 0.551 ms stddev 6.824 progress: 3573.0 s, 120148.7 tps, lat 0.639 ms stddev 8.537 progress: 3574.0 s, 131078.1 tps, lat 0.488 ms stddev 5.765 progress: 3575.0 s, 105341.2 tps, lat 0.509 ms stddev 5.548 progress: 3576.0 s, 121529.8 tps, lat 0.612 ms stddev 8.044 progress: 3577.0 s, 129391.4 tps, lat 0.479 ms stddev 5.173 progress: 3578.0 s, 86950.4 tps, lat 0.612 ms stddev 8.670 progress: 3579.0 s, 124735.8 tps, lat 0.616 ms stddev 8.173 progress: 3580.0 s, 120284.6 tps, lat 0.532 ms stddev 6.892 progress: 3581.0 s, 96491.2 tps, lat 0.559 ms stddev 7.261 progress: 3582.0 s, 121068.8 tps, lat 0.611 ms stddev 8.041 progress: 3583.0 s, 137346.3 tps, lat 0.466 ms stddev 4.567 progress: 3584.0 s, 104130.5 tps, lat 0.526 ms stddev 6.362 progress: 3585.0 s, 114540.8 tps, lat 0.639 ms stddev 8.500 progress: 3586.0 s, 128416.6 tps, lat 0.499 ms stddev 5.967 progress: 3587.0 s, 106762.0 tps, lat 0.527 ms stddev 6.358 progress: 3588.0 s, 102988.7 tps, lat 0.697 ms stddev 9.452 progress: 3589.0 s, 131513.3 tps, lat 0.487 ms stddev 5.648 progress: 3590.0 s, 116013.7 tps, lat 0.508 ms stddev 6.041 progress: 3591.0 s, 109295.3 tps, lat 0.632 ms stddev 9.098 progress: 3592.0 s, 113581.1 tps, lat 0.493 ms stddev 5.331 progress: 3593.0 s, 126594.3 tps, lat 0.569 ms stddev 6.812 progress: 3594.0 s, 116212.3 tps, lat 0.551 ms stddev 7.886 progress: 3595.0 s, 95710.3 tps, lat 0.532 ms stddev 6.368 progress: 3596.0 s, 125784.2 tps, lat 0.613 ms stddev 8.003 progress: 3597.0 s, 119773.1 tps, lat 0.534 ms stddev 6.934 progress: 3598.0 s, 97862.0 tps, lat 0.560 ms stddev 7.001 progress: 3599.0 s, 113585.9 tps, lat 0.644 ms stddev 8.661 progress: 3600.0 s, 128750.5 tps, lat 0.496 ms stddev 5.801 transaction type: <builtin: select only> scaling factor: 100000 query mode: prepared number of clients: 64 number of threads: 64 duration: 3600 s number of transactions actually processed: 424993812 latency average = 0.542 ms latency stddev = 6.751 ms tps = 118053.277351 (including connections establishing) tps = 118054.214576 (excluding connections establishing) statement latencies in milliseconds: 0.001 /set aid random(1, 100000 * :scale) 0.542 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
IO监测
Total DISK READ : 4.90 G/s | Total DISK WRITE : 0.00 B/s Actual DISK READ: 4.90 G/s | Actual DISK WRITE: 0.00 B/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 14357 be/4 postgres 74.24 M/s 0.00 B/s 0.00 % 43.98 % postgres: postgres postgres [local] SELECT 14257 be/4 postgres 88.29 M/s 0.00 B/s 0.00 % 42.37 % postgres: postgres postgres [local] idle 14318 be/4 postgres 75.55 M/s 0.00 B/s 0.00 % 41.86 % postgres: postgres postgres [local] SELECT 14322 be/4 postgres 73.42 M/s 0.00 B/s 0.00 % 41.53 % postgres: postgres postgres [local] SELECT 14356 be/4 postgres 77.37 M/s 0.00 B/s 0.00 % 41.49 % postgres: postgres postgres [local] idle 14319 be/4 postgres 90.94 M/s 0.00 B/s 0.00 % 41.41 % postgres: postgres postgres [local] SELECT 14307 be/4 postgres 83.40 M/s 0.00 B/s 0.00 % 41.40 % postgres: postgres postgres [local] SELECT 14327 be/4 postgres 84.56 M/s 0.00 B/s 0.00 % 41.33 % postgres: postgres postgres [local] SELECT 14351 be/4 postgres 92.47 M/s 0.00 B/s 0.00 % 41.14 % postgres: postgres postgres [local] SELECT 14333 be/4 postgres 84.52 M/s 0.00 B/s 0.00 % 40.96 % postgres: postgres postgres [local] SELECT 14352 be/4 postgres 85.67 M/s 0.00 B/s 0.00 % 40.85 % postgres: postgres postgres [local] SELECT 14301 be/4 postgres 74.16 M/s 0.00 B/s 0.00 % 40.82 % postgres: postgres postgres [local] SELECT 14348 be/4 postgres 78.53 M/s 0.00 B/s 0.00 % 40.52 % postgres: postgres postgres [local] SELECT 14358 be/4 postgres 75.76 M/s 0.00 B/s 0.00 % 40.50 % postgres: postgres postgres [local] SELECT 14353 be/4 postgres 81.66 M/s 0.00 B/s 0.00 % 40.47 % postgres: postgres postgres [local] SELECT 14317 be/4 postgres 82.71 M/s 0.00 B/s 0.00 % 40.28 % postgres: postgres postgres [local] SELECT 14347 be/4 postgres 86.47 M/s 0.00 B/s 0.00 % 40.25 % postgres: postgres postgres [local] SELECT 14312 be/4 postgres 82.92 M/s 0.00 B/s 0.00 % 40.20 % postgres: postgres postgres [local] SELECT 14334 be/4 postgres 77.82 M/s 0.00 B/s 0.00 % 40.19 % postgres: postgres postgres [local] SELECT 14310 be/4 postgres 72.51 M/s 0.00 B/s 0.00 % 40.08 % postgres: postgres postgres [local] SELECT 14335 be/4 postgres 75.72 M/s 0.00 B/s 0.00 % 39.64 % postgres: postgres postgres [local] SELECT 14359 be/4 postgres 77.37 M/s 0.00 B/s 0.00 % 39.57 % postgres: postgres postgres [local] SELECT 14341 be/4 postgres 81.28 M/s 0.00 B/s 0.00 % 39.49 % postgres: postgres postgres [local] SELECT 14314 be/4 postgres 99.25 M/s 0.00 B/s 0.00 % 39.06 % postgres: postgres postgres [local] SELECT 14325 be/4 postgres 92.97 M/s 0.00 B/s 0.00 % 38.86 % postgres: postgres postgres [local] SELECT 14272 be/4 postgres 102.70 M/s 0.00 B/s 0.00 % 38.53 % postgres: postgres postgres [local] SELECT 14306 be/4 postgres 73.62 M/s 0.00 B/s 0.00 % 38.52 % postgres: postgres postgres [local] SELECT 14321 be/4 postgres 84.34 M/s 0.00 B/s 0.00 % 37.38 % postgres: postgres postgres [local] SELECT 14344 be/4 postgres 73.31 M/s 0.00 B/s 0.00 % 37.33 % postgres: postgres postgres [local] SELECT 14349 be/4 postgres 82.12 M/s 0.00 B/s 0.00 % 36.34 % postgres: postgres postgres [local] SELECT 14350 be/4 postgres 84.84 M/s 0.00 B/s 0.00 % 36.25 % postgres: postgres postgres [local] SELECT 14354 be/4 postgres 88.81 M/s 0.00 B/s 0.00 % 36.24 % postgres: postgres postgres [local] SELECT 14329 be/4 postgres 66.78 M/s 0.00 B/s 0.00 % 34.95 % postgres: postgres postgres [local] idle 14332 be/4 postgres 77.94 M/s 0.00 B/s 0.00 % 34.89 % postgres: postgres postgres [local] SELECT 14326 be/4 postgres 73.88 M/s 0.00 B/s 0.00 % 34.81 % postgres: postgres postgres [local] SELECT 14324 be/4 postgres 72.59 M/s 0.00 B/s 0.00 % 34.74 % postgres: postgres postgres [local] SELECT 14309 be/4 postgres 70.08 M/s 0.00 B/s 0.00 % 34.62 % postgres: postgres postgres [local] SELECT 14336 be/4 postgres 76.49 M/s 0.00 B/s 0.00 % 34.60 % postgres: postgres postgres [local] SELECT 14340 be/4 postgres 78.11 M/s 0.00 B/s 0.00 % 34.54 % postgres: postgres postgres [local] SELECT 14308 be/4 postgres 67.45 M/s 0.00 B/s 0.00 % 34.48 % postgres: postgres postgres [local] SELECT 14337 be/4 postgres 70.05 M/s 0.00 B/s 0.00 % 34.35 % postgres: postgres postgres [local] idle 14305 be/4 postgres 67.95 M/s 0.00 B/s 0.00 % 34.33 % postgres: postgres postgres [local] SELECT 14311 be/4 postgres 64.25 M/s 0.00 B/s 0.00 % 34.16 % postgres: postgres postgres [local] SELECT 14313 be/4 postgres 79.62 M/s 0.00 B/s 0.00 % 34.09 % postgres: postgres postgres [local] SELECT 14316 be/4 postgres 84.10 M/s 0.00 B/s 0.00 % 34.06 % postgres: postgres postgres [local] SELECT 14355 be/4 postgres 66.73 M/s 0.00 B/s 0.00 % 34.01 % postgres: postgres postgres [local] SELECT 14345 be/4 postgres 83.61 M/s 0.00 B/s 0.00 % 33.96 % postgres: postgres postgres [local] SELECT 14343 be/4 postgres 83.96 M/s 0.00 B/s 0.00 % 33.88 % postgres: postgres postgres [local] SELECT 14323 be/4 postgres 58.07 M/s 0.00 B/s 0.00 % 33.86 % postgres: postgres postgres [local] SELECT 14282 be/4 postgres 72.93 M/s 0.00 B/s 0.00 % 33.80 % postgres: postgres postgres [local] SELECT 14320 be/4 postgres 71.65 M/s 0.00 B/s 0.00 % 33.70 % postgres: postgres postgres [local] SELECT 14328 be/4 postgres 71.67 M/s 0.00 B/s 0.00 % 33.60 % postgres: postgres postgres [local] SELECT
tpc-b 读写性能 (tps: 42058)
测试
pgbench -M prepared -v -r -P 1 -c 32 -j 32 -T 3600
结果
............... progress: 3585.0 s, 38970.6 tps, lat 0.821 ms stddev 2.700 progress: 3586.0 s, 37586.5 tps, lat 0.851 ms stddev 3.968 progress: 3587.0 s, 42752.9 tps, lat 0.748 ms stddev 0.505 progress: 3588.0 s, 43598.0 tps, lat 0.734 ms stddev 0.459 progress: 3589.0 s, 43036.5 tps, lat 0.744 ms stddev 0.502 progress: 3590.0 s, 43520.4 tps, lat 0.735 ms stddev 0.483 progress: 3591.0 s, 37351.6 tps, lat 0.857 ms stddev 4.399 progress: 3592.0 s, 36243.5 tps, lat 0.883 ms stddev 4.897 progress: 3593.0 s, 43355.3 tps, lat 0.738 ms stddev 0.487 progress: 3594.0 s, 43592.2 tps, lat 0.734 ms stddev 0.486 progress: 3595.0 s, 44001.3 tps, lat 0.727 ms stddev 0.454 progress: 3596.0 s, 40706.8 tps, lat 0.786 ms stddev 2.690 progress: 3597.0 s, 38404.0 tps, lat 0.833 ms stddev 3.124 progress: 3598.0 s, 43741.3 tps, lat 0.732 ms stddev 0.470 progress: 3599.0 s, 39648.9 tps, lat 0.807 ms stddev 3.401 progress: 3600.0 s, 43696.8 tps, lat 0.731 ms stddev 0.458 transaction type: <builtin: TPC-B (sort of)> scaling factor: 100000 query mode: prepared number of clients: 32 number of threads: 32 duration: 3600 s number of transactions actually processed: 151412741 latency average = 0.761 ms latency stddev = 1.873 ms tps = 42058.973764 (including connections establishing) tps = 42059.153928 (excluding connections establishing) statement latencies in milliseconds: 0.002 /set aid random(1, 100000 * :scale) 0.001 /set bid random(1, 1 * :scale) 0.001 /set tid random(1, 10 * :scale) 0.001 /set delta random(-5000, 5000) 0.029 BEGIN; 0.425 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.062 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 0.075 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 0.065 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.052 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.049 END;
IO监测
Total DISK READ : 2027.55 M/s | Total DISK WRITE : 661.01 M/s Actual DISK READ: 2028.33 M/s | Actual DISK WRITE: 653.42 M/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 14545 be/4 postgres 59.30 M/s 9.82 M/s 0.00 % 26.14 % postgres: postgres postgres [local] UPDATE 14588 be/4 postgres 58.05 M/s 11.29 M/s 0.00 % 26.07 % postgres: postgres postgres [local] BINDTE 14574 be/4 postgres 62.22 M/s 10.33 M/s 0.00 % 25.95 % postgres: postgres postgres [local] UPDATE 14582 be/4 postgres 60.69 M/s 10.70 M/s 0.00 % 25.94 % postgres: postgres postgres [local] UPDATE 14561 be/4 postgres 56.00 M/s 9.60 M/s 0.00 % 25.77 % postgres: postgres postgres [local] idle 14567 be/4 postgres 70.98 M/s 9.82 M/s 0.00 % 25.70 % postgres: postgres postgres [local] UPDATE 14589 be/4 postgres 67.12 M/s 9.80 M/s 0.00 % 25.68 % postgres: postgres postgres [local] INSERT 14563 be/4 postgres 60.19 M/s 9.95 M/s 0.00 % 25.68 % postgres: postgres postgres [local] UPDATE 14583 be/4 postgres 69.52 M/s 9.87 M/s 0.00 % 25.60 % postgres: postgres postgres [local] UPDATE 14566 be/4 postgres 58.28 M/s 9.92 M/s 0.00 % 25.59 % postgres: postgres postgres [local] UPDATE 14587 be/4 postgres 54.09 M/s 9.86 M/s 0.00 % 25.57 % postgres: postgres postgres [local] UPDATE 14565 be/4 postgres 59.45 M/s 9.79 M/s 0.00 % 25.43 % postgres: postgres postgres [local] BINDRT 14580 be/4 postgres 71.12 M/s 9.96 M/s 0.00 % 25.32 % postgres: postgres postgres [local] SELECTn transaction 14576 be/4 postgres 64.08 M/s 9.54 M/s 0.00 % 25.20 % postgres: postgres postgres [local] UPDATE 14568 be/4 postgres 57.60 M/s 10.07 M/s 0.00 % 25.16 % postgres: postgres postgres [local] UPDATE 14584 be/4 postgres 63.87 M/s 10.05 M/s 0.00 % 25.13 % postgres: postgres postgres [local] UPDATE 14564 be/4 postgres 68.96 M/s 9.78 M/s 0.00 % 25.00 % postgres: postgres postgres [local] UPDATE 14550 be/4 postgres 68.82 M/s 9.85 M/s 0.00 % 24.94 % postgres: postgres postgres [local] UPDATE 14562 be/4 postgres 80.99 M/s 10.59 M/s 0.00 % 24.88 % postgres: postgres postgres [local] UPDATE 14581 be/4 postgres 59.95 M/s 11.24 M/s 0.00 % 24.85 % postgres: postgres postgres [local] UPDATE 14578 be/4 postgres 65.43 M/s 10.52 M/s 0.00 % 24.83 % postgres: postgres postgres [local] UPDATE 14570 be/4 postgres 61.71 M/s 10.06 M/s 0.00 % 24.51 % postgres: postgres postgres [local] UPDATE 14573 be/4 postgres 64.77 M/s 10.00 M/s 0.00 % 24.50 % postgres: postgres postgres [local] UPDATE 14575 be/4 postgres 61.35 M/s 10.01 M/s 0.00 % 24.40 % postgres: postgres postgres [local] UPDATE 14585 be/4 postgres 59.80 M/s 10.36 M/s 0.00 % 24.29 % postgres: postgres postgres [local] UPDATE 14560 be/4 postgres 74.76 M/s 11.13 M/s 0.00 % 24.09 % postgres: postgres postgres [local] idle in transaction 14577 be/4 postgres 58.25 M/s 9.75 M/s 0.00 % 23.98 % postgres: postgres postgres [local] idle in transaction 14579 be/4 postgres 72.02 M/s 10.09 M/s 0.00 % 23.94 % postgres: postgres postgres [local] UPDATE 14571 be/4 postgres 57.67 M/s 10.01 M/s 0.00 % 23.63 % postgres: postgres postgres [local] UPDATE 14572 be/4 postgres 55.61 M/s 10.20 M/s 0.00 % 23.36 % postgres: postgres postgres [local] UPDATE 14569 be/4 postgres 64.92 M/s 12.51 M/s 0.00 % 23.32 % postgres: postgres postgres [local] UPDATE 14586 be/4 postgres 59.95 M/s 9.79 M/s 0.00 % 23.29 % postgres: postgres postgres [local] UPDATE 13639 be/4 postgres 27.33 K/s 23.99 M/s 0.00 % 1.14 % postgres: walwriter 13638 be/4 postgres 0.00 B/s 214.99 M/s 0.00 % 0.00 % postgres: background writer 13637 be/4 postgres 0.00 B/s 95.76 M/s 0.00 % 0.00 % postgres: checkpointer
写测试后的数据大小
postgres=# /dt+ pgbench* List of relations Schema | Name | Type | Owner | Size | Description --------+------------------+-------+----------+---------+------------- public | pgbench_accounts | table | postgres | 1264 GB | public | pgbench_branches | table | postgres | 16 MB | public | pgbench_history | table | postgres | 8701 MB | public | pgbench_tellers | table | postgres | 66 MB | (4 rows) postgres=# /di+ pgbench* List of relations Schema | Name | Type | Owner | Table | Size | Description --------+-----------------------+-------+----------+------------------+---------+------------- public | pgbench_accounts_pkey | index | postgres | pgbench_accounts | 209 GB | public | pgbench_branches_pkey | index | postgres | pgbench_branches | 8776 kB | public | pgbench_tellers_pkey | index | postgres | pgbench_tellers | 43 MB | (3 rows)
100亿 tpbc 性能数据小结
1、100亿TPCB 初始化
耗时：8385秒。
速度：约119万行/s。
2、100亿TPCB 创建索引（24并行）
耗时：43分50秒。
速度：约380万行/s。
3、100亿TPCB 空间占用
表：1.251 TB
索引：209 GB
4、100亿TPCB 只读3600秒
TPS: 118053
QPS: 118053
5、100亿TPCB 读写3600秒
TPS: 42058
QPS: 210290
“PostgreSQL11 tpcb性能测试分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/205089.html