pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bob Dusek <redusek@gmail.com>
To: Ivan Voras <ivoras@freebsd.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: performance config help
Date: Mon, 11 Jan 2010 13:57:31 -0500
Message-ID: <61039b861001111057t6443b526pdc312ff2717d6227@mail.gmail.com> (raw)
In-Reply-To: <hifm6q$dp8$1@ger.gmane.org>
References: <61039b861001110544o2c352763q13dcf9d78a156714@mail.gmail.com>
	<dcc563d11001110550s2af6d559l5db495d5e0fc6a57@mail.gmail.com>
	<61039b861001110842g15bb1f26jc50b6e6762e01427@mail.gmail.com>
	<hifm6q$dp8$1@ger.gmane.org>

>
>> RAID-0
>>
>
> And how many drives?
>
> Just two.

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).
>
>
As you mentioned in a separate response, we have fsync shut off.
Regardless, we shut off a lot of logging in our app and reduced that number
to approx 20 per second.  So, a lot of those writes were coming from outside
the db.  We do a lot of logging.  We should consider turning some off, it
seems.


 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?
>

Each of the 256 requests was being processed by a php process.  So, it could
certainly be faster.  But, the fact that we're seeing the db performance
degrade would seem to indicate that our application is fast enough to punish
the db.  Isn't that true?



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

view thread (46+ messages)  latest in thread

Message-ID: <61039b861001111057t6443b526pdc312ff2717d6227@mail.gmail.com>
Permalink:  ../61039b861001111057t6443b526pdc312ff2717d6227@mail.gmail.com/
Also on:    postgresql.org/message-id/61039b861001111057t6443b526pdc312ff2717d6227@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: redusek@gmail.com, ivoras@freebsd.org
  Subject: Re: performance config help
  In-Reply-To: <61039b861001111057t6443b526pdc312ff2717d6227@mail.gmail.com>
  References: <61039b861001110544o2c352763q13dcf9d78a156714@mail.gmail.com> <dcc563d11001110550s2af6d559l5db495d5e0fc6a57@mail.gmail.com> <61039b861001110842g15bb1f26jc50b6e6762e01427@mail.gmail.com> <hifm6q$dp8$1@ger.gmane.org> <61039b861001111057t6443b526pdc312ff2717d6227@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox