Julius Tuskenis <julius(at)nsoft(dot)lt> wrote:
> we connect to localhost on windows machine because we
> use ssh tunel to the postgresql server on gentoo linux.
Latency in the extra hop or from encryption/decryption of the
streams could account for at least some of the difference between
how long it took the server to run the query versus the elapsed time
between when the client started sending it out and when it got the
last of the response.
> The DB is used for web application writen in PHP. As the DB and
> WEB servers are on the same machine we can rule out the network
> issues I guess...
What you can't rule out is the possibility that the web app thread
reading the response doesn't get a time slice for some time after
the database is done.
> The behavior described is observed when there is high server
> usage. Using "server status" tool shows several such queries
> executed suddenly and then after some waiting everything goes back
> to normal.
Can you run `vmstat 1` and see what it shows during an episode?
This could be particularly valuable if you can timestamp the lines
to compare to log entries. You might want to turn on checkpoint
logging.
Again, we don't know anything about the amount of RAM, your
postgresql.conf settings, your disk subsystem (particularly your
RAID adapter and its configuration), etc. With more information we
can give better advice.
-Kevin