------------「PostgreSQL 設計・運用計画の鉄則」の読込みのつづき---------------
[P.139]
第9章 サーバ設定
■CPUの設定
■クライアント接続設定
PostgreSQLはクライアントからの接続要求１つにつき、バックエンドプロセスが一つ作成され、トランザクション処理や問い合わせ処理を行う。
・max_connectionsパラメータ
デフォルト値
せ70> grep max_connections /var/lib/pgdata/postgresql.conf
max_connections = 100 # (change requires restart)
【検証1】 max_connectionsパラメータを超えるクライアントからの接続が発生した場合どうなるか？
せ70> diff postgresql.conf.20180620 postgresql.conf
64c64
< max_connections = 100 # (change requires restart)
---
> max_connections = 2 # (change requires restart)
せ70> systemctl reload postgresql-10
Job for postgresql-10.service failed because the control process exited with error code. See "systemctl status postgresql-10.service" and "journalctl -xe" for details.
→起動失敗。starup.logの内容は下記の通り
2018-06-20 22:24:41.637 JST [3775] HINT: Future log output will appear in directory "log".
postgres: max_wal_senders must be less than max_connections
どうやら、postgresql.confの「max_wal_senders」の値よりmax_connectionsを大きくしないといけないらしい。
ちなみに、デフォルトでは、
せ70> grep max_wal_senders postgresql.conf
#max_wal_senders = 10 # max number of walsender processes
→max_connectionsを11に変更したらpostgresqlサービス起動成功。
11個もクライアント接続して検証するのがダルいので、max_wal_senders を2に変更して、max_connectionsを3にする。
せ70> diff postgresql.conf.20180620 postgresql.conf
64c64
< max_connections = 100 # (change requires restart)
---
> max_connections = 3 # (change requires restart)
238c238
< #max_wal_senders = 10 # max number of walsender processes
---
> #max_wal_senders = 2 # max number of walsender processes
→起動失敗。starup.logの内容は下記の通り
2018-06-20 22:27:31.208 JST [3834] HINT: Future log output will appear in directory "log".
postgres: superuser_reserved_connections must be less than max_connections
→postgresql.confの「superuser_reserved_connections」の値よりmax_connectionsを大きくしないといけない。
なので、postgresql.confを下記のように修正
せ70> diff postgresql.conf.20180620 postgresql.conf
64,65c64,65
< max_connections = 100 # (change requires restart)
< #superuser_reserved_connections = 3 # (change requires restart)
---
> max_connections = 3 # (change requires restart)
> superuser_reserved_connections = 1 # (change requires restart)
238c238
< #max_wal_senders = 10 # max number of walsender processes
---
> max_wal_senders = 2 # max number of walsender processes
せ70> systemctl restart postgresql-10
→無事起動した。
max_connections = 3 で、superuser_reserved_connections = 1 だから、理論上、2つのクライアントまで接続できて、3つめのクライアントは接続できないはず
早速、ターミナルを3つ起動してそれぞれのターミナルからpsqlコマンドでクライアント接続を試みてみる
[接続前]
せ70> ps -ef | grep postgres | grep -v grep
postgres 3921 1 0 22:45 ? 00:00:00 /opt/PostgreSQL/10/bin/postgres -D /var/lib/pgdata
postgres 3922 3921 0 22:45 ? 00:00:00 postgres: logger process
postgres 3924 3921 0 22:45 ? 00:00:00 postgres: checkpointer process
postgres 3925 3921 0 22:45 ? 00:00:00 postgres: writer process
postgres 3926 3921 0 22:45 ? 00:00:00 postgres: wal writer process
postgres 3927 3921 0 22:45 ? 00:00:00 postgres: autovacuum launcher process
postgres 3928 3921 0 22:45 ? 00:00:00 postgres: archiver process
postgres 3929 3921 0 22:45 ? 00:00:00 postgres: stats collector process
postgres 3930 3921 0 22:45 ? 00:00:00 postgres: bgworker: logical replication launcher
→まだクライアントもバックエンドプロセスも居ない。
３つのターミナルから３つのローカル接続
せ70> ps -ef | grep postgres | grep -v grep
postgres 3921 1 0 22:45 ? 00:00:00 /opt/PostgreSQL/10/bin/postgres -D /var/lib/pgdata
postgres 3922 3921 0 22:45 ? 00:00:00 postgres: logger process
postgres 3924 3921 0 22:45 ? 00:00:00 postgres: checkpointer process
postgres 3925 3921 0 22:45 ? 00:00:00 postgres: writer process
postgres 3926 3921 0 22:45 ? 00:00:00 postgres: wal writer process
postgres 3927 3921 0 22:45 ? 00:00:00 postgres: autovacuum launcher process
postgres 3928 3921 0 22:45 ? 00:00:00 postgres: archiver process
postgres 3929 3921 0 22:45 ? 00:00:00 postgres: stats collector process
postgres 3930 3921 0 22:45 ? 00:00:00 postgres: bgworker: logical replication launcher
root 3968 3682 0 22:52 pts/3 00:00:00 /bin/bash /opt/PostgreSQL/10/bin/psql -U postgres
root 3970 3968 0 22:52 pts/3 00:00:00 /opt/PostgreSQL/10/bin/psql.bin -U postgres
postgres 3972 3921 0 22:52 ? 00:00:00 postgres: postgres postgres [local] idle
root 3973 3500 0 22:52 pts/0 00:00:00 /bin/bash /opt/PostgreSQL/10/bin/psql -U postgres
root 3975 3973 0 22:52 pts/0 00:00:00 /opt/PostgreSQL/10/bin/psql.bin -U postgres
postgres 3977 3921 0 22:52 ? 00:00:00 postgres: postgres postgres [local] idle
root 3978 3646 0 22:52 pts/2 00:00:00 /bin/bash /opt/PostgreSQL/10/bin/psql -U postgres
root 3980 3978 0 22:52 pts/2 00:00:00 /opt/PostgreSQL/10/bin/psql.bin -U postgres
postgres 3982 3921 0 22:52 ? 00:00:00 postgres: postgres postgres [local] idle
→3つのクライアントから3つのローカル接続に成功。バックエンドプロセスも３つ居る。
4つめのターミナルを起動して４つめのローカル接続を試みる
せ70> /opt/PostgreSQL/10/bin/psql -U postgres
psql.bin: FATAL: sorry, too many clients already
→ローカル接続は3つまでできたけど4つ目は失敗
・３つのクライアントをリモート接続してみる
いったんローカルからのクライアント接続をすべて切断
せ70> ps -ef | grep postgres | grep -v grep
postgres 3921 1 0 22:45 ? 00:00:00 /opt/PostgreSQL/10/bin/postgres -D /var/lib/pgdata
postgres 3922 3921 0 22:45 ? 00:00:00 postgres: logger process
postgres 3924 3921 0 22:45 ? 00:00:00 postgres: checkpointer process
postgres 3925 3921 0 22:45 ? 00:00:00 postgres: writer process
postgres 3926 3921 0 22:45 ? 00:00:00 postgres: wal writer process
postgres 3927 3921 0 22:45 ? 00:00:00 postgres: autovacuum launcher process
postgres 3928 3921 0 22:45 ? 00:00:00 postgres: archiver process
postgres 3929 3921 0 22:45 ? 00:00:00 postgres: stats collector process
postgres 3930 3921 0 22:45 ? 00:00:00 postgres: bgworker: logical replication launcher
→まだクライアントもバックエンドプロセスも居ない。
３つのターミナルから３つのリモート接続
せ6> psql -h 192.168.2.70 -U postgres testdb
・・・
せ70> ps -ef | grep postgres | grep -v grep
postgres 3921 1 0 22:45 ? 00:00:00 /opt/PostgreSQL/10/bin/postgres -D /var/lib/pgdata
postgres 3922 3921 0 22:45 ? 00:00:00 postgres: logger process
postgres 3924 3921 0 22:45 ? 00:00:00 postgres: checkpointer process
postgres 3925 3921 0 22:45 ? 00:00:00 postgres: writer process
postgres 3926 3921 0 22:45 ? 00:00:00 postgres: wal writer process
postgres 3927 3921 0 22:45 ? 00:00:00 postgres: autovacuum launcher process
postgres 3928 3921 0 22:45 ? 00:00:00 postgres: archiver process
postgres 3929 3921 0 22:45 ? 00:00:00 postgres: stats collector process
postgres 3930 3921 0 22:45 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 4014 3921 0 23:00 ? 00:00:00 postgres: postgres testdb 192.168.2.26(44498) idle
postgres 4031 3921 0 23:01 ? 00:00:00 postgres: postgres testdb 192.168.2.26(44502) idle
postgres 4033 3921 0 23:01 ? 00:00:00 postgres: postgres testdb 192.168.2.26(44506) idle
→3つのクライアントから3つのリモート接続に成功。バックエンドプロセスが３つ居る。
4つめのターミナルを起動して４つめのリモート接続を試みる
せ6> psql -h 192.168.2.70 -U postgres testdb
psql: FATAL: sorry, too many clients already
→リモート接続は3つまでできたけど4つ目は失敗
ターミナルから１つめのローカル接続を試みる
せ70> /opt/PostgreSQL/10/bin/psql -U postgres
psql.bin: FATAL: sorry, too many clients already
→リモート接続がmax_connectionsに達しているので4つ目のバックエンドプロセス起動はローカルからも失敗
・superuser_reserved_connectionsパラメータが1に設定してあるので、
一般ユーザは接続は2クライアントに制限されて、スーパーユーザなら3つめのクライアントとして接続できるか確認
いったんローカルからのクライアント接続をすべて切断
一般ユーザでリモートからクライアント接続
せ6> psql -h 192.168.2.70 -U punisuke testdb
・・・
せ70> ps -ef | grep postgres | grep -v grep
postgres 3921 1 0 22:45 ? 00:00:00 /opt/PostgreSQL/10/bin/postgres -D /var/lib/pgdata
postgres 3922 3921 0 22:45 ? 00:00:00 postgres: logger process
postgres 3924 3921 0 22:45 ? 00:00:00 postgres: checkpointer process
postgres 3925 3921 0 22:45 ? 00:00:00 postgres: writer process
postgres 3926 3921 0 22:45 ? 00:00:00 postgres: wal writer process
postgres 3927 3921 0 22:45 ? 00:00:00 postgres: autovacuum launcher process
postgres 3928 3921 0 22:45 ? 00:00:00 postgres: archiver process
postgres 3929 3921 0 22:45 ? 00:00:00 postgres: stats collector process
postgres 3930 3921 0 22:45 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 4073 3921 0 23:11 ? 00:00:00 postgres: punisuke testdb 192.168.2.26(44512) idle
postgres 4076 3921 0 23:12 ? 00:00:00 postgres: punisuke testdb 192.168.2.26(44516) idle
→２つのリモートクライアントから一般ユーザで接続中。バックエンドプロセスが２つ居る。
ローカルクライアントから３つめ一般ユーザ接続を試みる
せ70> /opt/PostgreSQL/10/bin/psql -U punisuke testdb
Password for user punisuke:
psql.bin: FATAL: remaining connection slots are reserved for non-replication superuser connections
→max_connectionsは3だがsuperuser_reserved_connectionsが1なので３つ目の一般ユーザ接続は失敗
リモートクライアントから１つめスーパーユーザ接続を試みる
せ6> psql -h 192.168.2.70 -U postgres testdb
→ログイン成功
せ70> ps -ef | grep postgres | grep -v grep
postgres 3921 1 0 22:45 ? 00:00:00 /opt/PostgreSQL/10/bin/postgres -D /var/lib/pgdata
postgres 3922 3921 0 22:45 ? 00:00:00 postgres: logger process
postgres 3924 3921 0 22:45 ? 00:00:00 postgres: checkpointer process
postgres 3925 3921 0 22:45 ? 00:00:00 postgres: writer process
postgres 3926 3921 0 22:45 ? 00:00:00 postgres: wal writer process
postgres 3927 3921 0 22:45 ? 00:00:00 postgres: autovacuum launcher process
postgres 3928 3921 0 22:45 ? 00:00:00 postgres: archiver process
postgres 3929 3921 0 22:45 ? 00:00:00 postgres: stats collector process
postgres 3930 3921 0 22:45 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 4073 3921 0 23:11 ? 00:00:00 postgres: punisuke testdb 192.168.2.26(44512) idle
postgres 4076 3921 0 23:12 ? 00:00:00 postgres: punisuke testdb 192.168.2.26(44516) idle
root 4115 3500 0 23:21 pts/0 00:00:00 /bin/bash /opt/PostgreSQL/10/bin/psql -U postgres testdb
root 4117 4115 0 23:21 pts/0 00:00:00 /opt/PostgreSQL/10/bin/psql.bin -U postgres testdb
postgres 4119 3921 0 23:21 ? 00:00:00 postgres: postgres testdb [local] idle
→一般ユーザでログインしてるバックエンドプロセスが２つの他に、スーパーユーザでログインしたバックエンドプロセスが１つ居る。
・superuser_reserved_connectionsパラメータ
superuser_reserved_connectionsは、一般ユーザがクライアント接続を開放しないまま滞留した状況でも、データベースのメンテナンスなどが行えるように予約された接続数。
デフォルト値は先述の通り3
せ70> grep superuser_reserved_connections /var/lib/pgdata/postgresql.conf.20180620
#superuser_reserved_connections = 3 # (change requires restart)
なお、スタンバイサーバとの接続数も、max_connectionsにカウントされるので、スタンバイサーバが2台あって、superuser_reserved_connectionsが3に設定されていて、max_connectionsが100の場合は、一般ユーザの同時接続数は95に減る。
■max_wal_sendersとは何か？
レプリケーションデータを１つ、またはそれ以上複数のスタンバイサーバに送るいかなるサーバ上で設定することができます。マスターは常に送出サーバであるため、max_wal_sendersなどの「組み込みのストリーミングレプリケーション機能の動作を制御パラメータ」は常にマスター上に設定されなければなりません（max_wal_senders以外にもいくつかある）。これらのパラメータの役割と意味はスタンバイが後にマスターに昇格しても変わりません。
max_wal_senders (integer)
サーバが使用できるレプリケーションスロット(26.2.6. レプリケーションスロット参照)の最大数を指定します。デフォルトは0です。 このパラメータはサーバ起動時のみ設定可能です。 レプリケーションスロットが使用できるためには、wal_levelをreplica以上に設定しなければなりません。 現在存在しているレプリケーションスロットの数よりも少ない値を設定すると、サーバは起動しません。
https://www.postgresql.jp/document/9.6/html/runtime-config-replication.html
要するに、レプリケーション構成のマスターPostgreSQL側に設定して、スレーブの数だけmax_wal_sendersを指定する。
なお、このmax_wal_sendersはmax_connectionsの数に含まれるので、
一般ユーザクライアントの最大接続数 = max_connections - スレーブの数 -非常時のスーパユーザ専用管理コネクション数
【課題】
・jdbcコネクタ経由でもmax_connections、superuser_reserved_connections、max_wal_sendersの値による同時接続数を確認する
・[P.140] deadlock_timeout値の動作確認
