Re: Backend memory growing too much

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(at)pyrenet(dot)fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

ohp(at)pyrenet(dot)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

ohp(at)pyrenet(dot)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

Hi Andrew
On Tue, 17 Jul 2007, Andrew Dunstan wrote:

> Date: Tue, 17 Jul 2007 11:30:43 -0400
> From: Andrew Dunstan <andrew(at)dunslane(dot)net>
> To: ohp(at)pyrenet(dot)fr
> Cc: pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
> Subject: Re: [HACKERS] Backend memory growing too much
>
>
>
> ohp(at)pyrenet(dot)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(at)pyrenet(dot)fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)