Connection Pooling....an interesting question!! (was..Connection Pooling...(Repost)...please do help...) - Mailing list pgsql-interfaces

My quest for pooling odbc connections....some interesting questions
have occured to me.....at least they seem interesting to me ;-))

Problem:

Each machine keeping one connection open while application is
executing.....may or may not use odbc driver connection pooling...it
will just keep connection open for repeat queries....presently we are
getting the application to keep the connection open.

Now, supposing each machine keeps one connection open, how many
connections can a PG machine take? So will need connection cost
figures:

1....how much memory does each connection consume?
2....what is query cost? how does one go about estimating cost of a
query? CPU? Memory? Other variables?

In our particular case, we are using a Pentium 550 server with EIDE
HDD Linux 6.2 and 128 MB RAM. We are running Apache & Mail
Applications with medium to low load on it, in addition to two PG
daemons, one having Production data and one for development testing.

Presently, we have about 20 workstations opening one connection to the
PG daemons and they machines will increase to around 200 in 6 months
or so.

So how do we go about calculating the overheads for this scenario?
What would be the different metrics we would need to evaluate in order
to calculate PG load costs and selecting machine, ram & other issues?

Second, I thought about writing a connection manager on the server as
a middleware between odbc & PG and I have some interesting questions
there too:

Scenario one:

ODBC opens connections on this connection manager running on the
server and it in turn opens connections on PG daemon.

1...Should Connection Manager be run as an independent daemon/broker
or should PG be modified to serve that function?
2...How can the connection manager handle user level security, if
implemented in the DB?
3...If a single user is making all the connections to the
server(various programs running on the server itself), can such a
connection manager be possible?

Would you please make comments on feasibility/non-feasibility of
above?

Any other way this sort of thing can be achieved? I am thinking of
buying a C book?

With best regards.
Sanjay.

On Fri, 15 Dec 2000 15:04:26 -0500, in
tci.lists.rdbms.postgresql.interfaces you wrote:

>I haven't found PG to have much connection overhead, why would open/closing
>a connection-per-query require server side connection pooling?  You might
>try having your application acquire a connection on-demand and then having a
>timeout mechanism that discards the connection after a certain amount of
>idle time.  That if you have to fire off hundreds of small queries you don't
>encounter connection overhead.  If the machine is idle, it disconnects and
>the server doesn't get loaded down with tons of idle backends.
>
>This may sound overly complicated, but you might move the database
>operations out of the client-tier and into a application-tier.  DCOM would
>allow you to query from the COM layer and broker objects containing the
>results back to the client.  This way the middle tier could maintain it's
>own pool of connections iternally, plus you then get to take advantage of
>the other benefits of a distributed system.
>
>As a side note, I have a little research project going right now.  It is an
>HTTP XML Server that acts as a database liason by accepting HTTP POST
>requests (USER, PASSWORD, SQL, etc) and returning the results as XML.  It is
>Java based, but you could do it with any language you want.  Coming from a
>Win32 client, one could use IE 5's XML parser to process the results.  You
>could implement something very similar in a short amount of time.  (This is
>partially up-and-running with less than a week's work)  This is a perfect
>place to implement connection pooling (which happens to be what I am
>currently adding to it).  I would be glad to provide source once I get it
>stabilzed, although I don't know if it will ever be mature enough for
>production work...I just wanted to learn XML.  :)
>
>Anyway, I just wanted to throw some ideas out there...
>Joel
>
>-----Original Message-----
>From: sk@pobox.com [mailto:sk@pobox.com]
>Sent: Friday, December 15, 2000 11:32 AM
>To: pgsql-interfaces@postgresql.org
>Subject: Re: [INTERFACES] Connection Pooling...(Repost)...please do
>help..
>
>
>Thanks Joel,
>
>Yes there is. But it only keeps your unused connections on for a given
>amount of time, so as to avoid opening new connections when you open a
>connection again within the stipulated time.
>
>In our case, we keep the connection open till the application has any
>forms open. Problem in this case is that multiple users from different
>workstations are opening connections on the PG server on RH 6.2.
>
>What we want to do is start closing connections from applications
>after query and somehow pool the connections on the Linux server.
>
>Would like comments on this strategy and a how-to on this....help?
>anyone?
>
>With best regards.
>
>Sanjay.
>
>On Wed, 13 Dec 2000 15:19:53 -0500, in
>tci.lists.rdbms.postgresql.interfaces you wrote:
>
>>I know that the ODBC "engine" on Win32-based systems is capable of
>>performing connection pooling, but I am not sure if the driver has to
>>support it or not...anyone?  Check the "Connection Pooling" tab on the ODBC
>>admin window in control panel.  If you don't have one, you might try
>>upgrading MDAC.
>>
>>Joel
>>
>>-----Original Message-----
>>From: Sanjay Arora [mailto:sk@pobox.com]
>>Sent: Wednesday, December 13, 2000 7:19 PM
>>To: pgsql-interfaces@postgresql.org
>>Subject: [INTERFACES] Connection Pooling...(Repost)...please do help...
>>
>>
>>I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
>>Application accessing the DB through postgrSQL ODBC driver v. 6.50.
>>
>>I want to pool my connections on the postgreSQL server. Can some people
>>give me some pointers? Some Web Resources for studying this subject? My
>>experience on connection pooling is limited to MTS in MS environs.
>>
>>With best regards.
>>
>>Sanjay.
>>
>