Postgres Pro
Downloads
Home   >   mailing lists

Thread: Backend memory growing too much

Backend memory growing too much

From
ohp@pyrenet.fr
Date:
Hi everyone,

I've been using sqlgrey for some time now and I'm very surprised by the
memory taken by the backends to which sqlgrey is connected.

look at process 4111 and 28108 . They roughly take twice the space the
other backend take.

Could there be a memory leak?

I don't know much about DBI/DBD but I know sqlgrey uses a lot of
prepare/prepare_cached statements.

What could cause?

shared_buffer is 400MB here.

TIA

-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

Re: Backend memory growing too much

From
Tom Lane
Date:
ohp@pyrenet.fr writes:
> I don't know much about DBI/DBD but I know sqlgrey uses a lot of
> prepare/prepare_cached statements.

Well, those aren't exactly free.

Possibly you could learn something about it by attaching to one of
these backends with gdb and executing
call MemoryContextStats(TopMemoryContext)

This will dump a memory map to stderr.
        regards, tom lane

Re: Backend memory growing too much

From
Andrew Dunstan
Date:

ohp@pyrenet.fr wrote:
>
> I don't know much about DBI/DBD but I know sqlgrey uses a lot of
> prepare/prepare_cached statements.
>
>   

You can inhibit DBD::Pg from using server side prepares  if you need to, 
by executing:
 $dbh->{pg_server_prepare} = 0;

(as documented in the excellent DBD::Pg docs).

cheers

andrew

Re: Backend memory growing too much

From
ohp@pyrenet.fr
Date:
Hi Andrew
On Tue, 17 Jul 2007, Andrew Dunstan wrote:

> Date: Tue, 17 Jul 2007 11:30:43 -0400
> From: Andrew Dunstan <andrew@dunslane.net>
> To: ohp@pyrenet.fr
> Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] Backend memory growing too much
>
>
>
> ohp@pyrenet.fr wrote:
> >
> > I don't know much about DBI/DBD but I know sqlgrey uses a lot of
> > prepare/prepare_cached statements.
> >
> >
>
> You can inhibit DBD::Pg from using server side prepares  if you need to,
> by executing:
>
>   $dbh->{pg_server_prepare} = 0;
>
> (as documented in the excellent DBD::Pg docs).
>
> cheers
>
> andrew
>
Thanks! That made the trick!
I assume this is because sqlgrey prepares statements (always the same) and
neither finish them.

Best regards
-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)