Re: Urgent: 10K or more connections

> > > But I'm sure that with a few tweaks to the code here and there
> > > it's doable, just don't expect it to work "out of the box".
> >
> > I think you'd be sticking your neck out to assume that 10k
> > concurrent connections would perform well, even after tweaking.
> > I'd worry first about whether the OS can handle 10k processes
> > (which among other things would probably require order-of-300k
> > open file descriptors...).  Maybe Solaris is built to do that but
> > the Unixen I've dealt with would go belly up.  After that you'd
> > have to look at Postgres' internal issues --- contention on access
> > to the PROC array would probably become a significant factor, for
> > example, and we'd have to do some redesign to avoid linear scans
> > of the PROC array where possible.
>
> This page describes all the problems and strategies a web server
> would use to handle 10k concurrent connections.  This is the kind of
> thing that can bring an otherwise performant OS to it's knees.  And
> this is just to grab some data off disk and shovel it out over HTTP,
> consider how much more work a database must do.
>
> http://www.kegel.com/c10k.html

*lightning strikes/apple falls on head*

Whoa!  It's tough, sure, but _far_ from impossible.  My bread and
butter is big web __fill_in_the_blank__ and I routinely handle ~60K to
some of my web server _instances_.  Kegel's page and analysis are a
bit dated and most of it's still true and applicable.  Using kqueue(2)
on FreeBSD, it's pretty easy to have bazillions of concurrent
connections and maintain low latency rates when identifying processes
that are ready to be worked on (avoiding select(2) is _required_ to
get above a few thousand).  On Linux or Slowaris, poll(2) can be
substituted for kqueue(2) and on other OSes that are less fortunate,
select(2) will suffice and no one would be the wiser (except for in
the scalability dept.).

With OSes that allow passing of FD's between existing processes (iirc
PostgreSQL fork()'s with the connection, it doesn't pass FD's around)
and making use of a given platform's alternatives to select(2), it's
very plausible to imagine a world where a backend hands an idle
connection back to the parent process for safe keeping/process load
balancing.  Arguably, that function call should've been added to the
most recent libpq(3) update that way the feature could be added to the
backend and libs wouldn't have to be updated in order for the feature
to be available.

Now that I think about it, this is significantly easier to accomplish
than adding mmap(2) to the backend (~500-1,000 lines of code) ...  To
preserve ABI compatibility, a persistent flag would likely be set to a
PGconn (ex: PQpersistConnection(PGconn *conn, bool value)) or an
environment variable/symlink file (ex: /etc/malloc.conf) could be used
to globally apply this to all libpq apps.  When an app is done with a
backend, the backend calls PQfinish() as per normal, except instead of
closing the connection, it would send a message to the backend
informing it that the client is done with the connection for now.  On
the DB, the postmaster would kill off the given backend, and launch X
number of idle backends for the given database that the now idle
connection thinks its connected to.  In doing this, when the
connection gets used again and the most expensive parts of the DB
connection process is already done (fork()ing, populating the backend,
and if using SSL, going through the asymmetric crypto routines -
they're killer on machines without off a hardware backed /dev/crypto).
For web farms with many diverse applications that can't make use of a
database connection pooling app, this'd be dynamite.  When a process
dies, the connection would finally close in reality and the backend
postmaster would reap the connection.

Using kqueue(2), it's very plausible that a PostgreSQL instance could
handle about ~50K idle connections.  poll(2) could do probably about
~10K connections without any real degradation in performance.  Those
stuck with select(2), you'd be lucky to get beyond a few hundred idle.
If you're unfortunate enough to be apart of the Win32 crowd...
*smack* why are you running a high volume DB server on Win32?  You
should know better.


This is hugely valuable to me now...  hrm, guess I have my new summer
project.

-sc

--
Sean Chittenden