블로그 이미지
OSSW(Open Source System SoftWare

calendar

        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30  

Notice

1. PostgreSQL 논리적 디코딩 소개

http://postgresql.kr/docs/current/logicaldecoding.html


9.4 버전에서 새로 등장한 개념으로,

트랜잭션 로그를 출력 플러그인을 이용해서 사용자 정의 형태로 변환하는 기능을 말합니다.


기존 복제가 트랜잭션 처리에서 먼저 기록한(write-ahead) 내용을 다른 서버로 그대로 보내서 그것을 재실행하는 방식의 스트리밍 복제였다면, 논리적 디코딩을 이용하면 논리적 개념으로 데이터베이스 복제가 가능해 집니다.  이 말은 대상 데이터베이스가 똑 같은 OS에, 똑 같은 버전의 PostgreSQL 서버여야 할 필요가 없으며, 심지어 MySQL이나 기타 다른 데이터베이스, 더 나아가 굳이 데이터베이스가 아니어도 복제가 가능하다는 것을 의미합니다. 트랜잭션 로그를 분석해서 원하는 출력 양식으로 바꿀 수 있는 출력 플러그인만 있다면 말이지요.


2. 논리적 디코딩 출력 플러그인들

  • test_decoding
    배포판에 포함된 테스트 용도, SQL이나, pg_recvlogical 명령으로 확인 가능합니다.
  • decoder_raw
    https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw
    트랜잭션 로그를 만들었던 DML로 출력
  • pglogical
    https://2ndquadrant.com/en/resources/pglogical/
    트리거 기반 테이블 단위 복제 솔루션인 slony를 대체할 테이블 단위 복제 솔루션, 멀티 마스터 기능은 아니고, 마스터에서 배포하고 슬레이브에서 구독하는 방식으로 단방향 복제 솔루션입니다.
  • decoding_json
    https://github.com/leptonix/decoding-json
    트랜잭션 로그를 분석해서 그 내용을 json 양식으로 만듭니다. 자료를 뽑을 때는 pg_recvlogical 명령을 사용해서 확인해 볼 수 있습니다.

 

3. 개념

논리적 디코딩 기능을 이용 하는 절차는 다음 순서로 진행합니다.

  1. 원본 서버의 환경 설정값을 확인합니다.
    replication 권한이 있는 사용자가 있어야 하고, (슈퍼유저와 분리하는 것이 안전합니다, - 대신에 아래 pglogical 용이라면, 이 사용자에게 pglogical 관련 객체들 사용할 수 있는 권한도 부여해야합니다. 테스트 용도로라면, 그냥 postgres 슈퍼 유저를 사용하세요)
    CREATE ROLE replicatest REPLICATION LOGIN;

    pg_hba.conf 파일에서 해당 사용자가 스트리밍 복제를 할 수 있도록 설정하고,
    host replication replicatest 192.168.0.10/32 trust

    postgresql.conf 파일에서 wal_level 값은 logical, max_replication_slots 값은 0 보다 크게, max_wal_senders 값도 0보다 크게 지정하고,

    필요하다면 변경한 설정값을 반영하기 위해서 서버 재실행합니다.

    (pglogical 기능 테스트라면 복제 내용을 재 실행할 대기서버 쪽에도, max_replaction_slots 값이 0 이상이어야 하더군요)

  2. 원본 서버 (배포 서버)에서 pg_create_logical_replication_slot() 함수를 이용해서 논리 복제 슬롯을 만듭니다.

    여기서 기억해야 할 것은 이 슬롯이 만들어지는 순간부터 트랜잭션 로그가 논리 복제 기능을 사용하는 응용프로그램(또는 다른 서버)에서 다 빼내가기 전까지 트랜잭션 로그를 지우지 않는다는 것입니다.
    사용하지 않는 복제 슬롯을 만들어두면, 결국 pg_xlog 쪽 WAL 파일 재활용을 하지 않게 되고, 결국 해당 디렉터리 가용 공간이 없어지게 되어 서비스 장애까지 이어질 수 있습니다. 주의해야 합니다.

    논리적 복제 슬롯을 만들 때, 매개변수로 출력 플러그인을 지정합니다.
    SELECT
    pg_create_logical_replication_slot('슬롯이름', '출력플러그인이름', '플러그인에서사용하는옵션이름', '옵션값')
    형태로 SQL 구문으로 지정할 수 있고,
    pg_recvlogical 명령같이 논리적 복제를 구현하는 응용프로그램측에서 스트리밍 복제 프로토콜을 이용 해당 슬롯을 만들 수도 있습니다.

  3. 논리 디코딩을 이용하는 응용프로그램(또는 서버)에서 원본 서버에서 발생한 DML에 대한 트랜잭션 로그를 사용합니다. 사용은 두 가지가 있는데, 하나는 그 로그를 꺼내오는 경우(get)고, 다른 하나는 그냥 두고 가져다 쓰는 경우(peek)입니다.

    응용프로그램이 트랜잭션 로그 디코딩을 요구하면, 출력 플러그인이 해당 복제 슬롯 정보를 확인하고, 해석해야할 트랜잭션 로그를 WAL 파일에서 찾아서 그것을 해당 플러그인 출력 양식에 맞춰 응용프로그램 쪽으로 보냅니다. 이때, get 방식으로 요청하면, 복제 슬롯 상태값에서 lsn(로그 시퀀스 번호)을 증가합니다.

  4. 원본 데이터베이스 서버가 기억하는 복제 슬롯 정보는 마지막 꺼내간 lsn 번호입니다. 즉, 그것을 사용하는 응용프로그램에서 그 자료가 제대로 쓰였는지는 전혀 모릅니다. 또한 이 상태는 checkpoint 작업으로 디스크에 영구 보관됩니다. checkpoint 전 서버 비정상 종료가 있었다면, 서버가 다시 시작되고, 다시 해당 슬롯을 사용하는 응용프로그램 트랜잭션 로그를 달라고 요청하면 이전에 이미 받았던 자료가 올 수도 있습니다.  즉 논리적 복제 슬롯을 이용하는 응용 프로그램이나, 서버는 반드시 이 부분의 중복 처리에 대한 정책을 세워야 합니다.

  5. 해당 복제 슬롯의 상황은 pg_replication_slots 뷰에서 이 슬롯을 스트리밍 리플리케이션 프로토콜로 사용한다면, pg_stat_replication 뷰에서 그 상황을 볼 수 있습니다.


4. pglogical

2ndQuadrant 사에서 만든 PostgreSQL 확장 모듈입니다.

이 글은 https://2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/ 페이지 기준으로 몇가지 작업을 하면서 정리한 것입니다.


이 확장 모듈은 트리거 기반 복제 솔루션인 slony 의 대안으로 개발되었습니다.  왜냐하면, slony가 나온 뒤 PostgreSQL 서버는 background worker, logical decoding 등 많은 기능이 추가되어 이 기능들을 잘 쓰면 테이블 단위 복제가 가능할 것이다는 생각이 들었기 때문입니다.


기본 개념은 운영 서버에서 배포하고, 슬레이브 서버에서 구독하는 방식입니다. 이 배포와 구독은 pglogical 모듈을 설치하고, 재실행하면 생기는 background worker 프로세스가 관리하고, 내용 전달은 pglogical_output 이라는 모듈에서 제공하는 논리적 디코딩 출력 플러그인을 사용해서 스트리밍 복제 프로토콜을 이용합니다.


즉, 배포와 구독 서버간 스트리밍 복제가 가능하도록 설정이 먼저 되어 있어야 합니다. 물론 이전의 스트리밍 복제 대기 서버를 구축하는 것 처럼 pg_basebackup을 이용해서 운영서버 전체를 백업하고 재구축할 필요는 없습니다. 그저 깡통 데이터베이스에 필요한 테이블만 있으면 됩니다.


 

작업 방법은 다음과 같습니다.


  1. 제일 먼저 할 작업은 운영 서버는 배포 역할을 할 수 있도록, 대기 서버는 구독 역할을 할 수 있도록 스트리밍 복제 환경을 만들어야 합니다. 자세한 설명은 윗 페이지 참조

  2. pglogical 이라는 확장 모듈을 설치합니다.
    github (https://github.com/2ndQuadrant/pglogical) 에서 소스를 받아서 직접 컴파일을 하거나, 위 홈페이지에서 해당 패키지를 설치하거나, 자기 환경에 맞게 설치합니다.

  3. psql 창에서 CREATE EXTENSION pglogical; CREATE EXTESION pglogical_output; 두 모듈을 설치합니다.

  4. 각 서버(노드)는 pglogical 모듈을 이용해서 논리적 복제를 사용하겠다는 정보로, 제일 먼저 각 노드별 노드 등록을 합니다. pglogical.create_node() 함수 이용. 이 작업은 각각의 노드에서 해야 합니다. 즉, 접속 dsn 값은 자기 자신의 서버로 접속하는 정보를 입력합니다.

  5. 배포 서버 쪽에서는 배포할 세트를 만들고, 그 세트 안에 배포할 객체들을 등록합니다. 현재는 테이블과 시퀀스입니다. pglogical.create_replication_set(), pglogical.replication_set_add_sequence(), pglogical.replication_set_add_table() 함수를 이용합니다.  확장 모듈을 설치하면, 기본으로 사용할 수 있도록 default 라는 세트가 이미 등록되어 있기 때문에, 특별한 경우가 아니면, 그냥 default 세트에 원하는 객체들을 포함시기는 작업 (repliaction_set_add_*) 만 하면 됩니다.

  6. 구독 서버는 위에서 작업한 자신의 노드 등록과 함께, 구독 등록도 해야 합니다. pglogical.create_subscription() 함수 이용. 이 때 기억해야 할 것은 논리적 스트리밍 복제 슬롯이 만들어지는 시점 - 트랜잭션 로그를 보내는 시작 lsn 값이 지정되는 시점 - 은 구독 서버가 구독 작업을 시작하는 시점이라는 점입니다. 즉, 구독 이후부터 발생한 변경 사항이 구독 서버에 적용 될 것이라는 점입니다.
    (해당 테이블을 아에 그대로 복제하겠다면, pglogical.alter_subscription_resynchronize_table() 함수를 이용합니다.)

    또 하나 기억해야 할 것은 구독 시작 이후 그 구독 서버를 더 이상 사용하지 않아  서버가 중지된 상태여도, 명시적으로 구독 중지(pglogical.drop_subscription() 함수 이용) 처리를 하지 않았다면, 구독 신청으로 만들어진 논리적 복제 슬롯은 삭제 되지 않는다는 점입니다. 이 말은 해당 슬롯의 마지막 lsn 뒤 부터는 모든 트랜잭션 로그를 보관하겠다는 것을 의미합니다. 결국 pg_xlog 쪽 가용 공간이 점점 줄어 나중에는 서버 장애로까지 발전할 수도 있다는 것입니다.


4.1 pglogical 한계

논리적 복제를 이용하기 때문에, 논리적 복제 한계가 그대로 있습니다. 자세한 이야기는 이 글 맨 앞에 언급한 사용 설명서를 참조하세요.


CREATE EXTENSION 명령이 데이터베이스 단위로 이루워짐으로 결국 이 복제 작업도 데이터베이스 단위일 수 밖에 없으며, 여러 데이터베이스를 여러 객체를 복제하는 상황이라면, 그 만큼의 스트리밍 복제 환경이 구축되어야 합니다.


또한 복제 작업은 background worker 프로세스로 진행 됨으로 슈퍼 유저 권한으로 작업이 진행된다는 점입니다. 슈퍼 유저 권한을 부여하고 싶지 않다면, pglogical 스키마 안에 있는 모든 객체를 사용할 수 있는 권한을 스트리밍 복제 작업하는 계정에게 부여해야 합니다. 


그 외 여러 한계점들에 대해서는 2ndQuadrant 사 홈페이지를 참조하세요.


5. 마치며

이상으로 논리적 복제와 그것을 이용하는 pglogical 확장 모듈을 이용한 테이블 단위 복제에 대해서 살펴봤습니다.  실무 환경에서 이 복제 정책을 도입 할 때는 꽤 많은 다양한 예외 상황을 고려해야 합니다.  구독 서버에서 해당 자료가 변경 된 경우와, 그 테이블이 복잡한 관계성을 유지하고 있는 경우(트리거, 참조키, 상속)에서 자료 동기화 유연성을 어떻게 확보할지, 그리고, 구독 서버의 장애시 배포 서버의 트랜잭션 로그 관리 등 꼼꼼하게 여러
상황을 따져봐야 할 것입니다.


하지만, 좀 더 유연하게 생각하면, 파티션 테이블을 만들고, 하위 테이블 들에 대해서 서로 이 pglogical로 묶으면 부하 분산용 다중 마스터 환경도 구현 가능할 듯합니다. 참신한 아이디어만 있다면, 충분히 유용하게 사용될 수 있는 모듈임에는 분명한 듯합니다.


- posted by 김상기


posted by 김상기 ioseph

0. PostgreSQL 20주년

올해로 PostgreSQL이 세상에 나온 지 스물 돌이 되었습니다.

마이클 스턴브래커 어르신의 postgres 프로젝트까지 거슬러가면 이보다 훨씬 더 오래되었겠지만, PostgreSQL이라는 이름이 등장한 지는 올해로 20년이 되었네요.

이 기념으로 PostgreSQL 커뮤니티에서는 위와 같이 포스터도 만들면서 자축하고 있습니다. 또한 올 해로 열 번째 열린 PostgreSQL 개발자 사용자 회의가 캐나다 오타와에서 열렸고, 제가 다니고 있는 회사 지원으로 이번에 같이 일하고 있는 동료와 함께 저도 참석하게 되었습니다.

이 글은 올 해 열린 PGCon 행사 참관 후기입니다.


1. PGCon 소개

PGCon (http://pgcon.org) 행사는 PostgreSQL 개발자, 사용자가 일 년에 한 번 모이는 PostgreSQL 최대 행사입니다. 참석하는 사람들은 대부분 PostgreSQL 커뮤니티 메일링 리스트에서 꾸준히 활동하는 사람들로 매번 메일로만 의견을 주고 받던 사람들이 한 자리에 모여 여러 주제로 발표도 하고, 토의도 하고, 같이 술을 마시며 놀기도 하는 행사입니다.

주로 다루는 내용은 크게 세 부분으로 구분되는데,

하나는 PostgreSQL 차기 버전에 대한 토의 - 일반적인 새 기능 발표회 같은 분위기가 아니라 서로 의견을 주고 받으며 서로의 부족한 부분을 채워나가는 자리입니다. 여기서 발표되는 새 기능들은 대부분 차기 버전에 수용되기는 하지만 막상 구현된 모습은 이 발표 때의 모습과는 사뭇 다른 경우가 제법 있었습니다.

다른 하나는 각 기업들, 각 지역 커뮤니티들의 사례 발표들로 구성됩니다. 대부분의 발표는 PostgreSQL 엔진에 촛점이 맞춰져 글로벌 개발자 그룹도 자리를 함께 하면서 자신들이 만든 프로그램이 어떻게 쓰이고, 어떤 점이 미흡하고, 어떻게 개선해 나갈 것인가를 살펴봅니다.

또 다른 하나는 PostgreSQL을 사용하는데 필요한 부가 솔루션, PostgreSQL을 이용한 솔루션을 소개합니다. 그 또한 오픈 소스로 되어 있는 것이 대부분이고, 자기 회사의, 또는 자신들의 오픈 소스 프로젝트를 자랑하는 자리입니다.


2. 오타와 여행

우리나라에서 캐나다 오타와까지는 비행기로 한 번에 가는 교통편이 없어, 토론토를 거쳐 국내선으로 갈아타고 갔습니다. 인천에서 토론토까지 14시간, 국내선 갈아타는 시간 3시간, 토론토에서 오타와까지 1시간, 오토와 공항에서 숙소까지 1시간, 이동하는데만 거의 20시간이 걸렸습니다.

관광을 위해서는 박물관과 미술관 관람을 제외하면 하루면 충분히 다 둘러 볼 수 있는 자그마한 도시였습니다.

랜드마크인 캐나다 국회의사당과 그 옆에 흐르는 운하와 그 강변으로 있는 박물관과 미술관 그리고 앞에 있는 대성당, 그리고, 캠퍼스 담장이 없는 오토와 대학 - 첫날 회의 참석 등록을 하러 가는 길에 잠깐만 짬 내면 다 둘러 볼 수 있는, 한 국가의 수도라기 보다는 어느 한적한 마을을 들린 듯했습니다.

그리고 매일 식사를 책임졌던 바이워드 시장 - 재래시장이라고 소개는 하고 있으나, 식당과 상점들로 가득한 그냥 한 구역이였습니다.

다음은 행사장 건물 앞에서 찍은 사진입니다.



3. PGCon 2016 내용

http://pgcon.org/2016

총 5일에 걸친 긴 일정이었는데, 앞 이틀은 사용법 강좌를 다루고, 이틀은 각 주제들에 대한 발표와 질의응답 형태의 회의, 마지막 날은 사용자들이 모두 모여 그 자리에서 여러 주제들을 발의하고 토론하는 형태로 진행되었습니다.

참여자들 대부분이 글로벌 개발 그룹 구성원이거나, 각국 컨트리뷰터, PostgreSQL 전문 기술 지원 업체 엔지니어들이었기 때문에, 발표 중간 중간 질문과 답변이 오고 가고 자기 의견을 주장하기도 하고, 전세계 사람들이 모였으니, 의사소통이 안되는 부분은 그 자리에 참석한 다른 분이 대신 통역해 주기도 하는 참 역동적인 모임이었습니다.

참석한 발표들의 간단 요약


3.1. PostgreSQL 확장성 소개

http://www.pgcon.org/2016/schedule/attachments/424_PGCon-PostgreSQL-Extensibility.pdf

제가 생각해도 PostgreSQL 이야기의 키노트로 딱 맞는 주제였습니다.

PostgreSQL은 관계형 데이터베이스 관리 시스템이기도 하지만, 내가 꿈꾸는 어떤 데이터베이스를 만들고자 할 때 그 데이터베이스를 만들 수 있는 하나의 개발 도구로 사용할 수 있는 아주 훌륭한 도구임을 소개하고 있습니다.

그래프 데이터베이스를 PostgreSQL 기반으로 만들고 있는 국내 업체인 비트나인도 한 예가 될 것입니다.


3.2. 얀덱스 메일 전환 성공기

http://www.pgcon.org/2016/schedule/attachments/426_2016.05.19%20Yandex.Mail%20success%20story.pdf

러시아 포탈 업체의 메일 서비스 (https://mail.yandex.com/)를 오라클에서 PostgreSQL로 전환했던 엔지니어 삽질기를 소개했습니다. 중간 중간 실패한 이야기들과 지금의 모습으로 결정하고 사용자들이 전혀 눈치 못 채게 자료를 옮겨가는 전략 등, 다양한 마이그레이션 전략을 소개했습니다.

초당 25만 tps 성능을 유지하는 방법, 300TB의 거대 용량을 전환하는 전략 등 DB 입장에서도 놀랍지만, 전체 설계 입장에서도 살펴볼 것이 많은 발표였습니다. 

이 발표에서 가장 인상 깊었던, 누구나 공감할 수 밖에 없었던 순간


3.3. ERROR: snapshot too old

http://www.pgcon.org/2016/schedule/attachments/420_snapshot-too-old.odp

오라클에서 봤던 그 오류 메시지 도입에 대한 이야기였습니다.

이 오류가 필요했던 이유, 설정 방법, 대응 방법 등을 소개하고 있습니다.

기업 친화적인 Postgres Advanced Server를 판매하고 있는 EnterpriseDB사의 발표였습니다.

이번 발표 세션들 가운데, 성능 이슈 관련, 기업 운영 환경 입장에서의 개선 이슈들은 대부분 이 EnterpriseDB사가 맡았습니다. 하나의 실험 정신 투철한 데이터베이스가 기업에서 사용되고, 그 범위가 넓어지고, 이 회사의 매출이 증가하고 그것이 다시 오픈소스 발전에 기여할 수 있도록 하겠다는 흔히 말하는 오픈 소스 에코 시스템의 좋은 사례 같습니다.


3.4. B-Tree 이야기

http://www.pgcon.org/2016/schedule/attachments/423_Btree

러시아 대표 PostgreSQL 기술지원 업체인 Postgres Professional, https://postgrespro.ru/ 의 엔지니어가 설명한 PostgreSQL 색인 이야기입니다.

특별한 이야기는 없지만, PostgreSQL을 처음 접하는 이들에게는 딱 알맞는 내용으로 구성되어있습니다.

물론 국내 문서도 찾아보면 이 보다 더 자세히 소개한 문서들이 많지만, 잠깐 소개합니다.


3.5. 쿠바에서의 PostgreSQL

http://www.pgcon.org/2016/schedule/attachments/398_PgConf2016_Cuba.pdf

개인적으로는 가장 인상 깊었던 발표였습니다.

쿠바 PostgreSQL 사용자 그룹이 어떻게 만들어졌고, 어떤 활동을 해 왔고, 현재 이런 모습이다는 식을 낯선 스페인어 억양의 영어 발표여서, 읽을 수 있는 것은 윗 링크의 발표 화면들과 발표자의 표정과 몸짓 뿐.

가장 크게는 쿠바의 독특한 정치적 성향 때문이기도 하겠지만, 그 보다 산학 협력 환경, 커뮤니티를 유지하고, 자료를 발표하고, 그런 활동들로 기업 내에서도 저변 확대되어 가는 이야기를 들으면서 국내 PostgreSQL 활성화 방안에 대한 부분도 같이 고민해 볼 수 있었던 시간이었습니다.


3.6. 단순 쿼리의 속도 향상

http://www.pgcon.org/2016/schedule/attachments/400_RunSimpleQueryFaster.pdf

EDB사 엔지니어가 쿼리 실행기를 해킹해서 얻은 성능 향상에 대한 발표였습니다.

요지는 현 쿼리 실행기가 단순 쿼리에 대해서는 많이 느리니, 단순 쿼리로 판단되면, 실행기 내부 작업을 단순하게 처리하도록 작업 내역을 분기하자는 것입니다.

커뮤니티 메인 코드에 반영 될 가능성은 별로 없어 보였지만, 자신들의 해킹이 기업 환경에서는 이렇게 쓸모 있다는 식의 발표를 아주 정량적으로 자료를 수집하고, 논리적으로 기존 개발 그룹에 설득하는 작업이었습니다.

한편으로 보면, 오픈 소스 공동 개발에서 꼭 필요한 활동입니다.


3.7. 반짝 이야기들

열 명이 넘는 발표자들이 각 5분 정도 짧게 제 각각의 이야기를 발표하는 시간도 있었습니다.

pgAdmin4 열심히 만들고 있다. PGCon.Asia 를 싱가포르에서 진행되었다. TPC-H 벤치마킹의 필요성 방법, ....

이 중에 꽤 재미났던 발표는

http://www.pgcon.org/2016/schedule/attachments/408_LightningTalkSergeRielau.pdf

DB의 하드웨어 스펙은 어느 정도면 적당한가에 대한 한 사례 발표였습니다.


3.8. PostgreSQL 보다 빠르게

http://www.pgcon.org/2016/schedule/attachments/399_postgresql-96-scalability-perf-improvements.pdf

이 발표도 EDB 엔지니어가 했습니다.

이번에는 data page 크기에 대한 고려, 트랜잭션 로그 쓰기에서 잠금 문제 등 엔진 해킹을 통한 성능 개선 사례 발표였습니다.


3.9. Atomic 프로젝트 안에서의 PostgreSQL

컨테이너 자동화 프로젝트인 atomic 프로젝트 참여자가 atomic으로 PostgreSQL을 사용하면, 고가용성이 얼마나 손쉽고, 좋아지는가에 대한 소개였습니다.

http://jberkus.github.io/love_failover/

엉청나게 많은 오픈 소스 프로젝트를 소개했습니다.

여기서 배우는 것. 이젠 잘 만들어 쓰는 것보다 잘 가져다 쓸 놈이 어디 있고, 그 놈은 이런 장단점이 있어 이런 조합으로 구성하는 것이 제일 낫다는 것을 빨리 아는 것이 중요하다는 것.


3.10. PoWA

이번에는 프랑스 대표 PostgreSQL 기술지원 업체인 dalibo 의 자사 PostgreSQL 모니터링 도구인 PoWA 소개였습니다.

모니터링 도구가 필요하다면, 이것으로 구축해 보는 것도 좋을 것 같습니다. 이 도구의 특징은 실시간으로 해당 데이터베이스의 필요한 인덱스를 추천해 준다는 점입니다.


3.11. NTT 빌링 시스템 마이그레이션 이야기

http://www.pgcon.org/2016/schedule/attachments/422_A%20Challenge%20of%20Huge%20Billing%20System%20Migration_20160520.pdf

일본 통신사인 NTT의 빌링 시스템 차세대 전환 이야기인데, 놀라운 사실은 이런 통신 기업 기간계 시스템에서 조차 차근하게 준비해서 PostgreSQL로 운영하게 되었다는 점이었습니다. 이 안에는 당연히 pg_hint 같은 쿼리 실행 최적화기를 마음대로 건드리는 NTT 자체 기술력이 바탕이 되었기 때문이기도 합니다.

이 발표의 핵심도 PGCon 이라는 독특한 회의에 촛점을 맞춰, '왜 PostgreSQL 개발 그룹은 pg_hint 같이 쿼리 힌트를 거부하는가?' 라는 아주 저돌적인 질문이었습니다.  기업 환경에서는 쿼리 힌트 기능은 필요악이기 때문에, PostgreSQL에서도 당연히 쿼리 힌트가 지원되길 바란다는 내용이었습니다.


3.12. 데이터베이스 벤치마킹

http://www.pgcon.org/2016/schedule/attachments/413_Benchmarking%20Databases%202016.pdf

데이터베이스 벤치마킹의 작위성에 대한 이야기였습니다. IT 업계에 오래 있었던 분들은 대부분 납득 가는, 어떻게 수치가 임의적으로 해석되고, 그것이 어떻게 포장 되는가에 대한 엔지니어 입장에서의 발표였습니다. 그 어떤 벤치마크 수치도 객관적이다고 말하기는 힘들겠지만, 일단 벤치마킹을 하려는 이들이 갖춰야할 객관성을 살펴 볼 수 있습니다.


3.13. 사용자 언컨퍼런스

https://wiki.postgresql.org/wiki/Pgcon2016userunconference

PGCon 행사의 꽃인 토론 마당

늘 wiki 페이지로 올 해는 이런 이야기가 있었구나는 식의 글만 보다가 직접 참여하게 되었지만, 언어의 장벽을 넘기는 힘들었습니다. 일상 회화나 겨우 할 수 있는 상황에서 기술 이야기를 넘어 제반 토론에 참여하고 의견을 내고 할 형편이 못 되어 참 아쉬웠습니다. 역시나 이 부분은 윗 링크가 정리되는 대로 다시 한 번 읽어 보는 것으로 만족해야겠습니다.

이 토론 마당의 첫 주제는 PostgreSQL이 제 오랜 기간 동안 사용되었고, 이제 기업들도 그 개발에 참여하고, 사업가, 개발자, 사용자들간의 이해득실을 따지게 되고, 여러 사회적, 정치적, 도덕적 상황들이 초창기 그저 멋진 데이터베이스를 만들어 쓰겠다는 순수함(?)을 많이 훼손되고 있는 마당에 이제는 이런 문제를 어떻게 하면 민주적으로 풀어낼 것인가를 그네들끼리 열심히 토론하고 있었습니다.

그저 일반론적인 이야기로는 건전한 대안 있는 비판은 언제나 생기 넘치는 조직으로 유지하는데 꼭 필요하다는 것 정도. 그리고 다양성을 인정하고, 합의는 민주적이여야 한다는 교과서 이야기입니다.

이 사진은 언컨퍼런스 시작하면서 각 주제 선정 작업 결과였습니다.

한 사람씩 이야기를 나눴으면 좋겠다는 주제를 이야기하고 같이 참여하겠다는 사람들을 조사하고 그 자리에서 시간과 장소를 결정하는 형식이었습니다.


4. 마무리

오픈 소스 전체에서 PostgreSQL은 정말 작은 한 소프트웨어일 뿐인데, 이렇게 많은 이들이 한 자리에 모여 며칠씩 서로 이야기를 나누며 어떤 이는 자기 경험을 나누고, 어떤 이는 열심히 공부하고, 서로 토론하는 모습이 참 인상적이였습니다.

물론 기업의 매출 증대를 위해 이해 관계자들은 이번 자리에서도 아주 전략적으로 접근하기도 했겠지요. 하지만, 비영리 단체가 기업과의 묘한 관계 유지 하면서 매년 이렇게 행사를 계속 유지하고 있는 것이 놀랍기도 했습니다.

끝나는 시간 열린 경매 시간도 참 인상 깊었습니다. 비영리 단체의 자금 조달 방법.

한 편의 긴 '그들만의 리그'라는 다큐멘터리 영화를 본 듯합니다.


5. 보너스

오타와 운하에서 바라본 캐나타 문명 박물관과 가티노시 야경 - 이 사진 찍어 보여 달라는 팀원의 부탁에 자다가 찍으러 감.


Posted by 김상기


posted by 김상기 ioseph

1. pgcrypto 확장 모듈 설치

데이터베이스 관리자 권한으로 해당 데이터베이스에 접속해서,

CREATE EXTENSION pgcrypto

쿼리문을 실행


기본적으로 해당 확장 모듈에 포함된 함수들은 public 스키마에 만들어짐


2. 기본 사용법

postgres=# \dx+ pgcrypto "pgcrypto" 확장 기능 안에 포함된 객체들 객체 설명 ------------------------------------------------------- function armor(bytea) function armor(bytea,text[],text[]) function crypt(text,text) function dearmor(text) function decrypt(bytea,bytea,text) function decrypt_iv(bytea,bytea,bytea,text) function digest(bytea,text) function digest(text,text) function encrypt(bytea,bytea,text) function encrypt_iv(bytea,bytea,bytea,text) function gen_random_bytes(integer) function gen_random_uuid() function gen_salt(text) function gen_salt(text,integer) function hmac(bytea,bytea,text) function hmac(text,text,text) function pgp_armor_headers(text) ... (36개 행)

함수 설명

armor, dearmor, pgp_*() 함수는 openpgp 구현 관계 쪽으로 오라클 DBMS_CRYPTO 하고 상관 없음, 여기서는 설명 생략

crypt, digest, hmac : 사용자 비밀번호 문자열 뭉개는 함수 (역함수 없음)

encrypt, encrypt_iv, decrypt, decrypt_iv : 단일키 기반 자료 암호화, 복호화

gen_salt : 임의의 salt 만드는 함수

gen_random_* : 부가 함수.


pgp (개인 메시지(email) 서명 및 암복호화에 대한 한 방법) 기반 비대칭 암복화까지를 고려하지 않는다면, 

평문 뭉개는 기능(digest, hmac)과 단일키 기반 암복호화 크게 두가지로 나뉨


2.1 사용자 비밀번호 뭉개기

postgres=# select digest('mypass', 'sha256');
                               digest                               
--------------------------------------------------------------------
 \xea71c25a7a602246b4c39824b855678894a96f43bb9b71319c39700a1e045222
(1개 행)

기업마다 보안정책이 달라서 이 암호문 뭉개기 작업에 대한 정책에서 임의의 저장된 key가 있어야 하는 경우는 hmac() 함수를 사용함.

한편 뭉개진 문자열 안에 그 키(이때는 소금이라 함)를 포함시키는 전통적인 crypt() 함수는 gen_salt() 함수와 함께 사용함,

gen_salt() 에서 만드는 salt 형태에 따라 뭉개는 방식을 달리함

postgres=# select crypt('mypass',gen_salt('bf'));
                            crypt                             
--------------------------------------------------------------
 $2a$06$dRBVXc2WpqOZQZj4tmAZzur0tQ2owGnI74BKoaaNuu8uboNsaa6tW
(1개 행)


그럼 사용자가 입력한 비밀번호가 맞는지 확인하려면?

당연히 입력한 문자열을 똑같은 방식, 똑같은 키(소금)으로 뭉개서 그 뭉개진 결과가 같으면 같다라고 판단 함


팀: 즉, 그 누구도 DB에 저장된 자료를 복호화해서는 안되는 자료는 이 방식으로 뭉개야 함

비밀번호는 새로 발급 되어야 하지, 예전 비밀번호를 찾아주는 서비스를 제공하면 안됨


2.2 자료 암호화 복호화

postgres=# select encrypt('안녕하세요','내키', 'aes');
              encrypt               
------------------------------------
 \x7e1dba6095d28477fb6d30e45569f780
(1개 행)
postgres=# select decrypt(decode('7e1dba6095d28477fb6d30e45569f780','hex'), '내키', 'aes');
             decrypt              
----------------------------------
 \xec9588eb8595ed9598ec84b8ec9a94
(1개 행)
postgres=# select convert_from(decrypt(decode('7e1dba6095d28477fb6d30e45569f780','hex'), '내키', 'aes'), 'utf-8');
 convert_from 
--------------
 안녕하세요
(1개 행)

중요한 부분은 decrypt() 함수 반환자료형이 bytea 형이라는 점

그래서, 그 원본 값이 문자열이었다면, convert_from() 함수로 적당한 문자열 인코딩으로 변환해 주어야 함

encrypt_iv() 함수는 암호화 과정에서 사용할 초기화 문자열을 임의로 지정할 수 있도록 하는 것임


2.3 그 외

http://postgresql.kr/docs/current/pgcrypto.html (pgcrypto 모듈 한글 설명서)


3. 오라클 DBMS_CRYPTO 패키지를 이용한 함수 마이그레이션

3.1 비밀번호 뭉개기

Oracle: dbms_crypto.hash()

PostgreSQL: digest(), 알고리즘은 oracle에서 썼던 숫자값을 적당한 문자열로 바꿔줌 (des, md4, md5, sha1...)


3.2. 단순 암복호화

오라클 dbms_crypto.encrypt(), decrypt() 함수도 PostgreSQL의 bytea 형과 같은 raw 형을 입력 자료형으로 받기 때문에, cast() 함수를 이용해서 적당한 형 변환을 해 주어야 함

Oracle: utl_raw.cast_to_raw('01234567890123456789012345678901') 또는 UTL_I18N.STRING_TO_RAW() 형태의 raw 변환 함수는

PostgreSQL: cast('01234567890123456789012345678901' as bytea)

형태로 바꾸고,

Oracle: DBMS_CRYPTO.DES_CBC_PKCS5 형태로 오는 type 인자값

PostgreSQL: encrypt, decrypt 함수의 알고리즘 인자에 '알고리즘-모드/pad:패딩' 형태로 지정함, 윗 예제라면, 'des-cbc/pad:pkcs' 가 됨


3.3 키가 외부에 있어, utl_file 패키지를 사용한 경우

일반적으로 oracle에서는 utl_file 패키지를 이용해서 directory 객체를 만들고, 그 안에 있는 특정 파일을 읽을 수 있도록 제공하지만,

PostgreSQL에서는 $PGDATA 디렉토리 안에 있는 파일만 읽을 수 있음.

즉, 해당 키 파일은 $PGDATA 디렉토리 안으로 옮겨와야 함

파일 읽는 함수는

Oracle:

utl_file.fopen('directory', 'keyfile', 'rb');

utl_file.get_raw(fh, key_str, 32 );

utl_file.fclose();

PostgreSQL: pg_read_binary_file('keyfile',0,32); (한 줄!)


4. 기존 응용 프로그램 코드 변경을 최소화 하는 법

해당 함수의 입출력 자료형을 최대한 맞추며, (convert_from, encode, decode 함수를 이용)

oracle 패키지 형태로 만들었다면, PostgreSQL에서는 스키마를 만들고, 그 안에 함수로 등록하면,

응용 프로그램 코드 변경을 최소화 할 수 있음

(물론 프로시져로 만들어, exec, call 형태로 호출하는 경우라면, select 구문으로 부득이 변경해야겠지만)


5. 샘플 코드 전체

CREATE SCHEMA mycrypto;
CREATE OR REPLACE FUNCTION mycrypto.encrypt(p_plain character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
declare
vkey bytea;
begin
vkey := pg_read_binary_file('keyfile',0,16);
return upper(encode(encrypt_iv(cast(p_plain as bytea), vkey, cast('0123456789012345' as bytea),'aes'),'hex'));
end;
$function$;

CREATE OR REPLACE FUNCTION mycrypto.decrypt(p_encrypt character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
declare
vkey bytea;
begin
vkey := pg_read_binary_file('keyfile',0,16);
return convert_from(decrypt_iv(decode(p_encrypt , 'hex'), vkey, cast('0123456789012345' as bytea),'aes'),'utf-8');
end;
$function$;

사용예:

postgres=# select mycrypto.encrypt('무궁화꽃이피었습니다');
                             encrypt                              
------------------------------------------------------------------
 1D6337A6A1EF052D66AB80C04DBD402E5DC78E5F14FCECA963CACB9EE6A2CD5F
(1개 행)
postgres=# select mycrypto.decrypt('1D6337A6A1EF052D66AB80C04DBD402E5DC78E5F14FCECA963CACB9EE6A2CD5F');
       decrypt        
----------------------
 무궁화꽃이피었습니다
(1개 행)

Posted by 김상기



posted by 김상기 ioseph

ᇂ1. 데이터베이스 성능이란

일반적으로 tps 초당 트랜잭션 수로 이야기함

문제는 트랜잭션의 정의가 모호함


2. PostgreSQL & pgbench

pgbench 는 기본적으로 tpc-b type 트랜잭션을 제공함

tpc-b type은 클라이언트 - 서버 환경의 구시대 유물이 되었음


3. 아직도 pgbench 가 중요한 이유

그럼에도 불구하고, 데이터베이스 서버가 운영되고 있는 호스트의 하드웨어 사양과

postgresql.conf 설정을 최적의 상태로 만들어 갈 지표를 찾는데, 이주 편한 도구 임


4. 작업 방법

최대 288 클라이언트까지 테스트할 계획임으로

pgbench -i -s 500 으로  자료를 초기화함

pgbench -T 600 -c 클라이언트수 -j 쓰레드수

형태로 클라이언트수와 쓰레드수를 동일하게 해서

해당 호스트의 core 수의 배수로 600초 (5분) 동안 부하를 주고 그 결과를 수집함


for i in `seq 8 8 288`

do

pgbench -T 600 -c $i -j $i > $i.result

done

grep '.....' *.result


5. gnuplot으로 차트 만들기

set title 'pgbench -T 600'

set grid

set terminal png size 640, 480

set output 'pgbench.png'

set xlabel 'clients' x,y

set ylabel 'tps' x,y

plot 'pgbench.result' with lines title '9.5', 'pgbench.result' using 1:3 with lines title '9.4', 'pgbench.result' using 1:4 with lines title '9.5'



6. 차트 읽기

해당 검사에서 최대 성능을 냈던 구간은 모든 버전에서 56-72 구간임

즉, 8 core 환경에서 코어 당 8배 정도의 클라이언트를 수용할 때 최적의 성능을 냄을 알 수 있음(tpc-b 타입 트랜잭션인 경우)


7. 그 외 빠진 이야기

해당 작업은 반드시 OS 모니터링도 함께 진행되어야 함

CPU, 메모리, 디스크I/O, 네트워크 I/O도 함께 봐야 함


8. 마무리

전체적으로 PostgreSQL 버전별 tps 값은 9.5가 제일 낮았음.

병목 구간에서는 일정하게 각 버전별로 성능 차이가 있음

해당 차트로 짤 수 있는 전략

해당 DB 서버로는 최대 100 이하의 클라이언트가 동시에 사용할 수 있도록 하는 것이 최적임

(검사 장비의 하드웨어 사양은 8core, 16G Mem, 10G iscsi HDD)


posted by 김상기

posted by 김상기 ioseph

Postgresql의 처리순서도에 대해 알아보도록 하겠습니다.

 

 

 

 

SQL을 입력하고 나면, Parser 라는 곳에서 postgresql에 맞는 언어와 문법인지 확인

(참조하는 테이블의 권한 여부 및 오타, 문법 등을 확인)

문법 확인 후, 특별한 문제가 없으면 SQL을 어떻게 실행지 계획을 세우게 됨

계획 세우는 곳이 Planner 이다.

(사전에 정리된 통계정보를 가지고 어떻게 실행하는 것이 최고의 실행인지를 계획하는 곳)

일반 개발자들이 무시하는 곳이기도 하지만, 일반 개발자는 Parser만 되면 OK 라고 한다.

계획을 세우고 나면, 데이터 저장소로 부터 데이터를 가지고 옴

물론 메모리에 있는 경우에는 메모리로 부터 가지고 오게 된다.

메모리의 예를 들어보면 똑같은 SQL을 처음 시작할 때와 2번째 실행할 때의 처리시간이 다른 것을 알 수가 있다. (2번째 SQL을 수행하면 메모리에 이미 적재된 데이터를 가지고 오기 때문에 처음 수행할 때보다 빠른 것이다.)

Executor을 하고 나면 그 이후에 결과 값을 화면에 뿌려주게 된다.

 

SQL 처리 순서도는 DB가 성능이 떨어졌을 때 어디를 봐야 하는가를 알아보기 위함이기 때문에 중요하다.

 

posted by. 신기철 (11.27)

posted by DB,MW,OS OSSW(Open Source System SoftWare

훨씬 간편해진 Postgres 백업과 복구 (Postgres Backup and Recovery Just Got a Whole Lot Easier)

2014 10 20Jason Davis (October 20th, 2014 by Jason Davis)

Postgres  최근  년간 새로운 툴을 발전시켜왔다다행스럽게도만약  글을 읽는 당신이 Postgres  업무를 하고 있는 DBA 시스템 관리자라면 당신의 업무환경은 훨씬 간편해질 것이다. EnterpriseDB (EDB)  최근  주간 일본의 K.K Ashisuto  같은 고객들과 파트너사와 작업하며 우리의 새로운 EDB 백업과 복구  (BART)  대해 많은 비용을 써왔고우리가 이번 주에 백업복구 툴의 첫번째 버전을 출시하게  점을 기쁘게 생각한다, EDB BART  엄청나게 간편해져  동안 시간을 잡아먹는 절차들을 개선시켰다. EDB BART  PostgreSQL  EDB’s Postgres Plus Advanced Server  커뮤니티 모두에게 지원을 하고 있다.

EDB BART  시스템 전반적인 카탈로그와 명령어 인터페이스를 제공하여 백업과 복구를  관리할  있도록 도와준다. pg_basebackup  통한 온라인 물리적인 백업은 다운타임을 줄여주며,복구를 하는 동안 다른 경로에 있는 테이블스페이스를 지원하고자동복구는 디스크 스토리지 사용을 줄여주며 파일들이 유효하도록 보증한다.


 이상의 인크리멘탈 백업은 없다 (No More Incremental Backup)

트랜잭션 로그 아카이빙의 결합(wal_level=archive)으로, DBA들은 가장 최근의 베이스 백업 시점으로부터 “ 포워드”  가능하게 된다사용자들은 인크리멘탈 백업에 대해 궁금할   있다.사이즈에서  GB보다 작은 데이터베이스를 위해, Postgres point-in-time-recovery (PITR)  가능하게 하였다.

EDB 데이터베이스 개발자이자 PostgreSQL  기여자인 Kevin Grittner 씨는 중요성에 대해 설명할 기회를 가졌다그가 “만약 당신이 WAL 아카이빙하고 archive_timeout = 1h  설정하고 있다면당신은 시간단위로 인크리멘탈 백업을  수도 있고 다른 방법도 있으며 모든 복구 옵션을 가지고 있는 것이다기본적으로대부분의 사람들이 BART라는 툴을 통해  복구 포인트마다 트랜잭션 단위를 저장해야  공간을 절약해주는 셈이다.


 멀리 내다보자 (Looking Ahead)

일본의 파트너사 중에 하나인 K.K. Ashisuto   “EDB BART  매우 복잡한 프로세스를 간편화했고마켓에서 차별화된 점들을 발전시켜왔습니다 툴은 local 데이터베이스와 거의 동일한 속도로 remote 환경에서도 백업이 쉽게 됩니다.” 라고 언급하였다.

2,500 명이 넘는 고객들을 통해 EDB Postgres 이용자들에게  포지셔닝 되어있고그들의 요구에 대해 깊은 통찰력을 가질  있게 되었다이런 상호작용은 우리 제품의 로드맵에 영향을 미치게 되고 정기적으로 사용자의 요구에 이득을 가져올  있는 새로 나올 버전에 대해  혁신을 불러올  있게 된다.


EDB 툴에 대한  많은 정보를 원하신다면visit our site (우리 사이트를 방문하시거나contact us (직접 연락 부탁 드립니다) - EnterpriseDB  제품관리 선임, Jason Davis


출처 : http://blogs.enterprisedb.com/2014/10/20/postgres-backup-and-recovery-just-got-a-whole-lot-easier/


Post by. 김지선(2014.11.26)

posted by DB,MW,OS OSSW(Open Source System SoftWare

PostgreSQL Streaming Replication

 

설명 :

 스트리밍 복제 (Streaming Replication)은 PostgreSQL 9.0 이상에서 사용 가능한 복제 기능.
Insert/Update/Delete, Create/Drop 가능한 1 개의 Master DB에 SELECT만 할 수 있는 여러 Standby DB에 복제하는 것
Standby DB의 변경 결과가 반영되기까지 약간의 지연이 있지만 비교적 지연은 적게, 마스터 DB에 영향 작은 복제 방식.

 

 

목적 :

1. 많은 SELECT Query의 Server 간 분산
2. Master DB 이상시 신속한 Fail over
3. Master DB의 Disk 장애에 대비한 실시간 Backup

 

 

9.0 에 비해 강화된 9.1의 기능


1. 동기화 복제 (변경 결과 도착 보장)
2. 기반 Backup의 간소화 (초기 Data 복제 도구)
3. 복제 상태를 확인하기위한 System view
4. Fail Over 지원 강화 (전용 명령 다른 Master Server에 다시 연결)

 

 

 

- 구축절차

 

1. Master

 

Postgresql 9.1 설치 (생략)

복제용 사용자 생성

 

 -bash-4.1$ psql
psql (9.1.7)
Type "help" for help.
 
postgres=# CREATE ROLE repl_user LOGIN REPLICATION PASSWORD 'xxxxxxxx';
CREATE ROLE
postgres=# \q
-bash-4.1$

 


pg_hba.conf 수정

 

 vi $PGDATA/pg_hba.conf

 

host all         all       127.0.0.1/32 trust
host replication repl_user 127.0.0.1/32 md5
host all         all       ::1/128      trust
host replication repl_user ::1/128      md5

 


 


postgresql.conf 수정

 

vi $PGDATA/postgresql.conf

 

wal_level = hot_standby
max_wal_sender = 2 # 대기 DB의 수 + 1
# Archive 사용시
archive_mode = on
archive_command = 'cp "%p" /mnt/pgbackup/archive/"%f"'
# Archive 미사용시
archive_mode = off
wal_keep_segments = 8 # 8-32가 기준

 

 


pg_basebackup 으로 Master를 Backup

 

 Base Backup

 

-bash-4.1$
-bash-4.1$ /usr/pgsql-9.1/bin/pg_basebackup -h localhost -U repl_user -D /mnt/pgbackup/cluster/ -xlog -c fast -P
Password:
35430/35430 kB (100%), 1/1 tablespace
-bash-4.1$


 2. Slave

Postgresql 9.1 설치 (생략)

Master의 Backup을 restore


 

Master

-bash-4.1$ rsync -ra /mnt/pgbackup/cluster/* root@192.168.83.147:/mnt/pgbackup/cluster/
root@192.168.83.147's password:
-bash-4.1$


 

 

Slave

 

-bash-4.1$ cd /mnt/pgbackup/cluster
-bash-4.1$ cp -rf ./* /$PGDATA/
-bash-4.1$

 

 

 


postgresql.conf 수정

 

 

 vi $PGDATA/postgresql.conf

 

wal_level = minimal
max_wal_sender = 0
archive_command = 'cp "%p" /mnt/pgbackup/archive/"%f"'
hot_standby = on


 


recovery.conf 생성

 

 vi $PGDATA/recovery.conf

 

standby_mode = 'on'
primary_conninfo = 'host=192.168.83.146 port=5432 user=repl_user password=xxxxxxxx'

 


Postgresql start
 

 Master Start

 

[root@localhost mnt]# service postgresql-9.1 start
Starting postgresql-9.1 service:                           [  OK  ]
[root@localhost mnt]#

 

 

 Slave Start

 
[root@localhost mnt]# service postgresql-9.1 start
Starting postgresql-9.1 service:                           [  OK  ]
[root@localhost mnt]#


 

 

 

- 복제상태 확인 (Process)

Master

 


wal sender process 있는지 확인

 

-bash-4.1$ ps -ef | grep postgres
postgres  2927     1  0 Jan03 ?        00:00:00 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data
postgres  2930  2927  0 Jan03 ?        00:00:00 postgres: logger process                                       
postgres  2933  2927  0 Jan03 ?        00:00:01 postgres: writer process                                       
postgres  2934  2927  0 Jan03 ?        00:00:01 postgres: wal writer process                                   
postgres  2935  2927  0 Jan03 ?        00:00:00 postgres: autovacuum launcher process                          
postgres  2936  2927  0 Jan03 ?        00:00:00 postgres: archiver process   last was 000000010000000000000006.00000020.backup
postgres  2937  2927  0 Jan03 ?        00:00:00 postgres: stats collector process                              
postgres  3194  2927  0 Jan03 ?        00:00:00 postgres: wal sender process repl_user 192.168.83.147(44356) streaming 0/7028558
root      3227  2982  0 Jan03 pts/0    00:00:00 su - postgres
postgres  3228  3227  0 Jan03 pts/0    00:00:00 -bash
postgres  3695  3228  0 00:11 pts/0    00:00:00 ps -ef
postgres  3696  3228  0 00:11 pts/0    00:00:00 grep postgres
-bash-4.1$

 

 

 Slave


wal receiver process, startup process 있는지 확인.

 

-bash-4.1$ ps -ef | grep postgres
postgres  4212     1  0 Jan03 ?        00:00:00 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data
postgres  4214  4212  0 Jan03 ?        00:00:00 postgres: logger process                                       
postgres  4215  4212  0 Jan03 ?        00:00:00 postgres: startup process   recovering 000000010000000000000007
postgres  4216  4212  0 Jan03 ?        00:00:01 postgres: writer process                                       
postgres  4217  4212  0 Jan03 ?        00:00:00 postgres: stats collector process                              
postgres  4218  4212  0 Jan03 ?        00:00:02 postgres: wal receiver process   streaming 0/7028558          
root      4277  2961  0 Jan03 pts/0    00:00:00 su - postgres
postgres  4278  4277  0 Jan03 pts/0    00:00:00 -bash
postgres  4565  4278  9 00:12 pts/0    00:00:00 ps -ef
postgres  4566  4278  0 00:12 pts/0    00:00:00 grep postgres
-bash-4.1$

 

 

 


- Fail Over

 

Master Service 중지

 

 

 Maseter Postgresql stop

 

-bash-4.1$
-bash-4.1$ exit
logout
[root@localhost mnt]# service postgresql-9.1 stop
Stopping postgresql-9.1 service:                           [  OK  ]
[root@localhost mnt]#



Slave 에서 DDL 실행 : 실패

 

 

 Slave Postgresql 상태 확인 및 DDL 실행

 

-bash-4.1$
-bash-4.1$ exit
logout
[root@localhost mnt]# service postgresql-9.1 status
 (pid  4212) is running...
[root@localhost mnt]# su - postgres
-bash-4.1$ psql -d db_test_01
psql (9.1.6)
Type "help" for help.
db_test_01=# create table t_test_02 (col1 int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
db_test_01=# \q
-bash-4.1$


 

 pg_ctl promote 실행

 


-bash-4.1$
-bash-4.1$ /usr/pgsql-9.1/bin/pg_ctl promote -D $PGDATA
server promoting
-bash-4.1$


Slave 에서 DDL 실행 : 성공

 


-bash-4.1$
-bash-4.1$ psql
psql (9.1.6)
Type "help" for help.
postgres=# create table t_test_02 (col1 int);
CREATE TABLE
postgres=# \q
-bash-4.1$

 

 

 

 

Posted By 김득은

 

자료 출처

posted by DB,MW,OS OSSW(Open Source System SoftWare

PPAS 특장점

  • DynaTune
    PPAS에 대해 낯설거나 익숙하지 않은 사용자들을 위해서 Configuration parameter 값을 DB에 맞게 자동으로 조절해 주는 기능 입니다. 사용자가 두 가지의 설정 값만 수정을 하면, PPAS가 그 것을 토대로 하여 알고리즘을 생성해 Postgres의 parameter를 설정 해 줍니다

 

  • Query Optimizer Hints
    SQL Optimizer가 선택한 실행계획에 응용프로그램 개발자가 영향력을 행사할 수 있도록 허용합니다. PostgreSQL이 실행계획을 세웠을 때는 특정 쿼리가 맨 처음 실행되었던 시기라서 차후에 실행되는 경우의 대부분은 최적이 아닐 수 있습니다. 따라서, 빈번하게 작업이 발생하여 테이블 크기가 커질 경우에 Query Optimizer Hint를 적용하여 Postgres의 기본 실행계획을 재정의 할 수 있습니다.

 

  • EDB*Loader
    EDB*Loader는 로드 오류가 발생하면 계속 처리할 수 있도록 파일을 폐기하고, 잘못된 레코드를 이동시키는 기능이 있습니다. 그러므로 사용자는 별도로 폐기된 파일을 검사하고, 대량 로드 프로세스가 완료되면 특정 데이터 로드 문제를 해결할 수 있습니다. EDB*Loader 의 가장 대표적인 성능 향상 기능은 경로 로드를 직접 할 수 있는 것입니다. 이는 데이터 구조를 데이터 블록형식으로 직접 변환하여 훨씬 더 빠른 시간에 데이터 디렉토리에 직접 기록됩니다.

 

  • SQL Profiler
    선택한 데이터베이스의 활동을 검사하고 해당 데이터베이스에 대하여 실행된 SQL 명령에 대한 보고서를 생성합니다. SQL Profiler에 의한 보고서는 long run하는 SQL 명령을 향상 시킬 수 있도록 추적하거나, 자주 사용되는 SQL명령을 추적하기 위해 사용됩니다.

 

  • Postgres Enterprise Manager (PEM)
    c설치 된 PPAS 및 PostgreSQL의 모든 크기를 관리하기 위해 특별히 설계되었습니다. 모니터링 에이전트를 호스팅하는 데이터베이스에 대한 통계는 여러 GUI콘솔에서 액세스 할 수 있는 중앙 서버에 전송합니다. 간단하게 콘솔을 클릭하여 간편하게 차트 및 전체 데이터베이스의 인프라 상태를 반영하여 통계를 보여줍니다.

 

 

 

 

 

  • DRITA
    DB시스템에 영향을 미치는 대기상태의 이벤트들을 모니터링하는 카탈로그 뷰를 제공합니다. 이벤트의 발생 횟수나 대기에 소요된 시간을 기록하여 어떤 이벤트가 성능에 영향을 주는지 알아보고 조치를 취할 수 있도록 도와줍니다.

 

  • Infinite Cache
    모든 데이터가 Shared buffer Cache를 사용하고 있을 때, Infinite Cache는 네트워크에 있는 다른 컴퓨터 메모리 캐시에 액세스하여 사용할 수 있습니다

 

  • Open Client Library (OCL)
    OCL은 Oracle의 OCI와의 응용프로그램 상호 운용성을 지원합니다.
    이 전에는 변경할 수 없었던 응용프로그램이 이제는 코트 변경을
    최소로 하여 PPAS와 Oracle 환경 둘 다 사용이 가능합니다.
    OCL의 PPAS구현은 C로 작성되며, 다음 다이어그램은
    OCI 및 OCL의 스택을 비교하고 있습니다.

 

 

Posted by 김득은

posted by DB,MW,OS OSSW(Open Source System SoftWare

1.4. 실자료 지도 갱신

vacuum 작업은 실자료 지도를 갱신하는 작업을 한다. 실자료 지도(visibility map, vm)란 현재 작업 중인 트랜잭션들(또는 그 자료들이 변경 되기 전까지 이용할 미래의 모든 트랜잭션들)이 실제로 사용할 자료들에 대한 각 테이블별 지도다. 이 작업은 두가지 목적이 있다. 하나는 vacuum 작업은 이미 지도 정리 작업이 끝난 것에 대해서는 더 이상 그 작업을 하지 않는다는 것이다.

다른 하나는, 이 지도 정보는 인덱스 전용 쿼리들 - 더 이상 실제 테이블 자료를 검사 하지 않는 쿼리들 - 에 대해서 빠른 응답을 제공하는데 사용된다. PostgreSQL의 인덱스에는 실자료들에 대해서만 따로 모아서 그 정보를 제공하지 않는다. 즉, 어떤 자료를 해당 세션에게 보여 주어야 할지를 결정 하는 정보는 그 자료의 테이블 페이지까지 살펴 보아야 알 수 있다. 인덱스 전용 검색인 경우는 테이블 페이지를 검색하지 않고, 먼저 이 실자료 지도를 검색해서, 이곳에 해당 자료가 있다면, 그것을 사용한다. 그만큼 테이블 페이지 읽기 작업을 줄일 수 있는 있다. 특히나 테이블 크기가 큰 경우라면, 디스크 읽기 작업을 상당히 줄이는 효과를 볼 수 있다. 왜냐하면, 실제 테이블 페이지 보다, 이 실자료 지도의 크기는 훨씬 작기 때문이다.

1.5. 트랜잭션 ID 겹침 오류 방지

PostgreSQL에서는 트랜잭션 자료에 대한 MVCC 기법은 트랜잭션 ID (XID)를 숫자로 처리하고 그것을 비교하는 방식이다: 한 로우의 자료 입력 XID 값이 현재 트랜잭션 XID 보다 더 크다면 "앞으로 생길" - 다른 트랙잭션에서 입력된 - 자료이며, 현재 트랜잭션에서는 보이지 말아야할 자료임을 뜻한다. 그런데, 이 트랜잭션 ID는 32bit 정수형 크기이며, 이 값은 하나의 클러스터 기준으로 관리되기 때문에, 서버가 오랫 동안 운영 되었다면, (40억 트랜잭션을 넘게 사용했다면) 트랙잭션 ID 겹침 오류를 발생할 수 있다: 트랙잭션 ID 계산기가 40억을 넘어 다시 0부터 시작하려고 하면, 보관 되어 있는 모든 자료의 XID 값이 0보다 크기 때문에, 모든 자료는 보이지 말아야할 자료로 처리할 것이다. 단순하게 말하면, 자료가 엄청나게 꼬여 버릴 것이다. (실제로는 유효한 자료임에도 불구하고, 그 자료를 볼 수 없는 황당한 사태가 발생할 것이다.) 이런 문제를 방지하기 위해서, 모든 데이터베이스의 모든 테이블에 대해서 20억 트랜잭션을 사용하기 전에 vacuum 작업이 필요하다.

주기적인 vacuum 작업이 이 문제를 해결 할 수 있는 이유는 PostgreSQLFrozenXID 라는 특별 XID를 미리 예약 해 두었기 때문이다. 이 XID는 일반적인 XID 비교 대상에서 항상 제외되어 항상 보여지는(언제나 old version) XID이다. 일반 XID 비교 방법은 232 나머지 연산을 이용한다. 이 말은 20억 개의 "옛" XID와, 20억 개의 "새" XID 로 나누고, 이 XID 값은 계속 순환 하며 사용한다는 뜻이다. 그래서, 한 XID로 저장 되었다면, 그 이후 20억 개의 트랜잭션이 생기기 전까지는 그 자료는 "옛" XID로 처리되지만, 그 이상의 트랜잭션이 생기면, 그 현재 트랜잭션 기준으로 봤을 때, 그 옛 XID는 앞으로 저장될 XID로 간주해 버린다. 이 문제를 피하는 방법은 20억 트랜잭션이 생기기 전에, 그 옛 XID 자료의 XID 값을 FrozenXID로 바꾸는 것이다. 이렇게 "영구 보관용" 자료로 바꿔 놓으면, 트랜잭션 XID 비교 작업에서 항상 제외 되기 때문에, XID 겹침 오류를 피해갈 수 있게 된다. 이 XID 변경 작업을 바로 VACUUM 명령으로 한다. (이 작업을 자료 프리징(data freezing)이라고 한다. - 옮긴이)

vacuum_freeze_min_age 환경 변수는 FrozenXID로 바꾸기 전 얼마나 옛 XID를 남길 것인가를 지정한다. 이 값이 크다면, 그 만큼 트랜잭션 정보를 많이 보관할 것이고, 이 값을 줄이면, 그 만큼 해당 테이블에 많은 트랜잭션을 vacuum 작업 없이 저장할 수 있게 된다.

표준 VACUUM 작업은 한 자료 페이지에 모든 자료가 UPDATE나, DELETE 명령 없이 오직 INSERT 명령으로 자료가 입력된 것만 있다면, 그 페이지는 작업 대상에서 제외 한다. 그렇지만, 그 자료 페이지에도 아직 FrozenXID로 바뀌지 않은 XID들이 있을 것이다. 이 XID들도 당연히 FrozenXID로 바뀌어야 XID 겹침 오류를 피해갈 수 있다. 이 작업을 위해서, vacuum_freeze_table_age 환경 변수 값을 조정 해서 VACUUM 작업에서 모든 자료 페이지를 대상으로 이 XID 변경 작업 할 것인지를 결정 할 수 있다. 해당 테이블의 나이가 vacuum_freeze_table_age - vacuum_freeze_min_age 값 보다 크면 VACUUM 작업은 모든 자료 페이지를 검사해서 변경 작업을 진행한다. vacuum_freeze_table_age 값을 0으로 지정하면 효율적인 작업을 위해, 실자료 지도를 참조하지 않고, 항상 모든 페이지를 검사한다.

한 테이블이 vacuum 작업 없이 계속 트랜잭션 작업을 할 수 있는 간격은 그 테이블의 마지막 vacuum 이후부터 20억 - vacuum_freeze_min_age 값만큼의 트랜잭션이다. 즉, 이 이상 트랜잭션이 발생했고, vacuum 작업이 없었다면, 자료를 잃게 된다. 물론 현실적으로 이런 사태는 일어나지 않는다. 왜냐하면, autovacuum 기능을 사용하지 않더라도, autovacuum_freeze_max_age 환경 설정 값으로 지정한 간격이 생기면, 강제로 서버는 자체적으로 vacuum 작업을 진행하기 때문이다.

한 테이블에 대해 vacuum 작업을 한 번도 하지 않았더라도 autovacuum_freeze_max_age - vacuum_freeze_min_age 값 만큼의 트랜잭션이 발생했다면, autovacuum 작업이 자동으로 진행된다. 자료 변경, 삭제 작업이 빈번한 테이블들인 경우는 여유 공간 확보 작업과 동시에 진행 되기 때문에, 이 부분(트랜잭션 ID 겹침 방지 작업)은 별로 중요한 사항이 되지 않으나, 자료 추가만 계속 되는 테이블인 경우는 이 간격 만큼 주기적으로 vacuum 작업이 진행 됨을 알고 있어야 한다. 그래서 이 반복 주기를 크게 하려면, autovacuum_freeze_max_age 값을 보다 크게 설정하거나, vacuum_freeze_min_age 값을 보다 작게 설정한다.

vacuum_freeze_table_age 설정에 대한 최대값은 autovacuum_freeze_max_age * 0.95 이다. 이 보다 더 큰 값이 지정되어도 무시하고, 이 최대값이 사용된다. 95%로 지정한 이유는 이 값이 autovacuum_freeze_max_age 값보다 큰 경우는 어차피 autovacuum 작업 계획에 따라 무조건 vacuum 작업이 일어나기 때문에 의미가 없고, 이 정도의 여지를 둔것은 그 사이 사용자가 직접 VACUUM 작업을 할 것인지를 판단할 수 있도록 하기 위함이다. 대략, vacuum_freeze_table_age 값은 autovacuum_freeze_max_age 값보다 작은 값으로 지정해서, 그 차이값 만큼의 충분한 간격을 마련해 놓는 것이 좋다. 이렇게 해서, 사용자가 직접 실행하는 주기적인 VACUUM 작업이나, 자료 변경, 삭제 작업 때문에 자동으로 실행되는 autovacuum 작업들이 원활히 진행 되도록 한다. 이 값(autovacuum_freeze_max_age - vacuum_freeze_table_age)이 너무 작으면, 최근에 여유 공간 확보 작업을 위해 vacuum 작업을 했음에도 불구하고, 트랜잭션 ID 겹침 오류 방지 작업을 위해 또 vacuum 작업을 하는 빈도가 늘어날 것이며, 반대로 너무 크며, vacuum_freeze_table_age 작아서 잦은 테이블 자료 페이지 들을 전수 조사하는 빈도가 늘어날 것이다.

autovacuum_freeze_max_age 값( vacuum_freeze_table_age 값도 포함해서)을 크게 설정 할 때의 유일한 단점은 데이터베이스 클러스터 디렉토리의 하위 디렉토리인 pg_clog 디렉토리의 디스크 사용량이 커진다는 점이다. 왜냐하면, 그 만큼의 트랜잭션 커밋 정보를 보관하고 있어야 하기 때문이다. 트랜잭션 커밋 정보는 2비트를 사용함으로 autovacuum_freeze_max_age 갑을 최대치인 20억으로 지정 했다면, 그 디렉토리는 0.5GB의 공간이 필요하다. 이 정도의 크기가 데이터 클러스터 전체 크기에 비해서 별로 신경 쓸 크기가 아니라면, autovacuum_freeze_max_age 값으로 최대값을 지정하는 것이 좋을 것이다. 그렇지 않은 경우라면, 이 값을 적당히 조절 해서, pg_clog 디렉토리의 저장 공간을 조절할 필요가 있을 것이다. (기본값인 2억 트랜잭션이라면 pg_clog 디렉토리는 최대 50MB 공간을 사용한다.)

vacuum_freeze_min_age 환경 설정 값을 줄이는 것의 한 단점은 빈번한 FrozenXID 변환 작업을 통해 전체적으로 vacuum 작업 시간이 많이 걸린다는 것이다. (굳이 필요 없는 작업을 더 하는 꼴이 된다.) 따라서 이 값은 각 자료가 더 이상 변경 되지 않아, 영구 보관용으로 남기는 것이 좋겠다싶을 자료들이 대상이 되겠끔 충분히 큰 값으로 지정하는 것이 좋다. 이 값을 줄이는 것의 또 다른 단점은 해당 테이블의 많은 자료들이 FrozenXID로 변경 되어 버리면, 데이터베이스 장애 시 원인분석을 위해 참조할 정보가 그 만큼 줄어든다는 것이다. 그래서, 정적 테이블이 아니면, 이 값을 되도록이면 줄이지 않을 것을 권장한다.

한 데이터베이스에서 가장 오래된 XID 값을 조사하는 방법은, pg_classpg_database 테이블을 살펴보는 것이다. VACUUM 작업 뒤 XID 정보를 이 두 테이블에 보관하고 있기 때문이다. pg_class 테이블의 relfrozenxid 칼럼값은 VACUUM 작업으로 그 테이블 전체에 대해서 XID 정리 작업을 했던 트랜잭션 ID 값이다. 이 값은 FrozenXID로 변경 하는 작업 시, 이 값보다 오래된 트랜잭션에 대해서는 그 대상이 됨을 식별하는데 이용 된다. 이와 비슷하게, pg_database 테이블의 datfrozenxid 칼럼은 해당 데이터베이스 전체를 대상으로 각 테이블의 relfrozenxid 값들 가운데 가장 오래된 값이다. 이 정보를 살펴볼 방법은 다음과 같은 쿼리를 실행해 보면 된다:

SELECT c.oid::regclass as table_name,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');

SELECT datname, age(datfrozenxid) FROM pg_database;

age 칼럼 값은 현재 시점 기준으로 마지막 vacuum 작업으로 정리된 가장 오래된 XID의 간격(나이)를 말한다.

표준 VACUUM 작업은 마지막 vacuum 작업이 있은 뒤부터 변경된 데이터 페이지들에 대해서만 작업 대상으로 삼는다. 하지만, 다음 세가지 경우에는 테이블의 모든 페이지를 조사한다. 첫번째, 이 relfrozenxid 값을 조사해서, 이 값의 나이가 vacuum_freeze_table_age 값 보다 크다면 해당 테이블의 모든 페이지를 조사한다. 두번째, VACUUM 명령어에서 FREEZE 옵션을 사용할 때도 모든 페이지를 조사한다. 마지막으로 더 이상 사용되지 않는 자료를 정리하는 작업이 모든 페이지에 걸쳐 있어야 하는 경우에도 같은 작업을 한다. 보통은 이렇게 VACUUM 작업을 통해 모든 페이지가 조사 되었다면, 그 작업이 끝난 뒤 해당 테이블의 age(relfrozenxid) 값은 vacuum_freeze_min_age 약간 큰 값으로 보여진다. (VACUUM 작업이 시작되고, 확인 할 때까지의 트랜잭션 수 만큼 증가한다) 만일, VACUUM 작업을 계속 주기적으로 했으나, 항상 변경된 페이지들만 조사하는 작업만 했다면, age(relfrozenxid) 값이 autovacuum_freeze_max_age 값만큼 이르렀을 때 autovacuum 데몬에 의해 강제로 테이블의 모든 페이지를 조사해서 트랜잭션 ID 겹침 오류를 방지한다.

만일 어떤 알 수 없는 오류로, autovacuum 작업이 제대로 진행 되지 못하는 경우를 대비 해서, 그 데이이터베이스의 가장 오래된 트랜잭션이 위험 수위에 다다르면 - 통상 1천만 트랜잭션 정도 밖에 처리 할 수 없는 상황이면, 다음과 같은 서버 경고 메시지를 보여준다:

WARNING:  database "mydb" must be vacuumed within 177009986 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

(서버 힌트처럼 이런 경우는 관리자가 직접 수동으로 VACUUM 작업을 해야한다. 이 작업은 데이터베이스의 datfrozenxid 값까지 변경해야 함으로 반드시 데이터베이스 관리자 권한으로 진행되어야 한다.) 이 경고를 무시한다면 트랜잭션 ID 겹침 오류가 발생되기까지, 1백만 트랜잭션 정도 남았을 때, 데이터베이스 서버는 다음 오류 메시지를 남기고 이후 모든 트랜잭션 작업을 하지 않는다:

ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT:  Stop the postmaster and use a standalone backend to VACUUM in "mydb".

1백만 트랜잭션을 남겨 놓은 이유는 관리자가 작업 - 수동 VACUUM 작업 - 할 트랜잭션을 보장하기 위해서다. 이 메시지가 발생하고 있는 상황에서는 어떠한 작업도 할 수 없기 때문에, 관리자는 서버를 중지하고, 관리자 단독 모드로 서버를 실행해서, VACUUM 작업을 진행해야 한다. 관리자 단독 모드로 서버를 실행 하는 방법에 대해서는 postgres 설명서를 참조 하라.

1.5.1. 다중 트랜잭션과 겹침

(이 부분은 9.3 버전에서 새로 추가 된 부분이며, 트랜잭션 ID 겹침 오류 방지에 대한 설명과 크게 다르지 않아서 일단은 생략한다. 정말 할 일 없을 때, 우리말로 옮길 예정이다. - 옮긴이) Multixact IDs are used to support row locking by multiple transactions. Since there is only limited space in a tuple header to store lock information, that information is encoded as a "multiple transaction ID", or multixact ID for short, whenever there is more than one transaction concurrently locking a row. Information about which transaction IDs are included in any particular multixact ID is stored separately in the pg_multixact subdirectory, and only the multixact ID appears in the xmax field in the tuple header. Like transaction IDs, multixact IDs are implemented as a 32-bit counter and corresponding storage, all of which requires careful aging management, storage cleanup, and wraparound handling.

During a VACUUM table scan, either partial or of the whole table, any multixact ID older than vacuum_multixact_freeze_min_age is replaced by a different value, which can be the zero value, a single transaction ID, or a newer multixact ID. For each table, pg_class.relminmxid stores the oldest possible multixact ID still appearing in any tuple of that table. If this value is older than vacuum_multixact_freeze_table_age, a whole-table scan is forced. Whole-table VACUUM scans, regardless of what causes them, enable advancing the value for that table. Eventually, as all tables in all databases are scanned and their oldest multixact values are advanced, on-disk storage for older multixacts can be removed.

As a safety device, a whole-table vacuum scan will occur for any table whose multixact-age is greater than autovacuum_multixact_freeze_max_age. This will occur even if autovacuum is nominally disabled.

1.6. Autovacuum 데몬

PostgreSQL에서는 추가적인 기능이기는 하지만, VACUUM 명령과 ANALYZE 명령을 주기적으로 자동 실행하는 autovacuum이라는 기능을 기본적으로 사용하길 권장한다. 이 기능을 켜두면, 테이블의 자료가 많이 변경 되었을 때 - 추가, 변경, 삭제 작업이 많이 있었을 때 자동으로 해당 테이블에 대해서 자동으로 윗 명령들을 실행한다. 이런 자동 실행이 가능 하려면, 먼저 track_counts 환경 설정 값이 true로 지정되어 데이터베이스 작업에 대한 통계 정보를 자동으로 수집해야 한다. 이런 설정은 모두 기본 설정이며, 이와 관련된 다른 설정들도 모두 적당하게 이미 설정 되어 있다. (관리자가 임의로 이 설정을 끄지 않는다면, 서버가 시작되면 자동으로 처리됨을 의미한다. - 옮긴이)

"autovacuum 데몬"은 내부적으로 여러개의 프로세스로 구성된다. autovacuum launcher라는 이름의 프로세스가 항상 실행 되어 있으면서, 그 프로세스가 autovacuum worker라는 이름의 하위 프로세스를 실행 해서 모든 데이터베이스에 대한 vacuum 작업을 하는 방식으로 운영된다. launcher 프로세스는 autovacuum_naptime 값으로 지정한 초 간격으로 한 번에 하나의 데이터베이스를 작업 할 수 있도록 worker 프로세스의 실행 시간을 관리한다. (즉, N개의 데이터베이스가 있다면, autovacuum_naptime/N 초 간격으로 worker 프로세스는 자신이 작업 해야할 데이터베이스를 대상으로 작업을 시작한다.) 동시에 실행 될 수 있는 worker 프로세스의 최대 개수는 autovacuum_max_workers 환경 변수 설정값으로 지정할 수 있다. 만일 이 개수 보다 많은 데이터베이스가 있다면, 가장 먼저 실행한 worker 프로세스가 종료되는 즉시 남은 데이터베이스 가운데 하나를 대상으로 작업한다. worker가 하는 작업은 먼저 각 테이블의 상태를 조사해서, 필요하다면, VACUUM 또는 ANALYZE 명령 수행한다. log_autovacuum_min_duration 환경 변수 설정 값을 지정해서 autovacuum 상태를 지켜볼 수 있다.

만일 worker 프로세스가 각각 아주 큰 테이블의 vacuum 작업을 하게 된다면 동시에 모든 worker 프로세스가 아주 오랜 시간 작업을 하게 될 것이다. 이런 동안에는 다른 테이블들에 대해서는 autovacuum 프로세스가 vacuum 작업을 할 수 없게 됨을 관리자는 알고 있어야 한다. 하나의 데이터베이스에 대해서 동시에 실행 될 수 있는 worker 프로세스의 수는 제한이 없다. 각 프로세스들은 이미 다른 프로세스가 작업 중인 테이블에 대해서는 통과하고 다른 테이블을 조사해서 필요한 작업을 한다. 실행 되는 worker 프로세스 개수는 max_connections 설정값에 포함되지 않으며, superuser_reserved_connections 설정값에도 포함되지 않음을 알고 있어야 한다.

테이블의 나이(relfrozenxid 칼럼 값을 age() 함수로 조사한 값)가 autovacuum_freeze_max_age 설정으로 지정한 트랜잭션 수 보다 많다면, 그 테이블은 무조건 vacuum 작업을 한다. (또한 각 테이블 별로 저장 환경 설정 - 아래 참조 - 인 freeze 최대 나이값을 지정했다면, 그것을 참조해서 작업한다.) 또한, 테이블의 자료가 변경 되어, "vacuum 임계치"를 초과했다면, vacuum 작업을 진행 한다. 이 임계치 계산은 다음 식으로 산정한다:

vacuum 임계치 = vacuum 초기 임계치 + vacuum 배율값 * 로우수

vacuum 초기 임계치는 autovacuum_vacuum_threshold에서, vacuum 배율값은 autovacuum_vacuum_scale_factor에서, 로우수는 pg_class.reltuples에서 참조 한다. 더 이상 사용하지 않는, 쓸모 없는 로우 정보는 통계 수집기가 수집한 정보를 사용한다. 이 정보는 UPDATE 명령이나, DELETE 명령에 의해서 갱신된 비교적 정확한 수치다. (비교적 정확하다고 한 이유는 서버 과부하시에는 이 값이 정확하게 수집되지 않을 수도 있기 때문이다.) 테이블의 relfrozenxid 나이값이 vacuum_freeze_table_age 설정값보다 크다면, 테이블의 모든 로우를 조사해서, 영구 보관용 XID로 바꾸고, relfrozenxid 값을 변경 한다. 그렇지 않은 경우는 마지막 vacuum 작업 뒤 변경된 자료 페이지만을 대상으로 작업한다.

analyze 작업도 위에서 설명한 방식과 비슷하게 진행 된다. 이 임계치 계산식은 다음과 같다:

analyze 임계치 = analyze 초기 임계치 + analyze 배율값 * 로우수

이렇게 계산된 임계치와 마지막 ANALYZE 작업이 있은 뒤 발생한 INSERT, UPDATE, DELETE 작업의 대상이 된 모든 로우수와 비교해서, 작업을 진행한다.

임시 테이블은 autovacuum 대상에서 제외된다. vacuum 작업과, analyze 작업이 필요하다면, 해당 세션에서 SQL 명령으로 사용자가 직접 작업 해야 한다.

임기치와 배율값은 기본적으로 postgresql.conf 파일에서 지정한다. 하지만, 각 테이블 별로도 개별 지정이 가능하다. 이 부분에 대한 것은 Storage Parameters에서 자세히 다룬다. 이 값들이 각 테이블 별로 지정 되면, 그 테이블에 대해서는 서버 전역으로 설정된 값이 무시된다. autovacuum에 대한 환경 설정 매개변수들의 자세한 설명은 18.10절에서 다룬다.

위에서 설명 한 것 외에 테이블 단위 저장 옵션 환경 설정 매개변수는 여섯 개가 더 있다. 하나는 autovacuum_enabled 설정으로 이 값을 false로 지정하면, autovacuum 데몬은 트랜잭션 ID 겹침 방지에 대한 검사만 하고, 나머지 모든 작업을 하지 않는다. 다른 두 개는 autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit 인데, 테이블 단위 vacuum 작업 지연 기능에 관계된다. (18.4.4절 참조) 나머지 autovacuum_freeze_min_age, autovacuum_freeze_max_age, autovacuum_freeze_table_age 설정은 각각 vacuum_freeze_min_age autovacuum_freeze_max_age vacuum_freeze_table_age 설정과 같은 역할을 한다.

worker 프로세스가 여러 개인 경우, 위에서 지정한 비용 처리는 "균등배분" 방식이다. 즉, 실행 되는 프로세스가 많다고 해도, 시스템 전체적인 입장에서 그 비용은 동일하다.

 

출처 : http://postgresql.kr/docs/9.3/routine-vacuuming.html

by. 백준 (2014.11.26)

posted by DB,MW,OS OSSW(Open Source System SoftWare

1. 정기적인 Vacuum 작업

PostgreSQL 데이터베이스에서는 vacuum (배큠이라고 읽는다) 이라는 주기적인 관리 작업이 필요하다. (영어권에서는 vacuuming 단어는 '청소기 돌리기'라는 뜻으로 이미 일상 용어로 사용되고 있고, 이것을 데이터베이스 용어로 사용하였다. 이 글에서는 그냥 vacuum을 그대로 사용한다. - 옮긴이) 이 작업은 대부분의 서버 환경에서는 autovacuum 데몬이 담당해서 자동으로 처리 되기 때문에, 특별히 신경 쓸 필요는 없다.autovacuum 관련 환경 설정값을 바꾸어서 그 데몬의 동작 상태를 조절 할 수 있다. 또한 몇몇 데이터베이스 관리자는 VACUUM 명령을 직접 실행하는 것이 데이터베이스를 효율적으로 사용할 수 있다고 판단해서, cron 이나, 작업 스케줄러 같은 프로그램을 이용해서, 이런 주기적인 정리 작업을 한다. 이런 작업을 잘 하기 위해서는 여기서 설명하고 있는 내용을 잘 이해하고 있어야한다. autovacuum 기능을 이용하는 일반적인 환경에서 특별히 문제가 발생하지 않는다면, 게다가 다른 바쁜 일들이 많은 관리자라면 이 부분은 대충 읽어도 좋다.

1.1. Vacuum 기초

PostgreSQL에서 VACUUM 명령은 다음과 같은 여러 가지 이유로 정기적으로 각 테이블 단위로 실행되어야 한다:

 

 

  1. 변경 또는 삭제된 자료들이 차지 하고 있는 디스크 공간을 다시 사용하기 위한 디스크 공간 확보 작업이 필요하다.

  2. PostgreSQL 쿼리 실행 계획기가 사용할 자료 통계 정보를 갱신할 필요가 있다.

  3. 인덱스 전용 검색 성능을 향상하는데 이용하는 실자료 지도(visibility map, vm) 정보를 갱신하는 작업이 필요하다.

  4. 트랜잭션 ID 겹침이나, 다중 트랙잭션 ID 겹침 상황으로 오래된 자료가 손실 될 가능성을 방지해야할 필요가 있다.

이런 이유로 VACUUM 작업은 그 작업 이유에 맞게 다양한 주기로, 다양한 대상으로 진행된다. 이 부분에 대한 자세한 설명은 이 글의 하위 항목에서 각각 설명한다.

VACUUM 작업은 두 가지 종류가 있다: 표준 VACUUMVACUUM FULL이다. VACUUM FULL 작업은 물리적인 디스크 여유 공간을 확보할 수 있으나 그 작업 속도가 매우 느리다. 하지만 표준 VACUUM 작업은 운영 환경에서도 사용할 수 있도록 여러 다른 작업들(SELECT, INSERT, UPDATE, DELETE 이런 명령어로 수행되는 작업들)이 실행 되고 있어도 동시에 사용할 수 있다. (하지만, ALTER TABLE 명령과 같은 명령은 VACUUM 작업이 실행되고 있는 상황에서는 사용할 수 없다.) VACUUM FULL 명령은 해당 테이블에 대한 배타적 잠금(exclusive lock)을 지정하기 때문에, 어떤 작업도 할 수 없게 된다. 이렇기 때문에, 일반적 상황에서는 관리자는 VACUUM FULL 작업을 되도록이면 피하고, 표준 VACUUM 작업을 하겠끔 신경 써야한다.

VACUUM 작업은 추가적으로 디스크 입출력 부하를 만든다. 이 때문에 동시에 작업하고 있는 다른 세션의 성능을 떨어뜨린다. 

1.2. 디스크 여유 공간 확보

PostgreSQL에서는 UPDATEDELETE 작업 대상이 된 해당 자료의 옛 버전을 작업 완료 후 바로 버리지 않는다.  삭제된 자료를 다른 트랜잭션에서 사용하고 있다면, 그 자료가 삭제되면 안되기 때문이다. 하지만, 다른 트랜잭션이 더 이상이 그 옛 버전 자료에 대한 접근이 필요 없다면, 옛 버전 자료는 쓸모 없는 자료가 된다. 이 상태로 계속 운영 된다면, 디스크에는 쓸모 없는 자료들이 넘처나게 될 것이다. 이런 더 이상 사용할 수 없는, 사용해서는 안될 자료들을 정리해서 그 자료가 있었던 공간을 빈 공간으로 바꾸는 작업을 VACUUM 명령이 담당한다.

VACUUM 기본 작업은 테이블과 인덱스에서 삭제된 자료 (old version row, dead row 라고 한다) 를 정리하고, 그 자리를 다른 자료가 저장 될 수 있도록 빈공간으로 표시하는 것이다. 하지만, 이 작업은 운영체제 입장에서의 디스크 여유 공간을 확보하는 것을 의미하지는 않는다. 물론 한 테이블의 자료가 모두 지워졌고, 하나 또는 소수의 페이지만 없애면 되는데, 이 작업을 위해 테이블 전체의 배타적 잠금도 쉽게 할 수 있는 상황과 같이 특별한 경우는 해당 페이지를 삭제 해서 운영체제 입장의 디스크 여유 공간을 확보할 수도 있다. 이와 반대로, VACUUM FULL 작업은 해당 테이블의 사용할 수 있는 자료들만을 따로 모아 아에 새 파일에 저장하는 방식을 이용하기 때문에 운영체제 입장에서 디스크 여유 공간을 확보할 수 있다. 작업 결과로 해당 테이블에 대해서 최적의 물리적 크기로 테이블이 만들어진다. 하지만 그 작업은 일반 VACUUM 작업에 비해 시간이 꽤 걸린다. 또한 이 작업이 완료되기 전까지 이 작업을 할 수 있는 여유 공간이 있어야 작업을 할 수 있다.

일반적인 vacuum 전략은 주기적인 표준 VACUUM 작업을 해서, 꾸준히 빈 공간을 확보해서 디스크가 어느 정도 커지지만, 더 이상 크지지 않게 해서, 최대한 VACUUM FULL 작업을 해야하는 상황을 방지하는 것이다. autovacuum 데몬이 이런 전략으로 작업을 한다. 즉, autovacuum 기능을 사용한다면, VACUUM FULL 작업을 하지 않는 것을 기본 정책으로 하면 된다. 이런 전략은 각 테이블이 최소의 디스크 공간을 사용한다는 것을 의미하는 것이 아니라, 최적의 디스크 공간을 사용함을 의미한다. 각 테이블은 실재 자료가 저장되어 있는 공간과 함께 vacuum 작업으로 처리된 빈공간을 함께 사용함을 의미한다. 반면, 어떤 테이블은 더이상 변경, 삭제 작업이 없어, 최소의 디스크 공간만 사용하면 된다고 판단되면, VACUUM FULL 명령을 이용할 수도 있을 것이다. 이렇게 해서, 쓸모 없는 공간을 운영체제 쪽으로 반환할 수도 있다. 종합하면, 대용량 테이블을 관리하는 입장에서 보면, 비정기적인 VACUUM FULL 작업보다, 정기적인 표준 VACUUM 작업이 운영상 더 낫다.

어떤 관리자는 vacuum 작업을 사용량이 적은 밤 시간에 주기적으로 작업 하도록 직접 관리하려고 한다. 이렇게 특정 시간에 vacuum 작업을 할 때는 갑자기 자료 변경이 많은 작업이 생겨, 디스크 공간을 많이 쓰게 되는 경우도 함께 고려되어야 한다. 최악의 경우는 디스크 공간이 모자라 VACUUM FULL 작업을 선택해야 할 경우도 생기기 때문이다. autovacuum 데몬을 이용하면, 이런 예상치 못한 상황에 대해서도 자동으로 vacuum 작업이 진행되어 위와 같은 문제들을 피해갈 수 있다. 정확한 데이터베이스 사용량을 파악하지 않고 그냥 autovacuum 기능을 끄는 것은 현명하지 못한 선택이다. 한 타협점은 이 데몬의 실행 환경 설정값을 변경해서 예상치 못한 대량 변경 작업에 대해서만 vacuum 작업이 자동으로 실행 되겠끔 하는 것이다. 그래서, 주기적인 관리자 정의 VACUUM 작업을 유지하면서, 예외 상황에 대해서 자동으로 대처하도록 하는 것도 한 방법이 될 것이다.

autovacuum 기능을 사용하지 않는다면, 주의해야할 점은 해당 데이터베이스 서버에서 사용하고 있는 모든 데이터베이스에 대해서 VACUUM 작업을 해야한다는 것이다. 일반적으로 하루에 한 번 밤시간에 지정하는 것이 일반적이며, 자료가 빈번하게 변경되는 테이블에 대해서는 더 자주 vacuum 작업 하도록 설정한다. (아주 빈번한 테이블에 대해서 몇 분에 한 번씩 작업 하도록 설정해야할 필요도 있을 것이다.) 각 데이터베이스별 vacuum 작업을 할 때 vacuumdb 응용 프로그램을 이용하면 도움이 될 것이다.

작은 정보: 표준 VACUUM 작업은 해당 테이블 전체를 대상으로 하는 변경 작업이나, 삭제 작업과 같은 대량의 작업에 대해서는 만족할 만한 결과를 제공하지는 않는다. 이런 경우, 디스크 여유 공간을 확보해야 할 필요성이 있다면, VACUUM FULL 명령이나, CLUSTER 또는 ALTER TABLE 명령(CLUSTER ON 옵션)을 이용해서, 테이블을 아에 새롭게 만드는 방법을 선택할 수 있다. 이들 명령을 사용할 때는 반드시 주의해야할 사항은 이들 명령은 테이블 대상으로 배타적 잠금을 한다는 점이다. 즉, 이 작업이 완료되기 전까지 그 테이블을 대상으로 하는 다른 모든 작업들을 다른 세션에서는 할 수 없게 됨을 염두해 두어야 하며, 이들 작업은 작업 도중 원본과 다른 새로운 복사본 자료를 만들기 때문에 그만큼의 디스크 공간이 필요하다는 것도 기억하고 있어야 한다.

작은 정보: 테이블의 모든 자료를 아에 지워버리고자 한다면, DELETE 명령보다는 TRUNCATE 명령을 사용하는 것이 낫다. 이 명령은 작업 뒤에, VACUUM 이나 VACUUM FULL 명령을 사용할 필요가 없다. 단, 이 명령을 사용하게 되면 MVCC 대상에서 제외되기 때문에, 다중 세션 다중 트랜잭션 환경에서는 위험하다.

1.3. 실행계획 통계 정보 갱신

PostgreSQL 쿼리 실행 계획기는 쿼리의 좋은 실행 계획을 짜기 위해서 각 테이블에 저장된 자료를 바탕으로 수집된 통계 정보를 이용한다. 이 통계 정보는 ANALYZE 명령을 이용해서 만든다. 또한 VACUUM 명령을 수행하면서 옵션으로 이 작업을 할 수 있다. 이 통계 정보 갱신 작업이 제대로 되지 않으면 의도 되지 않은 쿼리 실행 계획이 짜여질 것이고, 이것으로 전체적으로 데이터베이스 성능을 떨어뜨리는 결과를 초래하기 때문에, 바른 통계 정보 갱신 작업을 주기적으로 하는 것은 중요하다.

autovacuum 기능을 이용한다면, 통계 정보를 갱신해야할 필요성이 있는 테이블들에 대해서 주기적으로 ANALYZE 명령을 자동으로 수행한다. 반면, 자료 변경 작업이 어떤 칼럼에 일어나고, 그 변경 작업 때문에, 통계 정보를 갱신해서, 실행 계획이 잘 짜여지는 것과 관련 없다고 명확하게 판단되는 경우라면, 관리자는 이 통계 정보 갱신 작업을 직접 수행하거나, 아에 안할 수도 있다. autovacuum 데몬은 이런 세세한 경우까지는 고려하지 않고, 테이블의 자료가 새로 추가 되었거나, 변경, 삭제 된 경우라면, 무조건 통계 정보 갱신 작업의 고려 대상이 된다고 판단한다.

통계 정보 갱신 작업도 디스크 여유 공간 확보를 위한 vacuum 작업과 마찬가지로 테이블의 자료 변화량이 많은 경우는 보다 빈번하게, 그 반대인 경우는 좀 더 드물게 진행됨이 좋다. 물론, 테이블의 자료가 빈번하게 변경된다고 하더라도, 그 변경 내용이 수집할 통계 정보와 관련 없는 것이라면, 당연히 통계 정보 갱신 작업이 필요 없다. 통계 정보를 갱신 할 빈도를 추측하는 가장 간단한 방법은 그 칼럼의 최소값과 최대값이 얼마나 자주 바뀌느냐를 살펴보는 것이다. 예를 들어 웹서비스에서 각 페이지의 마지막 접근 정보를 기록하는 테이블을 고려해 본다면, 마지막 접근 시각을 기록하는 timestamp 자료형의 칼럼은 URL을 담고 있는 칼럼보다 훨씬 빈번하게 변경될 것이다. 따라서 URL 정보의 통계 정보 갱신 작업보다 마지막 접근 시각의 통계 정보 갱신이 더 빈번하게 일어나야 보다 정확한 실행 계획이 짜여질 것이다.

ANALYZE 명령을 사용자가 직접 실행 할 때는, 한 테이블의 특정 칼럼 정보에 대해서만 통계 정보를 갱신하도록 할 수 있다. 이렇게 함으로 위에서 언급한 것 처럼 칼럼별 통계 정보 갱신 작업 빈도를 칼럼별로 조절할 수 있다. 하지만, 운영 환경에서는 일반적으로 데이터베이스 전체를 대상으로 이 작업을 한다. 왜냐하면, 기본적으로 자료 통계 정보 갱신 작업은 전체 자료를 대상으로 하지 않고, 임의의 샘플 자료만을 대상으로 하기 때문에, 꽤 빨리 작업이 끝난다.

작은 정보: 칼럼 단위로 ANALYZE 작업을 한다는 것이 운영 환경에서 사용하기에는 번거로운 일이긴 하지만, 보다 정확한 통계 정보를 싼 비용으로 갱신 할 수 있다는 점에서 장점이 있다. 예를 들어 한 칼럼의 자료 분포가 아주 넓고, 그 칼럼이 WHERE 절의 조건 검색으로 자주 사용된다면, 이 칼럼의 통계 정보는 다른 칼럼보다 더 꼼꼼하게 관리되는 것이 좋을 것이다. 이런 경우, ALTER TABLE SET STATISTICS 명령을 통해 해당 테이블의 개별 통계 수집 설정값을 바꾸어서 꼼꼼하게 관리 할 수 있다. 또한 서버 환경 설정인 default_statistics_target 값을 조정해서 데이터베이스 전체를 대상으로 조절할 수도 있다.

또한, 함수 사용에 대한 통계 정보는 기본적으로 제한된 정보만 제공 한다. 하지만, 함수 기반 인덱스를 만들었다면, 이 부분에 대해서는 통계 정보 갱신 작업이 함수 반환값을 대상으로 이루워 지기 때문에, 검색 조건으로 인덱스를 만들 때와 같이 해당 칼럼에 해당 함수를 사용하다면 쿼리 실행 계획기는 바르게 실행 계획을 짤 것이다.

작은 정보: autovacuum 데몬은 외부 테이블(foreign table)에 대해서는 ANALYZE 작업을 하지 않는다. 외부 테이블을 사용하고, 그것의 통계 정보가 갱신 될 필요성이 있다면, 직접 ANALYZE 명령을 주기적으로 실행해야 할 것이다.

 

 

출처 : http://postgresql.kr/docs/9.3/routine-vacuuming.html

by. 백준 (2014.11.24)

 

posted by DB,MW,OS OSSW(Open Source System SoftWare

Schema


* 클릭하시면 크게 볼수 있습니다.









by. 이은석(2014.11.23)


posted by DB,MW,OS OSSW(Open Source System SoftWare

Tablespace


* 클릭하시면 크게 볼수 있습니다.













by. 이은석(2014.11.23)



posted by DB,MW,OS OSSW(Open Source System SoftWare