PostgreSQL 설정 최적화

개발 중인 서비스가 PostgreSQL을 사용 중인데, 아직까지는 시범서비스 정도 수준이라서 성능 이슈가 없었다. 웹서핑을 하다가 BrainTree라는 업체에서 PostgreSQL을 경험들을 정리해놓은 포스팅을 보게 되어서 관심이 좀 생기고, 머리 아픈 일들이 쪼오금 정리되는 것 같아서 머리도 쉴 겸 DB 튜닝을 간단하게 해보았다.

인터넷의 여러가지 자료를 참고하였지만 기본은 아래 2가지를 사용하였다.

PostgreSQL 9.0 High Performance

Tuning Your PostgreSQL Server

데이터베이스 스키마나 SQL을 최적화 한 것은 아니고 설정만 살짝 튜닝하였다. (참고로 VM의 메모리는 8GB이다. 스펙이 그다지 좋지 않다.

max_connection을 설정한다. 서비스에서 사용할 최대 접속개수보다 20%정도 여분을 가지게 해놓으면 충분하다.

shared_buffer를 1GB로 변경한다. 데이터베이스만 돌린다면 2GB정도 잡아도 되겠지만 Apache, RabbitMQ, Python workers 들이 실행되고 있으므로 약간 작게 잡았다.

커널 설정에서도 큰 사이즈의 메모리를 지원할 수 있도록 sysctl을 사용하여 kernel.shmall과 kernel.shmmax를 수정해준다. 시스템 재시작에서도 항상 반영되도록 /etc/sysctl.conf에도 추가해준다. shmmax는 한 번에 잡는 공유 메모리의 최대크기이고, shmall은 시스템에서 사용 가능한 전체 공유 메모리의 크기이다. shmall은 단위가 페이지를 사용하는 것을 주의해야 한다. 보통 1page = 4096byte이다. 다른 크기를 사용하는 경우에는 그에 따라서 계산해야 한다.

sysctl -w kernel.shmmax=1073741824

sysctl -w kernel.shmall=393216

work_mem을 설정한다. 질의 수행시에 사용하는 메모리의 크기이다. 정렬 속도 등에 영향을 준다. 기본은 1MB이며 32MB로 변경한다. 접속마다 할당되므로 max_connection을 고려해서 잡아야 한다. 시스템의 전체 메모리에서 shared_buffer를 뺀 나머지에서 max_connection으로 나눈 값보다 좀 작게 해주면 된다. 주로 쓰는 질의의 복잡도도 고려해주면 좋다.

maintenance_work_mem을 설정한다. 이것은 vacuum작업 등에서 사용된다. 일시적으로 사용되고 vacuum이 빨리 끝나면 좋으므로 256MB정도 잡았다.

wal_buffer는 16MB를 사용한다. 특별한 이유가 없다면 최대값인 16MB를 추천한다.

checkpoint_segments, checkpoint_completion_target을 설정한다. 전자가 크게되면 성능이 좋아지나 복구시간이 오래 걸린다. 후자가 크면 디스크에 쓰기를 좀 더 게으르게 퍼트려서 한다. 많은 곳에서 전자는 10정도를 추천하나 8로 설정, 후자는 추천대로 최대한 게으르게 적도록 0.9를 설정한다.

effective_cache_size는 시스템 메모리의 50% 정도를 추천하나 서버에서 다른 것도 많이 실행하므로 보수적으로 1024MB만 잡았다.

이상의 것을 한 후 테이블 3개 조인, 인덱싱이 없는 컬럼에 대해서 조건문, sorting, aggregation을 하는 질의를 돌려보았다. 이전에 14,000ms정도에서 설정 변경 후 1,450ms로 바뀌었다.

PostgreSQL은 MySQL에 비해서 기본 설정이 매우 매우 보수적이다. 오래된 작은 메모리의 머신에서도 돌아가기 위한 설정이다. 그래서 나처럼 반나절 정도 웹서핑을 하고 설정파일에서 한 10줄 정도를 고쳐주면 성능이 몇 배도 올라갈 수 있다.

새 서버가 생기게 되면 날짜별로 테이블을 생성해서 데이터가 저장되어 있는 것을 합쳐서 하나의 테이블로 만드는 것을 해볼까 생각 중이다. 아무리 생각해도 날짜별로 테이블을 만들어 저장하는 것은 아름답지 않다. 기존에 테이블 1개 사이즈가 120GB정도 되고 250개 정도 되니 3TB정도 될듯한데, 파티셔닝과 샤딩을 잘 하면 어찌 이쁘게 되지 않을까 생각한다.

마지막으로 PostgreSQL은 9.2부터 JSON 데이터형을 지원하고 9.3부터는 JSON 데이터의 내부 값을 액세스할 수 있는 연산자도 제공한다. V8자바스크립트엔진을 포팅한 PLV8과 함께 사용하면 unstructured data에 대해서도 재미있게 써볼 수 있지 않을까 생각된다.

출처: Everyone and Everything