Configuration
OpenSQL팀이 PGPOOL-II 4.4.4 지원 시 적용하는 표준 pgpool.conf 입니다.
참고하여 환경에 맞는 conf 파일을 업데이트해 사용하시면 됩니다.
$ cat << EOF >> /etc/pgpool-II/pgpool.conf
——————————————————————-
# opensql for PostgreSQL PGPOOL CONFIG
——————————————————————-
enable_consensus_with_half_votes = on # 노드가 짝수일 경우 on
failover_on_backend_error = off # default : on
# 안정성을 위해 off 권장
# 임시적인 오류나 단기적인 장애로 인해
# 불필요한 failover 발생 방지
failover_on_backend_shutdown = off
—————————————————————————————————–
# CONNECTION
# 외부에서 pgpool로 접속할 때 사용할 접속 정보
# Pgpool을 통해 접속할 때는 9999을 사용하며, Unix Domain Socket을 사용할 때는 Default Path를 통해 통신이 이루어짐
—————————————————————————————————–
backend_clustering_mode = ‘streaming_replication’
listen_addresses = ‘*’ # default : localhost
port = 9999
pcp_listen_addresses = ‘*’ # default : localhost
pcp_port = 9898
num_init_children = 32 # 허용되는 최대 동시 세션 수
# Number of pools
# postgresql.conf 의 max_connection 수보다 -30 권장
# psql -p 9999 -c ‘show pool_processes ’
# ‘netstat -s’ 명령에서 ‘times the listen queue of a socket overflowed’ 문구로 수신 대기열이
# overflow 되었는지 확인할 수 있음
max_pool = 4 # pgpool은 num_init_children * max_pool만큼 PostgreSQL에 연결됨
# pool 당 연결 수(동일한 User와 DB에 대해 하나의 pool 프로세스가 유지할 수 있는 연결 수)
# 1 권장
———————————————–
# AUTEHNTICATION
———————————————–
enable_pool_hba = off # pool_hba.conf 사용해 클라이언트 인증 활성화 여부
——————————————————————
# BACKEND CONNECTION SETTINGS
# Cluster에 속한 PostgreSQL Server Connection 정보와 Data File path 명시
# 뒤에 붙는 Postfix 숫자(0,1)에 의해 서버 구분
—————————————————————–
backend_hostname0 = ‘192.168.80.100’
backend_port0 = 5432
backend_weight0 = 1 # select문에대한 load-balancing 분산 비율(10진수 값)
backend_data_directory0 = ‘/opensql/pg/15/data’
backend_flag0 = ‘ALLOW_TO_FAILOVER’ # failover 기능 사용
backend_application_name0 = ‘192.168.80.100’
backend_hostname1 = ‘192.168.80.101’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/opensql/pg/15/data’
backend_flag1 = ‘ALLOW_TO_FAILOVER’
backend_application_name1 = ‘192.168.80.101’
—————————————
# HEALTH CHECK
—————————————
health_check_period = 5
health_check_timeout = 20
health_check_user = ‘postgres’
health_check_password = ”
health_check_database = ‘postgres’
health_check_max_retries = 5
———————————————————————
# STREAMING REPLICATION CHECK
# Streaming Replication 상태 체크를 위한 설정( 10초(default)마다 postgres 계정으로 상태 Check )
———————————————————————-
sr_check_period = 10
sr_check_user = ‘postgres’ # Streaming Replication check할 user
sr_check_password = ”
sr_check_database = ‘postgres’
———————————-
# WATCHDOG(VIP)
———————————–
use_watchdog = on
delegate_ip = ‘192.168.80.110’
if_cmd_path = ‘/sbin’
arping_path = ‘/usr/sbin’
if_up_cmd = ‘/usr/bin/sudo /sbin/ip addr add 192.168.80.110/24 dev ens160 label ens160:0’
if_down_cmd = ‘/usr/bin/sudo /sbin/ip addr del 192.168.80.110/24 dev ens160’
arping_cmd = ‘/usr/bin/sudo /usr/sbin/arping -U 192.168.80.110 -w 1 -I ens160’
—————————————————————–
# WATCHDOG COMMUNICATION SETTINGS
——————————————————————
hostname0 = ‘192.168.80.100’
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = ‘192.168.80.101’
wd_port1 = 9000
pgpool_port1 = 9999
wd_lifecheck_method = ‘heartbeat’
wd_interval = 10
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_hostname0 = ‘192.168.80.100’
heartbeat_port0 = 9694
heartbeat_device0 = ”
heartbeat_hostname1 = ‘192.168.80.101’
heartbeat_port1 = 9694
heartbeat_device1 = ”
wd_escalation_command = ‘/etc/pgpool-II/escalation.sh’
————————
# LOGGING
————————-
log_destination = ‘stderr’
logging_collector = on
log_directory = ‘/opensql/pg/15/log/pgpool’
log_filename = ‘pgpool-%Y-%m-%d_%H%M%S.log’
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
—————————————
# SOCKET DIRECTORY
—————————————
socket_dir = ‘/var/run/postgresql’ # postgresql unix domain socket 경로(pg DB 접속 시 사용)
pcp_socket_dir = ‘/var/run/postgresql’ # pgpool-II의 pcp 명령과 통신할 때 사용하는 socket 경로
wd_ipc_socket_dir = ‘/var/run/postgresql’
pid_file_name = ‘/var/run/pgpool/pgpool.pid’
——————————————
# FAILOVER COMMAND
——————————————
failover_command = ‘/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S’
—————————————-
# FOLLOW COMMAND
—————————————-
follow_primary_command = ‘/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R’
——————————————-
# ONLINE RECOVERY
——————————————–
recovery_user = ‘postgres’
recovery_password = ”
recovery_1st_stage_command = ‘recovery_1st_stage’
EOF
Note
-- 리소스 최소 요건(성능 고려사항)
pgpool-II에는 공유 메모리와 프로세스 private 메모리의 두 가지 유형의 메모리 사용이 있습니다.
공유 메모리는 pgpool-II 메인 서버 프로세스 시작 시 할당되며 전체 pgpool-II 서버가 종료 될 때까지 해제되지 않습니다.
프로세스 private 메모리는 각 pgpool-II 자식 프로세스 내에 할당되며 프로세스가 끝날 때 해제됩니다.
ㆍShared memory requirement (in mega bytes) = 10 + num_init_children * max_pool * 0.02
( enable_shared_relcache가 on(default)인 경우 추가로 128MB의 공유 메모리 소모 )
ㆍProcess memory requirement in total (in mega bytes) = num_init_children * 0.16
저희 OpenSQL팀에서는 pgpool-II 리소스 최소 요건에 따라 안전성을 우선으로 work_mem 파라미터 값을 10% 하향 조정한 값을 적용하고 권장합니다. 반드시 필요한지는 환경에 따라 다르며, 메모리 사용량과 동시 세션 수를 기반으로 결정해야 합니다.
권장 전략:
1. 초기에는 10% 하향 조정 적용
2. 테스트 및 모니터링 후 필요에 따라 다시 조정
3. 메모리 여유가 충분하면 기존 값으로 복원
-- 부하분산 관련 파라미터
load_balance_mode = on
backend_weight = 1 # 10진수로 표현- Connection Pooling Test
pgpool.conf의 num_init_children, max_pool 파라미터를 조절하여 Test를 수행합니다.
Case1. num_init_children = 32, max_pool = 4
$ cat /etc/pgpool-II/pgpool.conf | grep "num_init_children|max_pool" | grep -Ev "#"
num_init_children = 32
max_pool = 4
$ ps x | grep "pgpool: wait for connection request" | grep -Ev 'grep' | wc -l
32
$ pcp_proc_count -w -v
No | PID
_____________________
0 | 1874
1 | 1875
2 | 1876
3 | 1877
ㆍ
ㆍ
ㆍ
28 | 1902
29 | 2171
30 | 1904
31 | 1905
Total Processes:32
$ pcp_proc_info -a -w | wc -l
256
Case2. num_init_children = 32, max_pool = 8
$ cat /etc/pgpool-II/pgpool.conf | grep "num_init_children|max_pool" | grep -Ev "#"
num_init_children = 32
max_pool = 8
$ ps x | grep "pgpool: wait for connection request" | grep -Ev 'grep' | wc -l
32
$ pcp_proc_count -w -v
No | PID
_____________________
0 | 3933
1 | 3934
2 | 3935
3 | 3936
ㆍ
ㆍ
ㆍ
28 | 3961
29 | 3962
30 | 3963
31 | 3964
Total Processes:32
$ pcp_proc_info -a -w | wc -l
512
Case3. num_init_children = 64, max_pool = 8
$ cat /etc/pgpool-II/pgpool.conf | grep "num_init_children|max_pool" | grep -Ev "#"
num_init_children = 64
max_pool = 8
$ ps x | grep "pgpool: wait for connection request" | grep -Ev 'grep' | wc -l
64
$ pcp_proc_count -w -v
No | PID
_____________________
0 | 4319
1 | 4320
2 | 4321
3 | 4322
ㆍ
ㆍ
ㆍ
60 | 4379
61 | 4380
62 | 4381
63 | 4382
Total Processes:64
$ pcp_proc_info -a -w | wc -l
1024- SELECT Loadbalance Test
pgpool.conf의 backend_weight 파라미터를 조절하여 Test를 수행합니다.
Case1. 1:1 SELECT Loadbalance Test
$ node
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 192.168.56.101 | 5432 | up | up | 0.500000 | primary | primary | 0 | true | 0 | | | 2024-11-28 23:48:54
1 | 192.168.56.102 | 5432 | up | up | 0.500000 | standby | standby | 0 | false | 0 | streaming | async | 2024-11-28 23:48:54
(2 rows)
-- 'select 1;' 100번 수행 후 결과
$ psql -p 9999 -c"select 1"
$ node
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 192.168.56.101 | 5432 | up | up | 0.500000 | primary | primary | 45 | true | 0 | | | 2024-11-28 23:48:54
1 | 192.168.56.102 | 5432 | up | up | 0.500000 | standby | standby | 55 | false | 0 | streaming | async | 2024-11-28 23:48:54
(2 rows)
Case2. 8:2 SELECT Loadbalance Test
$ node
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 192.168.56.101 | 5432 | up | up | 0.800000 | primary | primary | 0 | true | 0 | | | 2024-11-29 00:58:15
1 | 192.168.56.102 | 5432 | up | up | 0.200000 | standby | standby | 0 | false | 0 | streaming | async | 2024-11-29 00:58:15
(2 rows)
-- 'select 1;' 100번 수행 후 결과
$ psql -p 9999 -c"select 1"
$ node
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 192.168.56.101 | 5432 | up | up | 0.800000 | primary | primary | 79 | true | 0 | | | 2024-11-29 00:58:15
1 | 192.168.56.102 | 5432 | up | up | 0.200000 | standby | standby | 21 | false | 0 | streaming | async | 2024-11-29 00:58:15
(2 rows)
Case3. pgbench SELECT Loadbalance Test
$ pgbench -p 9999 -c 10 -j 10 -S -T 60
$ node
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 192.168.56.101 | 5432 | up | up | 0.800000 | primary | primary | 143546 | true | 0 | | | 2024-11-29 00:58:15
1 | 192.168.56.102 | 5432 | up | up | 0.200000 | standby | standby | 339044 | false | 0 | streaming | async | 2024-11-29 00:58:15
(2 rows)- pcp commands
PCP 명령은 네트워크를 통해 pgpool-II를 조작하는 UNIX 명령입니다.
| pcp command | 수행 결과 |
| pcp_node_info -a -w | $ pcp_node_info -w -v Hostname : 192.168.56.101 Port : 5432 Status : 2 Weight : 0.500000 Status Name : up Backend Status Name : up Role : primary Backend Role : primary Replication Delay : 0 Replication State : none Replication Sync State : none Last Status Change : 2024-11-12 13:56:14 Hostname : 192.168.56.102 Port : 5432 Status : 3 Weight : 0.500000 Status Name : up Backend Status Name : up Role : standby Backend Role : standby Replication Delay : 0 Replication State : streaming Replication Sync State : async Last Status Change : 2024-11-12 14:39:31 |
| pcp_health_check_stats -n0 -v -w pcp_health_check_stats -n1 -v -w |
$ pcp_health_check_stats -n0 -w -v Node Id : 0 Host Name : 192.168.56.101 Port : 5432 Status : up Role : primary Last Status Change : 2024-11-12 13:56:14 Total Count : 1246 Success Count : 1246 Fail Count : 0 Skip Count : 0 Retry Count : 0 Average Retry Count : 0.000000 Max Retry Count : 0 Max Health Check Duration : 75 Minimum Health Check Duration : 1 Average Health Check Duration : 4.371589 Last Health Check : 2024-11-12 19:13:30 Last Successful Health Check : 2024-11-12 19:13:30 Last Skip Health Check : Last Failed Health Check : $ pcp_health_check_stats -n1 -w -v Node Id : 1 Host Name : 192.168.56.102 Port : 5432 Status : up Role : standby Last Status Change : 2024-11-12 19:13:26 Total Count : 1247 Success Count : 262 Fail Count : 1 Skip Count : 984 Retry Count : 5 Average Retry Count : 0.019011 Max Retry Count : 5 Max Health Check Duration : 5014 Minimum Health Check Duration : 2 Average Health Check Duration : 24.463878 Last Health Check : 2024-11-12 19:13:40 Last Successful Health Check : 2024-11-12 19:13:40 Last Skip Health Check : 2024-11-12 19:13:20 Last Failed Health Check : 2024-11-12 14:39:31 |
| pcp_proc_count -w -v | $ pcp_proc_count -w -v — num_init_children 값과 동일 No | PID 0 | 13746 1 | 13747 2 | 13748 3 | 13876 ㆍ ㆍ ㆍ 66 | 13808 67 | 13809 68 | 13810 69 | 13811 Total Processes:70 |
| pcp_proc_info -a -w | $ pcp_proc_info -a -w — num_init_children * max_pool 값과 동일 ㆍ ㆍ ㆍ 2024-11-12 14:41:20 0 0 0 0 0 0 0 13811 0 Wait for connection 2024-11-12 14:41:20 0 0 0 0 0 0 0 13811 1 Wait for connection 2024-11-12 14:41:20 0 0 0 0 0 0 0 13811 0 Wait for connection 2024-11-12 14:41:20 0 0 0 0 0 0 0 13811 1 Wait for connection [opensql@localhost:pgpool-II]$ pcp_proc_info -a -w | wc -l 560 |
- pgpool command
pgpool-II 정보를 표시하는 명령입니다.
| pool command | 수행 결과 |
| show pool_pools ; | postgres=# show pool_pools ; -[ RECORD 559 ]———–+——————– pool_pid | 18723 start_time | 2024-11-12 19:17:38 client_connection_count | 0 pool_id | 3 backend_id | 0 database | username | backend_connection_time | client_connection_time | client_disconnection_time | client_idle_duration | 0 majorversion | 0 minorversion | 0 pool_counter | 0 pool_backendpid | 0 pool_connected | 0 status | Wait for connection -[ RECORD 560 ]———–+——————– pool_pid | 18723 start_time | 2024-11-12 19:17:38 client_connection_count | 0 pool_id | 3 backend_id | 1 database | username | backend_connection_time | client_connection_time | client_disconnection_time | client_idle_duration | 0 majorversion | 0 minorversion | 0 pool_counter | 0 pool_backendpid | 0 pool_connected | 0 status | Wait for connection |
| SHOW POOL_CACHE | postgres=# SHOW POOL_CACHE ; -[ RECORD 1 ]—————+—– num_cache_hits | 0 num_selects | 0 cache_hit_ratio | 0.00 num_hash_entries | 0 used_hash_entries | 0 num_cache_entries | 0 used_cache_entries_size | 0 free_cache_entries_size | 0 fragment_cache_entries_size | 0 |
| SHOW POOL_HEALTH_CHECK_STATS | postgres=# SHOW POOL_HEALTH_CHECK_STATS; -[ RECORD 1 ]—————-+——————– node_id | 0 hostname | 192.168.56.101 port | 5432 status | up role | primary last_status_change | 2024-11-12 13:56:14 total_count | 1545 success_count | 1545 fail_count | 0 skip_count | 0 retry_count | 0 average_retry_count | 0.000000 max_retry_count | 0 max_duration | 75 min_duration | 1 average_duration | 4.308091 last_health_check | 2024-11-12 20:03:22 last_successful_health_check | 2024-11-12 20:03:22 last_skip_health_check | last_failed_health_check | -[ RECORD 2 ]—————-+——————– node_id | 1 hostname | 192.168.56.102 port | 5432 status | up role | standby last_status_change | 2024-11-12 19:13:26 total_count | 1545 success_count | 560 fail_count | 1 skip_count | 984 retry_count | 5 average_retry_count | 0.008913 max_retry_count | 5 max_duration | 5014 min_duration | 2 average_duration | 14.060606 last_health_check | 2024-11-12 20:03:22 last_successful_health_check | 2024-11-12 20:03:22 last_skip_health_check | 2024-11-12 19:13:20 last_failed_health_check | 2024-11-12 14:39:31 |
| SHOW POOL_BACKEND_STATS | postgres=# SHOW POOL_BACKEND_STATS ; -[ RECORD 1 ]————– node_id | 0 hostname | 192.168.56.101 port | 5432 status | up role | primary select_cnt | 6 insert_cnt | 0 update_cnt | 0 delete_cnt | 1 ddl_cnt | 0 other_cnt | 7 panic_cnt | 0 fatal_cnt | 0 error_cnt | 1 -[ RECORD 2 ]————– node_id | 1 hostname | 192.168.56.102 port | 5432 status | up role | standby select_cnt | 0 insert_cnt | 0 update_cnt | 0 delete_cnt | 0 ddl_cnt | 0 other_cnt | 2 panic_cnt | 0 fatal_cnt | 0 error_cnt | 0 |
