Re: db server load

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(dot)nichele(at)gmail(dot)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.
>
>