[nSLUG] Tip on postgres and vacuumdb

D G Teed donald.teed at gmail.com
Tue Feb 10 21:43:36 AST 2009


I'm not a DBA, but sometimes slip into the role
within systems work.

If you run a postgres database, you may have heard that it is a
good idea to periodically vacuum the database to free up
resources and optimize.

What we learned recently on a busy moodle installation is the following:

1. You cannot vacuum only the main application's database.  Every DB
must be vacuumed or the main postgres DB can get out of whack
with the maintenance being done on other DBs.

2. vacuum is required every 2 billion transactions or postgres
will shut itself down to prevent transaction ID wraparounds,
which would otherwise lead to data loss.

http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html

For an application like moodle, I measured 30,000 transactions
every 10 minutes.  10,000 every 10 minutes on the weekend
when it is hardly being used.  It is possible to reach 2 billion
quite easily (we came within 10 million).

3. If your vacuumdb statement does not include -v switch, it can silently
succeed, while in reality it has not completed due to parameters in
need of tweaking in postgresql.conf.  For example statement_timeout
may be exceeded or there may be a variable such as max_fsm_pages
which needs a boost to complete the vacuum.

I recommend a late night cron by the postgres user like so:

vacuumdb -a -v -f -z

autovacuum is also a possibility, but I don't know where you would
learn of the problems I see reported by the -v switch above.

--Donald
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://nslug.ns.ca/mailman/private/nslug/attachments/20090210/f716f80a/attachment-0001.html>


More information about the nSLUG mailing list