Re: Performance question 83 GB Table 150 million rows, distinct select - Mailing list pgsql-performance

On Wed, Nov 16, 2011 at 6:27 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 17 Listopad 2011, 2:57, Scott Marlowe wrote:
>> On Wed, Nov 16, 2011 at 4:59 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>>
>>> But you're right - you're not bound by I/O (although I don't know what
>>> are
>>> those 15% - iowait, util or what?). The COUNT(DISTINCT) has to actually
>>> keep all the distinct values to determine which are actually distinct.
>>
>> Actually I meant to comment on this, he is IO bound.  Look at % Util,
>> it's at 99 or 100.
>>
>> Also, if you have 16 cores and look at something like vmstat you'll
>> see 6% wait state.  That 6% represents one CPU core waiting for IO,
>> the other cores will add up the rest to 100%.
>
> Aaaah, I keep forgetting about this and I somehow ignored the iostat
> results too. Yes, he's obviously IO bound.

I'm not so sure on the io-bound. Been battling/reading about it all
day. 1 CPU is pegged at 100%, but the disk is not. If I do something
else via another CPU I have no issues accessing the disks,
writing/deleting/reading. It appears that what was said about this
being very CPU intensive makes more sense to me. The query is only
using 1 CPU and that appears to be getting overwhelmed.

%util: This number depicts the percentage of time that the device
spent in servicing requests.

On a large query, or something that is taking a while it's going to be
writing to disk all the time and I'm thinking that is what the util is
telling me, especially since IOwait is in the 10-15% range.

Again just trying to absorb

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.93    0.00    0.60    9.84    0.00   88.62

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00    86.50 3453.00    1.50 55352.00    16.00
16.03     5.24    0.66   0.29 100.00

I mean await time and service time are in the .29 to .66 msec that
doesn't read as IObound to me. But I'm more than willing to learn
something not totally postgres specific.

But I just don't see it...  Average queue size of 2.21 to 6, that's
really not a ton of stuff "waiting"

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     3.50 3060.00    2.00 49224.00    20.00
16.08     2.21    0.76   0.33  99.95

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.80    0.00    0.51   11.01    0.00   87.68

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     5.00 3012.50    3.00 48200.00    92.00
16.01     2.11    0.74   0.33  99.95

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.93    0.00    0.60    9.84    0.00   88.62

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00    86.50 3453.00    1.50 55352.00    16.00
16.03     5.24    0.66   0.29 100.00