pgbenchを用いてpostgresql.confの設定 2010/4

テスト環境
マシン : 富士通 PRIMAGY TX100 S3
CPU : CELERON G550
MEM : 2GB
HD : Barracuda ST200DM001 2TB x 2 MDRaid0
OS : CentOS-6.4-x86_64
PG_VERSION : 9.2.1

過去の経験から、ext4でのパフォーマンス向上のため、
/etc/fstab のパラメータを defaults から defaults,barrier=0へ変更

pgbenchのインストールと初期化

pgbenchのインストール

$ cd src/postgresql-9.2.1/contrib/pgbench
$ make install 

※ v9.6ではpgbenchはデフォルトでインストールされるので、別途インストールの必要なし。

テストデータベースの作成

$ createdb -T template0 pgbench_testdb

pgbenchの初期化

$ pgbench -i -s 100 pgbench_testdb

※ -i = --initialize, -s = --scale

インストール直後

$ pgbench -c 20 -t 1000 pgbench_testdb
starting vacuum...end.
LOG: checkpoints are occurring too frequently (28 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (24 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (28 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 20000/20000
tps = 158.647365 (including connections establishing)
tps = 158.684453 (excluding connections establishing)

checkpoint_segments = 64 (デフォルトの3より変更)

$ pgbench -c 20 -t 1000 pgbench_testdb
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 20000/20000
tps = 1285.280715 (including connections establishing)
tps = 1287.727021 (excluding connections establishing)

shared_buffers = 256MB (デフォルトの32MBより変更)

$ pgbench -c 20 -t 1000 pgbench_testdb
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 20000/20000
tps = 1256.163444 (including connections establishing)
tps = 1258.512579 (excluding connections establishing)

shared_bufferd = 512MB

$ pgbench -c 20 -t 1000 pgbench_testdb
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 20000/20000
tps = 1355.392252 (including connections establishing)
tps = 1358.147226 (excluding connections establishing)

work_mem = 2MB (デフォルトの1MBより変更)

$ pgbench -c 20 -t 1000 pgbench_testdb
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 20000/20000
tps = 1343.942866 (including connections establishing)
tps = 1346.649724 (excluding connections establishing)

10回試行した中での最高値
tps = 1736.338252 (including connections establishing)
tps = 1740.840546 (excluding connections establishing)

10回試行した中での最低値
tps = 338.188223 (including connections establishing)
tps = 338.359563 (excluding connections establishing)

※最頻値は tps = 1300 から 1500くらいの間