From: Craig Ringer <craig@postnewspapers.com.au>
To: Brian Cox <brian.cox@ca.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: query slow; strace output worrisome
Date: Wed, 07 Apr 2010 10:32:52 +0800
Message-ID: <4BBBEED4.1000807@postnewspapers.com.au> (raw)
In-Reply-To: <4BBB604F.6060005@ca.com>
References: <4BBB604F.6060005@ca.com>
On 7/04/2010 12:24 AM, Brian Cox wrote:
> On 04/06/2010 01:18 AM, Craig Ringer [craig@postnewspapers.com.au] wrote:
>> I'm wondering if the issue is with strace rather than Pg. That is to
>> say, that strace is trying to print:
> Thanks, Craig: I do think that this is a strace issue.
>
>> As for what Pg is doing: creat() returns -1 on error and a file
>> descriptor otherwise, so the return value appears to indicate success.
>> I'm kind of wondering what the Pg backend is actually _doing_ though -
>> if it was using sort temp files you'd expect
>> open()/write()/read()/close() not just creat() calls.
> My quesiton exactly: what is the backend doing calling creat() over and
> over again? Note that this query does complete -- in 30-40 mins.
I'd assume it was tempfile creation, but for the fact that there's
nothing but creat() calls.
However, we can't trust strace. There may be more going on that is being
hidden from strace via limitations on the ptrace syscall imposed by
SELinux / AppArmor / whatever.
I suggest turning on the logging options in Pg that report use of
tempfiles and disk-spilled sorts, then have a look and see if Pg is in
fact using on-disk temp files for sorts or materialized data sets.
If it is, you might find that increasing work_mem helps your query out.
--
Craig Ringer
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: craig@postnewspapers.com.au, brian.cox@ca.com
Subject: Re: query slow; strace output worrisome
In-Reply-To: <4BBBEED4.1000807@postnewspapers.com.au>
* 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