Re: Vaccuum best practice: cronjob or autovaccuum? - Mailing list pgsql-general

On Thu, 2008-08-28 at 19:53 +0800, Phoenix Kiula wrote:
> On our database of about 5GB we vaccuum all of our 12 tables (only one
> is huge, all others have about 100,000 rows or so) every hour or so.

if you refer to manual VACUUM or VACUUM FULL every hour is probably too
much. You should aim your vacuum full for about 1ce per week.

>
> But we also have autovaccuum enabled. Is this okay? Do the two vaccuum
> processes contradict each other, or add unnecessary load to the
> system?

read the manuals in www.postgresql.org

specifically read this seciton:

http://www.postgresql.org/docs/8.3/static/maintenance.html

you'll find that once in a while (start at once/week and build up or
down from there) you can/should:

- vacuum full
- reindex your tables
- reindex your indexes

>
> The reason we introduced the cronjob we felt was that the autovaccuum
> was not really doing its job.

how did you realise that ? turn off the cron job, wait a few days. In
the meanwhile monitor your disk space ocupation (du
-sh /var/lib?/pgsql....????/base/)

if you see that size growing and the total row count (select count(*)
from whatever) isn't gorwing you need external vacuums

>  I wonder if anyone can share some
> insight on whether these settings are good for a DB that is basically
> 24x7:

like someone sayd: it's not the 24x7. it's the: how many tuples get
DELETEd or UPDTATEd (for the case of autovacuum) in one day, for
example.

If you find that your db updates/deletes many tuples per hour

>
> autovacuum                   = on
> autovacuum_vacuum_cost_delay = 20
> vacuum_cost_delay            = 20
> autovacuum_naptime           = 10
> stats_start_collector        = on
> stats_row_level              = on
> autovacuum_vacuum_threshold  = 75
> autovacuum_analyze_threshold = 25


> autovacuum_analyze_scale_factor  = 0.02
> autovacuum_vacuum_scale_factor   = 0.01

these two can be tricky: if your database is very big, then 2% of 'very
big' is 'a lot of changes' before autovacuum even tries to vacuum.

read the documentation about these two.

you might want to consider using scale factors of 0 and increase just a
bit both thresholds; p. ex

autovacuum_vacuum_threshold  = 20000
autovacuum_analyze_threshold = 10000

Autovacuum is something that you adjust to your needs;

Another tip: edit your postgresql.conf and bring the debug levels to:

log_min_messages = debug3
log_min_error_statement = debug3

then just follow the logfile (location depends on
distro; /var/log/postgresql;  /var/pgsql/data/; whatever)

IMPORTANT: don't leave the debug3 forever: it is very verbose and _will_
eat up your disc in no time;

you might want to do something like this:

tail -f logfile | grep vac

or

tail -f logfile | grep threshold


take your time :) autovacuum requires a bit of dedication but, in the
end it works fine (preventig disc space growth)

note also that VACUUM has nothing to do with REINDEXing and REINDEXing
also frees a considerable amount of disk space in certain cases.

very important: read the docs:

http://www.postgresql.org/docs/8.3/static/maintenance.html

In my case I have autovaccum with scale factors 0 and naptime 600; also
a cron job for vacuum full and reindex everything once a week (during
the night). its working fine on a db with about 2 Giga and average 10000
deletes a day and well above 200000 INSERTs/UPDATEs per day.

cheers
joao

PS: I'm saying all this because I'm just going through this process
myself and I think I'm on the right track. things are starting to work
the way I want them too :)

>
> Thanks! I've read up some websites for this kind of tuning, but often
> the info is for older versions of DBs and some of the tweaks have
> changed since. I am on 8.2.9.
>