Merlin Moncure wrote:
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.
top(1) has a batch mode (-b) that's useful for sending results to a file.
Craig
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
