On Tue, Oct 6, 2009 at 10:59 AM, Michal Vitecek <fuf@xxxxxxxx> wrote: > Merlin Moncure wrote: >>On Mon, Oct 5, 2009 at 5:17 AM, Michal Vitecek <fuf@xxxxxxxx> wrote: >> >>> Could the problem be the HW RAID card? There's ServerRAID 8k with 256MB >>> with write-back enabled. Could it be that its internal cache becomes >>> full and all disk I/O operations are delayed until it writes all >>> changes to hard drives? >> >>that's possible...the red flag is going to be iowait. if your server >>can't keep up with the sync demands for example, you will eventually >>outrun the write cache and you can start to see slow queries. With >>your server though it would take in the hundreds of (write) >>transactions per second to do that minimum. > > The problem is that the server is not loaded in any way. The iowait is > 0.62%, there's only 72 sectors written/s, but the maximum await that I > saw was 28ms (!). Any attempts to reduce the time (I/O schedulers, > disabling bgwriter, increasing number of checkpoints, decreasing shared > buffers, disabling read cache on the card etc.) didn't help. After some > 3-5m there occurs a COMMIT which takes 100-10000x longer time than > usual. Setting fsynch to off Temporarily improved the COMMIT times > considerably but I fear to have this option off all the time. > > Is anybody else using the same RAID card? I suspect the problem lies > somewhere between the aacraid module and the card. The aacraid module > ignores setting of the 'cache' parameter to 3 -- this should completely > disable the SYNCHRONIZE_CACHE command. I think you're right. One thing you can do is leave fsync on but disable synchronous_commit. This is compromise between fsync on/off (data consistent following crash, but you may lose some transactions). We need to know what iowait is at the precise moment you get the long commit time. Throw a top, give it short update interval (like .25 seconds), and watch. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
