PostgreSQL 의 최적화

PostgreSQL 의 최적화

원본 : http://www.phpbuilder.com/columns/smith20010821.php3
저자 : Ericson Smith
번역 : 정재익

Tim Perdue 의 MySQL 과 PostgreSQL 을 비교한 아주 좋은 글(http://www.phpbuilder.com/columns/tim20000705.php3)을 읽고 나는 이 데이터베이스를 설치해서 사용하기로 결정했다.나는 대부분의 작업을 사용하기 편하고, 비교할 수 없는 select query 의 속도 때문에 MySQL 을 사용했었으며, 지금도 그리하고 있다.

그러나 새로운 프로젝트에서는 많은 update 를 실행해야 하는데 MySQL 의 table locking feature 때문에 많은 고통을 받앗다.

우리가 설정한 PostgreSQL 서버 환경은 다음과 같다 :

. Redhat 7.1
. Dual PIII 650 MHz System
. 512 MB RAM
. 18 Gb SCSI for the postgresql data partition

다운로드와 설치하기

7.1.2 RPM 을 http://postgresql.org 로 부터 다운로드 받았다. 서버 설치를 위해서 단순히 postgresql-server 그리고 postgresql-7.1.2 (base) 만을 설치했다.

다음과 같이 서버를 기동했다 :

/etc/init.d/postgresql start

적은 크기의 데이터베이스 하나를 MySQL 로 부터 이동시켜 왔다 (5000 개 정도의 레코드를 포함한 세개의 테이블). PostgreSQL 최적화기가 사용할 충분한 인덱스를 생성했다. 그리고 이 테이블로 질의를 보내는 C로 작성된 작은 CGI 프로그램을 postgreSQL C client 인터페이스를 이용하여 수정했다. 이 작은 CGI 프로그램은 분당 수천개의 질의를 받도록 되어 있다.

최적화

CGI 프로그램에서 튜닝을 한후에 가장 처음으로 알게 된 것 중 하나는, 대부분의 질의들은 예전의 MySQL 기반의 시스템 처럼 빠르게 그 결과가 돌아오는데, 서버의 로딩이 훨씬 높다는 것이었다 -- 거의 90%에 육박하고 있었다. 그래서 나는 이것을 낮추기 위해 작업을 시작했다. 나는 이전에 MySQL 에서는 문제가 될만한 메모리 사이즈 보다도 더 크게 cache 와 buffer size 를 늘여 줌으로서 최적화를 시켰다.

PostgreSQL 을 실행하기 전에 가장 중요한 것은 충분한 shared buffer space 를 늘여 주는 것이다. 다시 반복해보자: 충분한 buffer space 를 제공해 줘라. 이 데이터베이스 서버에는 512 Mb 의 램이 있다. 여러분들은 이들 중 75% 정도를 shared buffer 로 전환시켜 줘야 한다. PostgreSQL 은 그 테이블의 대부분을 이 shared buffer 로 올릴수 있을때 가장 잘 또는 좀더 잘 동작할 수 있다. 우리의 경우 우리들 데이터베이스가 생각보다는 작았기 때문에 128 Mb 의 메모리만을 shared buffer space 로 결정했다.

/var/lib/pgsql/data/postgresql.conf 파일 내에 데이터베이스 서버에 대한 설정을 포함하고 있다. PostgreSQL 은 shared memory 를 buffer 로 사용한다. Linux system 에서는, 여러분들은 다음 명령어를 이용해서 시스템에 얼마나 많은 shared memory 가 할당되어 있는지 확인할 수 있다:

cat /proc/sys/kernel/shmmax

그리고 시스템이 사용하는 공유메모리는 다음 명령어로 볼수 있다:

ipcs

결과는 byte 단위로 보여 준다. Redhat 7.1 에서는 기본적으로 32 MB 의 공유메모리만을 할당하고 있다. 이것은 PostgreSQL 에서는 아주 모자라는 메모리이다. 나는 다음과 같은 명령어로서 이 한계를 증가시켰다.

echo 128000000 > /proc/sys/kernel/shmmax

만약 시스템을 리붓하게 되면 이 설정은 사라진다는 것을 명심하기 바란다. 여러분들은 이 명령어를 postgresql startup 파일 내부에 적어 놓는것이 좋다. /etc/sysctl.conf 파일을 수정하면 영구적으로 이 변화가 반영될 것이다.

그리고 나서 나는 postgresql.conf 파일에 shared_buffers 를 15200 으로 설정했다. PostgreSQL 은 8K segment 를 사용하기 때문에 128000/8192 + 512K overhead 해서 계산을 했다. 그리고 sort_mem 을 32168 (sort memory area 를 32M 로 설정)로 설정해 주었다. connection pooling 이 효율적으로 하기 위해서 max_connection 을 64로 설정했다. 그리고 fsync 는 false 로 설정해 주었다.

shared_buffers = 15200
sort_mem = 32168
max_connections=64
fsync=false

이들 설정을 조절하기 위해서 메뉴얼을 읽어 보기 바란다. 그러나 나는 그렇게 할 필요가 없다고 생각한다. 만약 여러분들이 shared_buffers 를 할당된 shared memory 한계보다 더 크게 설정했다고 생각해 보자. postgresql 은 기동시에 시작을 거부할 것이다. 이러한 사실은 처음에 아무런 로그 파일을 남기지 않았기 때문에 우리를 혼란 스럽게 만들었다. 여러분들은 /etc/init,d 내의 startup 파일에 로그 파일로 출력을 기록하도록 할수 있다. 다음과 같이 적혀 있는 부분을 찾아서...

/postmaster start > /dev/null 2>

다음으로 변경하도록 하라.

/postmaster start > /var/lib/pgsql.log 2>
(또는 여러분들이 로그를 적기 원하는 장소로 기록하면 될것이다)

이 로그파일을 쫓아 가 보면 문제가 무엇인지 알수 있을것이다.

모든 종류의 디버깅 정보들이 이 파일내에 기록될 것이다. 이것은 SQL 구문에러와 EXPLAIN state, emts, connection problem, authentication attempts 등등의 디버깅 정보들을 포함한다.

나는 postgresql 을 재기동하고 우리들의 CGI 를 온라인 시켰다. 우리들의 기우는 없어지고, postgresql 은 곧 buffer 를 사용하기 시작했다. 그리고 서버의 시스템 로딩은 10% 이하로 떨어 졌다.

초창기 시스템의 문제점은 postgresql connection 요구가 있을때마다 한번씩 이런 것들이 끊긴다는 것이었다. 그래서 나는 C library 의 connection pooling 방법을 사용하기 시작했다. 이 옵션으로 서버의 로딩이 순간적으로 증가하는 현상마저도 줄어 들었다. PHP 에서 여러분들은 이 효과를 얻기 위해서는 persistent connection (pg_connect 대신에 pg_pconnect 를 사용하라) 을 이용하면 될 것이다.

Indexes

PostgreSQL 에서 적절한 인덱싱의 필요성은 아무리 강조해도 지나치지 않다. 나의 초창기의 실수는 BIGINT column 에 대해서 인덱싱을 시도한 것이었다. 이 컬럼은 인덱싱은 잘되었다. 그러나 postgresql 은 그들을 이용하지 않는 것이었다. 이틀 후 나는 나의 머리를 쥐어 뜯어야 했다. 시스템 아키텍쳐가 32 bit 였던 것이다. 이것이 postgreSQL 은 64 bit (BIGINT) 를 인덱스로 사용할 수 없는 것일까? 이 자료형을 INTEGER 로 변경함으로써 이 문제를 해결할 수 있었다.내가 만약 64bit 의 Itanium processor 를 이용했다면 이런 문제는 없었을 것으로 생각된다.

결론

질의의 반응속도를 증가시키기 위한 SQL 구문을 변경시킬수 있다. 그러ㅏ 이들은 postgresql 문서에 적절하게 설명하고 있다.