Postgres vacuum question (7.2)

[Grant Shipley]

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
our instance.

Also, if anyone is interested, we are looking to hire a postgres /
mysql dba here at Red Hat.  Send me your resume.

--
grant


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.