Message-ID: <489ACF5B.8060200@reedyriver.com>
Date: Thu, 07 Aug 2008 06:32:59 -0400
From: "H. Hall" <hhall1001@reedyriver.com>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: C K <shreeseva.it@gmail.com>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Managing connections
References: <d3561eb00808061004w41371ee3h3d9a87be9ed9cd20@mail.gmail.com>	
	<489AC2D3.6030600@reedyriver.com>
	<d3561eb00808070309ra5419bcy519274e96fc2f7e7@mail.gmail.com>
In-Reply-To: <d3561eb00808070309ra5419bcy519274e96fc2f7e7@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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