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. /* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
