Postgres vacuum question (7.2)
gshipley at gmail.com
Thu Mar 8 07:02:12 MST 2007
It turns out, one of the core postgres developers actually works here.
We gave him a call and he spent the afternoon helping us optimize our
postgres instance. The first we did was an emergency upgrade to 8.x
series in production. I have posted his summary in this email if
anyone is interested what a core pgsql developer suggested for tuning
Also, if anyone is interested, we are looking to hire a postgres /
mysql dba here at Red Hat. Send me your resume.
These are the minimum things you want to tweak for performance in PG 8.1:
#max_connections = 100
Might be OK, but whatever you need it to be.
#shared_buffers = 1000 # min 16 or max_connections*2, 8KB each
Probably 100000 is a good number for starters. I am not sure whether
8.1 supports upwards of 2Gb buffers (I know 8.2 does).
#maintenance_work_mem = 16384 # min 1024, size in KB
I'd suggest boosting this some, maybe to 100000 or so.
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
You really need this large enough to cover your DB --- as I said on IRC,
500000 would be all right for the current size of prod DB.
vacuum_cost_delay = 10 # 0-1000 milliseconds
You want this set to reduce the impact of background vacuums. 10 is
probably a good starting point.
#wal_buffers = 8 # min 4, 8KB each
Might be worth raising this to 30 or 50 or so, or then again it might
make no difference.
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
Definitely needs to be way higher for intensive write loads. Try 100.
#autovacuum = off # enable autovacuum subprocess?
Turn this on.
It'd still be a good idea to have a cron-driven vacuum as a backstop,
but likely once a day would be enough.
More information about the PLUG