-- [SQL-10-2-1-1-a]
SHOW autovacuum_vacuum_scale_factor; -- 0.2
SHOW autovacuum_vacuum_threshold; -- 50
SQL
복사
-- [SQL-10-2-1-1-b]
SELECT t1.oid ,t1.relname ,t1.reltuples
,(current_setting('autovacuum_vacuum_threshold')::decimal
+ (current_setting('autovacuum_vacuum_scale_factor')::decimal * t1.reltuples)
) dead_tup_limit -- 오토베큠이 작동할 데드 튜플 임계치
,t2.n_dead_tup -- 현재 데드 튜플 수
FROM pg_class t1
LEFT OUTER JOIN pg_stat_user_tables t2
ON (t2.relid = t1.oid)
WHERE t1.oid IN ('startdbpg.ms_mbr_big'::regclass,'startdbpg.tr_ord_big'::regclass)
ORDER BY t1.relname;
SQL
복사
-- [SQL-10-2-1-2-a]
SHOW autovacuum_vacuum_insert_scale_factor; -- 0.2
SHOW autovacuum_vacuum_insert_threshold; -- 1000
SQL
복사
-- [SQL-10-2-1-2-b]
SELECT t1.oid ,t1.relname ,t1.reltuples
,( current_setting('autovacuum_vacuum_insert_threshold')::decimal
+ (current_setting('autovacuum_vacuum_insert_scale_factor')::decimal * t1.reltuples)
) ins_tup_limit -- 오토베큠이 작동할 신규 입력 튜플 임계치
,t2.n_ins_since_vacuum -- 마지막 베큠 후 신규 입력 튜플 수
FROM pg_class t1
LEFT OUTER JOIN pg_stat_user_tables t2
ON (t2.relid = t1.oid)
WHERE t1.oid IN ('startdbpg.ms_mbr_big'::regclass,'startdbpg.tr_ord_big'::regclass)
ORDER BY t1.relname;
SQL
복사
-- [SQL-10-2-1-3-a]
SHOW autovacuum_freeze_max_age; -- 200,000,000
SHOW autovacuum_multixact_freeze_max_age; -- 400,000,000
SQL
복사
-- [SQL-10-2-1-3-b]
SELECT t1.oid ,t1.relname
,age(t1.relfrozenxid) AS xid_age
,mxid_age(t1.relminmxid) AS mxid_age
FROM pg_class t1
WHERE t1.oid IN ('startdbpg.ms_mbr_big'::regclass, 'startdbpg.tr_ord_big'::regclass)
ORDER BY t1.relname;
SQL
복사
-- [SQL-10-2-1-4-a]
SHOW vacuum_freeze_table_age; -- 150,000,000
SHOW vacuum_multixact_freeze_table_age; -- 150,000,000
SQL
복사
-- [SQL-10-2-1-5-a]
ALTER TABLE startdbpg.tr_ord_big
SET (autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 50000,
autovacuum_vacuum_insert_scale_factor = 0,
autovacuum_vacuum_insert_threshold = 200000);
SQL
복사
-- [SQL-10-2-1-5-b]
SELECT UNNEST(COALESCE(t1.reloptions, '{}')) AS opt
FROM pg_class t1
WHERE t1.oid = 'startdbpg.tr_ord_big'::regclass;
SQL
복사
-- [SQL-10-2-1-5-c]
ALTER TABLE startdbpg.tr_ord_big
RESET (autovacuum_vacuum_scale_factor,
autovacuum_vacuum_threshold,
autovacuum_vacuum_insert_scale_factor,
autovacuum_vacuum_insert_threshold);
SQL
복사