Обсуждение: 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
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