pgbenchテストPGパラメータ

テスト環境

ストリーミングレプリケーション環境では、1主1備、1台のテスターを追加

しけんきかい

-試験機:10.37.64.52-MASTER:0.37.64.53-standby:0.37.64.54
3台の機器のOS構成は同じ:オペレーティングシステムRHEL 6.3(64 Bit)、構成8 C/16 G/500 G

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: 
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初期化操作を実行することに注意します.