postgresql.conf 기본적인 튜닝

postgresql.conf 튜닝 PostgreSQL 기본적인 튜닝은 수정 항목과 값이 어느 정도 정해져 있습니다.

튜닝에 관해서는 수치는 이런 것이다,라고 결론 짓고 설정합시다.

단, 항목마다의 의미를 이해하는 것은 PostgreSQL의 동작을 이해하는 데 중요하므로 숫자뿐만 아니라 의미도 제대로 파악하십시오.

그 위에, 서버의 상태를 확인하면서 적절한 조정을 할 수있게 합시다.

_ shared_buffers 7 버젼에서는 성능의 피크는 8000 ~ 10000 (약 80M)의 범위에 있습니다.

shared_buffers를 많이 가지고 있으면  버퍼 관리 오버 헤드가 생기고, 반대로 성능이 저하 될 수 있습니다.

8 버젼에서는 shared_buffers의 성능이 개선 된 150,000 정도까지 성능이 저하되지 않도록합니다.

성능의 피크는 100,000 (약 800M) 부근에있는 것 같습니다.

8 버젼에서는 메모리를 많이 쌓아 shared_buffers를 많이 확보하는 것이 좋습니다.

덧붙여서, PostgreSQL가 사용하는 공유 메모리는 ipcs 명령어를 확인 할 수 있습니다. bytes 공유 메모리 크기, nattch는 공유 메모리에 액세스하는 프로세스 단위로 연결 수와 거의 동일합니다.

# ipcs -m ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status0x0052e2c1 0 postgres 600 88162304 2

shared_buffers를 늘리면 대부분의 경우 로그에 아래와 같은 에러가 나옵니다.

FATAL : could not create shared memory segment : Invalid argument

이것은 하나의 프로세스가 확보 할 수있는 공유 메모리의 최대 크기를 초과하는 경우에 일어나는 전형적인 오류입니다.

Linux의 경우 / proc / sys / kernel / shmmax에 shared_buffers에서 사용하는 공유 메모리 크기보다 큰 값을 지정합시다. * 1

예를 들어 공유 메모리를 160M 확보하려면 다음과 같이 바이트로 설정합니다.

# echo 167772160> / proc / sys / kernel / shmmax (167772160 = 160 * 1024 * 1024)

설정 값을 확인하려면 ipcs -ml에서 확인합니다.

# ipcs -ml ------ Shared Memory Limits -------- max number of segments = 4096max seg size (kbytes) = 163840max total shared memory (kbytes) = 0min seg size (bytes) = 1

postmaster를 무단 kill 할 때 공유 메모리에 PostgreSQL의 쓰레기가 남아있을 수 있습니다.

이 경우 ipcrm 명령을 사용하여 수동으로 공유 메모리 영역의 쓰레기를 제거합니다.

# ipcrm -m 32768

_ max_connections7 계에서는 256 당이 성능의 피크 같습니다. * 2

최대 512 위에 그쳐 두는 편이 좋은 것이 아닐까.

8 계에서는 1000 정도도 그만큼 성능이 떨어지지 않는 것 같습니다.

특히 8.1에서는 버퍼 관리자의 과격한 개혁을 실시하고 동시 연결시 성능이 크게 향상하고 있습니다.

서버에 싣고있는 메모리를 고려한에서 max_connections는 스왑하지 않을 정도로 억제하십시오.

연결시에 소비하는 최대 메모리 견적은 다음의 식으로 산출 할 수 있습니다.

postmaster 크기 × max_connections

_ deadlock_timeout 교착 상태 검출 처리는 비교적 무거운 처리합니다.

SQL이 잠금 대기에 들어가 deadlock_timeout (ms) 경과 한 단계에서 교착 상태 검출 처리가 달립니다.

기본 1000ms 꽤 빨리 설정되어 있고, 조금 무거운 트랜잭션이 달리고 버리면 교착 상태가 아닌데 교착 상태 검출 처리가 달리는 수 있습니다.

참고로 1000 (ms) * 최대 동시 세션 수를 설정하는 것이 좋습니다.

원래 교착 상태를 일으키는 응용 프로그램을 써야 아니기 때문에 적당히 길게 설정해도 좋을지도 모릅니다.

_ effective_cache_size 커널과 PostgeSQL 공유 버퍼 등 PostgreSQL가 사용하는 버퍼 영역의 크기의 추정치입니다.

effective_cache_size * 8192 바이트로 환산 한 값입니다 (기본값은 8MB).

참조하는 테이블이 effective_cache_size의 범위 내에 있으면 최적화 적극적으로 인덱스를 사용하게됩니다.

몇 G의 메모리를 갖고있는 컴퓨터에서는 메모리 총량의 1 / 4 ~ 1 / 2 정도로 설정하고 둡시다.

_ wal_buffersWAL 데이터의 공유 메모리 버퍼 수입니다.

WAL 데이터는 트랜잭션 커밋 또는 버퍼가 넘치는하면 디스크에 기록되기 때문에 트랜잭션이 동시에 다수 달리는 경우 나 하나의 트랜잭션이 큰 경우는 크게 설정하는 편이 쓰기 효율이 좋아집니다.

기본값은 8 (* 8kb = 64kb)이며, 버퍼를 크게 잡아도 그다지 메모리를 압박하지 않으므로 32 ~ 64 정도로 설정하여두면 좋은 것이 아닐까요.

_ wal_sync_methodWAL 업데이트 디스크를 레코딩하는 방법을 지정합니다.

플랫폼에 따라 몇 가지 선택 (fsync, fdatasync, open_sync, open_datasync 등)이 있지만, 어느 것이 최적인지는 실제로 벤치 마크를 취해 보지 않으면 모르는 것 같습니다.

같은 조건에서 pgbench하고, 가장 빠른 방법을 선택합시다.

덧붙여서 fsync가 무효가되어있는 경우,이 항목은 무시됩니다.

_ commit_delay 일반적으로 트랜잭션이 커밋되는 시점에서 WAL에 커밋 레코드가 기록되지만 동시에 실행되는 트랜잭션 수가 많은 경우 쓰기 작업이 빈번 효율이 좋지 않습니다.

commit_delay을 설정하면 커밋하고 commit_delay (ms) 만 기다리고 WAL에 굽기 과정이 시작됩니다.

동시 트랜잭션 수가 많은 사이트의 경우 0 이상의 값을 설정하면 기록 효율이 올라갑니다.

그러나 활성 트랜잭션이 commit_siblings보다 적은 경우는 지연 처리는 행해지 지 않고 바로 로그에 기록됩니다.

_ random_page_count 데이터베이스 테이블의 1 페이지에 액세스 시간을 기준으로 인덱스를 이용한 액세스의 경우에는 얼마나 시간이 걸릴 것인가하는 기준 수치입니다.

몇 G의 메모리를 갖고있는 컴퓨터에서는 2 ~ 3 정도가 적당 기본 4는 너무 큰 것입니다. * 3

값을 작게하면 인덱스 스캔 비용이 떨어지기 때문에 최적화가 인덱스를 사용하기 쉽습니다.

관련 항목으로 하나의 인덱스 튜플의 처리 비용은 cpu_index_tuple_cost에서 지정 가능합니다.

_ max_fsm_pagesPostgreSQL는 갱신 · 삭제를하고도 오래된 행은 그대로 남아 추 기형의 RDBMS입니다.

이 오래된 불필요한 영역은 VACUUM를 실행하여 공유 메모리상의 FSM (Free Space Map)에 기록됩니다.

갱신 · 삭제를 할 경우, FSM을 검색하여 불필요한 공간을 회수합니다.

FSM의 크기는 max_fsm_pages로 지정합니다.

FSM이 너무 작아서 다시 사용할 수없는 영역이 늘어 데이터베이스 클러스터의 용량이 점점 부풀어 오릅니다.

또한 FSM이 너무 커서도 검색 오버 헤드가 커지고 성능이 떨어집니다. * 4

머신 스펙이 어려운 경우 max_fsm_pages는 10000 정도에 억제하고 불필요한 영역을 제대로 회수 할 수 있도록 VACUUM 횟수를 늘려 대응합시다.

모든 불필요한 공간을 회수하는 데 필요한 FSM의 크기는 vacuumdb의 로그를 확인합시다.

8.1의 경우는 아래와 같이 표시됩니다.

$ vacuumdb -a -z -v ... DETAIL : A total of 3136 page slots are in use (including overhead) .3136 page slots are required to track all free space.Current limits are : 200000 page slots 1000 relations, using 1237 KB.VACUUM

이 결과 모든 불필요한 공간을 회수하는 데 필요한 페이지 수는 3136 페이지임을 알 수 있습니다.

max_fsm_pages보다 필요한 페이지 수가 더 많은 경우

* max_fsm_pages를 필요한 페이지 수보다 많은 설정하기

* VACUUM FULL를 실시하고 불필요한 영역을 완전히 삭제 한 후, VACUUM의 빈도를 높일

같은 방법이 있습니다.

운영 단계에서 필요한 FSM의 크기는 변해가 수 있으므로 VACUUM 할 때 정기적으로 로그를 확인 FSM이 충분한 지 여부를 확인하는 것이 중요합니다.

관련 항목으로 max_fsm_relations 있습니다.

여기에는 데이터베이스 (군)의 최대 테이블 수를 지정합니다.

데이터베이스가 여러 개인 경우 각 데이터베이스에 속하는 테이블을 모두 합한 값을 지정합니다. * 5

기본값은 1000이므로 테이블 수가 많은 경우 적절하게 수정하십시오.

실제로 얼마나 많은 테이블이 있는지는 VACUUM 로그에 출력되기 때문에, 이쪽도 참조.