PostgreSQL Autovacuum기준으로 vacuum 대상 테이블 목록 스크립트
| WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold') , vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor') , fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') , sto AS (select opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation , pg_size_pretty(pg_table_size(c.oid)) as table_size , age(relfrozenxid) as xid_age , coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age , (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * pg_table_size(c.oid)) as autovacuum_vacuum_tuples , n_dead_tup as dead_tuples FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1) left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid WHERE c.relkind = 'r' and nspname <> 'pg_catalog' and ( age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) or coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * pg_table_size(c.oid) <= n_dead_tup -- or 1 = 1 ) ORDER BY age(relfrozenxid) DESC LIMIT 50; |
'1. IT Story > Scripts' 카테고리의 다른 글
| Oracle SQL SysMetric 조회 스크립트 (0) | 2019.04.13 |
|---|---|
| Oracle Session UNDO, TEMP 사용 모니터링 (0) | 2019.04.12 |
| PostgreSQL Top SQL 추출 스크립트 (0) | 2019.04.11 |
| Oracle CPU Time, Waited Time 조회 스크립트 (0) | 2019.04.10 |
| PostgreSQL 통계(analyze) 상태 확인 스크립트 (0) | 2019.04.07 |
| Oracle 통계정보 백업, 복구(export/import) (0) | 2019.04.06 |
| Oracle 통계정보 추출 스크립트 (0) | 2019.04.05 |
| Oracle 특정 테이블 통계정보 확인 스크립트 (0) | 2019.03.31 |
운명을바꾸는자
IT와 함께 살아가는 삶
