[PostgreSQL] dead tuple 모니터링 시 주의(vacuum full)

DBMS: PostgreSQL 14.9

- vacuum full 수행 시 pg_stat_all_tables 값(n_live_tup, n_dead_tup) 변경안됨(dead tuple 모니터링 시 주의)
- vacuum 수행 시 pg_stat_all_tables 값(n_live_tup, n_dead_tup) 변경됨

[목차여기]

VACUUM FULL 수행 시 n_dead_tup 값 변화

delete 작업으로 데이터 삭제하여 dead tuple 발생 후
vacuum full 수행 시 실제 사이즈는 줄어 dead tuple 삭제가 되었지만
pg_stat_all_tables의 n_live_tup, n_dead_tup 값은 변경되지 않고
ANALYZE 명령으로 통계 갱신 후 업데이트가 되는 것을 확인 할 수 있습니다.

> CREATE TABLE sk1.tab1 AS SELECT * FROM pg_class 

400 row(s) modified.

> ALTER TABLE sk1.tab1 SET (autovacuum_enabled = off)

0 row(s) modified.


> SELECT * FROM pg_catalog.pg_stat_all_tables 
WHERE schemaname = 'sk1'


relid |schemaname|relname|seq_scan|seq_tup_read|idx_scan|idx_tup_fetch|n_tup_ins|n_tup_upd|n_tup_del|n_tup_hot_upd|n_live_tup|n_dead_tup|n_mod_since_analyze|n_ins_since_vacuum|last_vacuum|last_autovacuum|last_analyze|last_autoanalyze|vacuum_count|autovacuum_count|analyze_count|autoanalyze_count|
------+----------+-------+--------+------------+--------+-------------+---------+---------+---------+-------------+----------+----------+-------------------+------------------+-----------+---------------+------------+----------------+------------+----------------+-------------+-----------------+
101919|sk1       |tab1   |       0|           0|        |             |      400|        0|        0|            0|       400|         0|                400|               400|           |               |            |                |           0|               0|            0|                0|

1 row(s) fetched.

> DELETE FROM sk1.tab1 

400 row(s) modified.


> SELECT * FROM pg_catalog.pg_stat_all_tables 
WHERE schemaname = 'sk1'


relid |schemaname|relname|seq_scan|seq_tup_read|idx_scan|idx_tup_fetch|n_tup_ins|n_tup_upd|n_tup_del|n_tup_hot_upd|n_live_tup|n_dead_tup|n_mod_since_analyze|n_ins_since_vacuum|last_vacuum|last_autovacuum|last_analyze|last_autoanalyze|vacuum_count|autovacuum_count|analyze_count|autoanalyze_count|
------+----------+-------+--------+------------+--------+-------------+---------+---------+---------+-------------+----------+----------+-------------------+------------------+-----------+---------------+------------+----------------+------------+----------------+-------------+-----------------+
101919|sk1       |tab1   |       1|         400|        |             |      400|        0|      400|            0|         0|       400|                800|               400|           |               |            |                |           0|               0|            0|                0|

1 row(s) fetched.


> SELECT nspname, 
relname,
pg_size_pretty(pg_relation_size(C.oid)) as size,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname = 'sk1'
AND c.relkind <> 'i'
ORDER BY pg_relation_size(C.oid) DESC


nspname|relname|size |total_size|
-------+-------+-----+----------+
sk1    |tab1   |88 kB|96 kB     |

1 row(s) fetched.

-- vacuum full 수행
> VACUUM FULL sk1.tab1 

0 row(s) modified.

-- dead tuple 값 변함없음
> SELECT * FROM pg_catalog.pg_stat_all_tables 
WHERE schemaname = 'sk1'


relid |schemaname|relname|seq_scan|seq_tup_read|idx_scan|idx_tup_fetch|n_tup_ins|n_tup_upd|n_tup_del|n_tup_hot_upd|n_live_tup|n_dead_tup|n_mod_since_analyze|n_ins_since_vacuum|last_vacuum|last_autovacuum|last_analyze|last_autoanalyze|vacuum_count|autovacuum_count|analyze_count|autoanalyze_count|
------+----------+-------+--------+------------+--------+-------------+---------+---------+---------+-------------+----------+----------+-------------------+------------------+-----------+---------------+------------+----------------+------------+----------------+-------------+-----------------+
101919|sk1       |tab1   |       2|         800|        |             |      400|        0|      400|            0|         0|       400|                800|               400|           |               |            |                |           0|               0|            0|                0|

1 row(s) fetched.

-- 테이블 사이즈는 줄어듬
> SELECT nspname, 
relname,
pg_size_pretty(pg_relation_size(C.oid)) as size,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname = 'sk1'
AND c.relkind <> 'i'
ORDER BY pg_relation_size(C.oid) DESC


nspname|relname|size   |total_size|
-------+-------+-------+----------+
sk1    |tab1   |0 bytes|8192 bytes|

1 row(s) fetched.

-- analyze 수행
> analyze sk1.tab1 

0 row(s) modified.

-- dead tuple 값 변경됨
> SELECT * FROM pg_catalog.pg_stat_all_tables 
WHERE schemaname = 'sk1'


relid |schemaname|relname|seq_scan|seq_tup_read|idx_scan|idx_tup_fetch|n_tup_ins|n_tup_upd|n_tup_del|n_tup_hot_upd|n_live_tup|n_dead_tup|n_mod_since_analyze|n_ins_since_vacuum|last_vacuum|last_autovacuum|last_analyze                 |last_autoanalyze|vacuum_count|autovacuum_count|analyze_count|autoanalyze_count|
------+----------+-------+--------+------------+--------+-------------+---------+---------+---------+-------------+----------+----------+-------------------+------------------+-----------+---------------+-----------------------------+----------------+------------+----------------+-------------+-----------------+
101919|sk1       |tab1   |       2|         800|        |             |      400|        0|      400|            0|         0|         0|                  0|               400|           |               |2024-04-21 22:02:42.551 +0900|                |           0|               0|            1|                0|

1 row(s) fetched.

반응형

VACUUM 수행 시 n_dead_tup 값 변화

dead tuple 발생 후 
vacuum 수행 시 pg_stat_all_tables의 n_live_tup, n_dead_tup 값이 바로 변경됩니다

> DROP TABLE sk1.tab1 

0 row(s) modified.

> CREATE TABLE sk1.tab1 AS SELECT * FROM pg_class 

400 row(s) modified.

> ALTER TABLE sk1.tab1 SET (autovacuum_enabled = off)

0 row(s) modified.


> SELECT * FROM pg_catalog.pg_stat_all_tables 
WHERE schemaname = 'sk1'


relid |schemaname|relname|seq_scan|seq_tup_read|idx_scan|idx_tup_fetch|n_tup_ins|n_tup_upd|n_tup_del|n_tup_hot_upd|n_live_tup|n_dead_tup|n_mod_since_analyze|n_ins_since_vacuum|last_vacuum|last_autovacuum|last_analyze|last_autoanalyze|vacuum_count|autovacuum_count|analyze_count|autoanalyze_count|
------+----------+-------+--------+------------+--------+-------------+---------+---------+---------+-------------+----------+----------+-------------------+------------------+-----------+---------------+------------+----------------+------------+----------------+-------------+-----------------+
101913|sk1       |tab1   |       0|           0|        |             |      400|        0|        0|            0|       400|         0|                400|               400|           |               |            |                |           0|               0|            0|                0|

1 row(s) fetched.

> DELETE FROM sk1.tab1 

400 row(s) modified.


> SELECT * FROM pg_catalog.pg_stat_all_tables 
WHERE schemaname = 'sk1'


relid |schemaname|relname|seq_scan|seq_tup_read|idx_scan|idx_tup_fetch|n_tup_ins|n_tup_upd|n_tup_del|n_tup_hot_upd|n_live_tup|n_dead_tup|n_mod_since_analyze|n_ins_since_vacuum|last_vacuum|last_autovacuum|last_analyze|last_autoanalyze|vacuum_count|autovacuum_count|analyze_count|autoanalyze_count|
------+----------+-------+--------+------------+--------+-------------+---------+---------+---------+-------------+----------+----------+-------------------+------------------+-----------+---------------+------------+----------------+------------+----------------+-------------+-----------------+
101913|sk1       |tab1   |       1|         400|        |             |      400|        0|      400|            0|         0|       400|                800|               400|           |               |            |                |           0|               0|            0|                0|

1 row(s) fetched.

-- vacuum 수행
> VACUUM sk1.tab1 

0 row(s) modified.

-- dead tuple 값 변경됨
> SELECT * FROM pg_catalog.pg_stat_all_tables 
WHERE schemaname = 'sk1'


relid |schemaname|relname|seq_scan|seq_tup_read|idx_scan|idx_tup_fetch|n_tup_ins|n_tup_upd|n_tup_del|n_tup_hot_upd|n_live_tup|n_dead_tup|n_mod_since_analyze|n_ins_since_vacuum|last_vacuum                  |last_autovacuum|last_analyze|last_autoanalyze|vacuum_count|autovacuum_count|analyze_count|autoanalyze_count|
------+----------+-------+--------+------------+--------+-------------+---------+---------+---------+-------------+----------+----------+-------------------+------------------+-----------------------------+---------------+------------+----------------+------------+----------------+-------------+-----------------+
101913|sk1       |tab1   |       1|         400|        |             |      400|        0|      400|            0|         0|         0|                800|                 0|2024-04-21 21:48:36.896 +0900|               |            |                |           1|               0|            0|                0|

1 row(s) fetched.

> analyze sk1.tab1 

0 row(s) modified.


> SELECT * FROM pg_catalog.pg_stat_all_tables 
WHERE schemaname = 'sk1'


relid |schemaname|relname|seq_scan|seq_tup_read|idx_scan|idx_tup_fetch|n_tup_ins|n_tup_upd|n_tup_del|n_tup_hot_upd|n_live_tup|n_dead_tup|n_mod_since_analyze|n_ins_since_vacuum|last_vacuum                  |last_autovacuum|last_analyze                 |last_autoanalyze|vacuum_count|autovacuum_count|analyze_count|autoanalyze_count|
------+----------+-------+--------+------------+--------+-------------+---------+---------+---------+-------------+----------+----------+-------------------+------------------+-----------------------------+---------------+-----------------------------+----------------+------------+----------------+-------------+-----------------+
101913|sk1       |tab1   |       1|         400|        |             |      400|        0|      400|            0|         0|         0|                  0|                 0|2024-04-21 21:48:36.896 +0900|               |2024-04-21 21:48:50.450 +0900|                |           1|               0|            1|                0|

1 row(s) fetched.