sdv mailer wrote:
> We used to run persistent connection until the DB
> servers got maxed out because of too many idle
> connections sucking up all the memory. Web servers run
> different loads than database servers and persistent
> connections are notorious for crashing your DB.
And this translates from your experiences with mysql to postgresql ? You
haven't made it clear which platforms and what level of concurrent
connections gave you this behaviour. Tom Lane has already explained that
most of the connection time is probably used in configuring the connection
based on the database required etc.
> Connection pooling (eg. SQLRelay) didn't work either
> because we needed to connect to hundreds of DB servers
> from each web server. Imagine having 200+ open
> connections on the web server and how many more of
> these connections remain idle. The situation gets
> worse when you multiply by an even greater number of
> web servers connected to all these database servers.
> Do the math! We're talking large server farm here, not
> 2 or 3 machines.
And "preforking" makes this different, how ? Perhaps having a pool of
processes ready to be handed a query to a specific database, where you
configure N connections to db1, M to db2 etc. still means lots of resource
usage. In effect a preforked database server *is* an idle connection, just
without the TCP establishment and teardown sequence which is negligable on
modern platforms - and even if it were not negligable, it would be
effectively identical regardless of the chosen DB platform.
> I think pre-forking can be beneficial and is a lot
> simpler than to rewrite a multi-threaded DB server.
This is open source, feel free to do a proof on concept (or pay someone to
do a proof of concept), run the numbers and see if your assertions work for
real. Many others here with more experience than myself of running thousands
of connections at once don't appear to think so. My limited expereience with
many hundreds of "idle" connections is that it is not particularly taxing at
all on any even semi-modern hardware (PIII/512MB etc).
Peter