Re: db server load
От | Stefano Nichele |
---|---|
Тема | Re: db server load |
Дата | |
Msg-id | 496B1665.4040201@gmail.com обсуждение исходный текст |
Ответ на | Re: db server load ("Scott Marlowe" <scott.marlowe@gmail.com>) |
Список | pgsql-performance |
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@gmail.com> 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. > >
В списке pgsql-performance по дате отправления: