From: Theo Kramer <theo@flame.co.za>
To: pgsql-performance@postgresql.org
Subject: Re: Performance problems with prepared statements
Date: Thu, 11 Oct 2007 12:33:18 +0200
Message-ID: <1192098798.2507.26.camel@localhost.localdomain> (raw)
In-Reply-To: <470DE90E.2060605@archonet.com>
References: <1192027540.2495.3.camel@localhost.localdomain>
<470CE918.4060608@dalibo.com>
<1192044840.2549.21.camel@fedmac>
<470DE90E.2060605@archonet.com>
On Thu, 2007-10-11 at 10:12 +0100, Richard Huxton wrote:
> Theo Kramer wrote:
> >
> > So I suspect that there is something more fundamental here...
>
> OK, so there must be something different between the two scenarios. It
> can only be one of:
> 1. Query
> 2. DB Environment (user, locale, settings)
> 3. Network environment (server/client/network activity etc)
I suspect that it could also be in the way the libpq PQprepare(), and
PQexecPrepared() are handled... as opposed to the way PREPARE and
EXECUTE are handled.
>
> Are you sure you have the parameter types correct in your long-running
> query?
Yes - the problem surfaced during a going live session on an 80 user
system... and we had to roll back to the previous system in a hurry.
This was a part of the application that had missed testing, but I have
had other reports from some of my other systems where this appears to be
a problem but not of the magnitude that this one is.
In any case I have managed to reproduce it in my test environment with
configuration settings the same.
> Try setting log_min_duration_statement=9000 or so to capture
> long-running queries.
Thanks - will give that a try.
>
> Make sure the user and any custom settings are the same. Compare SHOW
> ALL for both ways.
> You've said elsewhere you've ruled out the network environment, so
> there's not point worrying about that further.
It is definitely not a network problem - ie. the postgresql server load
goes way up when this query is run.
--
Regards
Theo
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: theo@flame.co.za
Subject: Re: Performance problems with prepared statements
In-Reply-To: <1192098798.2507.26.camel@localhost.localdomain>
* 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