기존에는 해당 테이블의 autovacuum 관련 설정이 다음과 같았습니다.

* autovacuum_vacuum_scale_factor = 0.01

* autovacuum_vacuum_threshold = 50

이렇게 하니 하루에 한 번 정도 autovacuum 이 동작하는데 한 번 동작할 때 마다 약 2시간 정도 걸리더군요.

그래서 다음과 같이 변경해서 현재 사용 중입니다.

* autovacuum_vacuum_scale_factor = 0.0

* autovacuum_vacuum_threshold = 300000

30만이라는 값을 지정한 이유는 지난 설정 때 row 가 6천만개여서 60만에 한 번씩 autovacuum 이 동작할 때 2시간 정도 걸렸으니 그 반이면 1시간 정도에 마무리 되지 않을까 라는 추측이었습니다.

뭐 일단 결과적으로는 1시간 30분이상 걸리긴 하더군요...ㅠㅠ

그게 아니더라도 scale_factor 를 통한 autovacuum 은 데이터가 커지면 커질수록 autovacuum 수행 시간이나 부하에 영향을 끼칠 수 있다고 판단하여 scale factor 는 0으로, threshold 만 줘서 현재 굴리고 있는 중입니다. 해당 결정을 내리게 된 참고 문서는 다음 링크를 보고 내린 결론이었습니다

https://lob.com/blog/supercharge-your-postgresql-performance/

재미난 현상이네요.

일단 DML과 autovacuum 작업이 동시에 있을 때,

DML 작업의 지연이 lock 과 관계되어 늦어진다고 생각하는 분이 많은데,

이는 아주 오래된 버전, 아마도 7.x 버전 때나 그랬습니다.

먼저 exclusive lock이 많이 생기는 경우는 대부분 트랜잭션이 많아지면 그렇습니다.

그래서, lock 정보를 수집할 때는 relation oid가 있는 DB 객체에 대해서만 수집하는 것이

분석하는데, 더 도움이 됩니다.

윗 현상 원인을 정확하게 파악하기 위해서는

저 상황에 어떤 쿼리가 row exclusive 로 얼마만큼의 비용이 드는 놈인지를 먼저 파악하는 것이 맞을 것 같습니다.

느낌상으로는 그 lock을 잡는 작업이 디스크 읽기가 많은 놈이고,

vacuum 쪽도 디스크 읽기가 있고 해서 OS 상에서의 디스크 read wait이 많아서 발생하는 현상 같습니다.

일단 lock을 파악할 때는 pg_locks.granted 값도 함께 살펴봐야합니다.

현 서비스가 데이터베이스 내부에서 발생하는 여러 lock 때문에, 대기하는 작업이 있는지를 살펴봐야 합니다.

단순히 윗 문제 때문에 vacuum 환경 설정을 좀 더 면밀하게 해야겠다는 생각은 엉뚱한 수고 - 흔히 삽질이라고 하죠, 요즘은 좀 품위있게 잉여짓 - 를 초래할 수 있습니다.

먼저 lock 때문에 대기하는 다른 작업들이 있는지, 있다면,  그 대기 수를 어떻게 줄일 것인지를 고민하는 것이 나을 것 같습니다.

네 맞습니다. 해당 테이블에 호출되는 작업을 조회해 봤는데 update 는 분당 2000번 정도 일어나고 있고 insert 의 경우에도 분당 1000번 이상 일어나는 것 같습니다. delete 작업은 없구요.

사실 지금 분당 2000번 정도 업데이트도 줄여서 만들어낸 상황입니다. 클라이언트에서 특정 row 의 업데이트 요청이 있어도 최대 4분동안 같은 row 의 중복 업데이트를 막는 형태의 버퍼구조를 서버에 만들어 두었거든요. (즉 한 row 당 아무리 잦아도 4분당 1번만 update 만 일어나도록) 그래도 분당 2천번 정도 update 가 일어나는데 이걸 조금 더 낮추는 방안을 찾아봐야겠군요.

조언 감사합니다.

-- 임계치는

-- pg_class.reltuples * autovacuum_vacuum_scale_factor + autovacuum_vacuum_threshold

-- 로 계산됩니다.

위 표현은 약간 부정확한데,  정확히 표현하면 아래와 같습니다.

dead row count >= autovacuum_vacuum_threshold &&

(dead row count)/(all row count)>=autovacuum_vacuum_scale_factor

-- http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html

-- vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

-- where the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale

-- factor is autovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples.

-- 위 내용을 빌렸는데요. 해석이 잘못된건가요?

네. 의역을 하셨어야 합니다. 그리고 영어 원문 자체가 햇깔리게 표현되어 있는 것이 더 큰 문제입니다.