On Fri, Dec 12, 2008 at 3:07 AM, Stefano Nichele <stefano.nichele@xxxxxxxxx> wrote: > Hi All, > I would like to ask to you, how many connections a db server can handle. I > know the question is not so easy, and actually I don't want to known a > "number" but something like: > - up to 100 connections: small load, low entry server is enough > - up to 200 connections: the db server starts to sweat, you need a dedicated > medium server > - up to 300 connections: hard work, dedicated server > - up to 500 connections: hard work, dedicated high level server > > I would like just to understand when we can talk about small/medium/high > load. Well, it's of course more than just how many connections you have. What percentage of the connections are idle? Are you running small tight multi-statement transactions, or huge reporting queries? The db server we have at work routinely has 100+ connections, but of those, there are only a dozen or so actively running, and they are small and transactional in nature. The machine handling this is very overpowered, with 8 opteron cores and 12 disks in a RAID-10 for data and 2 in another RAID-10 for everything else (pg_xlog, logging, etc) under a very nice hardware RAID card with battery backed cache. We've tested it to much higher loads and it's held up quite well. With the current load, and handling a hundred or so transactions per second, the top of top looks like this: top - 14:40:55 up 123 days, 2:24, 1 user, load average: 1.08, 0.97, 1.04 Tasks: 284 total, 1 running, 283 sleeping, 0 stopped, 0 zombie Cpu0 : 2.8%us, 0.4%sy, 0.0%ni, 96.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 2.5%us, 0.3%sy, 0.0%ni, 97.2%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 2.5%us, 0.2%sy, 0.0%ni, 97.1%id, 0.1%wa, 0.1%hi, 0.0%si, 0.0%st Cpu3 : 10.0%us, 0.7%sy, 0.0%ni, 89.0%id, 0.1%wa, 0.0%hi, 0.2%si, 0.0%st Cpu4 : 13.0%us, 0.9%sy, 0.0%ni, 85.9%id, 0.1%wa, 0.0%hi, 0.1%si, 0.0%st Cpu5 : 13.5%us, 0.9%sy, 0.0%ni, 85.3%id, 0.1%wa, 0.0%hi, 0.1%si, 0.0%st Cpu6 : 16.2%us, 1.1%sy, 0.0%ni, 82.2%id, 0.3%wa, 0.0%hi, 0.2%si, 0.0%st Cpu7 : 34.3%us, 2.4%sy, 0.0%ni, 61.3%id, 0.1%wa, 0.4%hi, 1.5%si, 0.0%st single line cpu looks like this: Cpu(s): 6.1%us, 0.8%sy, 0.0%ni, 92.9%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st a line from vmstat 30 looks like this: 1 0 12548 2636232 588964 27689652 0 0 0 3089 3096 4138 9 2 89 0 0 which shows us writing out at ~3M/sec. This machine, running pgbench on a db twice the size of the one it currently runs on, can get throughput of 30 to 50 megabytes per second. peaks at about 60, random access. > At the moment I'm using a quad-proc system with a 6 disk 1+0 RAID array and > 2 separate disks for the OS and write-ahead logs. Run some realistic load tests and monitor the machine with vmstat and top and iostat, etc... then compare those numbers to your day to day numbers to get an idea how close to max performance you're running to see how much headroom you have. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
