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 9883 게시물 읽기
No. 9883
AutoVacuum 메뉴얼 공식관련
작성자
지현명(gwise)
작성일
2017-09-11 11:21:25
조회수
568

 안녕하세요. 지현명입니다. 메뉴얼에 있는 AutoVacuum 공식 관련 질문입니다.

 아래 공식 메뉴얼 "24.1.6 The AutoVacuum Daemon)에 보면 

https://www.postgresql.org/docs/current/static/routine-vacuuming.html

 이러한 공식이 나옵니다. AutoVacuum이 실행되는 조건

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

그래서 테이블에 250개 입력하고 테스트를 했습니다.

 

공식 메뉴얼 상으로는 아래와 같이 100 row이상 삭제 했을때만 AutoVacuum이 실행 되어야 하는데

(AutoVacuum 실행여부는 select last_autovacuum from pg_stat_all_tables 에서 확인)

 

Autovacuum Daemon
vacuum base threshold 50 A
vacuum scale factor  0.2 B
전체행수(number of tuples) 250 C
vacuum scale factor * number of tuples 50 D=(B*C)
     
AutoVacuum threshold 100 A+D
     
전체 행수가 250일때 최소한 100 row 이상 삭제
되어야 AutoVacuum이 발생한다.

50~ 100  사이의 row를 삭제하면  AutoVacuum이 되는 경우도 있고 아닌 경우도 있습니다. (일관성이 없습니다...)

50, 60, 70,80,90 이렇게 테스트 했습니다.

확실히 50 이하에서는 AutoVauum이 실행되지 않았고, 100 row이상에서는 매번 AutoVacuum이 실행됐습니다.

메뉴얼 상으로는 이 공식이 맞는데 pg_stat_all_tables.n_dead_tup >= threshold + pg_class.reltuples * scale_factor

Mastering postgresql 9.6 책에는 아래와 같은 표현이 나옵니다.  50row는 20%의 옵션 조건이지 더하는건 아닌데 제가 테스트 한 바도

이 책 내용처럼 vacuum base threshold (50)을 더하는게 아닌거 같은데 Postgresql의 정확한 조건은 어떤건가요?

"Therefore autovacuum_vacuuum_threshold says that we need 20% and that 20% must be at least 50 rows. Otherwise, VACUUM won't kick in. "

제 pc에 설치된 Postgresql의 다른 환경 조건 때문에 AutoVacuum이 오작동 하는걸까요? Mastering Postgresql 9.6 책만 아니면 그냥 그렇구나 하나 넘어갔을 텐데 저 문구가 계속 걸립니다. 공식 메뉴얼이 틀릴리도 없고....궁금합니다.

p.s

40개 삭제하고 2분뒤에 다시 60개 삭제하면 AutoVacuum이 실행됐습니다.

 

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

50~100 row를 테스트하셨을때

한 개 테이블로 계속 하신건가요

아니면 별도의 테이블로 각각 해보신건가요..

그부분이 명확해야할듯합니다.

김주왕(kimjuking)님이 2017-09-11 12:33:49에 작성한 댓글입니다.

추가적으로 소스를 보시면

https://doxygen.postgresql.org/autovacuum_8c_source.html#l02925

(전 참고로 개발을 모릅니다..ㅋ)

 

vactuples = tabentry->n_dead_tuples;

vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;

dovacuum = force_vacuum || (vactuples > vacthresh);

 

force_vacuum은 1번 조건이고 현재 테스트하시는건 2번조건인데

n_dead_tuples를 테스트하기전에 비교해보시면 확인이 가능할 듯합니다.

또한 n_dead_tuple이 변경되려면 autoanalyze가 수행되야는데

50+0.1*250=75 row마다 갱신되므로

참고하시길 바랍니다.

김주왕(kimjuking)님이 2017-09-11 12:41:28에 작성한 댓글입니다.
이 댓글은 2017-09-11 12:44:55에 마지막으로 수정되었습니다.

그냥 작동 할 때 되면 작동합니다.

뭘 그리 고민을 하는지, 그 고민 보다, 작동되어 여유 공간을 잘 확보 해야 하는 테이블에 대해서 autovacuum 이 작동하지 않아서 테이블을 의도하지 않게 부풀려지는 문제가 DBA 한테는 더 중요합니다.

 

이 부분은 autovacuum_max_workers 수와 해당 데이터베이스에 속한 테이블들의 자료량과 관계됩니다. 그래서, 개별 테이블들의 autovacuum_vacuum_scale_factor 값을 개별 설정 해야하는 경우가 생깁니다. 이게 더 아주 번거롭게 합니다. 적어도 운영 6개월 이상 되면 좀 꼼꼼하게 살펴보아야합니다.

 

이건 일반화한 운영 방법론을 적용하기가 참 힘들어요.

개념은 딱 하나입니다. autovacuum worker가 모두 자기 할 일이 바빠서 미쳐 못 챙기는 테이블을 없게 하라.

 

운영상 또 하나 중요한 것은 트랜잭션이 많이 일어나는 서비스에 대해서 autovacuum freeze 관련 설정도 함께 고려해야합니다. freeze 작업은 데이터베이스 전체를 대상으로 일어나기 때문에 freeze 작업이 몰리면, 의도하지 않게 과도한 I/O가 발생해서 서비스 품질을 떨어뜨리기도 하기 때문입니다. 물론 9.6에서 많이 개선되기는 했지만, 잦은 DML이 발생하는 테이블이라면 전략을 잘 짜야합니다. 극단적 설정으로는 개별 테이블에 vacuum_freeze_table_age 값까지 개별 설정하기도 합니다. 이 또한 일반화 할 만한 방법도 없습니다. 데이터베이스가 얼마나 빨리 늙어가는지를 살펴보고, 그렇게 노화 원인을 찾고, 그 원인이 되는 테이블 대상으로 freeze 작업이 진행될 때 전체 데이터베이스에 어느 정도 영향을 끼치는지 예상하고, 그 i/o 부하를 어떻게 분산할 것인지를 결정하는 것 뿐입니다.

 

정말 인공지능적인 autovacuum 방법론이 PG 쪽에 도입되면 PG의 획기적인 변화가 될 것 같기도 해요. 그럼 DBA 업무에서 꽤 많은 부분이 사라질터이고, vacuum 작업으로 인한 부가적인 i/o가 획기적으로 개선될터이니 말입니다. 아직까지 글로벌 개발그룹이 그 답을 찾지는 못했습니다.

지현명님이 한 번 도전해 보세요.

 

김상기(ioseph)님이 2017-09-11 13:05:32에 작성한 댓글입니다.

두분의 답변 감사합니다. 

#1 한개 테이블로 테스트 할때마다 drop & create했습니다. 50, 60, 70 각 케이스 마다 drop & create

   케이스마다 별도 테이블로 했다고 보시면 됩니다. 

#2. 소스 저기서 볼수 있군요 감사합니다. ^^   공식 메뉴얼이 맞네요.

     메뉴얼하고 소스를 같이 봐야 겠습니다. 그럼 다른 이유가 있는건데 ...더 찾아 봐야겠습니다.

pg_stat_all_tables.n_dead_tuple 값은 AutoAnalyze 실행 여부와 관련없이 바로 반영이 되는데요.

60 라인 삭제하니 바로 n_dead_tuple에 60이 반영됐습니다. 

#3. 공식대로 작동하는지가 궁금했습니다. 

AutoVacuum을 자세히 보게된 계기는 다른 sesison에서 long query나 기타 lock상태가 되면 

다른 테이블임에도 불구하고 AutoVacuum이 실행되어야 하는데 작동이 안되어 이것저것 테스트하게 됐습니다.

이 경우는 vacuum도 작동이 안됐고 vacuum verbose로 아래 메시지를 확인했습니다.

 정보: "public.t_test" 청소 중 정보: "t_test": 삭제가능한 0개, 삭제불가능한 1000개의 행 버전을 46 페이지에서 발견했음.

DETAIL: 1000 개의 사용하지 않는 로우 버전을 아직 지우지 못했음.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

Query OK, 0 rows affected (execution time: 47 ms; total time: 47 ms)

 

지현명님이 2017-09-11 13:59:52에 작성한 댓글입니다. Edit

vacuum을 제대로 파보려고 하나보네요. 소스 보면 그것도 설명이 나오는데,

일반적으로 여유공간 확보가 어려운 안 쓰는 로우는 그 페이지를 다른 세션이 사용하고 있어서입니다.

해당 테이블을 누가 쓰고 있는지 확인하고 그 클라이언트가 더 이상 그 테이블을 사용하지 않도록 끊어버리든가, 트랜잭션을 닫든가 이런 저런 적절한 조치가 있고 나면 여유 공간 확보 작업이 의도한 대로 이루워질 것 입니다.

 

김상기(ioseph)님이 2017-09-11 14:33:17에 작성한 댓글입니다.

 다른 세션의 다른 테이블 이였는데 같은 페이지 였는지는 확인을 못했습니다. 

다시 테스트 해보겠습니다.

감사합니다.

 

지현명(gwise)님이 2017-09-11 15:54:30에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
9888MS-SQL 데이터를 PostgreSQL로 [2]
왕초보개발자
2017-09-14
499
9887C프로그램에서 Postgresql 접속 및 조회하는 .so 파일 호출시 문의드립니다. [4]
플그램초짜
2017-09-14
488
9884다른 Session에서 다른 table에 lock인 상태에서 Vacuum을 하게되면 [16]
지현명
2017-09-12
577
9883AutoVacuum 메뉴얼 공식관련 [6]
지현명
2017-09-11
568
9882AutoVAcuum인데 테이블(파일) size 작아지는 현상 [3]
지현명
2017-09-08
587
9881시간대별 조회 쿼리 궁금 합니다 [2]
추성민
2017-09-08
548
9880AutoVacuum 이 실행되는 조건 [5]
지현명
2017-09-08
572
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2017 DSN, All rights reserved.
작업시간: 0.076초, 이곳 서비스는
	PostgreSQL v9.6.3으로 자료를 관리합니다