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 9884 게시물 읽기
No. 9884
다른 Session에서 다른 table에 lock인 상태에서 Vacuum을 하게되면
작성자
지현명(gwise)
작성일
2017-09-12 02:24
조회수
7,822

Vacuum 테스트 케이스#4

 

Session#2에서 관련 없는 테이블에 lock발생(아니면 오래 실행되는 조회쿼리) 상태에서

 

Session#1에서 실행되는 Vacuum의 처리 결과 테스트 문의..

 

 

 

drop table t_test;

 

drop table t_test2;

 

drop table t_test3;

 

drop table t_test4;

 

drop table t_test5;

 

 

 

CREATE TABLE t_test (i1 int,v1 char(100));

 

CREATE TABLE t_test2 (i1 int,v1 char(100));

 

CREATE TABLE t_test3 (i1 int,v1 char(100));

 

CREATE TABLE t_test4 (i1 int,v1 char(100));

 

CREATE TABLE t_test5 (i1 int,v1 char(100));

 

 

 

insert into t_test SELECT i,'abcd' FROM generate_series(1, 100000) a(i);

 

insert into t_test2 SELECT i,'abcd' FROM generate_series(1, 100000) a(i);

 

insert into t_test3 SELECT i,'abcd' FROM generate_series(1, 100000) a(i);

 

insert into t_test4 SELECT i,'abcd' FROM generate_series(1, 100000) a(i);

 

insert into t_test5 SELECT i,'abcd' FROM generate_series(1, 100000) a(i);

 

 

 

--다른 Session#2에서 현재 session과 관련 없는 테이블에 lock발생

 

--lock이 아니더라도 오래 실해되는 select도 포함

 

--Session#2

 

begin;

 

select * from employee

 

where emp_id = 1

 

for update;

 

 

 

--다른 세션의 lock확인

 

select b.relname,a.locktype,a.pid,a.mode,a.granted

 

from pg_locks a,pg_class b

 

where a.relation=b.oid

 

order by pid;

 

 

 

SELECT * FROM employee AS a, pgrowlocks('employee') AS p

 

WHERE p.locked_row = a.ctid;

 

 

 

--Vacuum 모니터링 : null

 

select last_vacuum from pg_stat_all_tables

 

WHERE RELNAME = 't_test4'

 

 

 

--1개 row삭제 (AutoVacuum작도 안되게 하고 페이지 열어서 확인하기 쉽게 하나만 삭제)

 

delete from t_test4 where i1 = 1;

 

 

 

--Vacuum 실행 (현재 다른 session#2에 관련 없는 테이블 lock걸려 있음)

 

vacuum ( verbose) t_test4;

 

정보: "public.t_test4" 청소 중

 

정보: "t_test4": 지울 수 있는 자료 0개, 지울 수 없는 자료 100000개를 1725/1725개 페이지에서 찾았음

 

DETAIL: 1개의 죽은 로우 버전을 아직 지울 수 없습니다. <- Vacuum실패

 

0개의 사용되지 않은 아이템 포인터가 있습니다.

 

0 페이지를 버퍼 핀닝으로 건너 뛰었습니다.

 

 

 

--Vacuum 성공하지 못했는데 pg_stat_all_tables에는

 

--정상으로 처리 됐다고 반영됨.

 

-- last_vacuum에 작업시간 업데이트 됨...<-실패했는데 왜 성공했다고 기록을 하고 있는걸까?

 

-- pg_stat_all_tables.last_vacuum으로 모니터링 하고 있었는데 믿으면 안될듯.

 

select last_vacuum, last_autovacuum from pg_stat_all_tables

 

WHERE RELNAME = 't_test4'

 

 

 

--그렇다면 실제 Vacuum 반영 됐는지 확인 페이지 열어서 확인

 

--정상 vacuum 하기 전 상태로 되어 있음..t_xmin, t_xmax..t_infomask등 컬럼에 null값으로 되어야 하나 값 들어가 있음.

 

--t_data에도 데이터 들어가 있음. (Vacuum 적용 안된 것임)

 

SELECT * FROM heap_page_items (get_raw_page ( 't_test4', 0));

 

 

 

--Session#2 Lock 종료 후 몇분이 지나서 다시 확인해도 Vacuum반영 안됨.

 

 

 

--다시강제로 Vacuum 실행(다른 session에서 lock이 없는 상태에서..)

 

vacuum ( verbose) t_test4;

 

정보: "public.t_test4" 청소 중

 

정보: "t_test4": 1개의 행 버전을 1개 페이지에서 삭제했습니다.

 

정보: "t_test4": 지울 수 있는 자료 1개, 지울 수 없는 자료 65개를 2/1725개 페이지에서 찾았음

 

DETAIL: 0개의 죽은 로우 버전을 아직 지울 수 없습니다.

 

 

 

--페이지 열어서 확인해 보면 이제야 정상으로 Vacuum실행되어 적용됨.

 

--.t_xmin, t_xmax..t_infomask등 컬럼에 null값에 전부 null로 되어 있고 t_data에도 데이터 없음. <-정상 Vacuum적용된 것임...

 

SELECT * FROM heap_page_items (get_raw_page ( 't_test4', 0));

 

 

 

다른 session에서 다른 table에 lock발생 된 이후

 

현재 session에서 vacuum은 반영되지 않습니다.

 

AutoVacuum, Vacuum, Vacuum Full 모든 Vacuum은 작동안함.

 

아래 테이블에 정상으로 실행됐다고 되어 있지만

 

select last_vacuum from pg_stat_all_tables

 

WHERE RELNAME = 't_test4'

 

페이지 열어 보면 실제로는 반영되어 있지 않다.

 

SELECT * FROM heap_page_items (get_raw_page ( 't_test4', 0));

 

verbose옵션을 기록으로 남겨서 리턴되는 메시지를 반듯이 확인해야 한다.

 

vacuum ( verbose) t_test4;

 

 

 

 

 

--질문사항--

 

 

 

Vacuum이 성공하지도 않았는데 왜 Postgresql은

 

pg_stat_all_tables.last_vacuum 컬럼에 성공했다고 반영할까요? 이거로 모니터링 하고 있었는데.....

 

사용자는 verbose 옵션으로 리턴되는 결과를 별도로 저장해서

 

확인하는 방법 말고 다른 방법이 있을까요?

 

 

 

 

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

pg_stat_all_tables.last_vacuum 은 vacuum이 수행됬다는 것에

대한 통계정보를 기록하는 것이지

완전하게 정리했다라는 의미는 아닙니다.

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

 제가 궁금했던 것은  다른 세션의 다른 테이블에 lock이 발생했는데

같은 페이지도 아닌데...왜 Vacuum이 반영 안될까요?

 lock이 하나라도 있으면 db전체에 vacuum이 작동 안하는데 이렇게 되면 위험한거 아닐까요?

 

지현명(gwise)님이 2017-09-12 09:12에 작성한 댓글입니다.
이 댓글은 2017-09-12 09:14에 마지막으로 수정되었습니다.

김상기님이 말했던거처럼

다른세션에서 lock이 잡힌 row에 대해서는 처리를 안합니다.

실제 운영환경에서 lock이 동일한 row에 계속 잡혀있다면 그건

application단에서 잘못된 logic을 구현한 것일테고

그 부분에 대해서까지 해결을 요구하는 것보단 오래된 lock에 대한 모니터링으로

해결하심이 맞을거 같습니다.

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

 vacuum하려는 테이블이 다른 세션에서 lock이 없게 위에서 테스트를 진행했습니다.

(위에 보시면 vacuum하려는 테이블을 다른 세션에서 lock한게 아니고 전혀 관련 없는 테이블입니다.)

위에보시면 전혀 다른 테이블이고 페이지도 안겹치게 하려고 테이블 여러개 만들었습니다.

 

Vacuum하려는 테이블은 t_test4이고 다른 세션에서 lock이 발생된건 employee테이블입니다.

페이지도 안겹치게 하려고 t_test5까지 용량크게 만들었구요

 

테스트 하는 것을 동영상으로 녹화해서 아래에 올렸습니다.

(크롬말고 ie에서 "전체화면" 으로 봐주세요~)

http://blog.naver.com/gwise/221094754348

지현명(gwise)님이 2017-09-12 10:35에 작성한 댓글입니다.
이 댓글은 2017-09-12 10:58에 마지막으로 수정되었습니다.

일반적으로 lock 이러면, table 또는 row exclusive lock을 이야기하는지라, 읽는 사람이 오해를 할 수 있습니다. 그냥 쉽게 표현하면 다른 세션이 해당 테이블을 사용했고, 그 테이블의 한 페이지(블럭)를 수정했고, (dirty page로 만들었고), 그 세션이 아직 붙어있고, 그 페이지가 아직 체크 포인트 작업으로 디스크로 내려가지 않았다면, 여유 공간 확보 작업은 건너 뛴다로 이해하면 될 겁니다. 정확한 것은 코드를 보면 됩니다. 오래전에 본 부분이라 가물가물합니다. 

여튼 그래서, PostgreSQL에서의 idle in transaction 상태가 오래 지속되는 세션은 위험하다고 합니다.

그렇지 않은 경우라면, - 일반적으로 정상적인 commit이나 rollback 이 되고, 세션이 idle 세션 상태라면, 대부분 정리를 합니다.

autovacuum이 이런 상황에서 모든 변경된 페이지에 대해서 여유 공간을 확보하지 못한다고 오해하면 안됩니다.

김상기(ioseph)님이 2017-09-12 10:38에 작성한 댓글입니다.
이 댓글은 2017-09-12 10:39에 마지막으로 수정되었습니다.

@김상기님

답변 주신거에서 같은 테이블/같은 페이지라면 이해가 되지만

 

다른 테이블/ 다른 페이지 인데도 그런가요? 이부분이 이해가 안됩니다.

 

내가 Vacuum하려는 테이블은 지금 아무도 사용(select/insert/delete/update)하고 있지 않는데

같은 db에 다른 테이블/다른페이지가 사용(?)중인데 왜 Vacuum이 안될까요?.

지현명(gwise)님이 2017-09-12 10:57에 작성한 댓글입니다.

김상기님의 말은 해당 세션이 살아있는 상태이고 checkpoint가 발생안했다는 말씀인데

1. 세션을 종료

2. checkpoint 수행

이 두가지를 해보심이 좋을듯합니다.

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

 @김주왕

Session#2를 종료하든 checkpoint를 수행하든 이건 Session#1에서 vacuum하려는 테이블이나 페이지가 동일할 경우를 가정하시고 말씀하시는건가요?

 

Session#1에서 Vacuum하려는 테이블하고 관련이 없는데 Session#2를 종료하거나 checkpoint하는거랑 어떤 관련이 있을까요?

지현명(gwise)님이 2017-09-12 11:14에 작성한 댓글입니다.
이 댓글은 2017-09-12 11:15에 마지막으로 수정되었습니다.

다 나름대로 이유가 있었겠죠. 저 문제는 트랜잭션 안에서 lock 상태가 exclusive 로 변경되면서 일어나는 현상이네요. 그 트랜잭션이 앞으로 어떤 테이블의 자료를 더 잠글지 모르는 상황이기 때문에, 해당 데이터베이스의 전체 쓰지 않는 자료도 일단 보관하겠다는 정책이네요.

lock 트랜잭션이 delete 트랜잭션보다 먼저 있어, 그 트랜잭션이 아직 정리 되지 않으면, 여유공간 확보 작업이 보류되네요.

더 테스트를 해보세요.

 

세션1 - delete from a (txid = 1)

세션2 - begin ; select * from b for update (txid = 2)

세션1 - vacuum a (txid = 3) (세션 2의 txid = 2가 txid 1 보다 큼)  txid = 1 작업을 정상 처리

세션1 - delete from a (txid = 4)

세션1 - vacuum a (txid =5) (세션2의 txid = 2 가 현 txid =5 보다 작음) txid =4 작업 보류

 

이런식이네요.

당연한 정책으로 판단됩니다.

 

김상기(ioseph)님이 2017-09-12 11:25에 작성한 댓글입니다.
이 댓글은 2017-09-12 11:30에 마지막으로 수정되었습니다.

결론은 txid(트랜잭션id) 때문인거 같네요..

txid가 테이블이 아니라 db단위로 증가하니까(이건 mvcc하고 관련있고)

세션2에서 발생한 txid보다 큰것은 세션2의 txid가 어떻게 될지 모르니

세션1이 세션2이후에 발생되면 어떻게 할 수가 없는거였네요...

 세션#1 입장에서는 억울하겠지만...pg의 mvcc가 되려면 어쩔수 없네요....

 이거 하나만 봐서는 이해가 안되는데 pg전체 흐름을 봐야 겠군요..

 감사합니다.

 

p.s

다시 생각해 보니 mvcc하고 뭔 관련 있지? 어차피 데이터는 삭제 되서 commit됐고

vacuum만 안되는건데... 어렵네요..

지현명(gwise)님이 2017-09-12 11:37에 작성한 댓글입니다.
이 댓글은 2017-09-12 12:17에 마지막으로 수정되었습니다.

 저는 계속 테스트를 해도 Session#2가 RowShareLock이고 Exclusive로 상승 되지 않습니다.

select * from pg_locks 이거 말고 다른거로 확인 하신건가요?

지현명(gwise)님이 2017-09-12 13:52에 작성한 댓글입니다.

PostgreSQL에서는 dml이 있는 트랜잭션이나, 예상되는 트랜잭션인 경우만 트랜잭션번호를 부여합니다. 그래서, 그 트랜잭션 번호되는 경우면 위 정책이 반영되네요. lock mode 라기 보다는 pg_locks.transactionid 값에 따른 처리네요.

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

저기에 있었군요...

ExclusiveLock 지금 보입니다........

SELECT transactionid, pid, mode

FROM pg_locks WHERE locktype = 'transactionid'

transactionid : 2579  <- 얘는 pgrowlocks의 locker하고 값이 동일하네요....

pid : 3680

mode  : ExclusiveLock

 -> pid 3680 의 lock을 "ExclusiveLock"로 변경하라는 의미인가요?

pid 3680이  Session#2의 RowShareLock이니까 .....

 

 Vacuum은 일단 여기까지만 봐야 겠습니다. (더 궁금하긴 하지만...)

위에 내용은 알겠는데 왜 저렇다 하더라도 Vacuum이 안되게 해 놨는지는 이해가 되지는 않습니다. 

개발자 입장에서는 여기까지만 알아도 충분할거 같습니다. 

 

자세한 답변 감사합니다.   

지현명(gwise)님이 2017-09-12 15:21에 작성한 댓글입니다.

거의 스터디 수준이군요.

지현명님 답변에는 조심해야겠네요.

제가 낚였어요. 크크

더 궁금한 것이 있으시면 매달 있는 세미나에서 논의하시면 됩니다. 이런 것 하려고 만든 시간이었습니다.

여기서는 질답만.

 

김상기(ioseph)님이 2017-09-12 15:48에 작성한 댓글입니다.

매달 있는 세미나에 가고 싶은데 가정의 평화를 위해 육아를 ㅠㅠ

Vacuum은 이정도면 개발자한테는 충분할거 같아요 ㅎㅎ (진심 감사)

미끼 안물면 어쩌나 했는데 다행입니다. ^__^

이 주제는 시연아카데미 대표님이 알려주셨습니다. 

 

pg 개발팀에 부탁하고 싶은점.

Vacuum이야 Verbose로 메시지남겨서 보면 되지만 AutoVacuum은 Log파일을 찾아보지 않는 이상

위의 경우는 정확히 실행 됐는지 알수 없는데 pt_stat_all_tables나 다른 테이블 pg_vacuum_result(임의 테이블)에 결과를 남겨 줬으면 좋겠습니다. 

테스트 하면서 pg_stat_all_tables에 정상으로 vacuum컬럼에 시간이 업데이터 되서 이거만 믿고 테스트 끝내려고 하다가 혹시나 해서 페이지 열어보고 vacuum안되서 놀랬습니다. 

p.s

김주왕님도 답변해 주셔서 감사드립니다. ~

지현명(gwise)님이 2017-09-12 16:23에 작성한 댓글입니다.
이 댓글은 2017-09-12 16:29에 마지막으로 수정되었습니다.

9월 세미나 계획 있으신가요?

이 주제로 해 주시면 이때 참석하겠습니다. 

이해 안되는 부분이 더 있는데 넘어 가기에는 찜찜합니다.

지현명(gwise)님이 2017-09-12 17:56에 작성한 댓글입니다.
이 댓글은 2017-09-12 18:00에 마지막으로 수정되었습니다.

이때 왜 이렇게 열심히 pg가 궁금했을까

지현명(gwise)님이 2021-03-08 16:30에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
9889C# 콘솔 응용프로그램에서 [1]
초보개발자
2017-09-18
6736
9888MS-SQL 데이터를 PostgreSQL로 [2]
왕초보개발자
2017-09-14
7489
9887C프로그램에서 Postgresql 접속 및 조회하는 .so 파일 호출시 문의드립니다. [4]
플그램초짜
2017-09-14
7059
9884다른 Session에서 다른 table에 lock인 상태에서 Vacuum을 하게되면 [17]
지현명
2017-09-12
7822
9883AutoVacuum 메뉴얼 공식관련 [6]
지현명
2017-09-11
8245
9882AutoVAcuum인데 테이블(파일) size 작아지는 현상 [3]
지현명
2017-09-08
7138
9881시간대별 조회 쿼리 궁금 합니다 [2]
추성민
2017-09-08
7953
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.049초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다