Re: PostgreSQL pre-fork speedup - Mailing list pgsql-hackers

On Wednesday 05 May 2004 07:24 am, Rod Taylor wrote:
> > 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.
>
> In theory, it should drastically reduce the number of idle connections
> for poor connection pooling on the other end.
>

If the client is poorly written, nothing on the server side can really 
prevent them from being poorly written.

> The problem are pools for Apache that establish 1 connection per Apache
> backend. 100 Apache backends means 100 backend connections (50 of which
> may be idle as not all pages use the database). Multiply that by 40
> webservers and you have a real mess of idle connections.
>

Or, you run several seperate Apache webservers. The ones that serve static 
content or don't need database connections don't run with the ones that do. 
And just like each idle Apache process uses memory and other resources, 
each idle PostgreSQL connection does to. So managing the number of Apache 
connections so that there aren't too many or too few solves the problem of 
having too many or too few idle database connections. This is all stuff 
that I personally have managed and planned for, and it is quite easy to do 
without any connection pooling on the server side.

It all comes down to management, which Apache does a reasonable job of. 
Either we duplicate the efforts of Apache (they are non-trivial), or we 
piggy-back on their success. And who's to say that the right solution for 
Apache is the right solution for another application? Are we going to 
implement a different flavor of management for each kind of application?

I suggest you implement server-side connection pooling and see for yourself:

(a) How much overhead there is for configuration (which databases? How many 
idle?)

(b) How much easier it is to do on the client side after all.

If you really believe that you are right and I am wrong, then prove it. I'll 
be happy to be shown the error of my thinking (and see an improvement to 
PostgreSQL in the process).

That's the great thing about Open Source. We can all talk the talk, but it 
comes down to whoever actually walks the walk. In the proprietary world, no 
one gets a chance to walk the walk.

-- 
Jonathan Gardner
jgardner@jonathangardner.net