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

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.

But this actually means the pre-aggregating the data (as I described in my
previous post) would probably help him even more (less data, less CPU).

Tomas