Re: max_connections proposal

On 05/25/2011 10:58 PM, Craig Ringer wrote:
> max_connections = 100 # (change requires restart)
> # WARNING: If you're about to increase max_connections above 100, you
> # should probably be using a connection pool instead. See:
> # http://wiki.postgresql.org/max_connections
> #
> # Note: Increasing max_connections costs ~400 bytes of shared memory
> # per connection slot, plus lock space (see max_locks_per_transaction).

While tempting to do something this simple, the most useful path to
follow is probably to nail this head-on and comprehensively in the docs
instead. Discussion of this topic on the hackers list seems to have
concluded that connection pooling isn't as vital to do inside the
database, as a high priority relative to other development, because it's
addressed so well via external projects. Pointing people toward them
seems quite appropriate given that position. Really addressing this
well would take the following steps:

-Add a section to the external projects section of the documentation:
http://www.postgresql.org/docs/current/interactive/external-projects.html introducing
connection pooling as a useful type of additional software to add.
Shouldn't be controversial to suggest pgbouncer and pgpool-II as
examples there.

-Expand the documentation on max_connections to warn about how snapshot
visibility overhead makes extremely large numbers of connections impractical

-Also expand the documentation to suggest that CPU switching
inefficiency may make a much smaller number of connections than expected
optimal, and point toward the external project section for more
information about pooling.

-Add a warning to the postgresql.conf suggesting people read the
documentation for max_connections before increasing this value.

This area pops up enough that I've made a discussion of it part of even
my shortest talk about PostgreSQL performance issues to be wary of.
There's a good documentation patch project for somebody here, I just
haven't had time to get to it yet.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books