SEO

Hi,

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.

I have a requirement where the clent want 2000 simultaneous users and the only option we have now is to in crease the database connection but I am unale to find any document which indicates that this is a good or a bad practise.

thanks for your help and time.

regards

shiva


---------------------------------
Here’s a new way to find what you're looking for - Yahoo! Answers

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.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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


>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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.


Yeah - I thought that somewhere closer to 10000 connections is where you
get hit with socket management related performance issues.

Cheers

Mark

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Thu, Mar 01, 2007 at 12:49:14AM -0500, 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.

Are you sure? I've heard of at least one installation which runs with
5000+ connections, and it works fine. (you know who you are - I don't
know if it's public info, so I can't put out the details - but feel free
to fill in :P)

That said, there's certainly some overhead, and using pgpool if possible
is good advice (depending on workload). I'm just wondering about
the "dramatically" part.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

* Mark Kirkwood:

> Yeah - I thought that somewhere closer to 10000 connections is where
> you get hit with socket management related performance issues.

Huh? These sockets aren't handled in a single process, are they?
Nowadays, this number of sockets does not pose any problem for most
systems, especially if you don't do I/O multiplexing. Of course, if
you've got 10,000 connections which are active in parallel, most users
won't be content with 1/10,000th of your database performance. 8-/ (I
don't see why idle connections should be a problem from a socket
management POV, though.)

--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Magnus Hagander wrote:
> On Thu, Mar 01, 2007 at 12:49:14AM -0500, 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.
>
> Are you sure? I've heard of at least one installation which runs with
> 5000+ connections, and it works fine.

We have one that high as well and it does fine. Although I wouldn't
suggest it on less than 8.1 . 8.2 handles it even better since 8.2
handles >8 cores better than 8.1.

Joshua D. Drake


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org