PostgreSQL11でpgpool-IIを使って負荷分散

はじめに

PostgreSQLはストリーミングレプリケーションで動かし、pgpool-IIを利用して負荷分散させる環境構築メモです。

環境

使用した環境は以下のとおり。

• CentOS 7.5（firewalldとSELinuxは無効化済み）
• PostgreSQL 11.2
• pgpool-II 4.0.5

以下で構築したものを利用します。

• CentOS 7にPostgreSQL11をインストールする

pgpool-IIのインストールは以下を参考しました。

• Pgpool-II 4.0.5 文書 3.10. RPMからのインストール

構築

pgpool-IIのリポジトリパッケージをインストールします。
以下のURLから「RHEL/CentOS 7.x (Pgpool-II 4.0)」を利用します。

# yum install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-release-4.0-1.noarch.rpm

続けて、PostgreSQL11用のpgpool-IIをインストールします。

# yum install -y pgpool-II-pg11.x86_64 pgpool-II-pg11-debuginfo.x86_64 pgpool-II-pg11-devel.x86_64 pgpool-II-pg11-extensions.x86_64

インストール後に"pgpool -v"コマンドで確認します。

# pgpool -v
pgpool-II version 4.0.5 (torokiboshi)

pgpoolのサーバでもpsqlを使いたいので、以下のようにインストールしておきます。

# yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum -y install postgresql11

また、PostgreSQLサーバでは以下をインストールします。
（って書いてあったけど、他を探しても同じようにインストールしているのを見つけられなかった。本当に必要なのか？？？）

# yum install pgpool-II-pg11-extensions.x86_64

pgpool-IIの自動起動設定と起動

pgpoolを自動起動するように設定し、そのまま起動させます。
※今回はrootユーザで起動していますが、pgpoolのようなユーザを作成して起動するようにしたほうが良いです。

# systemctl enable pgpool.service
# systemctl start pgpool.service

pgpool-IIの設定変更

PostgreSQLはストリーミングレプリケーションを使用しているため、サンプル設定ファイルpgpool.conf.sample-streamを利用します。

まず、以下のように今の設定ファイルのバックアップと、サンプル設定のコピーを実行します。

# cp -p /etc/pgpool-II/pgpool.conf /etc/pgpool-II/pgpool.conf.org
# cp /etc/pgpool-II/pgpool.conf.sample-stream /etc/pgpool-II/pgpool.conf

pgpool.confを修正します。

vi /etc/pgpool-II/pgpool.conf

PostgreSQLのストリームレプリケーションと組み合わせる場合、pgpoo-IIはマスタースレーブモードを利用します。
マスタースレーブモードは以下の2つの設定を実施します。サンプルをコピーしているので設定済みです。

master_slave_mode = on
master_slave_sub_mode = 'stream'

負荷分散を有効に設定する。

load_balance_mode = on

バックエンドデータの設定を以下のように行います。
バックエンドとなるPostgreSQLサーバの数だけ設定します。
今回は2台構成なので、それぞれのパラメータの最後に0,1がついた設定を実施しています。

backend_hostname0 = '192.168.10.168' # バックエンドのホスト名かIPアドレス
backend_port0 = 5432 # バックエンドのポート番号
backend_weight0 = 1 # 負荷分散時の比率
backend_data_directory0 = '/data' # バックエンドのデータベースクラスタのディレクトリ
backend_flag0 = 'ALLOW_TO_FAILOVER' # バックエンドの挙動。今回は気にせずデフォルトの設定を利用

backend_hostname1 = '192.168.10.169'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

コネクションプールの設定を変更します。

connection_cache = on # バックエンドへの接続をキャッシュする
num_init_children = 30 # pgpool-IIのプロセスからバックエンドへの接続の数
max_pool = 2 # Pgpool-IIの各子プロセスがキャッシュするコネクションの最大数

pgool-IIの接続設定。listen_addressesはリモートからアクセスできるように"*"を設定し、portは"9999"に設定しています。

listen_addresses = '*'
port = 9999

ログの設定を変更。
デフォルトだと通常はほとんどログが出力されなくて、動作を確認できなかったので、一時的に変更。通常はデフォルトで十分？

client_min_messages = log # デフォルトはnotice
log_min_messages = info # デフォルトはwarning

ストリーミングレプリケーションのチェックの設定

sr_check_user = 'postgres' # ストリーミングレプリケーションのチェックを行うPostgreSQLユーザ名を指定
sr_check_password = '' # ストリーミングレプリケーションのチェックを行うsr_check_user PostgreSQLユーザのパスワードを指定
sr_check_database = 'postgres' # ストリーミングレプリケーションの遅延チェックを行うデータベース名を指定

上の設定でsr_check_passwordを設定しません。パスワードなしで接続できるように、pg_md5コマンドでpool_passwdに設定します。

# pg_md5 --md5auth --username=postgres postgres
# cat /etc/pgpool-II/pool_passwd 
postgres:md53175bce1d3201d16594cebf9d7eb3f9d

これで設定は終了。
負荷分散させておりどちらのサーバでSQLが実行されているか分からないので、確認のためログにSQLが表示されるように設定を変更。

testdb=# ALTER DATABASE testdb SET log_statement = 'all';
ALTER DATABASE

# 戻す場合は以下を実行
testdb=# ALTER DATABASE testdb SET log_statement = DEFAULT;

psqlを用いて以下のようにSQLを実行したら、各サーバのログにSQLが出力され、負荷分散されていることを確認できます。

# psql -h localhost -p 9999 -U testuser testdb -c "select * from test"

2019-05-27 13:14:47.180 CEST [3297] LOG:  statement: select * from test

pgpoolとクライアント間でMD5接続

PostgreSQLでの接続でmd5を使用している場合は、pgpoolとクライアント間でもMD5で接続するように設定する必要があります。

まず、pgpool.confで"enable_pool_hba = on"と設定します。

# vi /etc/pgpool-II/pgpool.conf

enable_pool_hba = on

pg_md5コマンドで認証ファイル（pool_passwd）を作成します。
"--username"でユーザ名を指定し、その後にパスワードを指定します。
実行すると"/etc/pgpool-II/pool_passwd"ファイルが生成されます。

# pg_md5 --md5auth --username=testuser testuser
# cat /etc/pgpool-II/pool_passwd
testuser:md5d70a0452418aeb8fb4030eae69ca2856

次にpool_hba.confに接続情報を追加します。

# vi /etc/pgpool-II/pool_hba.conf

host    testdb  testuser        192.168.10.0/24 md5

後はpgpoolを再起動します。

# systemctl restart pgpool

pgbenchでベンチマークを実行する

PostgreSQLではベンチマーク用のツールpgbenchが標準で同梱されています。
まずは、pgbenchでベンチマーク用のテーブルとデータを作成します。

$ pgbench -i testdb
$ pgbench -c 10 -t 100 testdb -h 192.168.10.163 -p 9999 -S
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 1000/1000
latency average = 7.983 ms
tps = 1252.714760 (including connections establishing)
tps = 1257.614213 (excluding connections establishing)

その他

(1) pgpoolにpsqlで接続しようとしたときに以下のエラーが発生しました。

原因は、pg_hba.confの設定ミスで、マスターとスレーブの設定が違うものになっていることでした。同じ設定になるように修正したらエラーは解消しました。

# psql -h localhost -p 9999 -U testuser testdb
psql: ERROR:  unable to read message length

(2) pgpoolが起動しなくなる

pgpoolを強制終了してしまうとSocketのファイルが残ってしまい、以下のように起動しなくなることがあります。

Jun 01 00:35:10 pgpoolserver1 systemd[1]: Started Pgpool-II.
Jun 01 00:35:10 pgpoolserver1 systemd[1]: Starting Pgpool-II...
Jun 01 00:35:10 pgpoolserver1 pgpool[3410]: 2019-06-01 00:35:10: pid 3410: FATAL:  failed to bind a socket: "/tmp/.s.PGSQL.9999"
Jun 01 00:35:10 pgpoolserver1 pgpool[3410]: 2019-06-01 00:35:10: pid 3410: DETAIL:  bind socket failed with error: "Address already in use"

ファイルを削除すれば、起動するようになります。

参考

• Pgpool-II 4.0.5 文書
• pgpool-IIでストリーミング・レプリケーションへ対応する by LCL Engineers Blog