Category: PostgreSQL

10/09/2016

Postgresql 9.x Replication – Streaming Log

Streaming log replication 은 Postgresql 9.0 부터 도입된 기능이다. 이 기능은 Primary 에서 Standby 서버로 직접 전송함으로서 replication delay 를 줄여준다. 따라서 pg_xlog 파일전송이 필요가 없다. 또 xlog 를 Streaming 으로 받기 위해서 Primary 서버에 REPLICATION 권한의 접속 계정이 필요하다.

Replication 권한 사용자 생성.

Primary 서버에서 REPLICATION 권한의 사용자를 다음과 같이 생성해준다.

CREATE ROLE repuser WITH REPLICATION PASSWORD '12345' LOGIN

1	CREATE ROLE repuser WITH REPLICATION PASSWORD '12345' LOGIN

그리고 Standby 서버에서 Primary 접속을 위해서 pg_hba.conf 파일을 Standby 서버 접속을 허용해 줍니다.

# The standby server must connect with a user that has replication privileges.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host       replication           repuser     192.168.96.26/32            md5

# The standby server must connect with a user that has replication privileges.

# TYPE DATABASE USER ADDRESS METHOD

host replication repuser 192.168.96.26/32 md5

중요한 것은 DATABASE 에 반드시 ‘replication’ 이여야 한다.

Streaming Replication 을 위한 postgresql.conf 를 다음과 같이 설정한다.

wal_level = archive
max_wal_senders = 2
wal_keep_segments = 32
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'

wal_level = archive

max_wal_senders = 2

wal_keep_segments = 32

archive_mode = on

archive_command = 'cp %p /path_to/archive/%f'

데이터백업/복구.

PostgreSQL 9.3 이후부터 pg_basebackup 명령어를 지원한다. 이 명령어는 REPLICATION 권한을 가진 사용자로 접속을해 Primary 의 데이터 디렉토리를 원격에서 로컬로 복제를 해준다.

Standby 서버를 추가할때에 Primary 서버의 데이터 디렉토리를 복제해야 한다. 앞에서 생성한 계정을 이용하면 된다.

]# sudo -u postgres /opt/pgsql-9.5.0/bin/pg_basebackup -h 192.168.96.24 -D /opt/pgsql-9.5.0/data -U repuser -v -P --xlog-method=stream
Password:
transaction log start point: 1/48000028 on timeline 1
pg_basebackup: starting background WAL receiver
486611/486611 kB (100%), 1/1 tablespace
transaction log end point: 1/480000C0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

]# sudo -u postgres /opt/pgsql-9.5.0/bin/pg_basebackup -h 192.168.96.24 -D /opt/pgsql-9.5.0/data -U repuser -v -P --xlog-method=stream

Password:

transaction log start point: 1/48000028 on timeline 1

pg_basebackup: starting background WAL receiver

486611/486611 kB (100%), 1/1 tablespace

transaction log end point: 1/480000C0

pg_basebackup: waiting for background process to finish streaming ...

pg_basebackup: base backup completed

정상적으로 실행이 되면 위와같이 나온다.

Streaming logging replication

pg_basebackup 을 실행해 Primary 데이터 디렉토리가 복제했고 이로 인해서 postgresql.conf 파일도 Primary 것일 것이다. 이것을 최초의 파일로 교체해 준다.

그리고 recovery.conf 파일을 다음과 같이 작성해 준다.

standby_mode = on
primary_conninfo = 'host=192.168.96.24 port=5432 user=repuser password=12345'
trigger_file = '/tmp/postgresql.trigger.5432'

standby_mode = on

primary_conninfo = 'host=192.168.96.24 port=5432 user=repuser password=12345'

trigger_file = '/tmp/postgresql.trigger.5432'

그리고 Standby 서버를 시작해 준다.

로그파일을 보면 다음과 같이 나온다.

LOG:  database system was interrupted; last known up at 2016-10-02 01:00:13 KST
LOG:  entering standby mode
LOG:  redo starts at 1/48000098
LOG:  consistent recovery state reached at 1/480000C0
LOG:  started streaming WAL from primary at 1/49000000 on timeline 1

LOG: database system was interrupted; last known up at 2016-10-02 01:00:13 KST

LOG: entering standby mode

LOG: redo starts at 1/48000098

LOG: consistent recovery state reached at 1/480000C0

LOG: started streaming WAL from primary at 1/49000000 on timeline 1

그리고 프로세스를 살펴보면 다음과 같다.

]# ps aux | grep post
postgres  1740  0.3  2.4 442504 25112 ?        S    01:14   0:00 /opt/pgsql-9.5.0/bin/postgres -D /opt/pgsql-9.5.0/data
postgres  1741  0.2  0.8 442544  8392 ?        Ss   01:14   0:00 postgres: startup process   recovering 00000001000000010000004A
postgres  1746  0.0  0.7 442504  7820 ?        Ss   01:14   0:00 postgres: checkpointer process
postgres  1747  0.0  0.7 442504  8156 ?        Ss   01:14   0:00 postgres: writer process
postgres  1748  1.0  0.9 447256  9296 ?        Ss   01:14   0:00 postgres: wal receiver process   streaming 1/4B000000
root      1752  0.0  0.0 103284   764 pts/0    S+   01:14   0:00 grep post

]# ps aux | grep post

postgres 1740 0.3 2.4 442504 25112 ? S 01:14 0:00 /opt/pgsql-9.5.0/bin/postgres -D /opt/pgsql-9.5.0/data

postgres 1741 0.2 0.8 442544 8392 ? Ss 01:14 0:00 postgres: startup process recovering 00000001000000010000004A

postgres 1746 0.0 0.7 442504 7820 ? Ss 01:14 0:00 postgres: checkpointer process

postgres 1747 0.0 0.7 442504 8156 ? Ss 01:14 0:00 postgres: writer process

postgres 1748 1.0 0.9 447256 9296 ? Ss 01:14 0:00 postgres: wal receiver process streaming 1/4B000000

root 1752 0.0 0.0 103284 764 pts/0 S+ 01:14 0:00 grep post

Primary 서버에서 Streaming Replication 상태를 다음과 같이 조회해 볼수 있다.

SELECT S.pid, S.usesysid, U.rolname AS usename, S.application_name, S.client_addr, S.client_port, S.backend_start FROM pg_stat_get_activity(NULL) AS S, pg_authid U WHERE S.usesysid = U.oid AND S.datid = 0;

1	SELECT S.pid, S.usesysid, U.rolname AS usename, S.application_name, S.client_addr, S.client_port, S.backend_start FROM pg_stat_get_activity(NULL) AS S, pg_authid U WHERE S.usesysid = U.oid AND S.datid = 0;

혹은 pg_stat_replication 을 조회해도 된다.

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 10285
usesysid         | 16668
usename          | repuser
application_name | walreceiver
client_addr      | 192.168.96.26
client_hostname  |
client_port      | 41866
backend_start    | 2016-10-02 01:14:40.954436+09
backend_xmin     |
state            | streaming
sent_location    | 1/4E000000
write_location   | 1/4E000000
flush_location   | 1/4E000000
replay_location  | 1/4E000000
sync_priority    | 0
sync_state       | async

postgres=# \x

Expanded display is on.

postgres=# select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------

pid | 10285

usesysid | 16668

usename | repuser

application_name | walreceiver

client_addr | 192.168.96.26

client_hostname |

client_port | 41866

backend_start | 2016-10-02 01:14:40.954436+09

backend_xmin |

state | streaming

sent_location | 1/4E000000

write_location | 1/4E000000

flush_location | 1/4E000000

replay_location | 1/4E000000

sync_priority | 0

sync_state | async

하지만 위 방법은 Standby 서버에는 접속할 수 없다. 접속을 시도하면 다음과 같은 에러를 낸다.

psql: FATAL: the database system is starting up

1	psql: FATAL: the database system is starting up

Hot Standby 세팅.

Hot Standby 는 Standby 서버에 접속해 읽기전용의 SQL 연산을 수행할 수 있도록 해준다. 이를 위해서는 먼저 Primary 서버에서 다음과 같이 postgresql.conf 파일에 Hot Standby 설정을 해줘야 한다.

wal_level = 'hot_Standby'

1	wal_level = 'hot_Standby'

Standby 서버도 다음과 같이 설정을 변경해 준다.

hot_Standby = on

1	hot_Standby = on

이제 서버들을 재시작해줘야 하는데 순서가 중요하다. 다음과 같은 순서로 재시작을 해준다.

Standby 서버 Shutdown
Primary 서버 재시작.
Standby 서버 시작.

Standby 서버의 log 파일을 살펴보면 다음과 같다.

]# tail -f data/logfile
LOG:  database system was interrupted; last known up at 2016-10-02 01:49:20 KST
LOG:  entering standby mode
LOG:  redo starts at 1/60000060
LOG:  consistent recovery state reached at 1/60000130
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 1/61000000 on timeline 1

]# tail -f data/logfile

LOG: database system was interrupted; last known up at 2016-10-02 01:49:20 KST

LOG: entering standby mode

LOG: redo starts at 1/60000060

LOG: consistent recovery state reached at 1/60000130

LOG: database system is ready to accept read only connections

LOG: started streaming WAL from primary at 1/61000000 on timeline 1

프로세스는 다음과 같이 나온다.

]# ps aux | grep post
postgres  2049  0.4  2.4 442500 25140 ?        S    01:49   0:00 /opt/pgsql-9.5.0/bin/postgres -D /opt/pgsql-9.5.0/data
postgres  2050  0.4  0.8 442540  8440 ?        Ss   01:49   0:00 postgres: startup process   recovering 000000010000000100000062
postgres  2051  0.0  0.7 442500  7816 ?        Ss   01:49   0:00 postgres: checkpointer process
postgres  2052  0.0  0.7 442500  8096 ?        Ss   01:49   0:00 postgres: writer process
postgres  2054  0.0  0.7 163404  7648 ?        Ss   01:49   0:00 postgres: stats collector process
postgres  2055  1.1  0.9 447124  9292 ?        Ss   01:49   0:00 postgres: wal receiver process   streaming 1/62000060
root      2067  0.0  0.0 103284   764 pts/0    S+   01:50   0:00 grep post

]# ps aux | grep post

postgres 2049 0.4 2.4 442500 25140 ? S 01:49 0:00 /opt/pgsql-9.5.0/bin/postgres -D /opt/pgsql-9.5.0/data

postgres 2050 0.4 0.8 442540 8440 ? Ss 01:49 0:00 postgres: startup process recovering 000000010000000100000062

postgres 2051 0.0 0.7 442500 7816 ? Ss 01:49 0:00 postgres: checkpointer process

postgres 2052 0.0 0.7 442500 8096 ? Ss 01:49 0:00 postgres: writer process

postgres 2054 0.0 0.7 163404 7648 ? Ss 01:49 0:00 postgres: stats collector process

postgres 2055 1.1 0.9 447124 9292 ? Ss 01:49 0:00 postgres: wal receiver process streaming 1/62000060

root 2067 0.0 0.0 103284 764 pts/0 S+ 01:50 0:00 grep post

테스트.

Primary 서버에 테이블을 하나 만들고 데이터를 입력해보고 이것이 Standby 서버로 잘 가는지를 살펴본다.

test=# CREATE TABLE guestbook (visitor_email text, vistor_id serial, date timestamp, message text);
CREATE TABLE
test=# INSERT INTO guestbook (visitor_email, date, message) values ('a@abc.com', current_date, 'This is a test.');
INSERT 0 1

test=# CREATE TABLE guestbook (visitor_email text, vistor_id serial, date timestamp, message text);

CREATE TABLE

test=# INSERT INTO guestbook (visitor_email, date, message) values ('a@abc.com', current_date, 'This is a test.');

INSERT 0 1

그리고 Standby 서버에서 guestbook 테이블이 존재하고 데이터가 존재하는지 살펴본다.

test=# \d
                      List of relations
 Schema |             Name             |   Type   |  Owner
--------+------------------------------+----------+----------
 public | guestbook                    | table    | postgres
 public | guestbook_vistor_id_seq      | sequence | postgres

test=# select * from guestbook;
 visitor_email | vistor_id |        date         |     message
---------------+-----------+---------------------+-----------------
 a@abc.com     |         1 | 2016-10-02 00:00:00 | This is a test.
(1 row)

test=# \d

List of relations

Schema | Name | Type | Owner

--------+------------------------------+----------+----------

public | guestbook | table | postgres

public | guestbook_vistor_id_seq | sequence | postgres

test=# select * from guestbook;

visitor_email | vistor_id | date | message

---------------+-----------+---------------------+-----------------

a@abc.com | 1 | 2016-10-02 00:00:00 | This is a test.

(1 row)

잘 보이면 정상이다.

02/21/2016

PostgreSQL Replication – Log Shipping

PostgreSQL 리플리케이션은 장애를 대비해 가용성을 높이는 최소한의 방법입니다. 현재 버전의 PostgreSQL 다양한 리플리케이션을 지원하는데, 이 문서는 가장 오래되고 기초적인 리플리케이션인 Log Shipping Replication 에 대해서 다룹니다.

이 리플리케이션은 Warm Standby 이라고 합니다. 이에 대한 설명은 PostgreSQL 문서에 다음과 같이 잘 나와 있습니다.

운영 서버에서 만드는 트랜잭션 로그 조각을 정기적으로 대기 서버로 옮기고, 그것을 적용시켜, 운영 서버가 장애로 멈추게 되면, 대기 서버를 운영해서, 가용성을 향상할 수 있다. 이 기능을 warm standby, 또는 log shipping 기능이라고 한다.

이 복제 방식은 먼저, 운영 서버와 대기 서버가 모두 실행 중이어야한다. 하지만, 두 서버 쌍방간의 연결 상태는 다른 복제 방식보다 나빠도 괜찮다. 운영 서버는 아카이브 모드로 운영 되어야하며, 운영 중에 생기는 다 쓴 WAL 세그먼트 파일(스위칭된 트랜잭션 로그 파일)을 차례대로 대기 서버로 보내고, 대기 서버는 복구 모드 전용(복구가 끝나도 다음 복구 파일이 있으면 계속 복구 작업을 하는 상태)으로 실행된다. 이 방식을 이용하면, 데이터베이스 테이블들을 수정해야할 필요가 없다. 또한 다른 복제 방식에 비해 관리 작업 비용도 적으며, 복제 작업이 운영 서버 쪽으로 끼치는 영향도 다른 복제 방식보다 적다.

이 방식의 구현 방법은 간단하다. 운영 서버에서 다 쓴 WAL 파일을 다른 서버로 운송(shipping) 하는 것 뿐이다. PostgreSQL에서는 그 로그 옮기는 작업은 한 번에 하나의 로그 파일을 옮길 수 있도록 구현되어 있다. WAL 파일(16MB)을 옮기는 작업은 데이터베이스 서버 밖에서 관리자가 정의한 방식으로 진행 되기 때문에, 같은 사이트 내로 옮겨도 되고, 전혀 다른 시스템 쪽으로 보내도 되고, 여러 시스템으로 한꺼번에 보내도 된다. 이 부분은 전적으로 관리자에게 맡긴다. 단지 고려해야할 사항은 파일이 전송될 때의 전송량 때문에 운영 서버에 영향을 줄 수도 있다. 이 부분이 염려되면, 전송 속도를 제한 할 수 있는 방법도 고려해야할 것이다. 아니면, 레코드 기반 로그 전달 방식 (스트리밍 복제)을 고려할 수 도 있다. (25.2.5절 참조)

로그 전달 방식은 비동기식임을 기억해야 한다. 다시 말하면, 전송하는 WAL 내용은 이미 커밋된 자료이기 때문에, 그 자료가 대기 서버로 미쳐 전달 되기전에 운영 서버가 멈춰버리면, 그 자료는 손실 된다. 자료 손실량을 줄이는 방법으로 archive_timeout 환경설정값을 몇 초 정도로 짧게 지정해 자주 사용하는 WAL 파일을 바꾸고, 그것을 전송하면, 되겠지만, 그 파일의 크기가 16MB이기 때문에, 잦은 WAL 파일 전송 작업으로 네트워크 사용량이 증가할 것이다. 스트리밍 복제 방식(25.2.5절 참조)을 이용하면, 이 손실 되는 자료량을 최소화 할 수 있다.

(물론 위에서 언급한 한계점이 있기는 하지만,) 대기 서버로 넘어 오는 WAL 파일이 제때에 잘 넘어 온다면, 운영 서버가 중지 되어 대기 서버가 그 역할을 맡기까지 서비스가 중지되는 시각은 극히 짧다. 이렇게 가용성을 향상 시킨다. 베이스 백업 자료를 준비하고, 지금까지 보관해둔 WAL 파일을 가지고, 서버를 복구 하는 방법은 이 방식보다 꽤 많은 서비스 중지 시간이 필요할 것이다. 서버가 복구 되는 기술적인 방식은 동일하지만, 가용성 입장에서는 차이가 난다. warm standby 방식으로 구현되면, 대기 서버 쪽에서는 어떤 쿼리도 사용할 수 없다. 대기 서버 쪽에서 읽기 전용 쿼리를 사용하려면, hot standby 방식으로 구축해야한다. 이 부분은 25.5절에서 자세히 설명한다.

시작하기에 앞서

WAL 파일

대부분의 데이터베이스 시스템은 데이터베이스에 변화와 관련된 모든 작업에 대해서 파일로 기록을 해 둡니다. 정확하게는 데이터를 조작하기에 앞서 어떤 SQL 를 명령했는지에 대해서 로깅을 한 후에 실제 데이터를 조작 합니다. 이는 갑자기 장애가 발생했을시에 이 로그 파일을 이용해 데이터를 복구할 수 있게 됩니다. 이를 WAL (Write Ahead Log) 파일이라고 합니다.

PostgreSQL 도 같은 방법으로 동작합니다. PostgreSQL 에서 WAL 파일은 pg_xlog 디렉토리에 생성되며 특징은 16MB 크기단위로 파일이 쪼개져 저장됩니다.

-rw-------. 1 postgres postgres 16M  2월 20 16:49 00000001000000000000002F
-rw-------. 1 postgres postgres 16M  2월 20 15:15 000000010000000000000040
-rw-------. 1 postgres postgres 16M  2월 20 15:14 00000001000000000000003F
-rw-------. 1 postgres postgres 16M  2월 20 15:14 00000001000000000000003E

-rw-------. 1 postgres postgres 16M 2월 20 16:49 00000001000000000000002F

-rw-------. 1 postgres postgres 16M 2월 20 15:15 000000010000000000000040

-rw-------. 1 postgres postgres 16M 2월 20 15:14 00000001000000000000003F

-rw-------. 1 postgres postgres 16M 2월 20 15:14 00000001000000000000003E

CheckPoint

모든 데이터베이스 시스템은 메모리 기반으로 동작합니다. 메모리에 자주 쓰이는 데이터를 캐쉬해기도 하고 트랜잭션시에도 메모리에 저장됩니다. 특정 시점이 되면 PostgreSQL 은 메모리에 있는 것을 디스크에 쓰기를 함으로써 실제 데이터가 영구적으로 저장이 됩니다.

그런데, 이러한 작업을 수동으로 해야할 때가 있습니다. 대표적으로 PITR(Point In Time Recovery) 백업을 하기 위해서는 모든 메모리에 내용을 강제로 디스크에 쓰여져야 합니다. 이때 PostgreSQL 가 하는 작업이 바로 CheckPoint 입니다.

다시말해, CheckPoint 는 메모리에 있는 내용을 디스크에 강제로 쓰기하는 작업을 말합니다.

리플리케이션 설정하기

설정하는 순서는 다음과 같습니다.

Master 서버가 standby 서버에 postgres 계정으로 ssh 패스워드 인증없이 바로 접속할 수 있도록 설정을 합니다.
Master 서버의 postgresql.conf 파일을 수정하고 서버를 재시작 합니다.
Master 서버에서 PITR 백업을 합니다.
백업본을 standby 서버로 전송합니다.
standby 서버에서 전송받은 백업 압축파일을 해제 합니다.
standby recovery.conf 파일을 작성하고 pg_xlog 디렉토리를 생성하고 소유권을 postgres 로 바꿉니다.
standby 서버를 기동 합니다.

SSH no password 접속 설정.

이는 RSA 키를 교환함으로써 가능 합니다. PostgreSQL 은 postgres 계정으로 동작하기 때문에 postgres 계정에서 Standby postgres 계정으로 RSA 키 접속을 설정해 줍니다.

먼저, Master 서버의 postgres 계정에서 다음과 같이 키를 작성해 줍니다.

-bash-4.2$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/opt/pgsql-9.5.0/.ssh/id_rsa): [그냥 엔터]
Created directory '/opt/pgsql-9.5.0/.ssh'.
Enter passphrase (empty for no passphrase): [그냥 엔터]
Enter same passphrase again: [그냥 엔터]
Your identification has been saved in /opt/pgsql-9.5.0/.ssh/id_rsa.
Your public key has been saved in /opt/pgsql-9.5.0/.ssh/id_rsa.pub.
The key fingerprint is:
61:e4:45:21:ed:fe:56:16:22:57:4b:ff:7f:84:28:00 postgres@localhost.localdomain
The key's randomart image is:
+--[ RSA 2048]----+
|        oo+.     |
|      Eo o.   o  |
|       .+.   o o |
|       ...o o o .|
|        So o o o.|
|          o . + o|
|           o o ..|
|            o   o|
|           .    .|
+-----------------+

-bash-4.2$ ssh-keygen

Generating public/private rsa key pair.

Enter file in which to save the key (/opt/pgsql-9.5.0/.ssh/id_rsa): [그냥 엔터]

Created directory '/opt/pgsql-9.5.0/.ssh'.

Enter passphrase (empty for no passphrase): [그냥 엔터]

Enter same passphrase again: [그냥 엔터]

Your identification has been saved in /opt/pgsql-9.5.0/.ssh/id_rsa.

Your public key has been saved in /opt/pgsql-9.5.0/.ssh/id_rsa.pub.

The key fingerprint is:

61:e4:45:21:ed:fe:56:16:22:57:4b:ff:7f:84:28:00 postgres@localhost.localdomain

The key's randomart image is:

+--[ RSA 2048]----+

| oo+. |

| Eo o. o |

| .+. o o |

| ...o o o .|

| So o o o.|

| o . + o|

| o o ..|

| o o|

| . .|

+-----------------+

이렇게하면 postgres 계정에 “.ssh/id_rsa, .ssh/id_rsa.pub” 두개의 파일이 생성이 됩니다. id_rsa 는 private key 이고 id_rsa.pub 는 public key 입니다. 다음과 같이 publickey 를 Standby 의 postgres 계정을 복사해 줍니다.

ssh-copy-id -i ./id_rsa.pub postgres@192.168.96.26
The authenticity of host '192.168.96.26 (192.168.96.26)' can't be established.
RSA key fingerprint is ba:6a:82:18:0c:88:2b:e8:6b:cb:cb:ef:53:77:ba:4a.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@192.168.96.26's password: [로그인 패스워드]

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'postgres@192.168.96.26'"
and check to make sure that only the key(s) you wanted were added.

ssh-copy-id -i ./id_rsa.pub postgres@192.168.96.26

The authenticity of host '192.168.96.26 (192.168.96.26)' can't be established.

RSA key fingerprint is ba:6a:82:18:0c:88:2b:e8:6b:cb:cb:ef:53:77:ba:4a.

Are you sure you want to continue connecting (yes/no)? yes

/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

postgres@192.168.96.26's password: [로그인 패스워드]

Number of key(s) added: 1

Now try logging into the machine, with: "ssh 'postgres@192.168.96.26'"

and check to make sure that only the key(s) you wanted were added.

이제 그냥 “ssh ‘postgres@192.168.96.26′” 을 입력하면 패스워드 입력 없이 접속이 가능해 집니다.

Master 서버에 postgresql.conf 파일 설정

다음과 같이 postgresql.conf 파일을 설정해 줍니다.

wal_level = archive # minimal, archive, or hot_standby
archive_mode = on # allows archiving to be done
# (change requires restart)
archive_command = 'scp -i /opt/pgsql-9.5.0/.ssh/id_rsa %p postgres@192.168.96.26:/opt/pgsql-9.5.0/archive/%f' # command to use to archive a logfile segment
archive_timeout = 30 # force a logfile segment switch after this
# number of seconds; 0 disables

wal_level = archive # minimal, archive, or hot_standby

archive_mode = on # allows archiving to be done

# (change requires restart)

archive_command = 'scp -i /opt/pgsql-9.5.0/.ssh/id_rsa %p postgres@192.168.96.26:/opt/pgsql-9.5.0/archive/%f' # command to use to archive a logfile segment

archive_timeout = 30 # force a logfile segment switch after this

# number of seconds; 0 disables

archive_command 에 설정한대로 Standby 서버에 archive 디렉토리를 만들어주고 postgres 소유권으로 바꿔 줍니다.

-bash-4.1$ mkdir /opt/pgsql-9.5.0/archive
-bash-4.1$ chown postgres: archive/

1 2	-bash-4.1$ mkdir /opt/pgsql-9.5.0/archive -bash-4.1$ chown postgres: archive/

서버를 재시작 해줍니다.

Standby 를 위한 데이터 백업하기

리플리케이션의 시작은 슬레이브 데이터베이스시스템을 위해 데이터베이스를 옮겨주는 것입니다. PostgreSQL 뿐만아니라 MySQL 도 그렇지만, 데이터베이스의 파일들을 압축해서 옮겨줘야 하는데, 이를 위해서는

Master 서버의 작업 중단
모든 메모리에 있는 내용을 디스크에 쓰기(CheckPoint)

뒤 두가지가 필요합니다. PostgreSQL 은 이 두가지를 명령어를 통해서 한번에 되도록 지원하는데 두가지 방법이 있습니다. 이 문서에서는 PITR 백업 방법을 설명합니다.

먼저, PostgreSQL 서버에 다음과 같이 해줍니다.

[root@localhost ~]# su - postgres
Last login: Sun Feb 21 23:00:28 KST 2016 on pts/0
-bash-4.2$ /opt/pgsql-9.5.0/bin/psql -U postgres -c "select pg_start_backup('/opt/pgsql-9.5.0/20160221.tar')"
 pg_start_backup
-----------------
 0/30000028
(1개 행)

-bash-4.2$ tar -cv --exclude=/opt/pgsql-9.5.0/data/pg_xlog -f /opt/pgsql-9.5.0/20160221.tar /opt/pgsql-9.5.0/data
/opt/pgsql-9.5.0/data/backup_label
-bash-4.2$ /opt/pgsql-9.5.0/bin/psql -U postgres -c "select pg_stop_backup(), current_timestamp"
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup |             now
----------------+------------------------------
 0/31000050     | 2016-02-21 23:05:05.56062+09
(1개 행)

[root@localhost ~]# su - postgres

Last login: Sun Feb 21 23:00:28 KST 2016 on pts/0

-bash-4.2$ /opt/pgsql-9.5.0/bin/psql -U postgres -c "select pg_start_backup('/opt/pgsql-9.5.0/20160221.tar')"

pg_start_backup

-----------------

0/30000028

(1개 행)

-bash-4.2$ tar -cv --exclude=/opt/pgsql-9.5.0/data/pg_xlog -f /opt/pgsql-9.5.0/20160221.tar /opt/pgsql-9.5.0/data

/opt/pgsql-9.5.0/data/backup_label

-bash-4.2$ /opt/pgsql-9.5.0/bin/psql -U postgres -c "select pg_stop_backup(), current_timestamp"

NOTICE: pg_stop_backup complete, all required WAL segments have been archived

pg_stop_backup | now

----------------+------------------------------

0/31000050 | 2016-02-21 23:05:05.56062+09

(1개 행)

이제 백업 완료된 파일을 Slave 서버로 옮겨 줍니다.

백업본을 Standby 서버로 옮겨주기

-bash-4.2$ scp -i /opt/pgsql-9.5.0/.ssh/id_rsa 20160221.tar postgres@192.168.96.26:./
20160221.tar                                                                                   100%  679MB  32.3MB/s   00:21
-bash-4.2$

-bash-4.2$ scp -i /opt/pgsql-9.5.0/.ssh/id_rsa 20160221.tar postgres@192.168.96.26:./

20160221.tar 100% 679MB 32.3MB/s 00:21

-bash-4.2$

다음으로 Slave 서버에서 전송되어진 압축파일을 다음과 같이 압축해제해 줍니다.

Standby 서버에서 전송받은 백업 압축파일을 해제

[root@localhost .ssh]# su - postgres
-bash-4.1$ tar xvf 20160221.tar -C /
-bash-4.1$ cd data
-bash-4.1$ mkdir pg_xlog

[root@localhost .ssh]# su - postgres

-bash-4.1$ tar xvf 20160221.tar -C /

-bash-4.1$ cd data

-bash-4.1$ mkdir pg_xlog

이제 recovery.conf 을 작성해 줍니다.

recovery.conf 파일 작성

다음과 같이 recovery.conf 파일을 작성하기 위해서 다음과 같이 data 디렉토리로 이동해 줍니다.

[root@localhost .ssh]# su - postgres
-bash-4.1$ cd data
-bash-4.1$ vim recovery.conf

[root@localhost .ssh]# su - postgres

-bash-4.1$ cd data

-bash-4.1$ vim recovery.conf

recovery.conf 는 다음과 같이 작성해 줍니다.

restore_command = 'cp /opt/pgsql-9.5.0/archive/%f %p'
archive_cleanup_command = '/opt/pgsql-9.5.0/bin/pg_archivecleanup /opt/pgsql-9.5.0/archive/ %r'
standby_mode = 'on'
trigger_file = '/tmp/postgresql.trigger.5432'

restore_command = 'cp /opt/pgsql-9.5.0/archive/%f %p'

archive_cleanup_command = '/opt/pgsql-9.5.0/bin/pg_archivecleanup /opt/pgsql-9.5.0/archive/ %r'

standby_mode = 'on'

trigger_file = '/tmp/postgresql.trigger.5432'

이렇게 파일을 작성하고 역시나 소유권을 postgres 로 변경해줍니다. 그리고 서버를 시작해줍니다.

체크하기

이 렇게 해놓으면 Master 서버에서는 archive 해야할 로그파일을 Standby 서버로 전송하고 Standby 서버는 이것을 replay 하게 됩니다. 그러니까 Standby 서버는 replay 로만 동작하게 됩니다. 이는 상태를 보면 알 수 있습니다. Standby 서버의 프로세스 상태는 다음과 같습니다.

postgres  2380  0.0  2.4 442480 24504 ?        S    23:20   0:00 /opt/pgsql-9.5.0/bin/postgres -D /opt/pgsql-9.5.0/data
postgres  2381  0.0  0.8 442516  8192 ?        Ss   23:20   0:00 postgres: startup process   waiting 000000010000000000000038
postgres  2383  0.0  0.9 442480  9920 ?        Ss   23:20   0:00 postgres: checkpointer process
postgres  2385  0.0  0.9 442480  9628 ?        Ss   23:20   0:00 postgres: writer process

postgres 2380 0.0 2.4 442480 24504 ? S 23:20 0:00 /opt/pgsql-9.5.0/bin/postgres -D /opt/pgsql-9.5.0/data

postgres 2381 0.0 0.8 442516 8192 ? Ss 23:20 0:00 postgres: startup process waiting 000000010000000000000038

postgres 2383 0.0 0.9 442480 9920 ? Ss 23:20 0:00 postgres: checkpointer process

postgres 2385 0.0 0.9 442480 9628 ? Ss 23:20 0:00 postgres: writer process

38번째 파일을 기다리고 있다고 나옵니다. . ‘/opt/pgsql-9.5.0/archive’ 에는 Master 에서 전송한 파일을 받고 받자마자 바로 replay를 합니다. 그리고 3개가 넘어가면 pg_archivecleanup 프로그램이 recovery.conf 에 명시된대로 삭제됩니다. 이는 ‘/opt/pgsql-9.5.0/archive’ 디렉토리를 모니터링 해보면 알 수 있습니다. 없던 파일이 나타나고 사라지는것을 확인할 수 있습니다.

-bash-4.1$ tail -f logfile
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다

-bash-4.1$ tail -f logfile

cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다

로그파일을 보면 현재 상태를 확인할 수 있습니다.

이 Replication 의 특징는 Standby 를 활용할 수 없습니다. Select 전용으로라도 활용할 수 없습니다. 왜냐하면 replay로 동작중이기 때문에 psql 를 이용해서 접속을 하면 다음과 같은 에러메시지를 만나게 됩니다.

-bash-4.1$ /opt/pgsql-9.5.0/bin/psql -U postgres
psql: FATAL:  the database system is starting up

1 2	-bash-4.1$ /opt/pgsql-9.5.0/bin/psql -U postgres psql: FATAL: the database system is starting up

만일 Master 서버에 여러 Standby 서버가 필요하다면 Master 서버에 postgresql.conf 파일에 ‘archive_command’ 에 스크립트를 넣어줍니다.

archive_command = 'myarchivescript %p %f'

myarchivescript 파일 내용.
scp $1 $STANDBYNODE1:$PGARCHIVE/$2
scp $1 $STANDBYNODE2:$PGARCHIVE/$2
scp $1 $STANDBYNODE3:$PGARCHIVE/$2

archive_command = 'myarchivescript %p %f'

myarchivescript 파일 내용.

scp $1 $STANDBYNODE1:$PGARCHIVE/$2

scp $1 $STANDBYNODE2:$PGARCHIVE/$2

scp $1 $STANDBYNODE3:$PGARCHIVE/$2

단점

이 Log Shipping Replication 은 자세한 Replication 상태를 모니터링 할 수가 없습니다. 파일 전송은 PostgreSQL 이 담당하는게 아닌 리눅스 시스템이 담당합니다. 거기다 Standby 서버에는 접속조차 할 수 없어서 Standby 서버에서 제대로 Recovering 이 되고 있는지를 알 수가 없습니다.

동작 방법이 16MB WAL 파일 작성이 완료되어지면 전송이 되어지는데, 그래서 WAL 파일이 자주 생성된다면 파일을 전송하는데 시스템 자원 소모가 커질 우려도 있습니다.

09/24/2015

pg_terminate_backend, pg_cancel_backend

PostgreSQL 에서 쿼리를 취소시키위한 함수로 pg_terminate_backend, pg_cancel_backend 를 제공 합니다. 공식문서에는 다음과 같이 나와 있습니다.

– pg_cancel_backend(pidint) : Cancel a backend’s current query. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.
– pg_terminate_backend(pidint) : Terminate a backend. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.

pg_cancel_backend and pg_terminate_backend send signals (SIGINT or SIGTERM respectively) to backend processes identified by process ID. The process ID of an active backend can be found from the pid column of the pg_stat_activity view, or by listing the postgres processes on the server (using ps on Unix or the Task Manager on Windows). The role of an active backend can be found from the usename column of the pg_stat_activity view.

이 함수를 이용해서 다음과 같이 30초이상 지속된 쿼리들을 차단할 수 있습니다.

=# select pg_terminate_backend(pid) from (select pid from pg_stat_activity where query_start < now() - '30 seconds'::interval) as t;
=# select pg_cancel_backend(pid) from (select pid from pg_stat_activity where query_start < now() - '30 seconds'::interval) as t;

1 2	=# select pg_terminate_backend(pid) from (select pid from pg_stat_activity where query_start < now() - '30 seconds'::interval) as t; =# select pg_cancel_backend(pid) from (select pid from pg_stat_activity where query_start < now() - '30 seconds'::interval) as t;

프로세스에 시그널(Signal)을 보내는거는 똑같은데, 하나는 SIGINT을 다른 하나는 SIGTERM을 받습니다.
SIGINT는 인터럽트(Interrupt) 시그널을 말합니다. 가장 흔하게 볼 수 있는게 어떤 프로그램을 포그라운드(Foreground)로 실행중에 Ctr+C 를 누르는 것이 바로 SIGINT 입니다. 이 시그널을 받은 프로그램은 별도의 행동을 정의하지 않았다면 프로그램을 종료 됩니다.
SIGTERM 은 Kill 시그널로 프로그램 종료 시그널 입니다. 이 시그널을 받은 프로그램은 이 신호를 무시하거나 접속을 차단하고 프로그램 종료를 위한 프로세스를 진행합니다. 한마디로 말해서 안전한 종료 입니다.
우리가 흔히 쓰는 ‘kill -9 pid’ 는 SIGKILL인데, 이것은 시그널을 받은 프로그램의 후속 행동과는 상관없이 즉시 프로그램을 강제 종료시키도록 합니다.

테스트

테스트는 PostgreSQL 의 프로세스에게 SIGTERM, SIGKILL, SIGINT를 보내 이에 어떤 반응을 보이는지를 보는 것이 주 목적입니다. 이를 위해 Linux 쉘 상에서 SIGTERM, SIGKILL을 주는 방법과 PostgreSQL 쉘상에서 위에서 소개했던 두개의 함수를 사용하는 방법을 채택했습니다.

모든 테스트는 화면과 PostgreSQL이 기록하는 로그를 기반으로 평가되었습니다.

테스트 쿼리

테스트를 위해서 실행했던 테스트 쿼리는 다음과 같습니다.

testdb=> SELECT pg_sleep(10);
 pg_sleep
----------
(1 row)

testdb=> SELECT pg_sleep(10);

pg_sleep

----------

(1 row)

위와같이 pg_sleep 함수를 사용한 상태에서 pg_stat_activity 를 보면 다음과 같이 나옵니다.

datid | datname |  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | waiting | state  |              query
-------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+---------------------------------
 16386 | testdb  | 14014 |    16385 | test    | psql             |             |                 |          -1 | 2015-09-22 13:18:13.553564+09 | 2015-09-22 14:43:58.054511+09 | 2015-09-22 14:43:58.054511+09 | 2015-09-22 14:43:58.054514+09 | f       | active | SELECT pg_sleep(100);

-------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+---------------------------------

16386 | testdb | 14014 | 16385 | test | psql | | | -1 | 2015-09-22 13:18:13.553564+09 | 2015-09-22 14:43:58.054511+09 | 2015-09-22 14:43:58.054511+09 | 2015-09-22 14:43:58.054514+09 | f | active | SELECT pg_sleep(100);

Linux 쉘상에서 SIGTERM

먼저 Linux 쉘상에서 SIGTERM 을 테스트 입니다.

saltmaster ~ # ps aux | grep post
postgres  6129  0.0  0.0 145096 15740 ?        S    10:57   0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf
postgres  6130  0.0  0.0 102748  1344 ?        Ss   10:57   0:00 postgres: logger process
root      6510  0.0  0.0  90232  4748 pts/10   S    11:01   0:00 su - postgres
postgres  6517  0.0  0.0  24224  5800 pts/10   S+   11:01   0:00 -su
postgres  6537  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: checkpointer process
postgres  6538  0.0  0.0 145096  4204 ?        Ss   11:01   0:00 postgres: writer process
postgres  6539  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: wal writer process
postgres  6540  0.0  0.0 145916  5800 ?        Ss   11:01   0:00 postgres: autovacuum launcher process
postgres  6541  0.0  0.0 104840  3732 ?        Ss   11:01   0:00 postgres: stats collector process
root     13977  0.0  0.0  90232  4624 pts/0    S    13:18   0:00 su - postgres
postgres 13984  0.0  0.0  24224  5932 pts/0    S    13:18   0:00 -su
postgres 14003  0.0  0.0 101452  7188 pts/0    S+   13:18   0:00 /usr/lib/postgresql/9.2/bin/psql -U test testdb
postgres 14014  0.0  0.0 146444  9552 ?        Ss   13:18   0:00 postgres: test testdb [local] SELECT    <--- 쿼리가 실행중입니다.
]# kill 14014

saltmaster ~ # ps aux | grep post

postgres 6129 0.0 0.0 145096 15740 ? S 10:57 0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf

postgres 6130 0.0 0.0 102748 1344 ? Ss 10:57 0:00 postgres: logger process

root 6510 0.0 0.0 90232 4748 pts/10 S 11:01 0:00 su - postgres

postgres 6517 0.0 0.0 24224 5800 pts/10 S+ 11:01 0:00 -su

postgres 6537 0.0 0.0 145096 3488 ? Ss 11:01 0:00 postgres: checkpointer process

postgres 6538 0.0 0.0 145096 4204 ? Ss 11:01 0:00 postgres: writer process

postgres 6539 0.0 0.0 145096 3488 ? Ss 11:01 0:00 postgres: wal writer process

postgres 6540 0.0 0.0 145916 5800 ? Ss 11:01 0:00 postgres: autovacuum launcher process

postgres 6541 0.0 0.0 104840 3732 ? Ss 11:01 0:00 postgres: stats collector process

root 13977 0.0 0.0 90232 4624 pts/0 S 13:18 0:00 su - postgres

postgres 13984 0.0 0.0 24224 5932 pts/0 S 13:18 0:00 -su

postgres 14003 0.0 0.0 101452 7188 pts/0 S+ 13:18 0:00 /usr/lib/postgresql/9.2/bin/psql -U test testdb

postgres 14014 0.0 0.0 146444 9552 ? Ss 13:18 0:00 postgres: test testdb [local] SELECT <--- 쿼리가 실행중입니다.

]# kill 14014

SIGTERM 을 Linux 쉘에서 보내자 PostgreSQL 쉘에서도 다음과 같은 반응 했습니다.

testdb=> SELECT pg_sleep(100);
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

testdb=> SELECT pg_sleep(100);

FATAL: terminating connection due to administrator command

server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.

다음은 PostgreSQL 로그 입니다.

2015-09-22 14:47:45 KST [14014-9] test@testdb FATAL:  terminating connection due to administrator command
2015-09-22 14:47:45 KST [14014-10] test@testdb STATEMENT:  SELECT pg_sleep(200);
2015-09-22 14:47:45 KST [14014-11] test@testdb LOG:  disconnection: session time: 1:29:32.146 user=test database=testdb host=[local]
2015-09-22 14:47:45 KST [19073-1] [unknown]@[unknown] LOG:  connection received: host=[local]

2015-09-22 14:47:45 KST [14014-9] test@testdb FATAL: terminating connection due to administrator command

2015-09-22 14:47:45 KST [14014-10] test@testdb STATEMENT: SELECT pg_sleep(200);

2015-09-22 14:47:45 KST [14014-11] test@testdb LOG: disconnection: session time: 1:29:32.146 user=test database=testdb host=[local]

2015-09-22 14:47:45 KST [19073-1] [unknown]@[unknown] LOG: connection received: host=[local]

PostgreSQL은 쿼리를 취소한게 아니라 Connection 을 차단하고 있음을 보여주고 있습니다.

Linux 쉘상에서 SIGKILL

이번에는 Linux 쉘상에서 SIGKILL에 대해 테스트 입니다.

saltmaster ~ # ps aux | grep post
postgres  6129  0.0  0.0 145096 15740 ?        S    10:57   0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf
postgres  6130  0.0  0.0 102748  1344 ?        Ss   10:57   0:00 postgres: logger process
root      6510  0.0  0.0  90232  4748 pts/10   S    11:01   0:00 su - postgres
postgres  6517  0.0  0.0  24224  5800 pts/10   S+   11:01   0:00 -su
postgres  6537  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: checkpointer process
postgres  6538  0.0  0.0 145096  4204 ?        Ss   11:01   0:00 postgres: writer process
postgres  6539  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: wal writer process
postgres  6540  0.0  0.0 145916  5800 ?        Ss   11:01   0:00 postgres: autovacuum launcher process
postgres  6541  0.0  0.0 104840  3732 ?        Ss   11:01   0:00 postgres: stats collector process
root     13977  0.0  0.0  90232  4624 pts/0    S    13:18   0:00 su - postgres
postgres 13984  0.0  0.0  24224  5932 pts/0    S    13:18   0:00 -su
postgres 14003  0.0  0.0 101452  7188 pts/0    S+   13:18   0:00 /usr/lib/postgresql/9.2/bin/psql -U test testdb
postgres 19073  0.0  0.0 146848  8504 ?        Ss   14:47   0:00 postgres: test testdb [local] SELECT
]# kill -9 19073

saltmaster ~ # ps aux | grep post

postgres 6129 0.0 0.0 145096 15740 ? S 10:57 0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf

postgres 6130 0.0 0.0 102748 1344 ? Ss 10:57 0:00 postgres: logger process

root 6510 0.0 0.0 90232 4748 pts/10 S 11:01 0:00 su - postgres

postgres 6517 0.0 0.0 24224 5800 pts/10 S+ 11:01 0:00 -su

postgres 6537 0.0 0.0 145096 3488 ? Ss 11:01 0:00 postgres: checkpointer process

postgres 6538 0.0 0.0 145096 4204 ? Ss 11:01 0:00 postgres: writer process

postgres 6539 0.0 0.0 145096 3488 ? Ss 11:01 0:00 postgres: wal writer process

postgres 6540 0.0 0.0 145916 5800 ? Ss 11:01 0:00 postgres: autovacuum launcher process

postgres 6541 0.0 0.0 104840 3732 ? Ss 11:01 0:00 postgres: stats collector process

root 13977 0.0 0.0 90232 4624 pts/0 S 13:18 0:00 su - postgres

postgres 13984 0.0 0.0 24224 5932 pts/0 S 13:18 0:00 -su

postgres 14003 0.0 0.0 101452 7188 pts/0 S+ 13:18 0:00 /usr/lib/postgresql/9.2/bin/psql -U test testdb

postgres 19073 0.0 0.0 146848 8504 ? Ss 14:47 0:00 postgres: test testdb [local] SELECT

]# kill -9 19073

‘kill -9 ‘ 는 SIGKILL 을 의미합니다. PostgreSQL 쉘은 다음과 같이 되었습니다.

testdb=> SELECT pg_sleep(100);
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>  <----- 쉘모양도 깨졌습니다.

testdb=> SELECT pg_sleep(100);

server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

The connection to the server was lost. Attempting reset: Failed.

!> <----- 쉘모양도 깨졌습니다.

SIGTERM과는 달리 “FATAL: terminating connection due to administrator command” 메시지도 없이 바로 접속이 차단되었습니다. 그리고 쉘 모양도 깨졌습니다.

PostgreSQL 에서의 로그는 다음과 같습니다.

2015-09-22 14:53:28 KST [6129-6] LOG:  server process (PID 19073) was terminated by signal 9: Killed
2015-09-22 14:53:28 KST [6129-7] DETAIL:  Failed process was running: SELECT pg_sleep(200);
2015-09-22 14:53:28 KST [6129-8] LOG:  terminating any other active server processes
2015-09-22 14:53:28 KST [6540-2] WARNING:  terminating connection because of crash of another server process
2015-09-22 14:53:28 KST [6540-3] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2015-09-22 14:53:28 KST [6540-4] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2015-09-22 14:53:28 KST [19452-1] [unknown]@[unknown] LOG:  connection received: host=[local]
2015-09-22 14:53:28 KST [19452-2] test@testdb FATAL:  the database system is in recovery mode
2015-09-22 14:53:28 KST [6129-9] LOG:  all server processes terminated; reinitializing
2015-09-22 14:53:28 KST [19453-1] LOG:  database system was interrupted; last known up at 2015-09-22 11:01:35 KST
2015-09-22 14:53:28 KST [19453-2] DEBUG:  checkpoint record is at 0/176D970
2015-09-22 14:53:28 KST [19453-3] DEBUG:  redo record is at 0/176D970; shutdown TRUE
2015-09-22 14:53:28 KST [19453-4] DEBUG:  next transaction ID: 0/725; next OID: 16387
2015-09-22 14:53:28 KST [19453-5] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2015-09-22 14:53:28 KST [19453-6] DEBUG:  oldest unfrozen transaction ID: 711, in database 1
2015-09-22 14:53:28 KST [19453-7] DEBUG:  transaction ID wrap limit is 2147484358, limited by database with OID 1
2015-09-22 14:53:28 KST [19453-8] LOG:  database system was not properly shut down; automatic recovery in progress
2015-09-22 14:53:28 KST [19453-9] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2015-09-22 14:53:28 KST [19453-10] LOG:  record with zero length at 0/176D9D0
2015-09-22 14:53:28 KST [19453-11] LOG:  redo is not required
2015-09-22 14:53:28 KST [19453-12] DEBUG:  resetting unlogged relations: cleanup 0 init 1
2015-09-22 14:53:28 KST [19453-13] LOG:  checkpoint starting: end-of-recovery immediate
2015-09-22 14:53:28 KST [19453-14] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s
2015-09-22 14:53:28 KST [19457-1] LOG:  autovacuum launcher started
2015-09-22 14:53:28 KST [6129-10] LOG:  database system is ready to accept connections

2015-09-22 14:53:28 KST [6129-6] LOG: server process (PID 19073) was terminated by signal 9: Killed

2015-09-22 14:53:28 KST [6129-7] DETAIL: Failed process was running: SELECT pg_sleep(200);

2015-09-22 14:53:28 KST [6129-8] LOG: terminating any other active server processes

2015-09-22 14:53:28 KST [6540-2] WARNING: terminating connection because of crash of another server process

2015-09-22 14:53:28 KST [6540-3] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2015-09-22 14:53:28 KST [6540-4] HINT: In a moment you should be able to reconnect to the database and repeat your command.

2015-09-22 14:53:28 KST [19452-1] [unknown]@[unknown] LOG: connection received: host=[local]

2015-09-22 14:53:28 KST [19452-2] test@testdb FATAL: the database system is in recovery mode

2015-09-22 14:53:28 KST [6129-9] LOG: all server processes terminated; reinitializing

2015-09-22 14:53:28 KST [19453-1] LOG: database system was interrupted; last known up at 2015-09-22 11:01:35 KST

2015-09-22 14:53:28 KST [19453-2] DEBUG: checkpoint record is at 0/176D970

2015-09-22 14:53:28 KST [19453-3] DEBUG: redo record is at 0/176D970; shutdown TRUE

2015-09-22 14:53:28 KST [19453-4] DEBUG: next transaction ID: 0/725; next OID: 16387

2015-09-22 14:53:28 KST [19453-5] DEBUG: next MultiXactId: 1; next MultiXactOffset: 0

2015-09-22 14:53:28 KST [19453-6] DEBUG: oldest unfrozen transaction ID: 711, in database 1

2015-09-22 14:53:28 KST [19453-7] DEBUG: transaction ID wrap limit is 2147484358, limited by database with OID 1

2015-09-22 14:53:28 KST [19453-8] LOG: database system was not properly shut down; automatic recovery in progress

2015-09-22 14:53:28 KST [19453-9] DEBUG: resetting unlogged relations: cleanup 1 init 0

2015-09-22 14:53:28 KST [19453-10] LOG: record with zero length at 0/176D9D0

2015-09-22 14:53:28 KST [19453-11] LOG: redo is not required

2015-09-22 14:53:28 KST [19453-12] DEBUG: resetting unlogged relations: cleanup 0 init 1

2015-09-22 14:53:28 KST [19453-13] LOG: checkpoint starting: end-of-recovery immediate

2015-09-22 14:53:28 KST [19453-14] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s

2015-09-22 14:53:28 KST [19457-1] LOG: autovacuum launcher started

2015-09-22 14:53:28 KST [6129-10] LOG: database system is ready to accept connections

SIGTERM 때와는 달리 서버가 재시작되었습니다. Recovery Mode 로 전환해 트랜잭션을 복구하고 checkpoint 를 날려 메모리를 리셋했습니다. checkpoint 는 PostgreSQL의 shared_buffer 메모리에 있는 모든 데이터를 디스크에 Flush 하도록하는 것으로 메모리 초기화로 부르기도 합니다. 이렇게 되면 메모리 그래프상으로는 갑자기 메모리 사용량이 줄어드는 현상을 보입니다.

어쨌든 SIGKILL은 PostgreSQL을 재시작하는 결과를 가지고 옵니다. 이는 프로세스의 시작시간이 바뀌것으로도 판단할 수 있습니다.

postgres@saltmaster ~/9.2/main/pg_log $ ps aux | grep post
postgres  6129  0.0  0.0 145096 15704 ?        S    10:57   0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf
postgres  6130  0.0  0.0 102748  1344 ?        Ss   10:57   0:00 postgres: logger process
root      6510  0.0  0.0  90232  4748 pts/10   S    11:01   0:00 su - postgres
postgres  6517  0.0  0.0  24224  5800 pts/10   S+   11:01   0:00 -su
root     13977  0.0  0.0  90232  4624 pts/0    S    13:18   0:00 su - postgres
postgres 13984  0.0  0.0  24224  5936 pts/0    S+   13:18   0:00 -su
postgres 19454  0.0  0.0 145096  3488 ?        Ss   14:53   0:00 postgres: checkpointer process
postgres 19455  0.0  0.0 145096  4204 ?        Ss   14:53   0:00 postgres: writer process
postgres 19456  0.0  0.0 145096  3488 ?        Ss   14:53   0:00 postgres: wal writer process
postgres 19457  0.0  0.0 145784  5664 ?        Ss   14:53   0:00 postgres: autovacuum launcher process
postgres 19458  0.0  0.0 104840  3564 ?        Ss   14:53   0:00 postgres: stats collector process
                                                 >프로세스시작시간<

postgres@saltmaster ~/9.2/main/pg_log $ ps aux | grep post

postgres 6129 0.0 0.0 145096 15704 ? S 10:57 0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf

postgres 6130 0.0 0.0 102748 1344 ? Ss 10:57 0:00 postgres: logger process

root 6510 0.0 0.0 90232 4748 pts/10 S 11:01 0:00 su - postgres

postgres 6517 0.0 0.0 24224 5800 pts/10 S+ 11:01 0:00 -su

root 13977 0.0 0.0 90232 4624 pts/0 S 13:18 0:00 su - postgres

postgres 13984 0.0 0.0 24224 5936 pts/0 S+ 13:18 0:00 -su

postgres 19454 0.0 0.0 145096 3488 ? Ss 14:53 0:00 postgres: checkpointer process

postgres 19455 0.0 0.0 145096 4204 ? Ss 14:53 0:00 postgres: writer process

postgres 19456 0.0 0.0 145096 3488 ? Ss 14:53 0:00 postgres: wal writer process

postgres 19457 0.0 0.0 145784 5664 ? Ss 14:53 0:00 postgres: autovacuum launcher process

postgres 19458 0.0 0.0 104840 3564 ? Ss 14:53 0:00 postgres: stats collector process

>프로세스시작시간<

프로세스 시작시간이 14:53분으로 이전과 달라졌습니다.

PostgreSQL 쉘 상에서 SIGTERM

PostgreSQL 쉘 상에서 SIGTERM은 pg_terminate_backend 함수를 사용함으로서 이루어집니다. pg_stat_activity 테이블에서 pid를 알아낸 다음 다음과 같이 쿼리를 했습니다.

postgres=# select pg_terminate_backend(19954);
 pg_terminate_backend
----------------------
 t
(1 row)

postgres=# select pg_terminate_backend(19954);

pg_terminate_backend

----------------------

(1 row)

쿼리를 수행중이던 다른 PostgreSQL 쉘에서는 다음과 같이 메시지가 나왔습니다.

testdb=> SELECT pg_sleep(200);
LOG:  disconnection: session time: 0:01:36.779 user=test database=testdb host=[local]
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

testdb=> SELECT pg_sleep(200);

LOG: disconnection: session time: 0:01:36.779 user=test database=testdb host=[local]

FATAL: terminating connection due to administrator command

server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.

그리고 PostgreSQL 의 로그는 다음과 같습니다.

2015-09-22 15:03:55 KST [20142-4] postgres@postgres LOG:  duration: 2.154 ms  statement: select pg_terminate_backend(19954);
2015-09-22 15:03:55 KST [19954-3] test@testdb FATAL:  terminating connection due to administrator command
2015-09-22 15:03:55 KST [19954-4] test@testdb STATEMENT:  SELECT pg_sleep(200);
2015-09-22 15:03:55 KST [19954-5] test@testdb LOG:  disconnection: session time: 0:01:36.779 user=test database=testdb host=[local]

2015-09-22 15:03:55 KST [20142-4] postgres@postgres LOG: duration: 2.154 ms statement: select pg_terminate_backend(19954);

2015-09-22 15:03:55 KST [19954-3] test@testdb FATAL: terminating connection due to administrator command

2015-09-22 15:03:55 KST [19954-4] test@testdb STATEMENT: SELECT pg_sleep(200);

2015-09-22 15:03:55 KST [19954-5] test@testdb LOG: disconnection: session time: 0:01:36.779 user=test database=testdb host=[local]

이는 Linux 쉘 상에서 SIGTERM을 보낸것과 같은 결과 입니다.

PostgreSQL 쉘 상에서 SIGINT

이번에는 SIGINT에 관한 것입니다.

postgres=# select pg_cancel_backend(20197);
 pg_cancel_backend
-------------------
 t
(1 row)

postgres=# select pg_cancel_backend(20197);

pg_cancel_backend

-------------------

(1 row)

PostgreSQL 쉘에서 쿼리를 보냈던 화면에는 다음과 같이 나왔습니다.

testdb=> SELECT pg_sleep(200);
ERROR:  canceling statement due to user request

1 2	testdb=> SELECT pg_sleep(200); ERROR: canceling statement due to user request

위 내용은 PostgreSQL 로그에도 동일 했습니다.

2015-09-22 15:11:37 KST [20142-6] postgres@postgres LOG:  duration: 1.750 ms  statement: select pg_cancel_backend(20197);
2015-09-22 15:11:37 KST [20197-3] test@testdb ERROR:  canceling statement due to user request
2015-09-22 15:11:37 KST [20197-4] test@testdb STATEMENT:  SELECT pg_sleep(200);

2015-09-22 15:11:37 KST [20142-6] postgres@postgres LOG: duration: 1.750 ms statement: select pg_cancel_backend(20197);

2015-09-22 15:11:37 KST [20197-3] test@testdb ERROR: canceling statement due to user request

2015-09-22 15:11:37 KST [20197-4] test@testdb STATEMENT: SELECT pg_sleep(200);

결론

SIGINT 은 SIGTERM과 중요한 차이점을 보여주고 있습니다. SIGTERM은 connection 자체를 차단하게 합니다. 따라서 쿼리를 하기위해서는 반드시 PostgreSQL로 재 접속이 이루어져야 합니다. 반면에, SIGINT은 쿼리문(statement) 자체를 취소(cancel)하게 함으로써 connection 자체에는 아무런 영향을 주지 않습니다.

09/22/2015

PostgreSQL 프로세스를 Kill 하면 발생하는 일

PostgreSQL 은 프로세스 복제 모델로 동작합니다. 접속을 하면 Postmaster 라 불리는 부모프로세스를 복제해서 접속을 하고 쿼리를 처리하도록 합니다. 그런데 데이터베이스를 운영하다보면 불필요한 쿼리를 없앤다고 운영체제에서 kill 명령어를 이용하는 경우가 있습니다.

결론부터 말하면 절대로 해서는 안되는 일 입니다. 이렇게하게되면 PostgreSQL은 다음과같이 모든 프로세스를 죽이고 서버를 재시작합니다.

2015-09-22 11:01:35 KST [6129-2] LOG:  server process (PID 6345) was terminated by signal 9: Killed
2015-09-22 11:01:35 KST [6129-3] LOG:  terminating any other active server processes
2015-09-22 11:01:35 KST [6135-2] WARNING:  terminating connection because of crash of another server process
2015-09-22 11:01:35 KST [6135-3] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2015-09-22 11:01:35 KST [6135-4] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2015-09-22 11:01:35 KST [6129-4] LOG:  all server processes terminated; reinitializing
2015-09-22 11:01:35 KST [6536-1] LOG:  database system was interrupted; last known up at 2015-09-22 10:57:06 KST
2015-09-22 11:01:35 KST [6536-2] DEBUG:  checkpoint record is at 0/176D910
2015-09-22 11:01:35 KST [6536-3] DEBUG:  redo record is at 0/176D910; shutdown TRUE
2015-09-22 11:01:35 KST [6536-4] DEBUG:  next transaction ID: 0/725; next OID: 16387
2015-09-22 11:01:35 KST [6536-5] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2015-09-22 11:01:35 KST [6536-6] DEBUG:  oldest unfrozen transaction ID: 711, in database 1
2015-09-22 11:01:35 KST [6536-7] DEBUG:  transaction ID wrap limit is 2147484358, limited by database with OID 1
2015-09-22 11:01:35 KST [6536-8] LOG:  database system was not properly shut down; automatic recovery in progress
2015-09-22 11:01:35 KST [6536-9] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2015-09-22 11:01:35 KST [6536-10] LOG:  record with zero length at 0/176D970
2015-09-22 11:01:35 KST [6536-11] LOG:  redo is not required
2015-09-22 11:01:35 KST [6536-12] DEBUG:  resetting unlogged relations: cleanup 0 init 1
2015-09-22 11:01:35 KST [6536-13] LOG:  checkpoint starting: end-of-recovery immediate
2015-09-22 11:01:35 KST [6536-14] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s
2015-09-22 11:01:35 KST [6540-1] LOG:  autovacuum launcher started
2015-09-22 11:01:35 KST [6129-5] LOG:  database system is ready to accept connections

2015-09-22 11:01:35 KST [6129-2] LOG: server process (PID 6345) was terminated by signal 9: Killed

2015-09-22 11:01:35 KST [6129-3] LOG: terminating any other active server processes

2015-09-22 11:01:35 KST [6135-2] WARNING: terminating connection because of crash of another server process

2015-09-22 11:01:35 KST [6135-3] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2015-09-22 11:01:35 KST [6135-4] HINT: In a moment you should be able to reconnect to the database and repeat your command.

2015-09-22 11:01:35 KST [6129-4] LOG: all server processes terminated; reinitializing

2015-09-22 11:01:35 KST [6536-1] LOG: database system was interrupted; last known up at 2015-09-22 10:57:06 KST

2015-09-22 11:01:35 KST [6536-2] DEBUG: checkpoint record is at 0/176D910

2015-09-22 11:01:35 KST [6536-3] DEBUG: redo record is at 0/176D910; shutdown TRUE

2015-09-22 11:01:35 KST [6536-4] DEBUG: next transaction ID: 0/725; next OID: 16387

2015-09-22 11:01:35 KST [6536-5] DEBUG: next MultiXactId: 1; next MultiXactOffset: 0

2015-09-22 11:01:35 KST [6536-6] DEBUG: oldest unfrozen transaction ID: 711, in database 1

2015-09-22 11:01:35 KST [6536-7] DEBUG: transaction ID wrap limit is 2147484358, limited by database with OID 1

2015-09-22 11:01:35 KST [6536-8] LOG: database system was not properly shut down; automatic recovery in progress

2015-09-22 11:01:35 KST [6536-9] DEBUG: resetting unlogged relations: cleanup 1 init 0

2015-09-22 11:01:35 KST [6536-10] LOG: record with zero length at 0/176D970

2015-09-22 11:01:35 KST [6536-11] LOG: redo is not required

2015-09-22 11:01:35 KST [6536-12] DEBUG: resetting unlogged relations: cleanup 0 init 1

2015-09-22 11:01:35 KST [6536-13] LOG: checkpoint starting: end-of-recovery immediate

2015-09-22 11:01:35 KST [6536-14] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s

2015-09-22 11:01:35 KST [6540-1] LOG: autovacuum launcher started

2015-09-22 11:01:35 KST [6129-5] LOG: database system is ready to accept connections

클라이언트와 접속중인 프로세스라서 kill -9를 해도 상관이 없다고 하겠지만 전혀 그렇지 않습니다. 위의 경우에는 데이터가 아무것도 없어서 금방 재시작이 되었지만 아주 바쁜 서버의 경우에, 다시 말해서 트랜잭션이 많은 서버에서는 재시작하는데도 상당히 오랜시간이 걸릴 수 있습니다.

만약 꼭 쿼리를 죽여야겠다고 한다면 psql 명령어로 PostgreSQL에 접속한 후에 pg_terminate_pid 함수를 이용해서 해야 합니다. 이 함수로 인해서 프로세스가 죽더라도 서버가 재시작되는 일은 거의 없습니다.

08/29/2015

읽기전용 PostgreSQL 튜닝.

이 문서는 읽기전용 PostgreSQL 서버를 대상으로 하는 튜닝 경험에 대해서 정리한 것입니다.

환경

테스트된 서버 환경은 다음과 같습니다.

CPU: Inter(R) Xeon(R) CPU E5606@2.13GHz * 2
RAM: 32GB
HDD: SAS 450GB
OS: Linux
PostgreSQL: 9.2

이 서버는 하루에 한번 덤프된 PosgreSQL 데이터를 전송받아서 Restore 하고 읽기전용, 그러니까 SELECT 쿼리만 사용 합니다.

Linux 튜닝

리눅스 튜닝에 핵심은 공유메모리, 최대 열수 있는 파일수, 메모리관련 커널 파라메터들 입니다. 이는 최대 물리 메모리에 영향을 받습니다.

공유메모리

리눅스의 최대 공유메모리는 다음과 같은 계산으로 세팅이 가능합니다. 단, 다음의 공식은 절대적인 기준은 아니며 PostgreSQL 서버에만 적용됩니다. 인터넷 상에는 다양한 공식들이존재하지만 이 공식으로 나온 값만으로도 충분히 잘 동작하였습니다.

_SHMALL=$(expr $(getconf _PHYS_PAGES) \* 30 / 100)
_SHMMAX=$(expr $(getconf PAGE_SIZE) \* $_SHMALL)

1 2	_SHMALL=$(expr $(getconf _PHYS_PAGES) \* 30 / 100) _SHMMAX=$(expr $(getconf PAGE_SIZE) \* $_SHMALL)

32GB 물리 메모리를 가진 서버의 경우에 다음과 같이 리눅스 커널의 최대 공유메모리를 설정할 수 있습니다.

kernel.shmall = 2470023
kernel.shmmax = 10117214208

1 2	kernel.shmall = 2470023 kernel.shmmax = 10117214208

swappiness

데이터베이스 시스템은 고속으로 동작되어야 함으로 스왑 파티션을 사용을 자제해야 합니다. 아예 사용을 하지 않도록 리눅스를 설정합니다.

vm.swappiness = 0

1	vm.swappiness = 0

Maximum number of open files

최대 열기 파일 설정은 기본적인 튜닝입니다. 대부분 다음과 같이 설정을 하면 충분합니다.이는 메모리가 크면 클수록 높여줄 수 있습니다.

postgres        soft      nofile        65535
postgres        hard      nofile        65535

1 2	postgres soft nofile 65535 postgres hard nofile 65535

리눅스 커널은 다음과 같이 설정할 수 있습니다.

fs.file-max = 65535

1	fs.file-max = 65535

IPCS

ipcs 명령어는 PIC(Process Internal Communication) 에 정보를 볼 수 있습니다. 여기서 중요한 것은 세마포어가 중요합니다. 세마포어는 동시 프로세스를 제어하는데 중요한 요소 입니다.

------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767

------ Semaphore Limits --------

max number of arrays = 128

max semaphores per array = 250

max semaphores system wide = 32000

max ops per semop call = 32

semaphore max value = 32767

기본 설정으로도 좋습니다.

kernel version

커널은 3.13 이상을 사용하는 것이 좋습니다. 왜냐하면 3.13 버전에서 아주 크게 Disk I/O 가 향상되었기 때문입니다. PostgreSQL 의 데이터도 결국에는 하드디스크에 저장이 됩니다. 더우기 SELECT 전용으로 사용한다면 데이터를 하드디스크에서 자주 읽어들일겁니다. 따라서 커널 버전을 3.13 이상을 사용해야 합니다.

PostgreSQL 튜닝.

PostgreSQL 튜닝의 핵심은 접속자와 메모리 입니다. 주요 핵심 파라메터는 다음과 같습니다.

name	context	shor_desc
default_statistics_target	user	Sets the default statistics target.
maintenance_work_mem	user	Sets the maximum memory to be used for maintenance operations.
constraint_exclusion	user	Enables the planner to use constraints to optimize queries.
checkpoint_completion_target	sighup	Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval
effective_cache_size	user	Sets the planner's assumption about the size of the disk cache
work_mem	user	Sets the maximum memory to be used for query workspaces.
wal_buffers	postmaster	Sets the number of disk-page buffers in shared memory for WAL.
checkpoint_segments	sighup	Sets the maximum distance in log segments between automatic WAL checkpoints.
shared_buffers	postmaster	Sets the number of shared memory buffers used by the server.

context 부분을 보면 그 값을 적용하기 위해서 무엇을 해야하는지 알려줍니다. user 부분은 PostgreSQL 를 재시작할 필요가 없습니다. 값을 변경하면 바로 그 값이 적용이 되며 오직 사용자 세션에게만 적용됩니다. 다른 사용자 세션에는 적용되지 않습니다. sighup 은 PostgreSQL 서버를 reload 해주거나 HUP Kill 시그널을 주면 적용됩니다. postmaster 는 반드시 PostgreSQL 서버를 재시작해줘야 적용이 됩니다.

default_statistics_target

이것은 통계와 관련이 있습니다. PostgreSQL 쿼리 플래너는 개별 테이블에 통계를 필요로 합니다. 만약 이 값이 적다면, 쿼리 플래너의 결과가 달라집니다. 대신 이 값이 너무 높으면 PostgreSQL 는 개별테이블의 통계를 수집하는데 많은 시간을 사용하게 됩니다.

만약 ‘LIKE’ 쿼리를 자주 사용한다면, 이값을 증가시켜줄 필요가 있습니다. 하지만 기본적인 값으로도 괜찮습니다.

maintenance_work_mem

maintenance 연산은 ‘CREATE INDEX, ALTER TABLE ADD FOREIGN KEY’ 그리고 ‘VACUUM’ 와 깊이 연관이 있습니다. SELECT 전용으로만 사용한다면 VACUUM은 필요가 없습니다. 하지만 덤프 파일을 Restore 할때에 인덱스를 생성하거나 ALTER TABLE 를 많이 사용되게 됩니다.

따라서 만약, 덤프 파일을 가지고 빠른 Restore 를 하고 싶다면 이 값을 조정해줘야 합니다. 단, 이 값은 work_mem 보다 항상 높아야 합니다. 그리고 이 값은 autovacuum_max_workers 값과 관련이 높습니다.

통상적으로 이값은 전체 메모리의 5% 정도로 잡습니다. 만약 32GB 라면 이값은 1600MB 입니다.

maintenance_work_em = 1600MB

1	maintenance_work_em = 1600MB

constraint_exclusion

이 값은 파티션 테이블과 관련이 깊습니다. 만약 파티션 테이블을 사용한다면, 반드신 설정을 해줘야 합니다. 성능향상을 위해서 상속이나 파티션된 테이블을 자주 사용됩니다.

checkpoint_completion_target

0.9 가 기본값인데, 대부분 괜찮습니다.

effective_cache_size

이 크기는 리눅스 시스템에서 모든 애플리케이션을 끈 상태에서 cache 메모리 크기를 봐야 합니다. 이 크기만큼 보통 할당을 합니다. 예를들어 리눅스 캐쉬 사용량이 22GB 라고한다면 이값을 22GB 정도 주면 됩니다.

이렇게 값을 준다고해서 실제로 메모리가 할당이 되지 않습니다. 쿼리 플래너가 작업을 할때에 잠깐사용 합니다. 만약 이를 값이 적다면 리눅스는 파일 시스템을 사용하게 되어서 성능이 저하됩니다.

work_mem

이 메모리는 복잡한 sort가 많이 사용되거나 큰 sort 가 필요하다면 이 값을 증가 시켜줄 필요가 있습니다. 한가지 조심해야 할 것은 이 값은 각 사용자 세션별로 할당이 됩니다. 따라서 이 값을 정할때에 PostgreSQL 의 최대 접속자를 고려해야 합니다.

이 값을 대략 다음과 같이 계산을 합니다.

( OS cache memory / connections ) * 0.5

만약 OS cache memory 사용량이 28GB 이거나 접속량이 20 이라면 다음과 같습니다.

(28/20)*0.5 = 700MB

shared_buffers

전체 메모리의 25% 정도가 적당합니다. 만일 32GB 메모리를 사용한다면 대략 8GB 정도면 충분합니다.

결론

정리를 한다면 다음과 같습니다.

/etc/sysctl.conf

kernel.shmall = 2470023
kernel.shmmax = 10117214208
vm.swappiness = 0
kernel.sched_migration_cost_ns = 5000000
fs.file-max = 65535

kernel.shmall = 2470023

kernel.shmmax = 10117214208

vm.swappiness = 0

kernel.sched_migration_cost_ns = 5000000

fs.file-max = 65535

/etc/security/limits.conf

postgres        soft    nofile           65535
postgres        hard    nofile           65535

1 2	postgres soft nofile 65535 postgres hard nofile 65535

postgresql.conf

default_statistics_target = 100 
maintenance_work_mem = 4GB 
constraint_exclusion = on 
checkpoint_completion_target = 0.9 
effective_cache_size = 16GB 
work_mem = 700MB 
wal_buffers = 8MB 
checkpoint_segments = 16 
shared_buffers = 8GB

default_statistics_target = 100

maintenance_work_mem = 4GB

constraint_exclusion = on

checkpoint_completion_target = 0.9

effective_cache_size = 16GB

work_mem = 700MB

wal_buffers = 8MB

checkpoint_segments = 16

shared_buffers = 8GB

07/12/2015

튜플(Tuple) 과 백쿰(VACUUM)

PostgreSQL 은 다른 데이터베이스 시스템은 테이블(Table)에 데이터를 레코드(Record) 혹은 로우(Row)라고 한다. 그런데, PostgreSQL 에서는 튜플(Tuple)이라는 것이 존재합니다. 이 튜플은 PostgreSQL 에서 매우 중요한 요소 입니다. 이에 대해서 간략하게 알아 보도록 하겠습니다.

통계정보 확보.

먼저 이 튜플을 알기 위해서는 PostgreSQL 이 제공하는 통계정보를 알아 볼 필요가 있다. 이 통계정보에는 로우 갯수와 튜플 갯수등을 볼 수 있고 로우에 변화에 따라서 이들이 어떻게 변하는지를 추적할 수 있으며 이를 토대로 튜플이 과연 무엇인지를 알 수 있게 된다.

샘플 테이블을 만들자.

testdb2=> CREATE TABLE tuple_test (
id NUMERIC NOT NULL,
rand_num NUMERIC NOT NULL,
birth DATE,
CONSTRAINT id_pk PRIMARY KEY (id)
);

testdb2=> CREATE TABLE tuple_test (

id NUMERIC NOT NULL,

rand_num NUMERIC NOT NULL,

birth DATE,

CONSTRAINT id_pk PRIMARY KEY (id)

);

그리고 샘플 데이터를 입력한다.

testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1, 1000);

1	testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()9000 + 10000)::int, CURRENT_DATE - (random() 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1, 1000);

1000 로우를 입력된다.

통계정보를 통해서 튜플의 상태를 확인해보자.

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup, 
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
  relname   | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size 
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
 tuple_test |  1000 |       1000 |          0 |          100.00 |            0.00 | 120 kB         | 48 kB
(1 row)

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,

round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,

pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,

pg_size_pretty(pg_relation_size(c.oid)) as rel_size

FROM pg_class as c

WHERE pg_stat_get_live_tuples(c.oid) > 0

AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;

------------+-------+------------+------------+-----------------+-----------------+----------------+----------

tuple_test | 1000 | 1000 | 0 | 100.00 | 0.00 | 120 kB | 48 kB

(1 row)

테스트

튜플의 개수는 로우 개수와 같다. 데이터 입력이 들어오면 튜플의 개수는 입력되어 들어온 만큼 증가 한다.

그럼 튜플이 뭔지를 보여주는, 특성을 볼수 있는 실험을 해보자. 대부분의 RDBMS 에서는 트랜잭션을 지원한다. 이 트랜잭션의 결과는 COMMIT 아니면 ROLLBACK 이다. COMMIT이 되면 트랜잭션에서 했던 작업이 확정되어 그로 인해서 발생된 결과물은 실제 데이터로 고정된다. ROLLBACK 은 트랜잭션에서 했던 작업일 모두 취소하는 것으로 실제 데이터로 고정되지 않고 삭제처리된다. PostgreSQL 도 트랜잭션을 지원하기 때문에 동작방법은 동일하다.

testdb2=> BEGIN;
testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1001, 1100);
testdb2=> COMMIT;
testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup, 
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
  relname   | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size 
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
 tuple_test |  1100 |       1100 |          0 |          100.00 |            0.00 | 120 kB         | 48 kB
(1 row)

testdb2=> BEGIN;

testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1001, 1100);

testdb2=> COMMIT;

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,

round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,

pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,

pg_size_pretty(pg_relation_size(c.oid)) as rel_size

FROM pg_class as c

WHERE pg_stat_get_live_tuples(c.oid) > 0

AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;

------------+-------+------------+------------+-----------------+-----------------+----------------+----------

tuple_test | 1100 | 1100 | 0 | 100.00 | 0.00 | 120 kB | 48 kB

(1 row)

COMMIT 후에는 확인결과 100개의 로우가 INSERT 되었고 튜플도 그만큼 늘었다. 그럼 ROLLBACK 도 테스트 해보자.

testdb2=> BEGIN;
BEGIN
testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1101, 1200);
INSERT 0 100
testdb2=> ROLLBACK;
ROLLBACK
testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup, 
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
  relname   | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size 
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
 tuple_test |  1200 |       1100 |        100 |           91.00 |            8.00 | 136 kB         | 56 kB
(1 row)

testdb2=> BEGIN;

BEGIN

testdb2=> INSERT INTO tuple_test (id, rand_num, birth) SELECT GENERATE_SERIES, (random()*9000 + 10000)::int, CURRENT_DATE - (random() * 365 * 10)::int - 40 * 365 FROM GENERATE_SERIES(1101, 1200);

INSERT 0 100

testdb2=> ROLLBACK;

ROLLBACK

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,

round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,

pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,

pg_size_pretty(pg_relation_size(c.oid)) as rel_size

FROM pg_class as c

WHERE pg_stat_get_live_tuples(c.oid) > 0

AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;

------------+-------+------------+------------+-----------------+-----------------+----------------+----------

tuple_test | 1200 | 1100 | 100 | 91.00 | 8.00 | 136 kB | 56 kB

(1 row)

총 튜플 수가 증가했다. 그런데, dead_tup 에 100 이 있고 live_tup 에는 1100 으로 로우 개수와 동일하다. 또, 이전과 비교해서 테이블의 용량이 모두 증가했다. 트랜잭션을 롤백해서 실제 데이터가 반영이 안되었음에도 불구하고 어째서 테이블 용량이 증가하고 총 튜플수가 증가하는 걸까?

이번에는 다음과 같이 간단하게 UPDATE 문을 실행해보자.

testdb2=> UPDATE tuple_test SET rand_num = rand_num + 1;
UPDATE 1100
testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup, 
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
  relname   | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size 
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
 tuple_test |  2300 |       1100 |       1200 |           47.00 |           52.00 | 232 kB         | 104 kB
(1 row)

testdb2=> UPDATE tuple_test SET rand_num = rand_num + 1;

UPDATE 1100

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,

round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,

pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,

pg_size_pretty(pg_relation_size(c.oid)) as rel_size

FROM pg_class as c

WHERE pg_stat_get_live_tuples(c.oid) > 0

AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;

------------+-------+------------+------------+-----------------+-----------------+----------------+----------

tuple_test | 2300 | 1100 | 1200 | 47.00 | 52.00 | 232 kB | 104 kB

(1 row)

데이터를 추가한것이 아닌 그냥 기존의 내용을 업데이트 했을 뿐인데, 총 튜플 수가 증가했다. 거기에 dead_tup 의 값도 증가했다. live_tup 만이 실제 데이터의 양이 바뀌지 않았음을 말해주고 있다. 데이터 용량을 말해주는 rel_size 는 저장되는 데이터의 증가와도 관련있어서 증가할 수도 있지만 그 증감이 너무나 크다.

도대체 왜 이러는 걸까? 왜 UPDATE, 트랜잭션의 ROLLBACK 시에 튜플이 증가하고 데이터 저장용량도 증가하는 걸까?

Dead Tuple 과 VACUUM FULL

결론부터 말하자면 MVCC 때문이다. 데이터베이스가 동시접속자 환경에서 데이터 일관성을 유지하기 위한 기법으로 ‘다중버전동시성제어’ 를 사용한다. PostgreSQL에서 트랜잭션이 발생하면 PostgreSQL 은 지금 있는 로우와 똑같은 것을 한번더 복제한다. 내부적으로는 테이블의 row 가 두가지가 생기는것이고 이는 두가지 버전이라고 생각하면 쉽다. 기존의 row 는 아직 COMMIT되기 이전에 사용자들이 보는 용도로, 복제된 또다른 버전의 row 는 트랜잭션 처리용으로 사용되어지게된다. 마치 스냅샷의 여러 버전들처럼 PostgreSQL은 row 에 다야한 버전을 생성하는데, 이것이 바로 튜플이다.

그럼 UPDATE 할때는 어떻게 될까? 위 예제에서는 데이터가 얼마 없어서 순식간에 UPDATE 끝났지만 아주 큰 데이터가 있을경우에는 시간이 걸린다. 그렇다고 SELECT 사용자에게 기다리고 할 순 없어서 PostgreSQL은 row 를 또 다른 버전을 만들어 UPDATE를 실행하고 완료되면 이전 버전은 삭제처리하게 된다.

그래서 UPDATE 시에 보면 dead_tup 의 갯수가 이전 양인 100 에서 전체 row 갯수만큼 1100이 증가된 것이다. PostgreSQL에서 튜플은 바로 이런 것이다. 그런데 여기서 문제가 있다.

UPDATE, ROLLBACK 이 완료되면 이전 버전의 row 들은 실제 활용하는 데이터가 아니기 때문에 하드디스크에 남아있으면 안된다. 하지만 PostgreSQL은 실제 삭제처리를 하지않고 사용자들에게 보이지 않도록만 한다. 튜플에 dead 라고 마킹(marking)만 하고 끝내는 것이다. DELETE 시에도 이렇게 실제데이터는 지우지 않고 Dead Tuple 로 처리된다. 그런데 SELECT를 할때는 어떻게 할까? PostgreSQL 엔진이 데이터 파일에서 데이터를 읽어들일때 이것이 dead 튜플인지 live 튜플인지 분간할 수 없다. 그냥 데이터 크기만큼 읽어들인다. 쉽게 말해서 Dead tuple 도 읽는다는 거다. 이거는 대단히 불합리하고 SELECT 성능에 영향을 미칠 수 밖에 없다.

튜플중에서 Dead Tuple 이 많으면 SELECT 성능이 떨어진다. Row 가 1개 인데, rel_size 용량이 수십GB 일수도 있다. 그렇게되면 PostgreSQL이걸 다 읽어보게되고 실행시간이 길어진다.

그렇다면 Dead Tuple 을 없앨려면 어떻게 해야할까? VACUUM FULL 을 한번 돌려주면 된다. VACUUM은 Dead Tuple 을 삭제처리 해준다. 이는 UPDATE, DELETE, ROLLBACK 이 많은 서버에 경우 반드시 VACUUM FULL을 해줘야 한다는 것을 뜻한다.

testdb2=> vacuum FULL verbose tuple_test;
INFO:  vacuuming "public.tuple_test"
INFO:  "tuple_test": found 1200 removable, 1100 nonremovable row versions in 12 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
VACUUM
testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,
pg_stat_get_live_tuples(c.oid) AS n_live_tup, 
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,
pg_size_pretty(pg_relation_size(c.oid)) as rel_size
FROM pg_class as c 
WHERE pg_stat_get_live_tuples(c.oid) > 0 
AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;
  relname   | t_tup | n_live_tup | n_dead_tup | live_tuple_rate | dead_tuple_rate | total_rel_size | rel_size 
------------+-------+------------+------------+-----------------+-----------------+----------------+----------
 tuple_test |  1100 |       1100 |          0 |          100.00 |            0.00 | 136 kB         | 48 kB
(1 row)

testdb2=> vacuum FULL verbose tuple_test;

INFO: vacuuming "public.tuple_test"

INFO: "tuple_test": found 1200 removable, 1100 nonremovable row versions in 12 pages

DETAIL: 0 dead row versions cannot be removed yet.

CPU 0.00s/0.00u sec elapsed 0.03 sec.

VACUUM

testdb2=> SELECT c.relname, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as t_tup,

pg_stat_get_live_tuples(c.oid) AS n_live_tup,

pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,

round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,

round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,

pg_size_pretty(pg_total_relation_size(c.oid)) as total_rel_size,

pg_size_pretty(pg_relation_size(c.oid)) as rel_size

FROM pg_class as c

WHERE pg_stat_get_live_tuples(c.oid) > 0

AND c.relname NOT LIKE 'pg_%' ORDER BY n_live_tup DESC;

------------+-------+------------+------------+-----------------+-----------------+----------------+----------

tuple_test | 1100 | 1100 | 0 | 100.00 | 0.00 | 136 kB | 48 kB

(1 row)

“tuple_test”: found 1200 removable 이 문장이 Dead Tuple 삭제가 가능하다는 것을 말하는 것이다. 실제 데이터 양(rel_size) 도 줄었다.

07/12/2015

Better VACCUM FULL For PostgreSQL 9.0

원문:https://wiki.postgresql.org/wiki/What’s_new_in_PostgreSQL_9.0#Better_VACUUM_FULL

지금까지 VACUUM FULL 은 매우 느렸다. 이 구문(Statement)는 테이블로부터 빈공간을 확보하고 그것의 크기(Size)를 줄였지만 VACUUM 은 만족할만큼 빠르게 동작하지 않았다.

이것이 느린 이유는 동작 방법에 문제가 있었기 때문이다. 레코드를 그들의 소스블럭으로부터 테이블에 시작점에 가까운 블럭으로 하나하나씩 읽어서 옮겼었다. 그리고 테이블에 끝이 빈공간이면 그 부분을 지웠다.

전략적으로 이러한 방법은 매우 비효율적(Inefficient) 이다. 레코드를 하나하나씩 옮기는 것은 랜덤 I/O를 발생시킨다. 게다가, 인덱스(Index)를 재구성하는 동안에 그것을 유지하고, 더 많은 비용이 들어가는, 인덱스는 파편화된다. 이럴바에는 VACUUM FULL이 다 끝나고 나서 재인덱스(reindex)를 하는 것이 낫다.

Version 9.0 에서 VACUUM FULL은 테이블과 동일한 또 하나의 테이블을 만들고 거거에 모든 레코드를 순차적이게 복사한다. 모든 레코드가 복사되고 나면, 인덱스를 재생성하고 예전 테이블은 삭제되고 새로운 테이블로 대체된다.

이것은 VACUUM 을 보다 빠르게 한다. 하지만 VACUUM FULL은 동작하는 동안에 AccessExclusiveLock 을 필요로 한다. 이전 방법과 비교했을때 이 방법의 결점은 VACUUM FULL을 했을 경우 새로운 버전의 테이블을 만들기 위해서 디스크에 테이블 크기의 2배의 용량을 사용할 수 있다는 것이다.

이제, 두가지 방법에 대해서 실시간으로 비교를 해보자. 먼저 아래와 같은 방법으로 테스트 데이터를 준비 했다.

marc=# CREATE TABLE test (a int); 
CREATE TABLE 
marc=# CREATE INDEX idxtsta on test (a); 
CREATE INDEX 
marc=# INSERT INTO test SELECT generate_series(1,1000000); 
INSERT 0 1000000 
marc=# DELETE FROM test where a%3=0; -- making holes everywhere 
DELETE 333333 
marc=# VACUUM test; 
VACUUM

marc=# CREATE TABLE test (a int);

CREATE TABLE

marc=# CREATE INDEX idxtsta on test (a);

CREATE INDEX

marc=# INSERT INTO test SELECT generate_series(1,1000000);

INSERT 0 1000000

marc=# DELETE FROM test where a%3=0; -- making holes everywhere

DELETE 333333

marc=# VACUUM test;

VACUUM

8.4 버전에서는.

marc=# \timing 
Timing is on. 
marc=# VACUUM FULL test; 
VACUUM 
Time: 6306,603 ms 
marc=# REINDEX TABLE test; 
REINDEX 
Time: 1799,998 ms

marc=# \timing

Timing is on.

marc=# VACUUM FULL test;

VACUUM

Time: 6306,603 ms

marc=# REINDEX TABLE test;

REINDEX

Time: 1799,998 ms

다 합해 대충 9초 정도 걸렸다.

9.0 버전에서는.

marc=# \timing 
Timing is on. 
marc=# VACUUM FULL test; 
VACUUM 
Time: 2563,467 ms

marc=# \timing

Timing is on.

marc=# VACUUM FULL test;

VACUUM

Time: 2563,467 ms

아직 이것은 제품에서(아마 서비스중에라는 뜻인듯..) VACUUM FULL 이 좋은 아이디어라는 뜻은 아니다. 만약 이것이 필요하다면, 아마도 VACUUM 정책이 올바르지 않기 때문일 것이다. (응?)

07/12/2015

명시적 락킹(Explicit Locking)

이 문서는 PostgreSQL 9.1.2 문서중에 13.3 챕터를 해석한 것입니다. 전문 번역가도 아니고 중학교 영어만으로 해석한 것이어서 의미전달이 제대로 되지 않을 수도 있음을 미리 밝힙니다.

원문: http://www.postgresql.org/docs/9.1/static/explicit-locking.html

13.3 명시적 락킹(Explicit Locking)

PostgreSQL 은 테이블의 데이터에 동시적 접근(concurrent access)을 제어하기 위해서 다양한 락 모드(Lock mode)를 제공합니다. 이러한 모드들은 MVCC(다중 버전닝 동시성 제어)가 바라던 해동을 제공해주지 않을때에 애플리케이션 제어 락킹을 하려고 할때에 사용되어질수 있습니다. 또 대부분의 PostgreSQL 명령어들(commands)은 명령어가 실행되는 동안에 비호환적인 방법으로 참조하는 테이블이 변경되거나 버려지진(Dropped)않도록 하기위해서 자동적으로 적절한 락을 획득합니다. (예를들어, TRUNCATE는 같은 테이블에서 다른 연산들을 가지고 안전하게 동시에 실행되어지지 않을 수 있습니다. 그래서 안전성을 획득하기 위해서 강정적으로 테이블에 배타적 락(Exclusive Lock)을 얻얼 수 있습니다.)

13.3.1 테이블 수준의 락들(Table-Level Locks)

아래의 리스트는 PostgreSQL에 의해서 자동적으로 사용되어지는 사용가능한 락 모드와 문맥(contexts)들을 보여줍니다. 당신은 LOCK 이라는 명령어를 통해서 명시적으로 이러한 락들을 획득할 수 있습니다. 한가지 주목할 것은, 비록 이름에 “row”라고 되어있어도 테이블 수준의 락들(Table-Level Locks)이라는 것입니다. 락 모드의 이름들은 역사적입니다.

몇몇 확장된(extent) 이름들은 각 락 모드마다 전통적인 사용법과 반대적입니다. 하지만 의미론적으로는 모두 같습니다. 하나의 락 모드와 다른 락과의 차이에 대한 한가지 사실은 각각 상충되는 락 모드가 설정된다는 것입니다. 두개의 트랜잭션(transaction)은 같은 시점에 같은 테이블에서 상충적인 락 모드(locks of conflicting, 서로 모순되는 락 모드) 를 가질 수 없습니다. (하지만, 하나의 트랙잭션은 그 자체적으로 출돌이 나지 않습니다. 예를 들면, 같은 테이블에서 ACCESS EXCLUSIVE LOCK을 획득하고 후에 ACCESS SHARE LOCK를 획득할 수 있습니다. ) 상충적이지 않은 락 모드는 많은 트랜잭션에 의해서 동시적으로 가질 수 있습니다. 주목할 것은 특정 상황에서 어떤 락 모드는 자체 상충적(self-conflicting)이 됩니다. (예를들어, ACCESS EXCLUSIVE LOCK는 같은 시점에서 하나의 트랜잭션보다 더 많이 가질 수 없습니다.)

Table-level Lock Modes

ACCESS SHARE

오직 ACCESS EXCLUSIVE 락 모드와 상충적입니다.

SELECT 명령어는 참조하는 테이블에 이 모드의 락을 획득합니다. 일반적으로, 테이블에서 수정은 하지 않고 오직 읽기만을 하는 모든 쿼리들은 이 락 모드를 획득합니다.

ROW SHARE

이것은 EXCLUSIVE 와 ACCESS EXCLUSIVE 락 모드와 상충됩니다.

SELECT FOR UPDATE 와 SELECT FOR SHARE 명령어들은 대상 테이블에서 이 모드의 락을 획득합니다. (추가적으로 ACCESS SHARE 는 selected FOR UPDATE/FOR SHARE 가 아닌 참조되어지는 다른 모든 테이블을 락 합니다.

ROW EXCLUSIVE

이것은 SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE 와 ACCESS EXCLUSIVE 락 모드와 상충됩니다.

UPDATE, DELETE, INSERT 명령어는 대상 테이블에서 이 락 모드를 획득합니다. (추가적으로 ACCESS SHARE 는 다른 참조되어지는 모든 테이블을 락 합니다. 일반적으로, 이 모드는 테이블에서 데이터를 수정하는 모든 명령어들에 의해서 이 모드는 획득되어 집니다.

SHARE UPDATE EXCLUSIVE

SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충됩니다. 이 모드는 VACUUM 실행과 동시적 스키마 변화에 대해서 테이블을 보호합니다.

VACUUM( FULL 제외), ANALYZE, CREATE INDEX CONCURRENTLY, 그리고 특정한 ALTER TABLE 폼에 의해서 획득됩니다.

ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE 그리고 ACCESS EXCLUSIVE 락 모드와 상충됩니다. 이 모드는 동시적으로 데이터를 변경하는 것에 대해 테이블을 보호 합니다.

CREATE INDEX(CONCURRENTLY 제외) 에 의해서 획득되어 집니다.

SHARE ROW EXCLUSIVE

ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충 됩니다. 이 모드는 동시적 데이터 변경과 특정시점에서 한 세션이 그것을 가지고 있기위해서 자체 exclusive(self-exclusive) 한 것에 대해서 테이블을 보호 합니다.

이 락 모드는 PostgreSQL의 어떤 명령어로도 자동적으로 획득되어지지 않습니다.

EXCLUSIVE

ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충 됩니다. 이 모드는 오직 동시적 ACCESS SHARE 락만을 허용합니다. 예를들어, 테이블로부터 읽을때에 한 트랜잭션이 이 락 모드로 홀딩되면 다중으로 실행을 할수 있습니다.

이 락 모드는 PostgreSQL의 어떤 명령어로도 테이블에 자동적으로 획득되어지지 않습니다.

ACCESS EXCLUSIVE

모든 모드의 락과 상충됩니다. 이 모드는 어떤 방법으로든 트랜잭션이 테이블에 접근하는 것을 보장합니다.

ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, 그리고 VACUUM FULL 명령어에 의해서 획득되어 집니다. 또, 이 모드는 LOCK TABLE 구문에 대한 기본적인 락 모드 입니다.

Tip: 오직 ACCESS EXCLUSIVE 는 SELECT 구문을 블럭하는 락입니다.

한번 획득되어진 락은 트랜잭션이 끝날때가지 들고 있습니다. 하지만 만약에 저장시점이 설립된 이후에 락이 획득되어진 것이라면, 저장시점이 롤백되는 즉시 해제 됩니다. This is consistent with the principle that ROLLBACK cancels all effects of the commands since the savepoint. The same holds for locks acquired within a PL/pgSQL exception block: an error escape from the block releases locks acquired within it.

07/12/2015

외부에서 Postgresql 접속할 수 있도록 설정하기

Postgresql 설치를 하게되면 외부에서 접속을 기본적으로 할 수 없게 되어 있다. 호스트 기반 인증 파일인 pg_hba.conf 파일만 고친다고 되지 않는다. 외부에서 접속하는 서버의 IP를 192.168.0.18 이라고 가정한다.

pg_hba.conf 파일

Postgresql 은 Host Based Authorization 기반으로 외부 접속을 제어 한다. 이는 pg_hba.conf 파일을 다음과 같이 편집함으로써 가능하다.

host    all             all             192.168.0.18/32           md5

1	host all all 192.168.0.18/32 md5

위 설정은 모든 데이터베이스에 대해서 모든 사용자에 대해서 192.168.0.18 에서 접속을 허용한다는 내용이다.

postgresql.conf 파일

이 파일도 반드시 설정을 바꿔줘야 외부에서 접속을 할 수 있다. 바꿔야 할 설정은 다음과 같다.

listen_addresses = '*'                  # what IP address(es) to listen on;

1	listen_addresses = '*' # what IP address(es) to listen on;

이걸하지 않하면, 외부에서 접속을 할 수 없다. 그리고 이 설정은 반드시 서버를 재시작 해줘야 한다. 따라서 보통 Postgresql 을 설치를 하면 기본적으로 이 설정을 해주고, pg_hba.conf 파일에서 모두 외부접속을 제어한다.

iptables 설정

보통 많은 사람들이 iptables 를 내려놓고 서버를 운영하는데, 매우 위험한 짓이다. 이를 내려놓고 하는 이유는 귀찮기 때문이다. 뭔가를 하는데 자꾸 않되고 하다가 찾다보면 결국 iptables 때문인걸 알게 되면 매우 화가나고 다음부터 이를 피하기위해서 iptables 를 꺼놓는다. 바보같은 짓거리에 서버 관리자로서 자질도 의심되는 행동이다.

Postgresql 을 위한 iptables 설정은 다음과 같다. 참고로 배포판에 rpm 으로 설치된 iptables 를 이용하는 방법이다.

]# vi /etc/sysconfig/iptables
-A INPUT -m state --state NEW,ESTABLISHED -m tcp -p tcp --dport 5432 -j ACCEPT

1 2	]# vi /etc/sysconfig/iptables -A INPUT -m state --state NEW,ESTABLISHED -m tcp -p tcp --dport 5432 -j ACCEPT

이렇게 한 후에 iptables 를 재시작 해주면 된다.

07/12/2015

PostgreSQL 과 문자셋

PostgreSQL 도 문자셋에 관해서 많은 옵션들을 제공한다. 그런데, 대부분은 이에 대해서 잘 모르는 듯해서 여기서 정리해 본다.

PostgreSQL 에서 문자셋 지정을 처음 하는 부분은 바로 설치를 마친후에 initdb 명령어를 사용하면서 부터다 대충 다음과 같이 사용한다.

su -l postgres -c "/usr/local/pgsql9.1/bin/initdb -E UTF8 --locale=ko_KR.UTF-8 --pgdata='$PGDATA' "

1	su -l postgres -c "/usr/local/pgsql9.1/bin/initdb -E UTF8 --locale=ko_KR.UTF-8 --pgdata='$PGDATA' "

문제는 저러한 문제셋 설정이 과연 향후 PostgreSQL 을 사용하는데 있어 어떤 영향을 주는가 하는 것이다. 먼저 PostgreSQL 은 ISO C 와 POSIX 등의 언어표현에 관해 지원 한다.

Locale

보통 initdb –locale=ko_KR.UTF-8 로 사용되어지는 것으로 운영체제에 종속적이다. 운영체제에서 지원하는 locale 만 사용할 수 있는데, 리눅스의 경우에는 ‘locale -a’ 명령어로 확인 가능하다.

이는 운영체제의 사용자에게 보여주는 메시지 문자를 지정한다. 만일 영어로 운영체제를 사용하고 싶다면 바로 이 locale 를 변경하면 된다. 그런데, locale 의 설정은 ‘ko_KR.UTF-8’ 처럼 나오는데 이는 language_territory.codeset 형태이다.

language 는 인간의 사용하는 언어이고 territory 는 ‘한 국가가 다스리는 영토, 지역’을 뜻한다. 예를들어서 fr_CA 도 있는데 이는 캐나다에서 사용하는 프랑스어라는 뜻이 된다. codeset 은 이러한 언어를 컴퓨터 언어로 표현하는 문자 셋이다.

이러한 형태가 나온 이유는 한 국가(영토, 지역)에서 두가지 이상의 언어를 사용할 경우를 대비한 것으로 풀이된다.

LC_COLLATE

이는 매우 중요한 것으로 다음과 같은 것에 영향을 미친다.

대소문자를 구분하는 기능.
문자열 정렬
‘like’ 문에서 인덱스를 사용여부 결정

이는 데이터베이스를 생성할때에도 지정할 수가 있는데, 지정하는 방법은 locale 과 같이 language_territory.codeset 형식이지만 codeset 은 생략하는 경우가 있다. 다음과 같은 명령어로 확인 가능하다.

=# show LC_COLLATE;

1	=# show LC_COLLATE;

문제는 이 LC_COLLATE 는 initdb 시에 한번 결정이 되면 바꿀 수 없고 하지만, Template 을 1번이 아닌 0번으로 할 경우에는 가능하다. Template1 은 initdb 때 생성되는 일종의 Master DB라 보면되고 모든 데이터베이스는 이 Template1 을 기반으로 생성되어진다.

LC_CTYPE

이는 문자의 범주를 정하는 것으로 각 언어마다 가지고 있는 고유한 특성을 타나내기도 한다. 예를들면 단어가 무엇인지, 대문자와 소문자,one byte 문자인지 multi byte 문자인지에 대한 정의등에 대한 것이다. 이는 initdb 실에 결정되면 바꿀 수 없다.

대부분 Locale 을 따라 지정하는게 낫다.

LC_MESSAGE

이는 접속한 클라이언트에게 어떤 언어로 보여줄지를 결정한다.

LC_MONETARY

이는 접속한 클라이언트에게 어떤 화폐단위로 보여줄 것인지를 결정한다.

LC_NUMERIC

이는 접속한 클라이언트에게 어떤 숫자단위로 보여줄 것인지를 결정한다.

LC_TIME

이는 접속한 클라이언트에게 날짜와 시간에 대한 포맷을 결정한다.