From: Tomas Vondra <tv@fuzzy.cz>
To: Tory M Blue <tmblue@gmail.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance question 83 GB Table 150 million rows, distinct select
Date: Thu, 17 Nov 2011 04:47:23 +0100
Message-ID: <3a853207fe95157fd0db8b62917ee544.squirrel@sq.gransy.com> (raw)
In-Reply-To: <CAEaSS0bzhbBNf5jWHZg01tsp=GBtr1nihrTEzMLta_s_n-9Qbw@mail.gmail.com>
References: <CAEaSS0b7D4eyDi4kt-Dt8u-yGq+akD=5PZwMk_TUHP-zujAnCQ@mail.gmail.com>
<e9ec6f004095262038f7a2b3d9e303e0.squirrel@sq.gransy.com>
<CAOR=d=2ZHAf=jubZXXOFOHyrwGqf-UUGwZ6TO0sHjbTzDN6_-Q@mail.gmail.com>
<57c79e7822e93c2325245ab4f5606acc.squirrel@sq.gransy.com>
<CAEaSS0Y2W=2h6aGj0eUnnjP7JnVUqtjj+7_mczQVBfGQ3L8ZGw@mail.gmail.com>
<CAOR=d=3dN+7gXYKCqZkFAJnEPAACoF-att42cE4rUeokWL6Wmg@mail.gmail.com>
<CAEaSS0bzhbBNf5jWHZg01tsp=GBtr1nihrTEzMLta_s_n-9Qbw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, tmblue@gmail.com, scott.marlowe@gmail.com
Subject: Re: Performance question 83 GB Table 150 million rows, distinct select
In-Reply-To: <3a853207fe95157fd0db8b62917ee544.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox