PostgreSQL 8.0 성능 점검 사항 #

by Josh Berkus and Joe Conway

---

이 문서는 Power PostgreSQL이라는 책의 홈페이지에서 퍼온 내용입니다. 아직 책은 출판되지 않은 것 같습니다. URL은 http://www.powerpostgresql.com/PerfList/입니다. 라이선스가 OPL이라고 하는데 저작자만 밝히면 마음데로 쓸 수 있는 것 같습니다. 혹시 제가 잘못 알고 있다면 지적해 주십시오.

---

이 글의 내용은 PostgreSQL 8.0 서버를 설정하는데 도움이 되는 몇가지 경험적 규칙들이다. 아래의 많은 내용은 실제 측정 실험과 알려지지 않은 증명에 기초를 두고 있다. 대부분은 OSDL에서 진행되고 있는 PostgreSQL의 성능에 대한 것들이지만 다른 사람들에게 시작점이 될 수는 있다. 모든 아래의 정보는 2005년 1월 12일 현재 유용한 내용이며 앞으로 계속 갱신할 것이다. 내가 General Bits에 써왔던 추천 설정들은 아래에 논의될 것들 때문에 더 이상 의미가 없다.

PostgreSQL 서버 설정을 위한 다섯가지 하드웨어 기초 #

1. Disks > RAM > CPU #

만약에 PostgreSQL 서버를 구입하려고 한다면 고성능의 disk array와 평범한 CPU, 충분한 RAM을 가지고 있는 서버에 지출하라. 돈이 좀 남는다면 RAM을 보강하도록하라. 다른 ACID 기반의 RDMBS들과 마찮가지로 PostgreSQL도
매우 많은 양의 I/O를 필요로 하며 아주 희귀한 경우에만 SCSI card보다 CPU를 더 필요로 한다. 이것은 8개의
CPU가 달린 대규모 서버는 물론 작은 서버에도 적용되는 원칙이다. 만약 저렴한 CPU를 구입하면서 절약한 비용으로 고성능
RAID와 다수의 디스크를 구입할 수 있다면 그렇게 하도록 한다.

2. 디스크가 많을 수록 좋다. #

디스크가 여러개 있다면 PostgreSQL과
대부분의 OS들은 database를 동시에 여러 디스크에서 읽고 쓰는 병렬 작업을 할 것이다. 트렌젝션을 처리하는 시스템에서
I/O를 병렬화 하면 엄청난 성능상의 차이가 생기며 RAM에 전체 Database를 넣지 못해서 Disk를 사용해야 하는 어떤
어플리케이션의 성능도 눈에띄게 향상 시킨다. 요즘 시판 되는 디스크들의 용량이 워낙 커서 디스크 하나만 사용하거나 디스크 두개로
RAID 1 미러링을 구성하는 것으로 충분하다고 생각될 수 있겠지만 4개 6개 또는 14개의 디스크를 사용하면 성능이 배가되는
것을 발견할 수 있을 것이다. 그리고 아직까지는 IDE보다 SCSI가 분명하게 DB 전송량이 높다. 심지어 Serial ATA를
사용한다고 해도 말이다.

3. Database와 트렌젝션 log를 분리한다. #

이
미 어지간한 규모의 디스크 어레이를 구입했다고 가정한다면 모든 것을 단일 RAID에 넣는 것 보다 나은 선택들이 남아 있다.
그중 하나는 database 트렌젝션 log(pg_xlog)를 독립된 전용 디스크 자원(어레이 또는 일반 하드 디스크)에
저장하는 것이다. 이렇게 하면 저장이 빈번한 database의 경우 성능을 12%가량 향상시킬 수 있다. 이 방법은 느린
SCSI나 IDE 디스크를 가지고 있는 소규모 시스템에 특별히 중요하다. 두개의 디스크만 가지고 있는 서버라고 할지라도 트렌젝션
로그를 OS가 설치된 시스템 디스크에 두어 어느정도 이득을 얻을 수 있다.

4. RAID 1+0/0+1 > RAID 5 #

불행히도 3개의 디스크로 구성된 RAID 5은 대형 서버 업체들이 만드는 보급형 서버들의 표준이 되어왔다. 이것은 아마도 PostgreSQL을 위한 가장 느린 어레이 구성일 것이다. 이 구성에서는 일반 SCSI 디스크보다도 50% 정도 느린 질의 성능 정도만 기대할 수 있다.

대
신에 둘 또는 넷, 여섯개의 디스크로 구성된 RAID 1, 1+0, 0+1를 생각해 볼 수 있다. 디스크가 6개 이상 되면서
부터는 RAID 5도 어느 정도 사용할만해지며, 성능 비교시 개별 디스크 컨트롤러에 더 영향을 받는 경향이 생긴다. 이것은
싸구려 RAID 카드의 책임일 가능성이 크다. 종종 소프트웨어 RAID를 쓰는 것이 서버에 따라오는 Adatec사의 내장형
카드를 쓰는 것보다 낫다.

5. 어플리케이션들은 충돌 없이 서로 잘 작동해야 한다. #

많은 기관에서 볼 수 있는 또 다른 큰 오류는 동일한 서버 자원을 얻기 위해 PostgreSQL과 경쟁하는 여러 다른 어플리케이션들을 한 서버에 작동시킨다는 것이다. 그 중 최악은 PostgreSQL과
다른 RDBMS들을 같은 기계에 넣는 것이다. 두 DBMS는 디스크 대역폭과 OS 디스크 케쉬를 얻기 위해서 서로 싸울 것이고
결국 둘다 형편없는 성능으로 작동할 것이다. 문서 서버들과 보안 로깅 프로그램들도 역시 비슷하게 궁합이 좋지 않다.

PostgreSQL은 메모리만 충분하다면 CPU와 RAM을 많이 사용하는, apache 같은 어플리케이션들과 기계를 공유 할 수 있다.

postgresql.conf 파일에서 조정하기 원할 만한 12가지 설정 #

postgresql.conf에는 진짜 깜짝 놀랄만한 새 옵션들이 추가 되었다. 심지어 지난 다섯 버젼에서부터 친숙해 있던 옵션들도 이름과 양식이 변경되었다. 이는 데이터베이스 관리자에게 보다 많은 제어권을 주기 위한 것이다.

다
음의 설정들은 대부분의 DBA들이 변경되기 원했을 - 특히 다른 무엇보다 성능에 중점을 두고 - 것들이다. 대부분의 사용자들이
건드리지 않을 것 같으면서도 반드시 찾게될 한두가지 아주 특별한 설정들이 있긴한데 이들은 "Power PostgreSQL"이 출판되기를 기다려야 할 것이다.

연결 #

listen_addresses : 7.4의 tcp_ip와 virtual_hosts 설정을 대체한다. 대부분의 설치에서는 기본 값이 localhost인데 이렇게 하면 콘솔에서만 접속할 수 있다. 많은 DBA들은 PostgreSQL을
네트워크로 접근할 수 있도록 이것을 모든 인터페이스를 뜻하는 "*"로 바꾸기 원할 것이다. pg_hba.conf 파일을 수정해서
권한을 적당하게 조절하는 작업이 뒤따라야 한다. 지난 버젼의 개선 사항으로써, 기본 값인 localhost는 많은 브라우저
기반의 유틸리티들이 loopback 인터페이스인 127.0.0.1 번 IP로 DB에 연결 할 수 있게 허용한다.

max_connections : 지난 버젼과 같이 예상되는 동시에 접속 연결 숫자를 지정해 주어야 한다. 높은 값은 보다 많은 공유 메모리(shared_buffers)를 요구한다. 매번 연결할 때 마다 PostgreSQL과
OS에서 생기는 부담은 매우 크기 때문에 많은 수의 사용자들을 대상으로 서비스 해야 한다면 connection pool을
사용하는 것이 중요하다. 예를 들어 중급의 단일 CPU를 가진 32비트 리눅스 서버는 150명의 활성화된 연결이 있을 때에
상당한 시스템 자원을 소비할 것이며 600개의 연결은 하드웨어 상의 한계에 해당할 것이다. 물론 튼튼한 하드웨어는 보다 많은
연결을 받아 줄 것이다.

메모리 #

shared_buffer : 명확히 하자면 이 숫자는 PostgreSQL이 작업하는데 사용할 전체 메모리가 아니다. 이것은 PostgreSQL이 실제 작업을 처리할 때에 사용하는 할당된 메모리 블럭으로 기계가 가지고 있는 RAM의 작은 일부가 될 것이다. PostgreSQL은
이 외에도 OS 디스크 케쉬도 사용한다. 불행이도 정확한 공유 버퍼 크기는 전체 램, 데이터베이스 크기, 연결 수, 질의의
복잡도가 고려된 복잡한 계산이 필요하다. 그러므로 어림짐작으로 적당한 숫자를 지정하고 조율을 하기 위해서 서버 모디터링(특히
pg_statio 뷰들)을 하는 것이 낫다.

전용 서버에서는 8MB와 400MB(1000에서
50000개의 8k 페이지) 사이가 유용한 값일 것 이다. 공유 버퍼를 증가시켜야 하는 요인으로서는 데이터베이스의 처리량 증가,
대량의 복잡한 질의, 대량 동시 DB 연결, 장기간 실행되는 프로스듀어와 트랙젝션, 활용가능한 여분의 램, 보다 빠른거나 보강된
CPU 등이 있다. 일반적인 예상과는 반대로 너무 과도하게 공유 버퍼를 활당하면 스케닝에 걸리는 시란 때문에 실 성능이 낮아질
수 있다. 아래는 여러 경험들과 리눅스에서 이뤄진 TPC 실험의 결과로 얻은 몇가지 예시들이다.

• 노트북, 셀러론 프로세서, 384MB RAM, 25MB DB: 12MB/1500
• 애슬론 서버, 1GB RAM, 10GB의 의사결정 지원 DB: 120MB/15000
• PIII 4CPU 서버, 4GB RAM, 40GB/150 동시연결의 중량급 트렌젝션 시스템: 240MB/30000
• Xeon 4CPU 서버, 8GB RAM, 200GB/300 동시연결의 중량급 트렌젝션 시스템: 400MB/50000
  

주의할 것은 공유 버퍼와 몇가지 메모리 설정값을 증가시킬 때에는 OS의 시스템 V 관련 설정도 수정해 줘야 한다. Postgresql 설명서에서 관련된 부분을 참고하도록 한다.

work_mem
: 지금까지 sort_mem으로 사용되었었지만 소트, 집합과 몇가지 지시자들을 처리하는데 사용되면서 이름이 변경되었다.
work_mem은 공유 메모리가 아니어서 매번 필요할 때 마다 - 질의 하나가 실행 할때마다 한번 이상 - 할당이 된다. 여기에
지정하는 값은 단위 작업마다 할당할 수 있는 최대값으로 이보다 큰 공간이 필요할 경우에는 디스크를 사용하게 된다. 이 설정 값은
실행 프로그램들과 공유 버퍼가 차지하는 공간을 제외한 가용메모리를 쿼리당 메모리를 필요로 하는 작업 수와 예상되는 최대 동시
처리 질의 수를 곱한 값으로 나눈 값에 기초해서 얻을 수 있다. 또한 매번 처리되어야 하는 질의가 필요로 하는 work_mem의
양도 고려해야 한다. 큰 자료를 처리할 때에는 더 많은 메모리가 필요하다. 질의가 단순하고 많은 동시 처리를 해야 하는 웹
어플리케이션의 경우 일반적으로 이 값을 매우 낮게 설정한다. 보통 512K에서 2048K라면 충분하다. 반면에 160라인의
쿼리와 천만 행의 집합처리 이뤄지는 의사결정 지원 어플리케이션은 무척 많이 필요하다. 메모리가 많은 서버에서는 500M 정도
생각할 수 있다. 다용도의 데이터베이스에서는 특정 질의에 보다 많은 RAM을 지정해 주기 위해서 이 파라메터를 DB 연결 마다
- 질의가 실행될 때에 - 지정 할 수 있다.

maintenance_work_mem : VACUUM, ANALYZE, CREATE INDEX와 외래키 추가시에 PostgreSQL이
사용하는 메모리의 양으로 지난 버젼에서는 vacuum_mem으로 알려졌었다. 이들 작업을 가능한 빠르게 하기 위해서는 테이블의
크기가 커짐에 따라 그리고 여분의 메모리가 많을 수록 이 값을 높게 해야 한다. 가장 큰 테이블이나 인덱스가 디스크 상에서
차지하는 크기의 50%에서 75% 정도를 사용하는 것이 좋으며 크기를 측정할 수 없는 상황에서는 32MB에서 256MB 정도를
사용하도록 한다.

디스크와 WAL #

checkpoint_segments
: 쓰기 작업에 사용되는 트렌젝션 로그의 디스크 케쉬 크기를 정의한다. 읽기를 주로하는 웹 데이터베이스에서는 무시해도 좋지만
트렌젝션을 처리해야하는 데이터베이스나 대용량 자료가 부가되는 보고용 데이터베이스에서는 이 값을 크게 하는 것이 성능에 큰 영향을
준다. 자료의 양에 따라서 이 값을 12에서 256 세그먼트 사이에서 증가시키는데 처음에는 보수적으로 작은 값에서 시작해서
로그에 경고 메세지가 나오기 시작할 때에 숫자를 키우도록 한다. 필요로 하는 디스크의 용량은
(checkpoint_segments * 2 +1) * 16MB으로 32로 지정했을 대에 1GB 정도의 용량이 된다. 따라서
충분한 디스크 공간이 확보되도록 해야한다.

max_fsm_pages
: 부분적으로 비어있는 페이지를 추적하기 위한 저장소 크기를 지정한다. 이 페이지들의 빈 공간에는 새 자료가 저장될 것이다.
올바르게 이 값을 지정하면 VACUUM이 빨라지고 VACUUM FULL과 REINDEX를 실행해야 하는 상황을 방지 할 수
있다. vacuum이 실행되고 그 다음 vacuum이 실행되는 사이에 갱신이나 삭제 작업으로 건드려지는 데이터 페이지의 총
수보다 약간 커야 한다. 이 값을 측정하는 두가지 방법 중 하나는 VACUUM VERBOSE ANALYZE을 실행하는 것이다.
또 다른 하나는 autovacuum을 사용 한다면 이 값을 데이터베이스에서 사용하는 총 데이터 페이지값의 백분율을 나타내는 -V
설정값에 따라 지정하는 것이다. fsm_pages는 매우 작은 양의 메모리만 요구하므로 충분한 값을 주는데 인색하지 않는 것이
좋다.

vacuum_cost_delay : 만약에 큰 테이블을
가지고 있고 동시에 많은 양의 쓰기 작업이 이뤄지고 있다면 VACUUM의 처리시간을 길게 하는 대신에 I/O에 부담을 적게 주는
이 새 기능을 사용하기 원할 것이다. 이 기능은 아주 새로운 것이라서 이에 종속된 5가지 복잡한 설정들로 성능 테스트를 몇번
해보지 못했다. vacuum_cost_delay를 0 이외의 값으로 증가시키면 이 기능은 작동 한다. 50에서 200ms
사이에서 합리적인 수치를 사용하도록 한다. 세밀한 조정을 위해서 vacuum_cost_page_hit를
증가시키고vacuum_cost_page_limit를 감소시키면 vacuum 작업들의 충격을 완화시키고 대신 더 오래 작업을 하게
만들 것이다. Jan Wieck의 트렌젝션 처리 테스트에서 delay는 200, page_hit는 6, limit는 100으로
했더니 vacuum의 충격이 80%이상 감소했고 실행 시간은 3배 늘어났다.

Query Planner #

이들 설정들은 질의를 어떻게 실행할지 계획을 세울 때에 작업 비용을 최소화 하고 최고의 가능한 질의 수행 계획을 도출하도록 해준다. 귀찮더라도 봐야할 가치가 있는 설정들은 다음 두가지이다.

effective_cache_size:
query planner에게 케쉬될 것이라 예상될 수 있는 가장 큰 데이터베이스 객체의 크기를 알려준다. 전용 서버라면 보통
RAM의 2/3정도로 설정 할 수 있다. 다용도 서버에서는 다른 어플리케이션이 사용하는 메모리 용량과 OS 디스크 케쉬가 얼마나
되는지 예측해서 그 만큼을 빼도록 한다.

random_page_cost
: index로 읽혀지는 데이터 페이지를 탐색하는데 소요되는 평균 비용의 예측값이다. 빠른 디스크 어레이를 가진 빠른 기계의
경우 3.0, 2.5나 심지어 2.0까지 작은 값을 줄 수 있다. 그러나 데이터베이스의 처리되는 부분이 RAM보다 큰 경우가
많다면 기본 값인 4.0까지 되돌리는 것이 낫다. 반면에 질의 성능에 따라 이 값을 조정하는 방법도 있다. 만약 인덱스 스켄
대신에 시퀀셜 스켄을 선호하는 것이 부당해 보인다면 이 값을 낮추어 본다. 만약 타지 말아야 할 느리 인덱스를 상용한다면 높여
본다. 테스트는 다양한 여러 종류의 질의로 해야 하고 2.0 이하로는 낮추지 말아야 한다. 만약 2.0보다 낮춰야 할 것 같다면
계획자 통계 같은 다른 영역의 설정을 바꿔보도록 한다.

Logging #

log_destination : 지난 버젼의 직관적이지 않은 syslog 설정을 대치한다. OS의 관리용 로그(syslog나 eventlog)나 분리된 PostgreSQL log(stderr)을 사용하도록 선택할 수 있다. 전자는 시스템 모니터링에 유리하며 후자는 DB 문제 해결과 튜닝에 유리하다. redirect_stderr: 분리된 PostgreSQL log를 사용하기로 결정 했다면 이 설정은 코멘드 라인 리디렉션 대신에 PostgreSQL에
내장된 유틸리티를 사용해서 파일에 로그를 저장 할 수 있게 해주며 자동으로 log를 로테이션 해준다. 이 값을 True로 하고
log_directory에 log가 저장될 디렉토리를 알려주도록 한다. log_filename과
log_rotation_size, log_rotation_age의 기본값은 대부분의 사람들에게 적합할 것이다.

Autovacuum과 당신 #

8.0
을 유용하게 사용하기 원한다면 VACUUM과 ANALYZE를 포함한 유지보수 계획을 수립하려고 할 것이다. 만약 가지고 있는
데이커베이스가 상당히 균일한 정도의 데이터 저장이 일어나면서도 대규모의 자료 저장과 삭제가 필요 없거나 빈번히 재시작되지
않는다면 일정 스케쥴에 따라 실행되는 vaccum 보다는 pg_autovacuum을 설치하는 것이 낫다는 뜻이다. 이유는 다음과
같다.

• 테이블들이 그 사용 정도에 따라 vacuum 되면 읽기만 한 테이블들은 제외된다.
• 데이터베이스의 사용량이 증가함에 따라 vacuum되는 빈도도 자동으로 증가한다.
• 사용 안하는 공간을 계산하기 쉽고 필요 이상의 공간을 사용하지 않도록 한다.
  

autovacuum을 설치하려면 PostgreSQL 소스의 contrib/pg_autovacuum 디렉토리에 있는 모듈을 간단히 컴파일 하면 된다. 윈도우에서는 ?PGInstall 패키지 안에 autovacuum이 포함되어 있다. README에 상세히 나와있는데로 stats 설정들을 켜야 한다. 설정을 했으면 PostgreSQL을 실행 한 이후 autovacuum을 별도의 프로세스로 실행한다.

이 프로세스는 PostgreSQL을 정지 시키면 자동으로 멈출 것이다.

autovacuum의 기본 설정값은 역시 매우 보수적이면서 매우 작은 데이터베이스에 보다 적합하도록 되어 있다. 저자는 보통 다음과 같이 약간 공격적인 설정을 사용한다.

-D -v 400 -V 0.4 -a 100 -A 0.3

이
설정은 테이블이 400 row + 40%의 테이블이 갱신되거나 삭제되었을 때에 vacuum이 실행 되도록 하며 100 row +
30%의 테이블이 생성, 갱신, 삭제된 후에analyze 한다. 이 설정을 사용할 때에는 max_fsm_pages를
데이터베이스의 페이지 수의 50%로 설정하는데 이 수가 부족해서 database에 필요이상 디스크 용량을 차지하지 않다는 확신이
있다. 현재 OSDL에서 다양한 설정으로 테스트 하고 있으며 곧 이 이상의 심한 수치도 적용해 볼 것이다.

vacuum
delay 옵션을 설정하기 위해서 postgresql.conf 대신에 autovacuum을 사용할 수도 있다. vacuum
delay는 매우 큰 테이블이나 인덱스를 가지고 있는 시스템에서는 절대적으로 중요해질 수 있다. 설정을 하지 않았다면 적절치
못한 시점에서 실행되는 autovacuum이 중요한 db 작업을 정지 시킬 수 있다.

불행히도 8.0의 autovacuum에는 앞으로 없어졌으면 하는 두가지 심각한 한계가 있다.

• 장기간 유지되는 메모리가 없다.autovacuum은 데이터베이스를 재시작할 때 마다 모든 작동 추적 정보를 잃어 버린다. 따라서
  일상적인 재시작을 한다면 재시작 바로 전이나 후에 전체 데이터베이스에 대해 vacuum analyze를 해야 한다.
  
• 서버가 얼마나 바쁜지 주의하지 않는다. 원래는 vacuum을 시작하기 전에 시스탬 부하를 점검할 계획이 있었다. 그러나 지금은 그런 기능이 없다. 따라서 극단적인 부하가 절정일 때에는 autovacuum이 오히려 방해가 된다.