database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 9556 게시물 읽기
No. 9556
autovacuum 과 row exclusive lock 의 관련
작성자
dalky
작성일
2015-06-09 19:44
조회수
11,183

 

 

 

해당 스크린샷에서 하늘색 그래프가 row exclusive lock 인데요, 오후 5시 20분 경부터 꾸준히 8 정도를 유지하면서 중간 중간 피크를 치던 것이 2시간 정도 지나서야 풀렸습니다.

왠지 느낌이 있어서 postgresql.log 를 뒤져보니 해당 시간동안 테이블에 autovacuum 이 수행중이었더라구요.

 

 

 

2015-06-09 19:20:00 KST [118566-1] LOG:  automatic vacuum of table moci.public.card: index scans: 1

pages: 0 removed, 2429771 remain

tuples: 18574 removed, 58095356 remain, 9936 are dead but not yet removable

buffer usage: 4043806 hits, 3473377 misses, 1444040 dirtied

avg read rate: 3.930 MB/s, avg write rate: 1.634 MB/s

system usage: CPU 8.00s/99.26u sec elapsed 6905.53 sec

 

질문은 다음과 같습니다.

1) autovacuum 이 수행중인 테이블엔 row exclusive lock 이 걸리는지요? 그리고 중간 중간 저렇게 row exclusive lock 이 피크를 치는 것도 autovacuum 의 영향인지요?

2) 해당 테이블은 하루에 약 100만건의 insert 와 수백만건의 update 가 일어나는 테이블입니다.(delete 는 없습니다.) 또한 현재 쌓인 row 는 약 6천만 건 정도 되는데요, 현재 autovacuum 관련 설정은 다음과 같습니다.

* autovacuum_vacuum_scale_factor = 0.01

* autovacuum_vacuum_threshold = 50

몇몇 도큐먼트에서는 조금 더 공격적인 autovacuum 을 위해서 autovacuum_vacuum_threshold 를 상수(5만에서 10만정도) 로 설정하고 autovacuum_vacuum_scale_factor 를 아예 0 으로 설정해 버려서 그냥 autovacuum 의 임계치를 상수로 설정하라는 말을 보았는데요, 적정한 autovacuum 의 임계치는 어떻게 설정하는 것이 좋을까요?

 

postgresql 을 잘 모르는 상태에서 제법 큰 데이터를 다루려다 보니 막히는 부분이 정말 많네요 ㅠㅠ

 

이 글에 대한 댓글이 총 5건 있습니다.

기존에는 해당 테이블의 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/

 

dalky님이 2015-06-11 16:13에 작성한 댓글입니다. Edit

재미난 현상이네요.

 

일단 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 때문에 대기하는 다른 작업들이 있는지, 있다면,  그 대기 수를 어떻게 줄일 것인지를 고민하는 것이 나을 것 같습니다.

김상기(ioseph)님이 2015-06-11 17:25에 작성한 댓글입니다.

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

 

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

조언 감사합니다.

dalky님이 2015-06-11 19:58에 작성한 댓글입니다. Edit

-- 임계치는

-- 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

초보대왕님이 2015-06-11 21:46에 작성한 댓글입니다.
이 댓글은 2015-06-11 21:47에 마지막으로 수정되었습니다. Edit
-- 임계치에 대한 부분은

-- 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.

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

 

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

초보대왕님이 2015-06-14 20:57에 작성한 댓글입니다.
이 댓글은 2015-06-14 20:58에 마지막으로 수정되었습니다. Edit
[Top]
No.
제목
작성자
작성일
조회
9560ORACLE INSTR같은 문자열 검색 함수 없나요? [2]
발코더
2015-06-24
10585
9559create extension dblink 명령 실행시 문의드립니다.
정성헌
2015-06-19
9521
9557login role과 schema [1]
초보
2015-06-18
9642
9556autovacuum 과 row exclusive lock 의 관련 [5]
dalky
2015-06-09
11183
9554checkpoint 작업 시 cpu 100% 가 지속되는 문제 [2]
dalky
2015-05-29
10356
9553pg_dump 후 다른 스키마로 restore 하는 법 [3]
정성헌
2015-05-27
9804
9550postgreSQL DB 용량을 제한할 수 있는지 궁금합니다. [3]
박종혁
2015-05-15
10317
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다