Re: performance config help

RAID-0

And how many drives?

We have an application server that is processing requests.  Each request consists of a combination of selects, inserts, and deletes.  We actually see degredation when we get more than 40 concurrent requests.  The exact number of queries executed by each request isn't known.  It varies per request.  But, an example request would be about 16 inserts and 113 selects.  Any given request can't execute more than a single query at a time.  

So, you are concurrently trying to achieve more than around 640 writes and 4520 reads (worst case figures...). This should be interesting. For 40 concurrent requests you will probably need at least 4 drives in RAID-0 to sustain the write rates (and I'll guess 5-6 to be sure to cover read requests also, together with plenty of RAM).

 avg-cpu:  %user   %nice %system %iowait  %steal   %idle
         34.29    0.00    7.09    0.03    0.00   58.58

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00   112.20  0.00 133.40     0.00  1964.80    14.73     0.42    3.17   0.04   0.48

The iowait seems pretty low, doesn't it?

Yes, but you are issuing 133 write operations per seconds per drive(s) - this is nearly the limit of what you can get with 15k RPM drives (actually, the limit should be somewhere around 200..250 IOPS but 133 isn't that far).

top - 10:11:43 up 12 days, 20:48,  6 users,  load average: 10.48, 4.16, 2.83
Tasks: 798 total,   8 running, 790 sleeping,   0 stopped,   0 zombie
Cpu0  : 33.3%us,  7.6%sy,  0.0%ni, 59.1%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

There is one other possibility - since the CPUs are not very loaded, are you sure the client application with which you are testing is fast enough to issue enough request to saturate the database?

--

Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance