On 17 Listopad 2011, 4:16, Tory M Blue wrote: > On Wed, Nov 16, 2011 at 7:02 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> > wrote: >> On Wed, Nov 16, 2011 at 7:42 PM, Tory M Blue <tmblue@xxxxxxxxx> wrote: >>> On Wed, Nov 16, 2011 at 6:27 PM, Tomas Vondra <tv@xxxxxxxx> wrote: >>>> On 17 Listopad 2011, 2:57, Scott Marlowe wrote: >>>>> On Wed, Nov 16, 2011 at 4:59 PM, Tomas Vondra <tv@xxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
