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