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@xxxxxxxxxxxxxx
<mailto:hhall1001@xxxxxxxxxxxxxx>> 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
