Re: Performance question 83 GB Table 150 million rows, distinct select

On Wed, Nov 16, 2011 at 8: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.

A followup.  A good tool to see how your machine is running over time
is the sar command and the needed sysstat service running and
collecting data.  You can get summary views of the last x weeks rolled
up in 5 minute increments on all kinds of system metrics.