pgbench测试PG参数

测试环境

流复制环境下,1主1备,加上1台测试机

测试机器

-测试机:10.37.64.52
-MASTER:10.37.64.53
-standby:10.37.64.54

3台机器的OS配置相同:
操作系统 RHEL 6.3 (64 Bit),配置8C/16G/500G

postgresql当前参数配置

listen_addresses = '*'
port = 5432
max_connections = 1024
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 4MB
maintenance_work_mem = 1GB
min_wal_size = 4GB
max_wal_size = 16GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
checkpoint_timeout = 30min
wal_level = replica
wal_compression = on
wal_log_hints = on
synchronous_commit = remote_write
max_wal_senders=5
wal_keep_segments = 1000
hot_standby = on
wal_sender_timeout = 5000
wal_receiver_status_interval = 2
max_standby_streaming_delay = -1
max_standby_archive_delay = -1
restart_after_crash = on
hot_standby_feedback = on
synchronous_standby_names = '*'

测试1

测试当前参数配置。

初始化

bash-4.1$ pgbench -i -s 100 -h 10.37.64.53 -p 5432 -U postgres
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 10000000 tuples (1%) done (elapsed 0.10 s, remaining 10.08 s)
...
10000000 of 10000000 tuples (100%) done (elapsed 13.92 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

不同并发测试

1并发

bash-4.1$  pgbench -c 1 -j 1 -r -P 1 -T 10 -h 10.37.64.53 -p 5432 -U postgres53 -p 5432 -U postgres
starting vacuum...end.
progress: 1.0 s, 578.0 tps, lat 1.724 ms stddev 0.495
progress: 2.0 s, 589.0 tps, lat 1.696 ms stddev 0.416
progress: 3.0 s, 591.0 tps, lat 1.690 ms stddev 0.335
progress: 4.0 s, 585.0 tps, lat 1.709 ms stddev 0.352
progress: 5.0 s, 580.0 tps, lat 1.724 ms stddev 0.395
progress: 6.0 s, 564.0 tps, lat 1.771 ms stddev 0.553
progress: 7.0 s, 570.0 tps, lat 1.753 ms stddev 0.384
progress: 8.0 s, 573.0 tps, lat 1.744 ms stddev 0.369
progress: 9.0 s, 588.0 tps, lat 1.701 ms stddev 0.377
progress: 10.0 s, 555.0 tps, lat 1.801 ms stddev 1.604
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 5774
latency average = 1.731 ms
latency stddev = 0.635 ms
tps = 577.300012 (including connections establishing)
tps = 577.468661 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.001  \set aid random(1, 100000 * :scale)
         0.000  \set bid random(1, 1 * :scale)
         0.000  \set tid random(1, 10 * :scale)
         0.000  \set delta random(-5000, 5000)
         0.115  BEGIN;
         0.244  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.179  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.213  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.251  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.172  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.547  END;

注1) 通过修改-c -j参数,测试并发数为8、16、32、64的情况。测试结果见“pgbench参数调优测试结果.xlsx”。

注2) 加上-S选项可以只测SELECT。

测试2

测试参数配置:当前参数配置+以下参数配置。

autovacuum_analyze_scale_factor = 0.05
autovacuum_max_workers = 5
autovacuum_naptime = 1
autovacuum_vacuum_cost_delay = 0
autovacuum_vacuum_scale_factor = 0.005
autovacuum_vacuum_threshold = 50

测试项目同 “测试1”,注意要执行pgbench初始化操作。

测试3

测试参数配置:当前参数配置+以下参数配置。

bgwriter_delay = 20ms

测试项目同 “测试1”,注意要执行pgbench初始化操作。

展开阅读全文

没有更多推荐了,返回首页