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(at)reedyriver(dot)com
> <mailto:hhall1001(at)reedyriver(dot)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