Message-ID: <4D23CEB5.2090406@2ndquadrant.com>
Date: Tue, 04 Jan 2011 20:51:49 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: fernando.mertins@gmail.com, pgsql-performance@postgresql.org
Subject: Re: Same stament sometime fast, something slow
References: <4D230EBD0200002500038FBB@gw.wicourts.gov>
In-Reply-To: <4D230EBD0200002500038FBB@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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