Shridhar Daithankar <[EMAIL PROTECTED]> writes: > Rosser Schwarz wrote: >> shared_buffers = 4096 >> sort_mem = 32768 >> vacuum_mem = 32768 >> wal_buffers = 16384 >> checkpoint_segments = 64 >> checkpoint_timeout = 1800 >> checkpoint_warning = 30 >> commit_delay = 50000 >> effective_cache_size = 131072
> First of all, your shared buffers are low. 4096 is 64MB with 16K block > size. I would say at least push them to 150-200MB. Check. Much more than that isn't necessarily better though. shared_buffers = 10000 is frequently mentioned as a "sweet spot". > Secondly your sort mem is too high. Note that it is per sort per query. You > could build a massive swap storm with such a setting. Agreed, but I doubt that has anything to do with the immediate problem, since he's not testing parallel queries. > Similarly pull down vacuum and WAL buffers to around 512-1024 each. The vacuum_mem setting here is 32Mb, which seems okay to me, if not on the low side. Again though it's not his immediate problem. I agree that the wal_buffers setting is outlandishly large; I can't see any plausible reason for it to be more than a few dozen. I don't know whether oversized wal_buffers can directly cause any performance issues, but it's certainly not a well-tested scenario. The other setting I was going to comment on is checkpoint_warning; it seems mighty low in comparison to checkpoint_timeout. If you are targeting a checkpoint every half hour, I'd think you'd want the system to complain about checkpoints spaced more closely than several minutes. But with the possible exception of wal_buffers, I can't see anything in these settings that explains the originally complained-of performance problem. I'm still wondering about foreign key checks. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])