Re: db server load - Mailing list 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.
>
>