Re: Managing connections

C K wrote:
> Thanks for this information. We are migrating our application from
> mysql to postgresql and use MS Access/ODBC for front end.
Mystery explained. Access is notorious for opening a connection for
everything. Visual Basic used to do the same thing. I think the dot net
stuff now uses a connection pool.
> When I tested on windows, PG starts it's new instance for each
> application that uses ODBC to connect.
Remember it's the app that calls for the connection through ODBC.
Postgres simply responds to the call.
> Even we open multiple tables from a single file, it use only one
> instance, but when another application with same username even from
> same machine is started, another PG instance is started. Now we are
> testing for other applications and impact on memory.
>
> Regards,
> CPK
>
>
> On Thu, Aug 7, 2008 at 3:09 PM, H. Hall <hhall1001@reedyriver.com
> <mailto:hhall1001@reedyriver.com>> wrote:
>
>     C K wrote:
>
>         Dear Friends,
>         I want to know if I open a table to view the data a new
>         connection is opened. If I open another table/execute a query
>         does a another new connection is opened? As per manual, I know
>         that for a database cluster and the same port a postgresql
>         instance runs. But if we run multiple SQL statements from same
>         client machine with same username, does postgresql creates a
>         new instance?
>
>     How can connections to the db are handled is determined by the
>     application program that accesses the database. Some application
>     programs create a connection for each user and re-use it each time
>     the user accesses the database. The app is also responsible for
>     closing the connection when the user signs off.
>
>     Other apps create a pool of connections and lease a connection on
>     demand to each operation that accesses the db. When the operation
>     is complete, the connection is returned to the pool.  This is the
>     most efficient way to use connections and I would recommend this
>     approach for just about any program.
>
>     We created our own connection pool program because we wanted a
>     connection pool that we could use to access any db and because we
>     considered it to be a key technology.  It's a good thing that we
>     did because we started out using MS SQLServer for our SaaS
>     applications and migrated to Postgres.  Migration was a breeze.
>
>     Postgresql offers a connection pool which I have not used but
>     appears to be highly regarded. You can find info about it here:
>     http://pgpool.projects.postgresql.org/
>
>     Some apps are not well designed and don't close connections or
>     un-lease connections when they should. I call this phenomena
>     "leaking connections". You can detect this by having everyone sign
>     off the application and see how many connections are still open.
>
>     Cheers,
>     HH
>
>         If yes, how to manage memory? (As per my observations,
>         normally each postgresql instance takes 5-10 MB. So if we have
>         to manage simultenious 1000 connections, it will require
>         1000*5MB=5GB + system memory requirements (approx. 1 GB) =
>         total 6 GB (min.) Does this is correct?)
>         Please give the details. I am using Win XP Pro with PostgreSQL
>         8.3.
>         Thanks
>         CPK
>
>         --
>         Keep your Environment clean and green.
>
>         --------------------------------
>
>
>
>
>
>     --
>     H. Hall
>     ReedyRiver Group LLC
>     http://www.reedyriver.com
>
>
>
>
> --
> Keep your Environment clean and green.
>
> --------------------------------
>
>
>


--
H. Hall
ReedyRiver Group LLC
http://www.reedyriver.com