http://www.powerpostgresql.com/Downloads/annotated_conf_80.html is a good intro to the various parameters you might set, with some valuable hints on the effective range you should be considering. I'd suggest you use that to identify the most likely things to increase, then read the manuals at http://www.postgresql.org/docs/8.2/interactive/runtime-config.html for more detail on what you're actually adjusting. To get you started, consider increasing effective_cache_size, checkpoint_segments, and work_mem; those are three whose defaults are very low for your
I'll play with these - I've definitely seen the (for me) confusing use of seqscan rather than index scan that the annotated page says is caused by too little effective_cache_size. That first link is really great; I can't believe I've never seen it before.
One big RAID 5 volume is probably the worst setup available for what you're doing. Luke already gave you a suggestion for testing write speed; you should run that test, but I wouldn't expect happy numbers there. You
I've run dstat with a really busy postgres and seen 94 MB read and write simultaneously for a few seconds. I think our RAID cards have 16MB of RAM, so unless it was really freakish, I probably wasn't seeing all cache access. I'll try the some tests with dd tomorrow when I get to work.
might be able to get by with the main database running like that, but think about what you'd need to do to add more disks (or reorganize the ones you have) so that you could dedicate a pair to a RAID-1 volume for holding the WAL. If you're limited by write performance, I think you'd find adding a separate WAL drive set a dramatically more productive upgrade than trying to split the app to another machine. Try it on your home machine first; that's a cheap upgrade, to add another SATA drive to there, and you should see a marked improvement (especially once you get the server parameters set to more appropriate values).
Is the WAL at the same location as the xlog (transaction log?)? The checkpoint_segments doc says increasing that value is really only useful if the xlog is separate from the data, so do I put both WAL and xlog on the separate drive, or is that automatic (or redundant; I don't know what I'm talking about...)?
I'd also suggest that you'd probably be able to get more help from people here if you posted a snippet of output from vmstat and iostat -x with a low interval (say 5 seconds) during a period where the machine was busy; that's helpful for figuring out where the bottleneck on your machine really is.
I'll try to stress a machine and get some real stats soon.
Do you have the exact text of the error? I suspect you're falling victim to the default parameters being far too low here as well, but without the error it's hard to know exactly which.
Well, I tried to repeat it on my home machine with 20 million rows, and it worked fine in about two minutes. I'll have to see what's going on on that other system... Thanks for the help!
