Re: Connection Pooling....an interesting question!! (was..Connection Pooling...(Repost)...please do help...)

As (I believe) Joel mentioned, you should use a distributed architecture.
Clients shouldn't directly access your db server. I believe it is
"acceptable" if you are only looking at a small app that 10 people are going
to use, but 200 hundred clients is a lot.

You should have postgres on one tier, your clients on one, and devise a
middle tier that acts as a relay between your clients and postgres. That
way the 200 connections are not handled by postgres. Postgres will only
need to handle the 10 or so you pool with the middle tier.

You said you were familiar with MTS pooling... that is basically your
answer. Put your business logic on MTS to talk to and pool with postgres
and have your clients access the MTS.

Not to mention it is easier to manage your code on one server than on 200
workstations.

As for the other reply involving the http server as a liason for a db and
spitting out XML... that would be a very nice add-on to postgres. I have
heard several people inquire if postgres is going to support XML (much like
SQL Server 2000 does now).

Something liek that would move Postgres into that arena without having to
have core developers worry about it. It can actually turn into a whole
offshoot project. I was thinking of looking into it also. Using the Apache
Xerces project (their HTTP webserver implementation of XML).

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Sanjay Arora" <sk(at)pobox(dot)com>
To: <pgsql-interfaces(at)postgresql(dot)org>
Sent: Saturday, December 16, 2000 10:51 AM
Subject: [INTERFACES] Connection Pooling....an interesting question!!
(was..Connection Pooling...(Repost)...please do help...)

> 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(at)pobox(dot)com [mailto:sk(at)pobox(dot)com]
> >Sent: Friday, December 15, 2000 11:32 AM
> >To: pgsql-interfaces(at)postgresql(dot)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(at)pobox(dot)com]
> >>Sent: Wednesday, December 13, 2000 7:19 PM
> >>To: pgsql-interfaces(at)postgresql(dot)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.
> >>
> >