标签
PostgreSQL , tpcb , pgbench , 100亿
背景
PostgreSQL 11 发布在即，以下是tpcc与tpch测试的结果：
《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab》
《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》
tpcb是pgbench标准测试CASE，本文使用与以上同样的测试机器（部署方式请参考以上），测试100亿tpch的性能。
参数
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
小结
PostgreSQL 在oltp, olap混合场景下有着出色的表现（100亿 TPCB 只读qps 11.8万，读写qps 21万 ； 1000万 TPCC：103万 tpmC； SF=10 TPCH 150秒，SF=200 TPCH 39 min；），再加上对Oracle兼容性的加持，以及阿里、平安集团、邮储、国家电网、中兴、华为、铁总、苏宁、去哪儿、探探等PG企业用户的背书，已成为市场去O的标志性替代产品，帮助企业实现几乎无痛的去O。
1、阿里云ADAM 去O 专版 PPAS(PostgreSQL高级版) 提供免费去O评估。
2、Ora2pg 开源去O产品.
3、《Oracle migration to Greenplum - (含 Ora2pg)》
参考
man pgbench
pgbench --help
《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab》
