Re: vacuum locking - Mailing list pgsql-performance

Shridhar Daithankar writes:
> You should try 7.4 beta and pg_autovacuum which is a contrib module
> in CVS tip.

It's on our todo list. :)

How does pg_autovacuum differ from vacuumdb?  I mean it seems to call
the vacuum operation underneath just as vacuumdb does.  I obviously
didn't follow the logic as to how it gets there. :-)

> Make sure that you have FSM properly tuned. Bump it from defaults to
> suit your needs. I hope you have gone thr. this page for general
> purpose setting.

I didn't start vacuuming regularly until recently, so I didn't see
this problem.

> Assuming those were incremental figures, largest you have is ~8000
> tuples per 15 minutes and 26 pages. I think with proper FSM/shared
> buffers/effective cache and a pg_autovacuum with 1 min. polling
> interval, you could end up in lot better shape.

Here are the numbers that are different.  I'm using 7.3:

shared_buffers = 8000
sort_mem = 8000
vacuum_mem = 64000
effective_cache_size = 40000

free says:
             total       used       free     shared    buffers     cached
Mem:       1030676    1005500      25176          0      85020     382280
-/+ buffers/cache:     538200     492476
Swap:      2096472     272820    1823652

It seems effective_cache_size is about right.

vacuum_mem might be slowing down the system?  But if I reduce it,
won't vacuuming get slower?

max_fsm_relations is probably too low (the default in my conf file
says 100, probably needs to be 1000).  Not sure how this affects disk
usage.

Here's the summary for the two active tables during a vacuum interval
with high activity.  The other tables don't get much activity, and are
much smaller.  As you see the 261 + 65 adds up to the bulk of the 5
minutes it takes to vacuum.

INFO:  Removed 8368 tuples in 427 pages.
        CPU 0.06s/0.04u sec elapsed 1.54 sec.
INFO:  Pages 24675: Changed 195, Empty 0; Tup 1031519: Vac 8368, Keep 254, UnUsed 1739.
        Total CPU 2.92s/2.58u sec elapsed 65.35 sec.

INFO:  Removed 232 tuples in 108 pages.
        CPU 0.01s/0.02u sec elapsed 0.27 sec.
INFO:  Pages 74836: Changed 157, Empty 0; Tup 4716475: Vac 232, Keep 11, UnUsed
641.
        Total CPU 10.19s/6.03u sec elapsed 261.44 sec.

How would vacuuming every minute finish in time?  It isn't changing
much in the second table, but it's taking 261 seconds to wade through
5m rows.

Assuming I vacuum every 15 minutes, it would seem like max_fsm_pages
should be 1000, because that's about what was reclaimed.  The default
is 10000.  Do I need to change this?

Sorry to be so dense, but I just don't know the right values are.

Thanks muchly for the advice,
Rob