I've been trying to search through the archives, but it hasn't been
successful.
We recently upgraded from pg7.0.2 to 7.3.4 and things were happy. I'm
trying to fine tune things to get it running a bit better and I'm trying
to figure out how vacuum output correlates to tuning parameters.
Here's the msot recent vacuum for the "active" table. It gets a few
hundred updates/inserts a minute constantly throughout the day.
INFO: Pages 27781: Changed 0, Empty 0; Tup 2451648: Vac 0, Keep 0, UnUsed
1003361.
Total CPU 2.18s/0.61u sec elapsed 2.78 sec.
I see unused is quite high. This morning I bumped max_fsm_pages to 500000.
If I'm thinking right you want unused and max_fsm to be closish, right?
(Yesterday it was down around.. oh.. 600k?)
I'm thinking vacuum full's may be in order. Which stinks because I was
hoping to do away with the db essentially down for 10 minutes (includes
all the db's on that machine) while it vacuum'd.
The upside is: it is performing great. During the vacuum analyze I do get
a few multi-second pauses while something occurs. I figured it was a
checkpoint, so I bumped checkpoint_timeout to 30 seconds and wal_buffers
to 128. (I'm just guessing on wal_buffers).
Machine is weenucks 2.2.17 on a dual p3 800, 2gb ram, 18gb drive (mirrored).
If you guys need other info (shared_buffers, etc) I'll be happy to funish
them. but the issue isn't query slowness.. just want to get this thing
oiled).
thanks
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/