概要
pgpoolをWebサーバー、DBサーバーどちらに設置したらよいかテストして決める
コネクションプーリングのみ使用する想定
参考URL
pgpool-IIの接続性能の改善
http://lets.postgresql.jp/documents/technical/pgpool-II-tcp-tuning/1/pgbenchの使いこなし(※ソースからのインストール部分は内容が古いので読み飛ばす)
http://lets.postgresql.jp/documents/technical/contrib/pgbench/
マシンスペックおよびパラメータを表示
どんなマシンでベンチを取ったか記録しておく
- OSバージョン、クロック、コア数、メモリ
echo "OS: $(cat /etc/redhat-release) / kernel: $(uname -r)" ; \ cat /proc/cpuinfo |grep "model name"|uniq ; \ echo core $(cat /proc/cpuinfo |grep processor |wc -l) ; \ cat /proc/meminfo |grep MemTotal
ServerA spec
OS: CentOS release 6.7 (Final) / kernel: 2.6.32-573.1.1.el6.x86_64
model name : Intel(R) Xeon(R) CPU E5-2430 0 @ 2.20GHz
core 6
MemTotal: 3924040 kB
ServerB spec
OS: CentOS release 6.7 (Final) / kernel: 2.6.32-573.1.1.el6.x86_64
model name : Intel(R) Xeon(R) CPU E5-2430 0 @ 2.20GHz
core 8
MemTotal: 16333576 kB
-
pgpool,postgresのバージョン
pgpool -v pgpool-II version 3.4.3 (tataraboshi) rpm -qa |grep postgres postgresql94-server-9.4.5-1PGDG.rhel6.x86_64
-
pgpool(web)の設定
diff -ub /etc/pgpool-II/pgpool.conf /etc/pgpool-II/pgpool.conf.org |grep ^- -listen_addresses = '*' -port = 5432 -backend_hostname0 = 'db' -backend_port0 = 9999 -backend_data_directory0 = '/var/lib/pgsql/9.4/data' -pool_passwd = '' -num_init_children = 60
-
pgpool(db)の設定
diff -ub /etc/pgpool-II/pgpool.conf /etc/pgpool-II/pgpool.conf.org |grep ^- -listen_addresses = '*' -port = 5432 -backend_port0 = 9999 -backend_data_directory0 = '/var/lib/pgsql/9.4/data' -pool_passwd = '' -num_init_children = 60 -debug_level = 1
-
Postgresの設定
diff -ub /var/lib/pgsql/9.4/data/postgresql.conf /var/lib/pgsql/9.4/data/postgresql.conf.org |grep ^- -listen_addresses = '*' # what IP address(es) to listen on; -port = 9999 # (change requires restart) -max_connections = 250 # (change requires restart) -shared_buffers = 4096MB # min 128kB -work_mem = 16MB # min 64kB -maintenance_work_mem = 160MB # min 1MB -log_filename = 'postgresql-%Y%m%d.log' # log file name pattern, -log_min_duration_statement = 3000 # -1 is disabled, 0 logs all statements -log_line_prefix = '%t [%p]' # special values: -escape_string_warning = off
pgbench実施に当たって石井さんのコメント
pgbench -Sで実行されるような単純SELECTNではクエリキャッシュは
逆効果です.OFFにして測定してみてください.
それから,pgbenchをこのように使ってもコネクションプーリングの
効果はわかりません.pgbenchはデフォルトではpostmasterに接続しっぱなし
にするからです.
毎回pgbenchに接続するオプション -C を付ければコネクションプーリングの
効果が分かるはずです.
あと,言うまでもないと思いますが,1台のマシンで
pgpoolとpostmasterを複数動かしても性能検証には
あまりなりません.
最低でもマルチCPU,できればマシンを複数使わないと,並列動作できませんから.
pgbenchのマニュアル
マシンスペックを変えた場合、-j オプションの数値をいじってみる。
pgbenchを順番に実行してみる
●A(pgpool)→B(postgres)
●A→B(pgpool)→B(postgres)
●A→B(postgres)
●other→A(pgpool)→B(postgres)
/usr/local/pgsql/bin/pgbench -C -j 30 -c 60 -t 100 test -N -p 5432 -h localhost -U postgres ; \
/usr/local/pgsql/bin/pgbench -C -j 30 -c 60 -t 100 test -N -p 5432 -h db -U postgres ; \
/usr/local/pgsql/bin/pgbench -C -j 30 -c 60 -t 100 test -N -p 9999 -h db -U postgres ; \
/usr/local/pgsql/bin/pgbench -C -j 30 -c 60 -t 100 test -N -p 5432 -h web -U postgres
starting vacuum...end.
transaction type: Update only pgbench_accounts
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 30
number of transactions per client: 100
number of transactions actually processed: 6000/6000
latency average: 0.000 ms
tps = 290.598035 (including connections establishing)
tps = 293.758237 (excluding connections establishing)
starting vacuum...end.
transaction type: Update only pgbench_accounts
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 30
number of transactions per client: 100
number of transactions actually processed: 6000/6000
latency average: 0.000 ms
tps = 3393.592219 (including connections establishing)
tps = 9318.489187 (excluding connections establishing)
starting vacuum...end.
transaction type: Update only pgbench_accounts
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 30
number of transactions per client: 100
number of transactions actually processed: 6000/6000
latency average: 0.000 ms
tps = 1591.020703 (including connections establishing)
tps = 8438.154353 (excluding connections establishing)
starting vacuum...end.
transaction type: Update only pgbench_accounts
scaling factor: 1
query mode: simple
number of clients: 30
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 3000/3000
latency average: 0.000 ms
tps = 798.809029 (including connections establishing)
tps = 4101.745156 (excluding connections establishing)
postgresと同居させるのが一番早いのか・・?