Urgent: 10K or more connections

On Fri, 18 Jul 2003, Francois Suter wrote:

Hi all,

I have received a question via the Advocacy site and I am not knowledgeable
enough to answer. Can you help?

The question is: can PostgreSQL handle between 10'000 and 40'000
simultaneous connections? The persone asking the question has to choose
between Oracle and PostgreSQL, and my guess is that they would be relieved
if they could go with PostgreSQL.

Do you have any additional advice I could transmit to this person about
handling that many connections. I'm sure any help we can provide will be an
additional selling point.

Wow! That's quite a few connections. I would say that 10,000 connections
is a lot for ANY database to hold open.

Can this person use connection pooling? Or do they need an actual 10,000
parallel accesses to get things done? If they can't use connection
pooling, or connection pooling only gets them down to 10k to 40k
connections, then that's a huge system. I wouldn't run something that big
on Oracle or Postgresql, I'd use a farm of mainframes running something
like TPF like the airlines do.

Francois Suter <dba@paragraf.ch> writes:

Hi all,

I have received a question via the Advocacy site and I am not knowledgeable
enough to answer. Can you help?

The question is: can PostgreSQL handle between 10'000 and 40'000
simultaneous connections? The persone asking the question has to choose
between Oracle and PostgreSQL, and my guess is that they would be relieved
if they could go with PostgreSQL.

On a big enough system, sure. Each PG connection backend is a
separate process, so you'd need to make sure the process table was big
enough, open file and shared memory limits set high, etc. You'd want
a really big machine, hopefully 64-bit like a Sparc or IA64, with lots
of memory. But you'd want that for Oracle, too.

You'd definitely want to spend a lot of time tuning and testing for
that activity level, but again, you'd do that for Oracle too.

-Doug

On 18 Jul 2003, Doug McNaught wrote:

Francois Suter <dba@paragraf.ch> writes:

Hi all,

I have received a question via the Advocacy site and I am not knowledgeable
enough to answer. Can you help?

The question is: can PostgreSQL handle between 10'000 and 40'000
simultaneous connections? The persone asking the question has to choose
between Oracle and PostgreSQL, and my guess is that they would be relieved
if they could go with PostgreSQL.

On a big enough system, sure. Each PG connection backend is a
separate process, so you'd need to make sure the process table was big
enough, open file and shared memory limits set high, etc. You'd want
a really big machine, hopefully 64-bit like a Sparc or IA64, with lots
of memory. But you'd want that for Oracle, too.

You'd definitely want to spend a lot of time tuning and testing for
that activity level, but again, you'd do that for Oracle too.

I'm gonna go out on a limb and guess that if you want 10k concurrent
connections, you're likely gonna be spending some time here on the list
getting postgresql to perform in that environment. I.e. little
inefficiencies in shared memory access and IPC are gonna cause this to
crawl even on a Sun E10k with 64 CPUs and 64 gigs of ram.

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 have received a question via the Advocacy site and I am not
knowledgeable enough to answer. Can you help?

The question is: can PostgreSQL handle between 10'000 and 40'000
simultaneous connections? The persone asking the question has to
choose between Oracle and PostgreSQL, and my guess is that they
would be relieved if they could go with PostgreSQL.

Do you have any additional advice I could transmit to this person
about handling that many connections. I'm sure any help we can
provide will be an additional selling point.

Actually, this begs the question: are there any "reverse DB" proxy
servers around that people have used? Having a reverse libpq proxy
server would _rock_. Some light weight multi-threaded proxy that
relays active connections to the backend and holds idle connections
more efficiently than PostgreSQL... well... it'd be a life saver in
sooooo many situations. Granted it'd have its short comings
(connections would persist to the backend along transactions, once
committed, the front end would "detatch" from the backend that it was
using), but this is achitecturally similar to what MS and ORA do to
handle gazillions of connections to a database that in reality, can
only handle a few hundred (maybe a thousand or two) active
connections.

-sc

--
Sean Chittenden

On Fri, 18 Jul 2003, Sean Chittenden wrote:

I have received a question via the Advocacy site and I am not
knowledgeable enough to answer. Can you help?

The question is: can PostgreSQL handle between 10'000 and 40'000
simultaneous connections? The persone asking the question has to
choose between Oracle and PostgreSQL, and my guess is that they
would be relieved if they could go with PostgreSQL.

Do you have any additional advice I could transmit to this person
about handling that many connections. I'm sure any help we can
provide will be an additional selling point.

Actually, this begs the question: are there any "reverse DB" proxy
servers around that people have used? Having a reverse libpq proxy
server would _rock_. Some light weight multi-threaded proxy that
relays active connections to the backend and holds idle connections
more efficiently than PostgreSQL... well... it'd be a life saver in
sooooo many situations. Granted it'd have its short comings
(connections would persist to the backend along transactions, once
committed, the front end would "detatch" from the backend that it was
using), but this is achitecturally similar to what MS and ORA do to
handle gazillions of connections to a database that in reality, can
only handle a few hundred (maybe a thousand or two) active
connections.

I thin usogres does this. not sure though, I haven't played with it, just
heard of it.

I have received a question via the Advocacy site and I am not
knowledgeable enough to answer. Can you help?

The question is: can PostgreSQL handle between 10'000 and 40'000
simultaneous connections? The persone asking the question has to
choose between Oracle and PostgreSQL, and my guess is that they
would be relieved if they could go with PostgreSQL.

Do you have any additional advice I could transmit to this person
about handling that many connections. I'm sure any help we can
provide will be an additional selling point.

Actually, this begs the question: are there any "reverse DB" proxy
servers around that people have used? Having a reverse libpq proxy
server would _rock_. Some light weight multi-threaded proxy that
relays active connections to the backend and holds idle connections
more efficiently than PostgreSQL... well... it'd be a life saver in
sooooo many situations. Granted it'd have its short comings
(connections would persist to the backend along transactions, once
committed, the front end would "detatch" from the backend that it was
using), but this is achitecturally similar to what MS and ORA do to
handle gazillions of connections to a database that in reality, can
only handle a few hundred (maybe a thousand or two) active
connections.

I thin usogres does this. not sure though, I haven't played with it, just
heard of it.

Hrm...

Usogres

Usogres is Synchronizing Option for postGRESql. That's the meaning
of name for this system.

What is Usogres?

Real-time Backup Utility Usogres is the system which executes
database duplication for PostgreSQL(http://www.postgresql.org/) which
replication wasn't realized now. It is fundamentally different from
replication even if it is said that it executes database duplication.
Persistently, it is defined to be making the same database in
real-time.

Looks like it's closer to a replication solution to me, but you may be
able to stick one of these infront of PostgreSQL and have it handle
higher numbers of connections. ::shrug:: I may have to check it out
and see... -sc

--
Sean Chittenden

Sean Chittenden wrote:

I have received a question via the Advocacy site and I am not
knowledgeable enough to answer. Can you help?

The question is: can PostgreSQL handle between 10'000 and 40'000
simultaneous connections? The persone asking the question has to
choose between Oracle and PostgreSQL, and my guess is that they
would be relieved if they could go with PostgreSQL.

Do you have any additional advice I could transmit to this person
about handling that many connections. I'm sure any help we can
provide will be an additional selling point.

Actually, this begs the question: are there any "reverse DB" proxy
servers around that people have used? Having a reverse libpq proxy
server would _rock_. Some light weight multi-threaded proxy that
relays active connections to the backend and holds idle connections
more efficiently than PostgreSQL... well... it'd be a life saver in
sooooo many situations. Granted it'd have its short comings
(connections would persist to the backend along transactions, once
committed, the front end would "detatch" from the backend that it was
using), but this is achitecturally similar to what MS and ORA do to
handle gazillions of connections to a database that in reality, can
only handle a few hundred (maybe a thousand or two) active
connections.

There are 1000's of references to postgresql and connection pooling.

http://www.google.com/search?hl=en&amp;ie=UTF-8&amp;oe=UTF-8&amp;q=pooling+postgresql

Maybe somthing there will work.

There are 1000's of references to postgresql and connection pooling.

http://www.google.com/search?hl=en&amp;ie=UTF-8&amp;oe=UTF-8&amp;q=pooling+postgresql

Maybe somthing there will work.

Those are all application level connection pooling links. I'm
thinking about something that's done on the database side like ORA
Listener and passes active connections back to the backend that way
it's completely transparent and applies to every libpq app regardless
of the language, application, etc.

-sc

--
Sean Chittenden

"scott.marlowe" <scott.marlowe@ihs.com> writes:

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.

I don't doubt that we could support 10k concurrent *users*, given
connection pooling of some kind. I'm dubious about 10k concurrent
database sessions though.

regards, tom lane

On Friday 18 July 2003 01:28 pm, Sean Chittenden wrote:

There are 1000's of references to postgresql and connection pooling.

http://www.google.com/search?hl=en&amp;ie=UTF-8&amp;oe=UTF-8&amp;q=pooling+postgresql

Maybe somthing there will work.

Those are all application level connection pooling links. I'm
thinking about something that's done on the database side like ORA
Listener and passes active connections back to the backend that way
it's completely transparent and applies to every libpq app regardless
of the language, application, etc.

-sc

Perhaps this is the answer? I've never used it, but I remember seeing ti on
freshmeat.net.

http://sqlrelay.sourceforge.net/

Regards,
Jeff Davis

There are 1000's of references to postgresql and connection pooling.

http://www.google.com/search?hl=en&amp;ie=UTF-8&amp;oe=UTF-8&amp;q=pooling+postgresql

Maybe somthing there will work.

Those are all application level connection pooling links. I'm
thinking about something that's done on the database side like ORA
Listener and passes active connections back to the backend that way
it's completely transparent and applies to every libpq app regardless
of the language, application, etc.

-sc

Perhaps this is the answer? I've never used it, but I remember
seeing ti on freshmeat.net.

http://sqlrelay.sourceforge.net/

Very cool... too bad rudiments (prerequisit) doesn't compile with
newer versions of GCC and this requires you to develop your apps using
sqlrelay's API and not libpq.... though it's better than nothing. :)
-sc

--
Sean Chittenden

On Fri, 18 Jul 2003, Tom Lane wrote:

"scott.marlowe" <scott.marlowe@ihs.com> writes:

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

Kris Jurka

Sean Chittenden <sean@chittenden.org> writes:

Some light weight multi-threaded proxy that
relays active connections to the backend and holds idle connections
more efficiently than PostgreSQL...

What excuse is there for postgres connections being heavyweight to begin with?
The only real resource they ought to represent is a single TCP connection.
Servers that manage 10,000 TCP connections are a dime a dozen these days.

Any database context that has to be stored for the connection, the state of
binary/text or autocommit mode or whatever, will have to be maintained by any
pooling interface anyways. And I think both of those examples are now much
cleaner more or less stateless per-request flags anyways.

Basically what I'm asking is, hypothetically, if postgres were implemented
using threads instead of processes, are there any per-connection resources
that really couldn't be completely disposed of when the connection was
completely idle between (ie at the start of) transactions?

Ideally if every per-connection resource could be completely disposed of
whenever the connection was completely idle then you wouldn't need a whole
extra layer for the communication to traverse and a whole extra layer of
complexity for the protocol semantics to be maintained. A multithreaded server
could easily handle 10k-40k mostly idle connections without any unusual
resource needs.

--
greg

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

Some light weight multi-threaded proxy that relays active
connections to the backend and holds idle connections more
efficiently than PostgreSQL...

What excuse is there for postgres connections being heavyweight to
begin with? The only real resource they ought to represent is a
single TCP connection. Servers that manage 10,000 TCP connections
are a dime a dozen these days.

Any database context that has to be stored for the connection, the
state of binary/text or autocommit mode or whatever, will have to be
maintained by any pooling interface anyways. And I think both of
those examples are now much cleaner more or less stateless
per-request flags anyways.

Basically what I'm asking is, hypothetically, if postgres were
implemented using threads instead of processes, are there any
per-connection resources that really couldn't be completely disposed
of when the connection was completely idle between (ie at the start
of) transactions?

Ideally if every per-connection resource could be completely
disposed of whenever the connection was completely idle then you
wouldn't need a whole extra layer for the communication to traverse
and a whole extra layer of complexity for the protocol semantics to
be maintained. A multithreaded server could easily handle 10k-40k
mostly idle connections without any unusual resource needs.

PostgreSQL will never be single proc, multi-threaded, and I don't
think it should be for reliability's sake. See my above post,
however, as I think I may have a better way to handle "lots of
connections" without using threads. -sc

--
Sean Chittenden

Sean Chittenden wrote:

PostgreSQL will never be single proc, multi-threaded, and I don't
think it should be for reliability's sake. See my above post,
however, as I think I may have a better way to handle "lots of
connections" without using threads. -sc

never is a VERY long time ... Also, the single proc/multiple proc thing
does not have to be exclusive. Meaning you could "tune" the system so
that it could do either.

I have developed a single process server that handled thousands of
connections. I've also developed a single process database (a while
back) that handled multiple connections but I'm not sure I would do it
the "hard" way again as the cost of writing the code for keeping context
was not insignificant, although there are much better ways of doing it
than how I did it 15 years ago.

What you talk about is very fundamental and I would love to have another
go at it .... however you're right that this won't happen any time
soon. Connection pooling is a fundamentally flawed way of overcoming
this problem. A different design could render a significantly higher
feasable connection count.

G

PostgreSQL will never be single proc, multi-threaded, and I don't
think it should be for reliability's sake. See my above post,
however, as I think I may have a better way to handle "lots of
connections" without using threads. -sc

never is a VERY long time ... Also, the single proc/multiple proc
thing does not have to be exclusive. Meaning you could "tune" the
system so that it could do either.

True. This topic has come up a zillion times in the past though. The
memory segmentation and reliability that independent processes give
you is huge and the biggest reason why _if_ PostgreSQL does
spontaneously wedge itself (like MySQL does all too often), you're
only having to cope with a single DB connection being corrupt,
invalid, etc. Imagine a threaded model where the process was horked
and you loose 1000 connections worth of data in a SEGV. *shudder*
Unix is reliable at the cost of memory segmentation... something that
I dearly believe in. If that weren't worth anything, then I'd run
everything in kernel and avoid the context switching, which is pretty
expensive.

I have developed a single process server that handled thousands of
connections. I've also developed a single process database (a while
back) that handled multiple connections but I'm not sure I would do
it the "hard" way again as the cost of writing the code for keeping
context was not insignificant, although there are much better ways
of doing it than how I did it 15 years ago.

Not saying it's not possible, just that at this point, reliability is
more paramount than handling additional connections. With copy on
write VM's being abundant these days, a lot of the size that you see
with PostgreSQL is shared. Memory profiling and increasing the number
of read only pages would be an extremely interesting exercise that
could yield some slick results in terms of reducing the memory foot
print of PG's children.

What you talk about is very fundamental and I would love to have
another go at it .... however you're right that this won't happen
any time soon. Connection pooling is a fundamentally flawed way of
overcoming this problem. A different design could render a
significantly higher feasable connection count.

Surprisingly, it's not that complex at least handling a large number
of FDs and figuring out which ones have data on them and need to be
passed to a backend. I'm actually using the model for monitoring FD's
from thttpd and reapplying bits where appropriate. It's abstraction
of kqueue()/poll()/select() is nice enough to not want to reinvent the
wheel (same with its license). Hopefully ripping through the incoming
data and figuring out which backend pool to send a connection to won't
be that bad, but I have next to no experience with writing that kind
of code and my Stevens is hidden away in one of 23 boxes from a move
earlier this month. I only know that Apache 1.3 does this with
obviously huge success on basically every *nix so it can't be too
hard.

-sc

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:

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.

And your current database, user authorization, prepared statements,
SET values, cached plpgsql plans, etc etc go where exactly?

The notion that a Postgres session can be replaced by a lightweight
object is just not workable IMHO; we've developed far too many features
that require persistent state on the backend side.

For applications that don't need those features (or, more realistically,
want the same persistent state for all transactions they engage in),
client-side connection pooling solves the problem. It seems very
unlikely that apps that are too diverse to share a client-side pool
would be able to share a backend session if only the connection
mechanism were a bit different.

regards, tom lane

Sean Chittenden wrote:

PostgreSQL will never be single proc, multi-threaded, and I don't
think it should be for reliability's sake. See my above post,
however, as I think I may have a better way to handle "lots of
connections" without using threads. -sc

never is a VERY long time ... Also, the single proc/multiple proc
thing does not have to be exclusive. Meaning you could "tune" the
system so that it could do either.

True. This topic has come up a zillion times in the past though. The
memory segmentation and reliability that independent processes give
you is huge and the biggest reason why _if_ PostgreSQL does
spontaneously wedge itself (like MySQL does all too often), you're
only having to cope with a single DB connection being corrupt,
invalid, etc. Imagine a threaded model where the process was horked
and you loose 1000 connections worth of data in a SEGV. *shudder*
Unix is reliable at the cost of memory segmentation... something that
I dearly believe in. If that weren't worth anything, then I'd run
everything in kernel and avoid the context switching, which is pretty
expensive.

Yep, but if you design it right, you can have both. A rare occasion
where you can have the cake and eat it too.

I have developed a single process server that handled thousands of
connections. I've also developed a single process database (a while
back) that handled multiple connections but I'm not sure I would do
it the "hard" way again as the cost of writing the code for keeping
context was not insignificant, although there are much better ways
of doing it than how I did it 15 years ago.

Not saying it's not possible, just that at this point, reliability is
more paramount than handling additional connections. With copy on
write VM's being abundant these days, a lot of the size that you see
with PostgreSQL is shared. Memory profiling and increasing the number
of read only pages would be an extremely interesting exercise that
could yield some slick results in terms of reducing the memory foot
print of PG's children.

Context switching and cache thrashing are the killers in a multiple
process model. There is a 6-10x performance penalty for running in
separate processes vs running in a single process (and single thread)
which I observed when doing benchmarking on a streaming server. Perhaps
a better scheduler (like the O(1) scheduler in Linux 2.6.* would improve
that but I just don't know.

What you talk about is very fundamental and I would love to have
another go at it .... however you're right that this won't happen
any time soon. Connection pooling is a fundamentally flawed way of
overcoming this problem. A different design could render a
significantly higher feasable connection count.

Surprisingly, it's not that complex at least handling a large number
of FDs and figuring out which ones have data on them and need to be
passed to a backend. I'm actually using the model for monitoring FD's
from thttpd and reapplying bits where appropriate. It's abstraction
of kqueue()/poll()/select() is nice enough to not want to reinvent the
wheel (same with its license). Hopefully ripping through the incoming
data and figuring out which backend pool to send a connection to won't
be that bad, but I have next to no experience with writing that kind
of code and my Stevens is hidden away in one of 23 boxes from a move
earlier this month. I only know that Apache 1.3 does this with
obviously huge success on basically every *nix so it can't be too
hard.

No epoll ?