Thread: Same stament sometime fast, something slow

Hi there, this is my first post.

I have a PostgreSQL 8.3.6, compiled by Visual C++ build 1400 running on Windows with virtual machine, 2 GB ram, and configured the postgresql.conf file to log statements duration >= 500 ms.

And I have this query/log entry:
2011-01-03 23:06:29 BRT LOG:  duration: 2843.000 ms  statement: SELECT DESCRICAO FROM CURSO WHERE CODCURSO = 2

My question is, this same query executes many times a day and many times fast/normal, but why in some cases its run slowly? Especialy because the "CODCURSO" column is PK and this table has only 3 registers (tiny table).

Thank you in advance!
Fernando

Fernando Mertins  wrote:

> I have a PostgreSQL 8.3.6

You should consider upgrading to the latest minor release:

http://www.postgresql.org/support/versioning

http://www.postgresql.org/docs/8.3/static/release.html

> My question is, this same query executes many times a day and many
> times fast/normal, but why in some cases its run slowly? Especialy
> because the "CODCURSO" column is PK and this table has only 3
> registers (tiny table).

Two common causes for this are blocking and overloading the I/O
system at checkpoint.  You might want to turn on logging of
checkpoints to see if this happens only during checkpoints.  See this
page for techniques to look at blocking:

http://wiki.postgresql.org/wiki/Lock_Monitoring

If neither of these helps, please review this page and post again
with more details:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

-Kevin

On 01/05/2011 05:03 AM, Fernando Mertins wrote:
> Hi there, this is my first post.
>
> I have a PostgreSQL 8.3.6, compiled by Visual C++ build 1400 running on
> Windows with virtual machine
           ^^^^^^^^^^^^^^^^^^^^

What kind of VM host? where? what else is on the same host?

Your most likely culprit is I/O contention from other guests on the same
host, possibly combined with I/O queuing policies on the host that
favour throughput over request latency.

Checkpoints might also be a factor.

--
Craig Ringer

Kevin Grittner wrote:
> Two common causes for this are blocking and overloading the I/O
> system at checkpoint.  You might want to turn on logging of
> checkpoints to see if this happens only during checkpoints.  See this
> page for techniques to look at blocking:
>
> http://wiki.postgresql.org/wiki/Lock_Monitoring
>

I just updated this to mention use of log_lock_waits to help here.
Looking for patterns in log_min_duration_statement, log_checkpoints, and
log_lock_waits entries, seeing which tend to happen at the same time, is
the usual helpful trio to investigate when having intermittent slow
queries.  Of course, with Windows running on a VM, there's a hundred
other things that could be causing this completely unrelated to the
database.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books