Re: increasing database connections - Mailing list pgsql-performance

At 01:18 AM 3/1/2007, Joshua D. Drake wrote:
>Jonah H. Harris wrote:
> > On 3/1/07, Shiva Sarna <shivasarna@yahoo.co.in> wrote:
> >> I am sorry if it is a repeat question but I want to know if database
> >> performance will decrease if I increase the max-connections to 2000. At
> >> present it is 100.
> >
> > Most certainly.  Adding connections over 200 will degrade performance
> > dramatically.  You should look into pgpool or connection pooling from
> > the application.
>
>huh? That is certainly not my experience. I have systems that show no
>depreciable performance hit on even 1000+ connections. To be fair to the
>discussion, these are on systems with 4+ cores. Usually 8+ and
>significant ram 16/32 gig fo ram.
>
>Sincerely,
>
>Joshua D. Drake

Some caveats.

Keeping a DB connection around is relatively inexpensive.
OTOH, building and tearing down a DB connection =can be= expensive.
Expensive or not, connection build and tear down are pure overhead
activities.  Any overhead you remove from the system is extra
capacity that the system can use in actually answering DB queries
(...at least until the physical IO system is running flat out...)

So having 1000+ DB connections open should not be a problem in and of
itself (but you probably do not want 1000+ queries worth of
simultaneous HD IO!...).

OTOH, you probably do !not! want to be constantly creating and
destroying 1000+ DB connections.
Better to open 1000+ DB connections once at system start up time and
use them as a connection pool.

The potential =really= big performance hit in having lots of
connections around is in lots of connections doing simultaneous
heavy, especially seek heavy, HD IO.

Once you have enough open connections that your physical IO subsystem
tends to be maxed out performance wise on the typical workload being
handled, it is counter productive to allow any more concurrent DB connections.

So the issue is not "how high a max-connections is too high?".   It's
"how high a max connections is too high for =my= HW running =my= query mix?"

The traditional advice is to be conservative and start with a
relatively small number of connections and increase that number only
as long as doing so results in increased system performance on your
job mix.  Once you hit the performance plateau, stop increasing
max-connections and let connection caching and pooling handle things.
If that does not result in enough performance, it's time to initiate
the traditional optimization hunt.

Also, note Josh's deployed HW for systems that can handle 1000+
connections.  ...and you can bet the IO subsystems on those boxes are
similarly "beefy".  Don't expect miracles out of modest HW.
Ron