Tagged: postgresql

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 권한의 사용자를 다음과 같이 생성해준다.

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

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

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

데이터백업/복구.

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

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

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

Streaming logging replication

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

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

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

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

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

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

혹은 pg_stat_replication 을 조회해도 된다.

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

Hot Standby 세팅.

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

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

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

  1. Standby 서버 Shutdown
  2. Primary 서버 재시작.
  3. Standby 서버 시작.

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

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

테스트.

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

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

잘 보이면 정상이다.

PostgreSQL Replication – Log Shipping

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

File based replcation in PostgreSQL

이 리플리케이션은 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 크기단위로 파일이 쪼개져 저장됩니다.

 

CheckPoint

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

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

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

 

리플리케이션 설정하기

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

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

SSH no password 접속 설정.

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

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

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

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

Master 서버에 postgresql.conf 파일 설정

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

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

서버를 재시작 해줍니다.

Standby 를 위한 데이터 백업하기 

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

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

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

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

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

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

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

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

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

recovery.conf 파일 작성

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

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

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

체크하기

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

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

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

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

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

단점

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

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

리눅스 공유 메모리

리눅스 공유 메모리는 아주 특별하고 중요합니다. 튜닝하는데 있어서 매우 중요한 요소이기 때문입니다. PostgreSQL 를 세팅할때에도 반드시 해줘야 하는 것이기에 정확하게 무엇인지 짚고 넘어가고자 아는 선에 적습니다.

페이지(Page)

가장 먼저 이야기할 것이 바로 페이지(Page) 입니다. 리눅스 시스템은 메모리를 가상으로 만들어 관리합니다. 웃기게도 리눅스 시스템에서 동작하는 프로그램들은 자신들이 시스템의 모든 메모리를 사용할 수 있다, 아니 그보다 더 많은 메모리를 사용할 수 있다라고 착각을 합니다. 이는 리눅스 시스템이 메모리를 가상화해서 프로그램들에게 보여주기 때문입니다.

문제는 리눅스 시스템은 가상화된 메모리들을 페이지(Page)라는 단위로 쪼개서 관리합니다. 특정 크기를 정해서 페이지단위로 나뉘어 관리를 하는데 이렇게 해야지만이 가상화를 하기에 훨씬 쉽고 운영체제를 제작하는데 손이 덜 간다고 합니다.

그런데 이 페이지는 크기를 가지고 있으며 이를 확인하는 방법은 다음과 같습니다.

단위는 bytes 여서 정확하게 4kb 가 됩니다.

이게 무척이나 중요한 것이 PostgreSQL 데이터베이스 세스템에서 Shared_Buffer 값을 계산할때에 페이지 크기를 알아야 합니다.

공유 메모리(Shared Memory)

리눅스 시스템은 놀랍게도 공유메모리(Shared Memory)를 제공합니다. 이게 정말로 뚱딴지 같은 소리인데 왜냐하면 이 공유 메모리는 프로세스간에 서로 공유하기위해서 메모리이기 때문입니다.

프로세스는 자신만의 메모리를 필요로 합니다. 그것으로 끝입니다. 하나의 프로세스가 다른 프로세스의 메모리에 자료를 가져가면 그건 잘못된 것이고 절대로 그렇게 동작하지도 않습니다. 그런데 프로세스 사이에 자료를 공유하고 싶을때 어떻게 해야 할까요?

이럴때 바로 공유 메모리를 사용합니다. 공유 메모리의 특징은 다음과 같습니다.

  • 공유메모리는 최초로 공유 메모리를 만드는 프로세스에 의해서 만들어 집니다. 이렇게 만들어진 메모리는 커널이 관리해 줍니다.
  • 한번 만들어진 공유 메모리 공간은 직접 삭제를 하거나 리눅스 시스템이 재부팅을 하거나 해야지만 없어집니다. 모든 프로세스가 더 이상 공유 메모리를 사용하지 않는다고 자동 삭제되는 일은 결코 없습니다.

뭔가 대단히 있어보이는 듯한데, 한마디로 정의 하면

프로세스간에 데이터를 공유하고자 할때 사용하는 메모리로 커널에 의해서 관리 되어진다.

입니다. 참 쉽조잉~

그런데, 이 공유 메모리는 아주 많이 사용되어지기 때문에 이 공유메모리 양을 얼마로 해주냐가 리눅스 튜닝의 시작입니다. 실제로 커널 파라메터에 다음과 같은게 있습니다.

많이들 하는 튜닝입니다. 최대 공유 메모리를 얼마나 할 것인가 하는 겁니다.

공유 메모리는 최소(SHMMNI), 최대(SHMAX), 그리고 시스템을 통틀어 모든 공유 메모리 세그먼트의 총합(SHMALL) 로 구분이 되는데 이들의 최대 용량(제한 용량)은 다음과 같이 확인할 수 있습니다.

그런데, 여기서 주의해야 할 것이 있습니다. 커널은 이값을 지정할때에 shmmax 는 ‘bytes’ 단위이고 shmall 은 ‘pages’ 단위 입니다.

위에 보시면 커널에 할당된 shmall 값과 ‘ipcs -lm’ 해서 나온 값이 다릅니다. 이는 단위의 차이로 인한 것으로 커널의 shmall 값은 page 단위이기 때문에 다음과 같이 계산하면 ‘ipcs -lm’가 똑같아 집니다.

SHMMAX 값은 새로운 메모리 영역을 할당할때, 그러니까 shmget() 함수를 호출할때에  사용가능한 최대 메모리 양을 말합니다. 다시 강조하면 shmget()함수를 이용할때입니다. 이는 단일 프로세스가 공유메모리를 호출하기 위한 최대 값이라는 이야기 입니다.

SHMALL 은 시스템을 통틀어 모든 프로세스가 사용 가능한 공유메모리 양입니다.

이게 왜 중요해?

대부분 데이터베이스 시스템을 다룰때에 이 부분이 문제가 됩니다. ORACLE, PosgreSQL의 경우에 바로 이 공유 메모리 설정이 작으면 설치가 진행이 안된다거나 서버가 구동된다하더라도 오류 메시를 내보내고 오류를 내는 경우가 많습니다.

어떻게 설정해야 잘 하는 것일까? 혹은 적절한 값은 무엇인가는 어떤 시스템을 다루느냐에 따라서 달라집니다. PostgreSQL 의 경우에 Shared_buffers 값이 있는데, 이 값은 PostgreSQL에서 데이터를 캐쉬하는데 얼마의 RAM을 사용할 것인지를 지정합니다. PostgreSQL의 경우에 Subprocess 모델 기반으로 각 프로세스별 데이터를 공유하기위해서는 바로 공유메모리가 필요하고 Shared_buffers 는 결국 공유 메모리 입니다. 따라서 운영체제에서 PostgreSQL이 요구하는 Shared_buffers 메모리 값보다 적은 공유 메모리를 설정하면 PostgreSQL이 동작하지 않습니다.

PostgreSQL 의 경우에 Shared_buffers 메모리는 32GB 경우에 8GB 를 Shared_buffers 로 권장하고 있습니다. Shared_buffers 메모리는 공유메모리이고 단일 프로그램이 사용할 것이 때문에 SHMMAX 는 최소 8GB로 해야 합니다.

최대 공유메모리는 32GB-4GB 정도해서 28GB를 SHMALL로 지정해줍니다. 정리를하면

위와같이 지정할 수 있겠습니다.

공유 메모리 사용현황

공유메모리 사용현황은 ‘ipcs -a’ 명령어를 이용해서 확인할 수 있습니다.

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초이상 지속된 쿼리들을 차단할 수 있습니다.

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

테스트

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

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

테스트 쿼리

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

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

Linux 쉘상에서 SIGTERM

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

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

다음은 PostgreSQL 로그 입니다.

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

Linux 쉘상에서 SIGKILL

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

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

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

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

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

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

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

PostgreSQL 쉘 상에서 SIGTERM

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

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

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

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

PostgreSQL 쉘 상에서 SIGINT

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

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

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

결론

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

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

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

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

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

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

읽기전용 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 서버에만 적용됩니다. 인터넷 상에는 다양한 공식들이존재하지만 이 공식으로 나온 값만으로도 충분히 잘 동작하였습니다.

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

swappiness

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

Maximum number of open files

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

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

IPCS

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

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

kernel version

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

 

PostgreSQL 튜닝.

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

namecontextshor_desc
default_statistics_targetuserSets the default statistics target.
maintenance_work_memuserSets the maximum memory to be used for maintenance operations.
constraint_exclusionuserEnables the planner to use constraints to optimize queries.
checkpoint_completion_targetsighupTime spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval
effective_cache_sizeuserSets the planner's assumption about the size of the disk cache
work_memuserSets the maximum memory to be used for query workspaces.
wal_bufferspostmasterSets the number of disk-page buffers in shared memory for WAL.
checkpoint_segmentssighupSets the maximum distance in log segments between automatic WAL checkpoints.
shared_bufferspostmasterSets 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 입니다.

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

/etc/security/limits.conf

postgresql.conf

 

 

 

튜플(Tuple) 과 백쿰(VACUUM)

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

통계정보 확보.

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

샘플 테이블을 만들자.

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

1000 로우를 입력된다.

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

테스트

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

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

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

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

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

데이터를 추가한것이 아닌 그냥 기존의 내용을 업데이트 했을 뿐인데, 총 튜플 수가 증가했다. 거기에 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을 해줘야 한다는 것을 뜻한다.

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

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배의 용량을 사용할 수 있다는 것이다.

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

8.4 버전에서는.

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

9.0 버전에서는.

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

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

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

pg_hba.conf 파일

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

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

postgresql.conf 파일

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

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

iptables 설정

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

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

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

PostgreSQL 과 문자셋

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

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

문제는 저러한 문제셋 설정이 과연 향후 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

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

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

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

문제는 이 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

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