2009/8/9 Scott Marlowe <scott.marlowe@xxxxxxxxx>
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.
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?
On Sun, Aug 9, 2009 at 4:06 AM, Kobus Wolvaardt<kobuswolf@xxxxxxxxx> wrote:Is this an app you can fix yourself, or are you stuck with this
> 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.
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.
Are each of these connections quite different from each other or
> 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).
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 inIf another pooling solution won't fix this, then you need more memory
> massive issues. I don't mind slower operation for now, I just need to keep
> it working.
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
