"Gnanakumar" <gnanam(at)zoniac(dot)com> wrote:
>> When you hit that issue, there is not a continual slowdown --
>> queries which normally run very fast (a small fraction of a
>> second) may periodically all take tens of seconds. Is that the
>> pattern you're seeing?
>
> Yes, you're correct. Queries those normally run fast are becoming
> slow at the time of this slowdown.
But the question is -- while the update application is running is
performance *usually* good with *brief periods* of high latency, or
does it just get bad and stay bad? The *pattern* is the clue as to
whether it is likely to be write saturation.
Here's something I would recommend as a diagnostic step: run `vmstat
1` (or equivalent, based on your OS) to capture about a minute's
worth of activity while things are running well, and also while
things are slow. Pick a few lines that are "typical" of each and
paste them into a post here. (If there is a lot of variation over
the sample, it may be best to attach them to your post in their
entirety. Don't just paste in more than a few lines of vmstat
output, as the wrapping would make it hard to read.)
Also, you should try running queries from this page when things are
slow:
http://wiki.postgresql.org/wiki/Lock_Monitoring
If there is any blocking, that might be interesting.
-Kevin