pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSame stament sometime fast, something slow
4+ messages / 4 participants
[nested] [flat]
* Same stament sometime fast, something slow
@ 2011-01-04 18:03 Fernando Mertins <fernando.mertins@gmail.com>
2011-01-05 01:24 ` Re: Same stament sometime fast, something slow Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Fernando Mertins @ 2011-01-04 18:03 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Same stament sometime fast, something slow
2011-01-04 18:03 Same stament sometime fast, something slow Fernando Mertins <fernando.mertins@gmail.com>
@ 2011-01-05 01:24 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Craig Ringer @ 2011-01-05 01:24 UTC (permalink / raw)
To: Fernando Mertins <fernando.mertins@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Same stament sometime fast, something slow
@ 2011-01-04 18:12 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-01-05 01:51 ` Re: Same stament sometime fast, something slow Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2011-01-04 18:12 UTC (permalink / raw)
To: fernando.mertins@gmail.com; pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Same stament sometime fast, something slow
2011-01-04 18:12 Re: Same stament sometime fast, something slow Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-01-05 01:51 ` Greg Smith <greg@2ndquadrant.com>
0 siblings, 0 replies; 4+ messages in thread
From: Greg Smith @ 2011-01-05 01:51 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: fernando.mertins@gmail.com; pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-01-05 01:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-04 18:03 Same stament sometime fast, something slow Fernando Mertins <fernando.mertins@gmail.com>
2011-01-05 01:24 ` Craig Ringer <craig@postnewspapers.com.au>
2011-01-04 18:12 Re: Same stament sometime fast, something slow Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-01-05 01:51 ` Greg Smith <greg@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox