읽기전용 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 튜닝의 핵심은 접속자와 메모리 입니다. 주요 핵심 파라메터는 다음과 같습니다.

name	context	shor_desc
default_statistics_target	user	Sets the default statistics target.
maintenance_work_mem	user	Sets the maximum memory to be used for maintenance operations.
constraint_exclusion	user	Enables the planner to use constraints to optimize queries.
checkpoint_completion_target	sighup	Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval
effective_cache_size	user	Sets the planner's assumption about the size of the disk cache
work_mem	user	Sets the maximum memory to be used for query workspaces.
wal_buffers	postmaster	Sets the number of disk-page buffers in shared memory for WAL.
checkpoint_segments	sighup	Sets the maximum distance in log segments between automatic WAL checkpoints.
shared_buffers	postmaster	Sets 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