Re: Postgres memory question

2009/8/9 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>

> On Sun, Aug 9, 2009 at 4:06 AM, Kobus Wolvaardt<kobuswolf(at)gmail(dot)com>
> wrote:
> > Hi,
> >
> > We have software deployed on our network that need postgres, we have
> server
> > that hosts the server and all worked fine until we crossed about 200
> users.
> > The application is written so that it makes a connection right at the
> start
> > and keeps it alive for the duration of the app. The app is written in
> > Delphi. The postgres server runs on a windows 2008 server with quad core
> cpu
> > and 4 GB of ram.
>
> Is this an app you can fix yourself, or are you stuck with this
> mis-step in design?

It is our app but it is not going to be easy to change it. It will get
changed, but the time frame is a bit long and we need a solution to hold us
over.

>
>
> > The problem after +-200 connections is that the server runs out of
> memory,
> > but most of these connections are idle... it only gets used every 20
> minutes
> > to capture a transaction.
> >
> > It looks like every idle connection uses about 10MB of ram which sees
> high,
> > but I cannot find a config option to limit it.
> >
> > I tried pgbouncer to do connection pooling, but for each connection to
> > pgbouncer one connection is made to the server which results in exactly
> the
> > same amount of connection. If I run it in transaction pooling mode it
> works
> > for simple queries, but something goes lost says the programmer (views
> that
> > were setup or something).
>
> Are each of these connections quite different from each other or
> something? I'm not that familiar with pgbouncer so I don't know if
> this behaviour is normal. Can you get by with pgpool for this? Does
> it work any better?

When using pgbouncer (it is supposed to be a light weight connection pooler
similar to pgpool) it makes a connection for each connection. All our
connections are to the same DB with the same username. So really pooling
should work perfectly. Should views and temporary tables and such work over
poolers? Do you need to indicate the end of a session for the pooler to
reuse the connection? If I make a connection at the start of an app and just
leave it right till the end and then use it, will that get pooled?

>
>
> > Any help or pointers would be nice, either on how to make usage less, or
> on
> > how to get pooling to work.
> >
> > P.S. We are growing the users by another 20% soon and the will result in
> > massive issues. I don't mind slower operation for now, I just need to
> keep
> > it working.
>
> If another pooling solution won't fix this, then you need more memory
> and a bigger server. pg on windows is 32 bit so you might have some
> problems running it well on a larger windows machine, if that's the
> case, then it would likely help if you could run this on 64 bit linux
> with 8+Gigs of ram. This solution would allow you to grow to several
> hundred more connections before you'd have issues. Also, performance
> might be better on linux with this many connections, but I have not
> empirical evidence to support that belief.
>

Jip, I think that might be the way forward, and for all our other clients we
are already doing this, but the largest one, the one giving us the problems
unfortuanetly have less flexible IT people. Still if it is the only solution
they will have to budge,

Thanks,
Kobus