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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance