[PostgreSQL - DBA] Running out of temp space 관련..

프로젝트 내에서 Heroku 내의 postgresql DB 구동 중 Running out of temp space 관련 Warning이 발생했다.

검색해보니 나오는 사이트..

https://help.heroku.com/1I3XBKMT/my-heroku-postgres-instance-is-running-out-of-temporary-space

My Heroku Postgres instance is running out of temporary space - Heroku Help

위 본문을 참조해 보니.. 아래 크게 3가지 이유라고 한다.

문제

PGError: 임시 파일의 블록 blockid를 쓸 수 없습니다. 장치에 남은 공간이 없어 문제를 잘 설명하지만 임시 저장소를 더 많이 사용해야 합니다.

해결

모든 Heroku Postgres 데이터베이스는 기본적으로 temp_tablespace특정 별도의 임시 디스크를 가리키도록 구성됩니다. 임시 디스크의 크기는 모든 Heroku Postgres 계획에 대해 고정되어 있으며 Heroku Postgres 지표 로그는 임시 디스크 사용률에 대한 특정 정보를 제공합니다.

데이터베이스에 연결된 애플리케이션이나 클라이언트가 대량의 임시 파일/데이터를 생성하는 경우 이 임시 디스크에 공간이 부족할 수 있으며 에 대해 "장치에 남은 공간 없음"이 표시되기 시작할 수 있습니다 pgsql_tmp.

이 문제를 해결하기 위한 몇 가지 접근 방식이 있습니다.

1. 이를 유발하는 쿼리를 찾아 최적화하여 임시 파일의 사용량을 줄일 수 있는지 확인하세요.
2. work_mem PostgreSQL 설정을 조정하여 각 쿼리 작업이 임시 파일에 데이터 쓰기를 시작하기 전에 사용할 수 있는 메모리 양을 조정하면 쿼리가 생성하는 파일 수를 줄이는 데 도움이 될 수 있습니다. ( 자세한 내용은 PostgreSQL 설명서를 확인하세요.)
3. temp_tablespaces데이터베이스 호스트에서 임시 파일이 생성되는 위치를 결정하는 다른 테이블스페이스를 사용합니다 .

다른 접근 방식을 따르기로 결정하더라도 쿼리를 분석하고 최적화하는 것이 좋습니다. 제안된 대로 데이터베이스 구성을 변경하면 문제가 해결될 수 있더라도 쿼리가 최적화되지 않으면 실행하는 데 여전히 오랜 시간이 걸릴 수 있습니다.

 

ㅋㅋ.. 그래서 work_mem이 뭔데..?
일단 공식 문서를 참고해봤다..

영어로 되어있는건 바로 번역기 돌려서..

work_mem (integer) :

임시 디스크 파일을 쓰기 전에 내부 정렬 명령 및 해시 테이블에서 사용되는 기본 최대 메모리 양을 지정한다. 단위를 지정하지 않으면 kB로 간주한다. 기본값은 4메가바이트이다(4MB). 복잡한 쿼리의 경우 몇 가지 정렬 또는 해시 명령이 병렬로 실행될 수 있다. 각각의 명령은 데이터를 임시 파일에 쓰기 전에 이 값에 지정된 크기만큼 메모리를 사용할 것이다. 또는 실행 중인 여러 세션은 해당 명령을 동시에 실행할 수도 있다. 따라서 사용될 총 메모리는 work_mem의 몇배가 될 것이다. 값을 선택할 때 이러한 사실을 유념할 필요가 있다. 정렬 명령은 ORDER BY, DISTINCT 및 머지 조인에 사용된다. 해시 테이블은 해시 조인, 해시 기반 집계(aggregation), IN 서브쿼리의 해시 기반 처리에 사용된다.

흠.. 결국 이게 작으면 복잡한 쿼리를 돌릴 때 메모리가 모자를 수도 있다는 것인가 보다.

그래서 해결방법 3개 중에 가장 첫번째에서 자원 소모가 큰 쿼리를 찾아서 수정하라는 것인가 보다.

 

좀더 찾아보니 work_mem을 마냥  늘렸다간.. 쿼리에서 work_mem을 병렬로 처리하면서 여러 세트의 work_mem을 사용하다 보면 설정된 work_mem 용량의 배수만큼 서버 메모리를 사용하게 되면서 메모리가 오히려 풀 날수도 있단다..

근데.. heroku는 SaaS 라서 DB 설정을 내가 한 것도 아니고 설정을 보기도 힘든데.. 최대한 work_mem을 늘리는 방안은 신중해야 겠다..

 

일단 work_mem의 기본 설정 가이드에 대해 찾아봤다. work_mem을 늘리던 말던 이게 권장 설정 값이 몇인지를 대략 알아야 하던 말던 할테니..

그래서 찾아본게 아래 3가지 였다.

sort, merge, join 등에 사용하는 메모리 사용량을 의미합니다. default는 4M로 설정되어 있으며 각 서버 사양마다
산정방법이 다르다.
(1) (시스템 전체 메모리) / (최대 커넥션 수 * 16)  
(2) (OS cache memory / max_connections) * 0.5 
(3) 실제 메모리 / (max_connections * 2)
예를들어(1) 16G메모리 데이터베이스서버에 100대의 커넥션이 생긴다면
(16G * 1024) / (100대 * 16) = 16,384 / 1600 = 10.24M = 약 10M 로 설정.

예를들어(3) 1GB의 서버 메모리에 max_connections가 100이면 1024/200 = 5.12로 약 5MB로 설정.

그렇다고 work_mem을 너무 크게 잡으면 한 프로세스가 사용하는 메모리는 적지만 work_mem이 많이 잡혀있다면 프로세스를 다 띄우지 못하고 초과되어 다 써버리게 되면 Out of Memory가 발생할 수 있다.
 
Out of Memory 에러를 발견하는 즉시 서버 RAM을 증설하거나 work_mem 사이즈를 줄여준다.

참고 : https://kwomy.tistory.com/83 

[PostgreSQL] - 튜닝옵션 : 메모리 설정

 

음.. 찾아보니 shared_buffers 값이 메모리와 밀접한 관계가 있는 듯 하다.

실제 메모리가 있고, shared_buffers 값이 있는데, 실제 메모리가 16GB고 shared_buffers 가 8GB면 이 DB는 메모리를 8GB 쓴다고 설정되어 있는 것 같다..(확실하지가 않다 좀 더 계속 찾아봐야겠다.)

그럼 위 내용 기반으로 현재 시스템 설정을 체크해 봤다.

-- (3) 실제 메모리 / (max_connections * 2)
select current_setting('shared_buffers'); -- 8GB
select current_setting('work_mem'); -- 430MB
select current_setting('max_connections'); -- 500

current_setting() 을 쓰면 보기 좋게 사이즈를 뱉어준다.

(3)번 계산식으로 계산해보면... 권장 work_mem 사이즈는 8GB / (500 * 2) = 약 8MB.. 이네..?

근데 지금 430MB 이다.. 흠..? 이건 모니터링을 계속 해야할 만한 듯 하다..

아직은 잘 모르는 부분 이기 때문에 좀 더  찾아봐야 겠다..