Hello,
Recently we encountered a issue where the disc space is continuously
increasing towards 100%. Then a manual vacuum freed the disc space. But
again it is increasing. When digged more it is found that auto-vacuuming
was not running or it is either stucked/hanged.
Version: 9.1.12
Auto vacuum is enabled: check configuration details attached file.
Auto vacuum daemon running.
>From stats it shows that auto-vacuum last run almost more than month back.
There are no error logs from database.
The attached file has all these details. If any other details needed please
let me know. I will try to collect it and share. Please help to analyze why
auto-vacuum stopped suddenly?
--
Cheers,
Prakash
1) Auto-vacuum configuration:
=============================================================================================================================================================================
autovacuum | on
| Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1
| Number of tuple inserts, updates or deletes prior to analyze as a fraction of
reltuples.
autovacuum_analyze_threshold | 50
| Minimum number of tuple inserts, updates or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000
| Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3
| Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_naptime | 1min
| Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 20ms
| Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1
| Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor | 0.2
| Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 50
| Minimum number of tuple updates or deletes prior to vacuum.
log_autovacuum_min_duration | -1
| Sets the minimum execution time above which autovacuum actions will be logged.
=============================================================================================================================================================================
2) Auto-vacuum daemon is running:
ps -eg | grep autovacuum
5432 6504 6001 0 Apr14 ? 00:02:45 postgres: autovacuum launcher
process
3) Table sizes:
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size",
pg_size_pretty(pg_total_relation_size(C.oid) - pg_relation_size(C.oid)) as
"External Size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
relation | total_size | External Size
------------------+------------+---------------
public.abcd | 1108 MB | 592 kB
public.xyz | 5904 kB | 56 kB
4) Last run auto-vacuum:
SELECT now(), schemaname, relname, last_vacuum, last_autovacuum, vacuum_count,
autovacuum_count FROM pg_stat_user_tables;
now | schemaname | relname | last_vacuum |
last_autovacuum | vacuum_count | autovacuum_count
-------------------------------+------------+---------------+-------------+-------------------------------+--------------+------------------
2015-06-10 01:03:03.574212+02 | public | abcd | |
2015-04-18 00:52:35.008874+02 | 0 | 2
2015-06-10 01:03:03.574212+02 | public | xyz | |
2015-05-02 06:01:35.220651+02 | 0 | 20
NOTE: I changed the relname for above two tables due to confidentiality.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
