From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sören Meyer-Eppler <soerenme@google.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 9.0.4 blocking in lseek?
Date: Thu, 27 Oct 2011 20:32:58 -0400
Message-ID: <9398.1319761978@sss.pgh.pa.us> (raw)
In-Reply-To: <4EA82B7C.1040008@google.com>
References: <4EA82B7C.1040008@google.com>
=?ISO-8859-1?Q?S=F6ren_Meyer-Eppler?= <soerenme@google.com> writes:
> A fairly complex insert query on an empty destination table will run for
> an indefinite amount of time (we waited literally days for the query to
> complete). This does not happen every time we run the query but often.
> Now ordinarily I'd assume we did something wrong with our indices or
> query, but the execution plan looks sane and, more tellingly, we have:
> - no CPU load
> - no network traffic
> - no disk I/O
> - no other load on the server except this single query
> and strace displaying a seemingly endless list of lseek calls.
> So my assumption is that we are not running into bad Big-O() runtime
> behavior but rather into some locking problem.
If it were blocked on a lock, it wouldn't be doing lseeks().
The most obvious explanation for a backend that's doing repeated lseeks
and nothing else is that it's repeatedly doing seqscans on a table
that's fully cached in shared buffers. I'd wonder about queries
embedded in often-executed plpgsql functions, for instance. Can you
identify which table the lseeks are issued against?
(Now, having said that, I don't see how that type of theory explains no
CPU load. But you're really going to need to provide more info before
anyone can explain it, and finding out what the lseeks are on would be
one good step.)
regards, tom lane
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: tgl@sss.pgh.pa.us, soerenme@google.com
Subject: Re: PostgreSQL 9.0.4 blocking in lseek?
In-Reply-To: <9398.1319761978@sss.pgh.pa.us>
* 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