데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

PostgreSQL Q&A 6785 게시물 읽기

No. 6785

vacuum에 관한 문의 드립니다.
작성자	김영호(wineoflove)	작성일	2006-07-20 10:58	조회수	4,801

CREATE TABLE hospitalmanage
(
hoscode char(32) NOT NULL,
hosname varchar(50),
hosip varchar(20),
lastechotime varchar(24),
hostel varchar(32),
hoslocal1 varchar(32),
hoslocal2 varchar(32),
CONSTRAINT hosm_pk PRIMARY KEY (hoscode),
CONSTRAINT hospitalmanage_uk UNIQUE (hoscode)
)
WITH OIDS;
ALTER TABLE hospitalmanage OWNER TO postgres;

위와 같은 테이블이 있고,

약 74개 정도릐 row 가 있습니다.

데이터들은 5분단위로 전체 row 가 갱신이 됩니다.

그런데,, 문제가

2~3일에 한번씩 full vacuum 을 안돌리면,,,

업데이트 쿼리나 select 쿼리가 엄청나게 느려진다라는 겁니다..

현재 운영체제는 CentOS 4.2 이고 DB 버젼은 8.1 입니다.

대체,, 이유가 먼지 알수가 없어서,, 이렇게 문의 드립니다..

혹시나 해서 pgadmin 에서 2틀만에 full, analyze 모드로 maintenance 돌린 결과물을 아래 첨부 합니다.

많은 고수분들의 답변 기다리겠습니다.

--------------------------------------------------------------------------------

INFO: vacuuming "public.hospitalmanage"
INFO: "hospitalmanage": found 757631 removable, 74 nonremovable row versions in 14707 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 104 to 191 bytes long.
There were 49 unused item pointers.
Total free space (including removable row versions) is 117142860 bytes.
14691 pages are or will become empty, including 0 at the end of the table.
14707 pages containing 117142860 free bytes are potential move destinations.
CPU 0.39s/0.23u sec elapsed 6.99 sec.
INFO: index "hospitalmanage_uk" now contains 74 row versions in 9827 pages
DETAIL: 757631 index row versions were removed.
9636 index pages have been deleted, 9636 are currently reusable.
CPU 0.85s/1.13u sec elapsed 66.17 sec.
INFO: index "hosm_pk" now contains 74 row versions in 5885 pages
DETAIL: 757631 index row versions were removed.
5718 index pages have been deleted, 5718 are currently reusable.
CPU 0.83s/1.16u sec elapsed 84.27 sec.
INFO: "hospitalmanage": moved 74 row versions, truncated 14707 to 2 pages
DETAIL: CPU 1.07s/0.28u sec elapsed 28.49 sec.
INFO: index "hospitalmanage_uk" now contains 74 row versions in 9827 pages
DETAIL: 74 index row versions were removed.
9649 index pages have been deleted, 9649 are currently reusable.
CPU 0.30s/0.06u sec elapsed 8.31 sec.
INFO: index "hosm_pk" now contains 74 row versions in 5885 pages
DETAIL: 74 index row versions were removed.
5731 index pages have been deleted, 5731 are currently reusable.
CPU 0.15s/0.03u sec elapsed 5.45 sec.
INFO: analyzing "public.hospitalmanage"
INFO: "hospitalmanage": scanned 2 of 2 pages, containing 74 live rows and 0 dead rows; 74 rows in sample, 74 estimated total rows

Total query runtime: 201125 ms.

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

그냥 vacuum 만 해도 느려지나요?

8.1 버전이시면 autovacuum 을 이용해 보시면 좋겠네요.

postgresql.conf 파일에서 autovacuum을 활성화 해주시고 autovacuum을 위한 통계 수집 관련 설정을 두어개 풀어주셔 보세요.

DB전체에 락을 거는 full vacuum 말고 일반 vacuum만으로 피해가실 수 있을 것 같네요.

신기배(소타)님이 2006-07-21 22:49에 작성한 댓글입니다.

저도 Autovacuum옵션 키시는걸 권장합니다. 저도 비슷한 성격의 테이블이 있는데 매번 Delete/Insert/Update가 수시로 일어나죠.

근데. 이 POSTGRESQL은 좀 특이하게 반응해서 이 테이블의 사이즈가 계속 증가합니다. 즉 74개의 row가 있다고 해서 이 정도의 데이터만 데이터파일에 있는게 아니라 이전에 있던 Space가 남아있게 됩니다.

(물론 사용하지 않게 되지만요.)

Vacuum Full작업시에 이 Space를 없애주는 역할을 하구요.

이때 autovacuum의 과정이 들어간다면 이전에 남아있는 space를 사용하지 않는다는 marking을 하고 그곳에 다시 새로운 데이터를 적게되어 실제 Data File사이즈는 증가하지 않게 됩니다.

이 Datafile 사이즈가 증가하면 SELECT/UPDATE등 쿼리가 느려지게 됩니다.(데이터 파일 사이즈가 커서 이를 위한 IO 비용이 증가하게 됩니다.)

확인하시려면

select * from pg_class where relname like 'hospitalmanage%';

에서 rel_page라는 값을 보시면 대충 이것이 얼마나 사용하는지 확인하실수 있습니다.

autovacuum을 사용하시면 많이 증가하지 않습니다.

아 로그를 보니까 상당히 많이 쓸모없는 공간이 많더라구요.

황제펭귄님이 2006-07-24 02:57에 작성한 댓글입니다. Edit Ｘ

움,, postgresql 자체가 그런거라,, 주기적인 vacuum 같은건

어쩔수 없는거군요,,,

일단 select * from pg_class where relname like 'hospitalmanage%';

위 쿼리 날린 결과보니까

rel_page 값이 10993 이 나옵니다.

일단은,, autovacuum은, 사용 방법을 보고 있는 중이라,

크론으로 하루에 한번 vacuumdb -z 로 돌리게 해놨습니다.

당분간 상태를 좀 지켜 봐야 될것 같습니다. ㅠㅠ;;

그동안 db 가 주로 insert 와 select 만 일어나는 거라 이런 문제가 없었는데,

한동안 좀 답답했었습니다.

암튼 너무너무 감사 합니다. ^^

김영호(wineoflove)님이 2006-07-24 12:03에 작성한 댓글입니다.

한가지 꽁수 알려드리면

혹시 Insert/select 말고 delete * from

이런식의 작업이 있으시다면 delete * from 대신에 truncate 문장 사용을

권장합니다.

(전체 로우 삭제 하는 작업이 없으시면 SKIP)

delete * from 은 page 를 반환하지 않는 대신에

truncate는 Page를 반환합니다.(0 으로 만들어버리지요.)

테이블 전체 데이터 삭제 작업은 truncate로 하시기 바랍니다.

황제펭귄님이 2006-07-24 17:46에 작성한 댓글입니다. Edit Ｘ

^^ 감사 합니다~

전체 로우 삭제하는 일은 거의 없지만,

가끔 그럴 필요가 있을때가 있었는데 앞으로는

delete 대신 truncate를 쓰면 되겠네요~

저희 시스템에서는 insert / select 외에는 다른 쿼리는 거의 없습니다.

단지 이번에 메인 관리용 DB를 만들면서;; update가 수시로 일어나는 DB여서;

한참 당황을 했었죠 ^^

아참,, 몰랐었는데,, 국내보다 일본은 PGSQL 이 훨씬 활성화 되어 있나 보더군요?

이번에 일본에 진출하면서 조사하다 보니 알게 됬는데 ㅎㅎ

아 국내에서 처럼,,;; pgsql 쓴다고 무시 당할일은;; 그래도 많이 적지 않을까?

하는 기대 하고 있습니다~

암튼 답변 주신 분들 너무 감사 합니다.~

김영호(wineoflove)님이 2006-07-26 11:30에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
6790	PL/TCL , PL/PYTHON [1]	rotor	2006-07-27	3406
6787	변수 바인딩 지원 안되나요? [1]	백수환	2006-07-25	3413
6786	외부 접근 가능하게 도와 주세요 [1]	전그루	2006-07-21	3775
6785	vacuum에 관한 문의 드립니다. [5]	김영호	2006-07-20	4801
6784	델파이에서 Postgre에 update 시 에러발생(업 테이트는됨) [1]	권태수	2006-07-17	3379
6781	PostgreSQL 모델링 툴 추천부탁드립니다; [1]	최시원	2006-07-15	3657
6780	PostgreSQL 이 설치되지 않습니다.	초보	2006-07-15	3307