Thread: increasing database connections
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
Heres a new way to find what you're looking for - Yahoo! Answers
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
Heres 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/
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/
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
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
* 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
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
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/