Re: Performance problems with prepared statements - Mailing list pgsql-performance

From Theo Kramer
Subject Re: Performance problems with prepared statements
Date
Msg-id 1192098798.2507.26.camel@localhost.localdomain
Whole thread Raw
In response to Re: Performance problems with prepared statements  (Richard Huxton <dev@archonet.com>)
Responses Re: Performance problems with prepared statements
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Richard Huxton
Date:
Subject: Re: Performance problems with prepared statements
Next
From: henk de wit
Date:
Subject: Huge amount of memory consumed during transaction