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

On Wed, Nov 16, 2011 at 7:47 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 17 Listopad 2011, 4:16, Tory M Blue wrote:
>> On Wed, Nov 16, 2011 at 7:02 PM, Scott Marlowe <scott.marlowe@gmail.com>
>> wrote:
>>> On Wed, Nov 16, 2011 at 7:42 PM, Tory M Blue <tmblue@gmail.com> wrote:
>>>> 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
>>>
>>> Look here in iostat:
>>>
>>>> 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
>>>
>>> See that last column, it's % utilization.   Once it hits 100% you are
>>> anywhere from pretty close to IO bound to right on past it.
>>>
>>> I agree with the previous poster, you should roll these up ahead of
>>> time into a materialized view for fast reporting.
>>>
>> Ya I'm getting mixed opinions on that. avg queue size is nothing and
>> await and svctime is nothing, so maybe I'm on the edge, but it's not
>
> What do you mean by "nothing"? There are 3060 reads/s, servicing each one
> takes 0.33 ms - that means the drive is 100% utilized.
>
> The problem with the iostat results you've posted earlier is that they
> either use "-xd" or none of those switches. That means you can's see CPU
> stats and extended I/O stats at the same time - use just "-x" next time.
>
> Anyway the results show that "%iowait" is about 6% - as Scott Marlowe
> pointed out, this means 1 core is waiting for I/O. That's the core running
> your query. Try to execute the query 16x and you'll see the iowait is
> 100%.

Yes this I understand and is correct. But I'm wrestling with the idea
that the Disk is completely saturated. I've seen where I actually run
into high IO/Wait and see that load climbs as processes stack.

I'm not arguing (please know this), I appreciate the help and will try
almost anything that is offered here, but I think if I just threw
money at the situation (hardware), I wouldn't get any closer to
resolution of my issue. I am very interested in other solutions and
more DB structure changes etc.

Thanks !
Tory