Re: Vacuum settings

dforums <dforums 'at' vieonet.com> writes:

> 2Q) Here are my settings for vacuum, could you help me to optimise
> those settings, at the moment the vacuum analyse sent every night is
> taking around 18 h to run, which slow down the server performance.

It's a lot of time for a daily job (and it is interesting to
vacuum hot tables more often than daily). With typical settings,
it's probable that autovacuum will run forever (e.g. at the end
of run, another run will already be needed). You should first
verify you don't have bloat in your tables (a lot of dead rows) -
bloat can be created by too infrequent vacuuming and too low FSM
settings[1]. To fix the bloat, you can dump and restore your DB
if you can afford interrupting your application, or use VACUUM
FULL if you can afford blocking your application (disclaimer:
many posters here passionately disgust VACUUM FULL and keep on
suggesting the use of CLUSTER).

Ref:
[1] to say whether you have bloat, you can use
contrib/pgstattuple (you can easily add it to a running
PostgreSQL). If the free_percent reported for interesting
tables is large, and free_space is large compared to 8K, then
you have bloat;

another way is to dump your database, restore it onto another
database, issue VACUUM VERBOSE on a given table on both
databases (in live, and on the restore) and compare the
reported number of pages needed. The difference is the
bloat.

live=# VACUUM VERBOSE interesting_table;
[...]
INFO: "interesting_table": found 408 removable, 64994 nonremovable row versions in 4395 pages

restored=# VACUUM VERBOSE interesting_table;
[...]
INFO: "interesting_table": found 0 removable, 64977 nonremovable row versions in 628 pages

=> (4395-628)*8/1024.0 MB of bloat

(IIRC, this VACUUM output is for 7.4, it has changed a bit
since then)

--
Guillaume Cottenceau