Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id DF23764FCEA for ; Thu, 7 Aug 2008 07:09:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 26648-01 for ; Thu, 7 Aug 2008 07:09:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.157]) by postgresql.org (Postfix) with ESMTP id 443D364FCF4 for ; Thu, 7 Aug 2008 07:09:17 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so283863fgg.23 for ; Thu, 07 Aug 2008 03:09:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=K4xKO778deC81qkP/tEflwpzCchTYxNFQRKnODeTLY0=; b=KvvYWV4eSYetL1WIZ4lH9Qh02Sx2m6IYBo0r6ZWopVypePcZJ9lTmrJGlowreQJFfW czLA18GqQn8JdxxKIRFmKx3NI5hvNcOwYqO4wzkQi421gGyXmL9MGN4uYx6rSg35qlE0 wC/GRc5YmKLZYsq6wh8cIHkb9Ov92+wBNtJaQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=pd89Rg8UaSRWrYOwWNVee+QpX1BEWlOhsqpZDFHSE+oQR6ZJE/sz9oplhWv4dnksIg NRLZQWaBB435pgqK139wrnaPOPzasYCGjwWjOkJuS+KILAsxY9RKexgPAVwyK2kamd/Q QS+1WS5ewq65v+LUsiyUnRvToSOGf0ERKkx20= Received: by 10.86.72.15 with SMTP id u15mr1584962fga.22.1218103754863; Thu, 07 Aug 2008 03:09:14 -0700 (PDT) Received: by 10.86.71.10 with HTTP; Thu, 7 Aug 2008 03:09:14 -0700 (PDT) Message-ID: Date: Thu, 7 Aug 2008 15:39:14 +0530 From: "C K" To: "H. Hall" Subject: Re: Managing connections Cc: "pgsql-admin@postgresql.org" In-Reply-To: <489AC2D3.6030600@reedyriver.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_18865_32173533.1218103754831" References: <489AC2D3.6030600@reedyriver.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/28 X-Sequence-Number: 29873 ------=_Part_18865_32173533.1218103754831 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks for this information. We are migrating our application from mysql to postgresql and use MS Access/ODBC for front end. When I tested on windows, PG starts it's new instance for each application that uses ODBC to connect. 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 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. ------=_Part_18865_32173533.1218103754831 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Thanks for this information. We are migrating our application from mysql to postgresql and use MS Access/ODBC for front end. When I tested on windows, PG starts it's new instance for each application that uses ODBC to connect. 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> 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.
------=_Part_18865_32173533.1218103754831--