On Wed, Nov 16, 2011 at 7:47 PM, Tomas Vondra <tv@xxxxxxxx> wrote: > 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%. Yes this I understand and is correct. But I'm wrestling with the idea that the Disk is completely saturated. I've seen where I actually run into high IO/Wait and see that load climbs as processes stack. I'm not arguing (please know this), I appreciate the help and will try almost anything that is offered here, but I think if I just threw money at the situation (hardware), I wouldn't get any closer to resolution of my issue. I am very interested in other solutions and more DB structure changes etc. Thanks ! Tory -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
