Pgpool-II で backend_hostname0 に Writer、backend_hostname1 に Reader を指定、load_balance_mode = on にして backend_weight0 = 0、backend_weight1 = 1 にすることで、参照クエリを全て Reader に振ることができる。また、/*NO LOAD BALANCE*/ ヒントを付与することで、参照クエリを Writer に発行することができる。ことを確認した。
backend_hostname0 = 'aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com' ★ Writer backend_port0 = 5432 backend_weight0 = 0 ★こちらにはロードバランスしない backend_flag0 = 'ALWAYS_MASTER' backend_application_name0 = 'server0' backend_hostname1 = 'aurora-postgres117.cluster-ro-******.ap-northeast-1.rds.amazonaws.com' ★ Reader backend_port1 = 5432 backend_weight1 = 1 ★こちらにはロードバランスする backend_application_name1 = 'server1'
$ psql "host=localhost dbname=writer port=9999 user=awsuser" psql (9.2.24, サーバー 11.7) 注意： psql バージョン 9.2, サーバーバージョン 11.0. psql の機能の中で、動作しないものがあるかもしれません。 "help" でヘルプを表示します. writer=> show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | r eplication_state | replication_sync_state | last_status_change ---------+-----------------------------------------------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-- -----------------+------------------------+--------------------- 0 | aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com | 5432 | up | 0.000000 | primary | 0 | false | 0 | | | 2020-09-24 14:33:02 1 | aurora-postgres117.cluster-ro-******.ap-northeast-1.rds.amazonaws.com | 5432 | up | 1.000000 | standby | 0 | true | 0 | | | 2020-09-24 14:33:02 (2 行) writer=> select inet_server_addr(); inet_server_addr ------------------ 172.17.2.181 ★ Reader にクエリを発行している (1 行) writer=> show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | r eplication_state | replication_sync_state | last_status_change ---------+-----------------------------------------------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-- -----------------+------------------------+--------------------- 0 | aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com | 5432 | up | 0.000000 | primary | 0 | false | 0 | | | 2020-09-24 14:33:02 1 | aurora-postgres117.cluster-ro-******.ap-northeast-1.rds.amazonaws.com | 5432 | up | 1.000000 | standby | 1★ | true | 0 | | | 2020-09-24 14:33:02 (2 行) ★ Reader の select_cnt が +1 している writer=> /*NO LOAD BALANCE*/ select inet_server_addr(); inet_server_addr ------------------ 172.17.4.241 ★ /*NO LOAD BALANCE*/ ヒントを付与すると Writer にクエリを発行している (1 行) writer=> show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | r eplication_state | replication_sync_state | last_status_change ---------+-----------------------------------------------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-- -----------------+------------------------+--------------------- 0 | aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com | 5432 | up | 0.000000 | primary | 1 ★ | false | 0 | | | 2020-09-24 14:33:02 1 | aurora-postgres117.cluster-ro-******.ap-northeast-1.rds.amazonaws.com | 5432 | up | 1.000000 | standby | 1 | true | 0 | | | 2020-09-24 14:33:02 (2 行) ★ Writer の select_cnt が +1 している writer=>
- pgbench で参照クエリを発行する
- 10 クライアントで 1,000 回クエリ = 10,000 回クエリを発行。
$ pgbench -Sn -c 10 -j 10 -t 1000 -U awsuser -h localhost -d writer -p 9999
$ psql "host=localhost dbname=writer port=9999 user=awsuser" psql (9.2.24, サーバー 11.7) 注意： psql バージョン 9.2, サーバーバージョン 11.0. psql の機能の中で、動作しないものがあるかもしれません。 "help" でヘルプを表示します. writer=> show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | r eplication_state | replication_sync_state | last_status_change ---------+-----------------------------------------------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-- -----------------+------------------------+--------------------- 0 | aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com | 5432 | up | 0.000000 | primary | 2 | false | 0 | | | 2020-09-24 14:33:02 1 | aurora-postgres117.cluster-ro-******.ap-northeast-1.rds.amazonaws.com | 5432 | up | 1.000000 | standby | 10000★ | true | 0 | | | 2020-09-24 14:33:02 (2 行) ★
Pgpool-II 設定
- pgpool.conf
listen_addresses = '*' port = 9999 pcp_listen_addresses = '*' pcp_port = 9898 backend_hostname0 = 'aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com' ★ Cluster エンドポイント backend_port0 = 5432 backend_weight0 = 0 ★ロードバランスしない backend_flag0 = 'ALWAYS_MASTER' backend_application_name0 = 'server0' backend_hostname1 = 'aurora-postgres117.cluster-ro-******.ap-northeast-1.rds.amazonaws.com' ★ Reader エンドポイント backend_port1 = 5432 backend_weight1 = 1 ★ロードバランスする backend_application_name1 = 'server1' enable_pool_hba = on pool_passwd = 'pool_passwd' load_balance_mode = on ★ ロードバランス有効化 white_function_list = 'inet_server_addr' ★ 参照クエリとみなす関数を指定 allow_sql_comments = on ★ コメントを有効化 statement_level_load_balance = on ★クエリレベルでロードバランスする master_slave_mode = on sr_check_user = 'awsuser' sr_check_database = 'mydb' health_check_user = 'awsuser' health_check_database = 'mydb'
- /etc/pgpool-II/pool_hba.conf
（中略） host all all 172.17.1.0/24 md5 host all all 172.17.2.0/24 md5 host all all 172.17.3.0/24 md5 host all all 172.17.4.0/24 md5
- /etc/pgpool-II/pool_passwd
awsuser:md5******
- /etc/pgpool-II/pcp.conf
pgpool:ba777e4c2f15c11ea8ac3be7e0440aa0
- ~/.pcppass
localhost:9898:pgpool:pgpool
- ~/.pgpass
localhost:9999:writer:awsuser:****** aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com:9999:writer:awsuser:****** urora-postgres117.cluster-ro-******.ap-northeast-1.rds.amazonaws.com:9999:writer:awsuser:******
