Hi Scott, as you know since the other thread, I performed some tests: -bash-3.1$ pgbench -c 50 -t 1000 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 50 number of transactions per client: 1000 number of transactions actually processed: 50000/50000 tps = 377.351354 (including connections establishing) tps = 377.788377 (excluding connections establishing) Some vmstat samplings in the meantime:procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 0 4 92 127880 8252 3294512 0 0 458 12399 2441 14903 22 9 34 35 0 11 49 92 125336 8288 3297016 0 0 392 11071 2390 11568 17 7 51 24 0 0 2 92 124548 8304 3297764 0 0 126 8249 2291 3829 5 3 64 28 0 0 1 92 127268 7796 3295672 0 0 493 11387 2323 14221 23 9 47 21 0 0 2 92 127256 7848 3295492 0 0 501 10654 2215 14599 24 9 42 24 0 0 2 92 125772 7892 3295656 0 0 34 7541 2311 327 0 1 59 40 0 0 1 92 127188 7952 3294084 0 0 537 11039 2274 15460 23 10 43 24 0 7 4 92 123816 7996 3298620 0 0 253 8946 2284 7310 11 5 52 32 0 0 2 92 126652 8536 3294220 0 0 440 9563 2307 9036 13 6 56 25 0 0 10 92 125268 8584 3296116 0 0 426 10696 2285 11034 20 9 39 32 0 0 2 92 124168 8604 3297252 0 0 104 8385 2319 4162 3 3 40 54 0 0 8 92 123780 8648 3296456 0 0 542 11498 2298 16613 25 10 16 48 0
-bash-3.1$ pgbench -t 10000 -c 50 starting vacuum...end. transaction type: SELECT only scaling factor: 100 number of clients: 50 number of transactions per client: 10000 number of transactions actually processed: 500000/500000 tps = 8571.573651 (including connections establishing) tps = 8594.357138 (excluding connections establishing) -bash-3.1$ pgbench -t 10000 -c 50 -S starting vacuum...end. transaction type: SELECT only scaling factor: 100 number of clients: 50 number of transactions per client: 10000 number of transactions actually processed: 500000/500000 tps = 8571.573651 (including connections establishing) tps = 8594.357138 (excluding connections establishing) (next test is with scaling factor 1) -bash-3.1$ pgbench -t 20000 -c 8 -S pgbench starting vacuum...end. transaction type: SELECT only scaling factor: 1 number of clients: 8 number of transactions per client: 20000 number of transactions actually processed: 160000/160000 tps = 11695.895318 (including connections establishing) tps = 11715.603720 (excluding connections establishing) Any comment ? I can give you also some details about database usage of my application: - number of active connections: about 60 - number of idle connections: about 60 Here some number from a mine old pgfouine report: - query peak: 378 queries/s - select: 53,1%, insert 3,8%, update 2,2 %, delete 2,8 % The application is basically a web application and the db size is 37 GB. How would you classify the load ? small/medium/high ? Cheers, ste Scott Marlowe wrote:
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
