From: dan@sidhe.org
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Trying to track down weird query stalls
Date: Mon, 30 Mar 2009 15:35:10 -0400 (EDT)
Message-ID: <63838.199.172.169.7.1238441710.squirrel@localhost> (raw)
In-Reply-To: <603c8f070903301220i26469be1mcbb6ab758fe78bb8@mail.gmail.com>
References: <58166.199.172.169.15.1238435452.squirrel@localhost>
<603c8f070903301131p8086067ob552d09e8b21c82@mail.gmail.com>
<56921.199.172.169.36.1238438533.squirrel@localhost>
<603c8f070903301220i26469be1mcbb6ab758fe78bb8@mail.gmail.com>
> On Mon, Mar 30, 2009 at 2:42 PM, <dan@sidhe.org> wrote:
>>> On Mon, Mar 30, 2009 at 1:50 PM, <dan@sidhe.org> wrote:
>> I'm not executing any of the EXPLAINs by hand, because I didn't want to
>> have to worry about typos or filling in temp tables with test data.
>> Inside
>> the app the SQL for the problematic query's stored in a variable -- when
>> the task runs with debugging enabled it first executes the query with
>> EXPLAIN ANALYZE prepended and dumps the output, then it executes the
>> query
>> itself. It's possible something's going wrong in that, but the code's
>> pretty simple.
>>
>> Arguably in this case the actual query should run faster than the
>> EXPLAIN
>> ANALYZE version, since the cache is hot. (Though that'd only likely
>> shave
>> a few dozen ms off the runtime)
>
> Well... yeah. Also EXPLAIN ANALYZE has a non-trivial amount of
> overhead, so that is quite bizarre. I have to suspect there is some
> subtle difference between the way the EXPLAIN ANALYZE is done and the
> way the actual query is done... like maybe one uses parameter
> substitution and the other doesn't or, well, I don't know. But I
> don't see how turning on debugging (which is essentially what EXPLAIN
> ANALYZE is) can prevent the query from being slow.
Hence the query to the list. *Something* is going on, and beats me what.
I'm assuming I'm triggering some bug in the postgres back end, or there's
some completely bizarre edge case that this tickles. (The massive
kread/kwrite activity that truss showed me when I checked seemed rather
unusual, to say the least)
EXPLAIN ANALYZE is my normal means of diagnosing performance problems, but
that isn't helping as it shows perfectly sane results. That leaves
abnormal means, and outside of trussing the back end or attaching with dbx
to get a stack trace I just don't have any of those. I'm not even sure
what I should be looking for when I do get a stack trace.
-Dan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dan@sidhe.org, robertmhaas@gmail.com
Subject: Re: Trying to track down weird query stalls
In-Reply-To: <63838.199.172.169.7.1238441710.squirrel@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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