pgpool을 사용한 DB 복제 및 부하분산

pgpool을 사용한 DB 복제 및 부하분산

1. 테스트 환경

postgresSQL DB 2개, pgpool 1개

 

2. 테스트 내용

1) pgpool에 2개의 postgreSQL을 등록하여 Master/slave를 구성

2) DML/DDL 실행시 각 DB에 수행

3) load_balance_mode와 backend_weight 파라미터를 설정하여 select 쿼리 분산 테스트

 

3. 파라미터 설정

backend_weight 설정은 각노드의 값을 더했을 때 1로 보며 다음과 같이 1, 4로 설정하였을 경우

backend0 = 20%, backend1 = 80%로 load balancing 수행하므로 select 쿼리의 경우 backend1에서 우선 실행 한다.

backend_hostname0 = '192.168.60.133' backend_port0 = 8888 backend_weight0 = 1 backend_data_directory0 = '/home/post1/9.4/pgsql/data' backend_flag0 = 'ALLOW_TO_FAILOVER'   backend_hostname1 = '192.168.60.133' backend_port1 = 18888 backend_weight1 = 4 backend_data_directory1 = '/home/post2/9.4/pgsql/data' backend_flag1 = 'ALLOW_TO_FAILOVER'   replication_mode = on replicate_select = off insert_lock = on   load_balance_mode = on master_slave_mode = off master_slave_sub_mode = 'slony'

 

4. TEST 결과

1) pgpool에 2개의 DB를 등록하여 Master/slave를 구성

“# - Backend Connection Settings -“ 에 각DB의 ip, port, data_directory를 설정하여 구성

2) DML/DDL 실행시 각 DB에 수행

"replication_mode = on"으로 설정(재기동 필요) 후 pgpool을 통해 DB로 접속하여 DML 실행시 각 DB로 정상 수행됨

 

- node_1 log

2016-01-15 16:08:07 KST [192.168.60.133][postgres][psql][44403]LOG: statement: BEGIN 2016-01-15 16:08:07 KST [192.168.60.133][postgres][psql][44403]LOG: statement: create table test (col_1 numeric); 2016-01-15 16:08:07 KST [192.168.60.133][postgres][psql][44403]LOG: statement: COMMIT 2016-01-15 16:08:23 KST [192.168.60.133][postgres][psql][44403]LOG: statement: BEGIN 2016-01-15 16:08:23 KST [192.168.60.133][postgres][psql][44403]LOG: statement: insert into test values (1); 2016-01-15 16:08:23 KST [192.168.60.133][postgres][psql][44403]LOG: statement: COMMIT

- node_2 log

2016-01-15 16:08:07 KST [192.168.60.133][postgres][psql][44404]LOG: statement: BEGIN 2016-01-15 16:08:07 KST [192.168.60.133][postgres][psql][44404]LOG: statement: create table test (col_1 numeric); 2016-01-15 16:08:07 KST [192.168.60.133][postgres][psql][44404]LOG: statement: COMMIT 2016-01-15 16:08:23 KST [192.168.60.133][postgres][psql][44404]LOG: statement: BEGIN 2016-01-15 16:08:23 KST [192.168.60.133][postgres][psql][44404]LOG: statement: insert into test values (1); 2016-01-15 16:08:23 KST [192.168.60.133][postgres][psql][44404]LOG: statement: COMMIT

 

3) load_balance_mode와 backend_weight 파라미터를 설정하여 select 쿼리 분산 테스트

"load_balance_mode = on"과 “backend_weight” 파라미터를 설정하여 쿼리실행의 우선순위를 slave가 높게 설정 하였다.

 

- 현재 노드상태정보

[post1:/home/post1/9.4/pgpool/etc]$psql -p 9999 -U postgres -c "show pool_nodes" node_id | hostname | port | status | lb_weight | role ---------+----------------+-------+--------+-----------+-------- 0 | 192.168.60.133 | 8888 | 2 | 0.200000 | master 1 | 192.168.60.133 | 18888 | 2 | 0.800000 | slave

 

- 쿼리 수행

[post1:/home/post1/9.4/pgpool/etc]$psql -p 9999 -U postgres psql (9.4.4) Type "help" for help.   postgres=# select * from test; col_1 ------- (0 rows)   postgres=# insert into test values (10000); INSERT 0 1   postgres=# select * from test; col_1 ------- 10000 (1 rows)   postgres=# \q

 

- node_1 log

2016-01-15 16:11:51 KST [][[unknown]][[unknown]][44619]LOG: connection received: host=192.168.60.133 port=37721 2016-01-15 16:11:51 KST [192.168.60.133][postgres][[unknown]][44619]LOG: connection authorized: user=postgres database=postgres   2016-01-15 16:12:25 KST [192.168.60.133][postgres][psql][44619]LOG: statement: BEGIN 2016-01-15 16:12:25 KST [192.168.60.133][postgres][psql][44619]LOG: statement: insert into test values (10000); 2016-01-15 16:12:25 KST [192.168.60.133][postgres][psql][44619]LOG: statement: COMMIT   2016-01-15 16:15:51 KST [192.168.60.133][postgres][psql][44619]LOG: statement: DISCARD ALL 2016-01-15 16:15:51 KST [192.168.60.133][postgres][psql][44619]LOG: disconnection: session time: 0:04:00.444 user=postgres database=postgres host=192.168.60.133 port=37721

 

- node_2 log

2016-01-15 16:11:51 KST [][[unknown]][[unknown]][44620]LOG: connection received: host=192.168.60.133 port=40577 2016-01-15 16:11:51 KST [192.168.60.133][postgres][[unknown]][44620]LOG: connection authorized: user=postgres database=postgres   2016-01-15 16:11:58 KST [192.168.60.133][postgres][psql][44620]LOG: statement: select * from test;   2016-01-15 16:12:25 KST [192.168.60.133][postgres][psql][44620]LOG: statement: BEGIN 2016-01-15 16:12:25 KST [192.168.60.133][postgres][psql][44620]LOG: statement: insert into test values (10000); 2016-01-15 16:12:25 KST [192.168.60.133][postgres][psql][44620]LOG: statement: COMMIT   2016-01-15 16:12:40 KST [192.168.60.133][postgres][psql][44620]LOG: statement: select * from test;   2016-01-15 16:15:51 KST [192.168.60.133][postgres][psql][44620]LOG: statement: DISCARD ALL 2016-01-15 16:15:51 KST [192.168.60.133][postgres][psql][44620]LOG: disconnection: session time: 0:04:00.442 user=postgres database=postgres host=192.168.60.133 port=40577

 

5. 고려해야 할 점

1) pgpool을 통해 접속했을 경우에는 DDL/DML을 각DB에 실행을 하여 정합성에 문제가 없으나, DB로 직접 접속을 하여 object 또는 data를 변경 하였을경우에 대한 정합성 보장을 할수 없음.

2) 한 개의 DB가 down 되었을 경우 다른 노드로 서비스가 가능하나, 이후 복구에 대한 방안이 필요함

 

6. appendix

<< pgpool.conf >>

# CONNECTIONS

# - pgpool Connection Settings -
listen_addresses	TCP / IP 연결을 받아들이 주소를 호스트 이름 또는 IP 주소로 지정합니다. "*"를 지정하면 모든 IP 인터페이스에서의 연결을 허용합니다. '' ''를 지정하면 TCP / IP 연결을 허용하지 않습니다
port	pgpool가 연결을 받아들이는 포트 번호입니다. 기본값은 9999입니다
socket_dir	PostgreSQL 서버의 Unix domain socket 디렉토리입니다. 기본값은 '/ tmp'입니다.

# - pgpool Communication Manager Connection Settings - (pgpool Control Port)
pcp_listen_addresses	TCP / IP 연결을 받아들이 주소를 호스트 이름 또는 IP 주소로 지정합니다. "*"를 지정하면 모든 IP 인터페이스에서의 연결을 허용합니다. '' ''를 지정하면 TCP / IP 연결을 허용하지 않습니다
pcp_port	PCP에 연결하기위한 포트 번호입니다. 기본값은 9898입니다.
pcp_socket_dir	PCP의 Unix domain socket 디렉토리입니다. 기본값은 '/ tmp'입니다.

# - Backend Connection Settings -
backend_hostname0	PostgreSQL의 호스트 이름 또는 IP address를 지정합니다
backend_port0	PostgreSQL의 포트 번호입니다.
backend_weight0	로드 밸런스 모드에 배분 가중치를 0에서 1의 값으로 설정합니다. 모든 backend 서버에서 지정한 값에서 상대적 가중치를 계산하여 배분합니다.
backend_data_ directory0	pgpool-II는 PostgreSQL과 연결할 때 사용하는 PostgreSQL의 UNIX 도메인 소켓이 놓여져있는 디렉토리입니다. 이 매개 변수는 온라인 복구시에 사용합니다. 온라인 복구를 사용하지 않는 경우에는 설정할 필요가 없습니다.
backend_flag0	백엔드 단위의 다양한 동작을 제어하는 플래그입니다. "backend_flag"뒤에 숫자를 붙여서 어떤 백엔드 플래그를 지정합니다.   - ALLOW_TO_FAILOVER 장애와 분리 할 수​​ 있습니다. 기본값은 ALLOW_TO_FAILOVER 이며, DISALLOW_ TO_FAILOVER동시에 지정할 수 없습니다. - DISALLOW_TO_FAILOVER 장애와 분리가 되지 않습니다. heartbeat이나 Pacemaker 등의 HA (High Availabi lity)를 사용하여 백엔드를 이중화를 사용하여 pgpool-II 에서 장애의 제어를 원하지 않는 경우 사용하며 .ALLOW_TO_FAILOVER 동시에 지정할 수 없습니다.

※ backend 뒤에 숫자를 붙여 PostgreSQL을 추가 할 수 있습니다.

# - Authentication -
enable_pool_hba	pool_hba.conf를 사용한 클라이언트 인증 기능을 활성화 하려면 ‘on’ 합니다.
pool_passwd	md5로 암호화 된 사용자 이름과 암호가 저장된 pool_passwd 인증파일을 생성하여 사용할 수 있습니다.
authentication_timeout	인증 시간 제한을 설정합니다. 기본값은 60(1분) 입니다.

 

# POOLS

# - Pool size -
num_init_children	prefork하는 pgpool 서버 프로세스의 수입니다. 기본값은 32으로되어 있습니다.
max_pool	pgpool의 각 서버 프로세스가 유지하는 PostgreSQL에 대한 최대 연결 수입니다. pgpool는 사용자 이름, 데이터베이스가 같으면 연결을 재사용되지만 그렇지 않으면 새로운 PostgreSQL에 연결을 시도합니다. 따라서 데이터베이스 이름 쌍의 종류 수만 max_pool에 지정해야 합니다.   max_pool의 기본값은 4입니다. 또한 pgpool 전체적으로는 num_init_children * max_pool만큼 PostgreSQL에 연결이 되는 점에 주의하십시오

# - Life time -
child_life_time	유휴 상태에서 child_life_time 초 경과하면 종료하고 새로운 프로세스를 시작합니다. 메모리 누수 다른 장애에 대비 한 예방 조치입니다. child_life_time의 기본값은 300 초, 즉 5 분입니다. 0을 지정하면이 기능이 작동하지 않습니다. 또한, 아직 한번도 연결을 수락하지 프로세스는 child_life_time 적용되지 않습니다.
child_max_connections	커넥션 풀에서 커넥션의 유효 기간을 초 단위로 지정합니다. 0을 지정하면 유효 기간은 무한합니다. connection_life_time의 기본값은 0입니다.
connection_life_time	각 pgpool 자식 프로세스에 연결 횟수가 값을 초과하면 자식 프로세스를 종료합니다
client_idle_limit	클라이언트에서 접속한 쿼리에서 client_idle_limit를 넘어도 다음의 쿼리가 실행되지 않는경우(계속적인 idle상태) 클라이언트 연결을 강제로 해제하고 클라이언트에서 다음 연결을 기다리도록 합니다. 기본값은 0 (해제)입니다

# LOGS

# - Where to log -
log_destination	stderr 또는 syslog 중 하나에 로그를 쓸 수 있습니다. 기본값은 stderr입니다.   주의 : syslog를 사용하는 경우에는 syslog 데몬의 설정을 변경 해야 합니다.

# - Syslog specific -
syslog_facility	syslog를 사용하는 경우, "facility"로 설정 하세요
syslog_ident	syslog를 사용하는 경우 이 매개 변수는 syslog 메시지에 나타나는 프로그램 이름을 설정합니다. 기본값은 "pgpool"입니다.

# FILE LOCATIONS

pid_file_name	pgpool-II의 pid 를 기록한 file(프로세스 ID를 저장 한 파일)의 전체 경로 입니다. 기본값은 '/var/run/pgpool/pgpool.pid'입니다.
logdir	pgpool의 다양한 로그 파일을 저장하는 디렉토리 입니다. logdir의 기본값은 '/ tmp'입니다.

# CONNECTION POOLING

connection_cache	PostgreSQL에 대한 연결을 캐시합니다. 기본값은 ‘on’ 입니다
reset_query_list	세션이 종료 될 때 연결을 초기화하는 SQL 명령을 ";"으로 구분합니다. 기본값은 'ABORT; DISCARD ALL' 이지만, 임의의 SQL 문을 추가해도 상관 없습니다.

# REPLICATION MODE

replication_mode	복제 모드로 동작시키는 경우는 ‘on’을 지정하십시오. 기본값은 ‘off’입니다.
replicate_select	‘on’이라면 SELECT를 복제하여 스텐바이에도 전송하며, ‘off’면 마스터에만 전송 합니다.
insert_lock	insert_lock을 ‘on’으로 하면 자동으로 트랜잭션의 시작과 종료를 합니다.   <example> INSERT INTO ..​​. -> BEGIN; LOCKTABLE... INSERTINTO... COMMIT;
lobj_lock_table	큰 개체 복제를 수행하고자 할 때 잠금 관리에 사용하기 위한 테이블 이름을 지정합니다. 이 테이블이 지정되어 있고, 대형 개체 생성 요청이 클라이언트에서 전송되고, 또한 그 요구에 큰 개체 ID의 명시적인 지정이 포함되지 않은 경우 (즉, lo_creat에서 큰 개체를 만들 경우), pgpool-II는 배타 제어를 위해이 테이블을 잠근 후 큰 개체를 저장하는 시스템 카탈로그 pg_largeobject 대형 개체에 저장되어있는 ID의 최대 값을 꺼내어 그 값 +1 ID를 사용 lo_create ()를 호출하여 큰 개체 생성합니다 (lo_create ()이없는 버전 8.1 이전의 PostgreSQL에서는이 처리되지 않습니다.) 이렇게하면 모든 DB 노드에서 동일한 ID를 가진 라지 오브젝트가 작성되는 것이 보증됩니다.

 

# LOAD BALANCING MODE

load_balance_mode	‘on’을 설정 한 경우 SELECT 등의 문의가 load balance 됩니다.
ignore_leading_white_space	‘on’설정과 ‘load_balance_mode = on’ 설정 시 공백을 무시합니다.

# MASTER/SLAVE MODE

master_slave_mode	‘on’ 설정 시 마스터/슬레이브 모드에서 pgpool를 기동합니다. 기본값은 ‘off’ 이며, 이 모드는 replication_mode과 양립하지 않습니다.
master_slave_sub_mode	마스터/슬레이브 모드에서 pgpool.conf의 replication_mode를 ‘off’로 master_slave_mode를 ‘on’ 으로 합니다(동시에 true로는 할 수 없습니다). 또한 'master_slave_sub_mode'을 지정합니다 위 조건 충족 시 'slony'(기본값)/'stream'을 선택합니다. 'slony'는 Slony-I를 사용할 때 지정합니다.

Posted by Bitnine(비트나인)