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

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%.

> "at face value", the cause of the slow query times. I think the data
> structure is, however as it seems I need to query against all the
> data, I'm unclear how to best set that up. Partitioning is not the
> answer it seems.

I'm not sure I understand what you mean by accessing all the data. You can
do that with partitioning too, although the execution plan may not be as
efficient as with a plain table. Try to partition the data by date (a
partition for each day / week) - my impression is that you're querying
data by date so this is a "natural" partitioning.

Anyway what I've recommended in my previous post was intelligent reduction
of the data - imagine for example there are 1000 unique visitors and each
of them does 1000 actions per day. That means 1.000.000 of rows. What you
can do is aggregating the data by user (at the end of the day, thus
processing just the single day), i.e. something like this

SELECT uid, count(*) FROM users WHERE log_date ... GROUP BY uid

and storing this in a table "users_aggregated". This table has just 1000
rows (one for each user), so it's 1000x smaller.

But you can do this

SELECT COUNT(DISTINCT uid) FROM users_aggregated

and you'll get exactly the correct result.


Tomas