On Fri, Nov 4, 2011 at 3:26 PM, Shaun Thomas <sthomas@xxxxxxxxx> wrote: > On 11/04/2011 12:22 PM, Claudio Freire wrote: > >> bgwriter_delay = 1000ms >> wal_writer_delay=2000ms >> commit_delay=10000 > > !? >snip > "Setting commit_delay can only help when there are many concurrently > committing transactions, and it is difficult to tune it to a value that > actually helps rather than hurt throughput." > > Meaning it may halt all of your commits up to *ten seconds* if it doesn't > think there was enough activity to warrant a write. Ouch. I think you're misinterpreting the value. It's in microseconds, that's 10 *milli*seconds > Your bgwriter_delay and wal_writer_delay settings are equally odd. You've > made the background writer so passive that when it does finally run, it's > going to have a ton of work to do, causing giant write spikes. I'm not sure > whether or not this also causes compounding problems with locks and backend > write delays. I don't think 1 second can be such a big difference for the bgwriter, but I might be wrong. The wal_writer makes me doubt, though. If logged activity was higher than 8MB/s, then that setting would block it all. I guess I really should lower it. > The number you > really care about is "sync=0.055 s" which is how much time the controller > spent syncing that data to disk. > > If you're having real problems writing or lock delays due to IO stalls, > you'll see that sync parameter shoot way up. This can also be elevated in > certain NVRAM-based solutions. Once you start seeing whole seconds, or > minutes, it might actually matter. Nice to know, I thought so, now I know so. :-) So... I'm leaning towards lowering wal_writer_delay and see how it goes. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
