Message-ID: <3112082e07a5271c09b6b4435ed92385.squirrel@sq.gransy.com>
In-Reply-To: <4E651DFE.805@squeakycode.net>
References: <4E61D6B4.9090804@ai.wu.ac.at>
	<33fadeb6ac1bade5ef01bd08d1fd7807.squirrel@sq.gransy.com>
	<4E64D3D3.8000205@squeakycode.net> <4E64D7EA.3010704@ai.wu.ac.at>
	<CAOR=d=2fQwbTt3AoVW=pACezkWBG7r8pd4cqubyj-gbxie2MdA@mail.gmail.com>
	<4E651DFE.805@squeakycode.net>
Date: Mon, 5 Sep 2011 22:38:56 +0200
Subject: Re: Sudden drop in DBb performance
From: "Tomas Vondra" <tv@fuzzy.cz>
To: "Andy Colson" <andy@squeakycode.net>
Cc: "Scott Marlowe" <scott.marlowe@gmail.com>,
	"Gerhard Wohlgenannt" <wohlg@ai.wu.ac.at>, "Tomas Vondra" <tv@fuzzy.cz>,
	pgsql-performance@postgresql.org, "Heinz-Peter Lang" <heinz@langatium.net>,
	"Gerhard Wohlgenannt" <wohlg@ai.wu-wien.ac.at>,
	"Weichselbraun, Albert" <albert.weichselbraun@wu.ac.at>
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

On 5 Září 2011, 21:07, Andy Colson wrote:
> On 09/05/2011 01:45 PM, Scott Marlowe wrote:
>> On Mon, Sep 5, 2011 at 8:08 AM, Gerhard Wohlgenannt<wohlg@ai.wu.ac.at>
>> wrote:
>>> Below please find the results of vmstat 2 over some periode of time ..
>>> with
>>> normal database / system load.
>>>
> 2  1 1344204 240924 104156 31462484  350    0  1906   234 3687 4512 12  3
> 77  9
>>
>> Your IO Wait is actually pretty high.  On an 8 core machine, 12.5%
>> means one core is doing nothing but waiting for IO.
>>
>
> My server is 2-core, so these numbers looked fine by me.  I need to
> remember core count when I look at these.
>
> So the line above, for 2 core's would not worry me a bit, but on 8 cores,
> it pretty much means one core was pegged (with 9% wait?  Or is it one core
> was pegged, and another was 72% io wait?)

AFAIK it's as if one core was 72% io wait. Anyway that's exactly why I was
asking for "iostat -x" because the util% gives a better idea of what's
going on.

> I have always loved the vmstat output, but its starting to get confusing
> when you have to take core's into account.  (And my math was never strong
> in the first place :-) )

That's why I love dstat, just do this

$ dstat -C 0,1,2,3,4,5,6,7

and you know all you need.

> Good catch, thanks Scott.

Yes, good catch.

Still, this does not explain why the queries were running fast before, and
why the RAID array is so sluggish. Not to mention that we don't know what
were the conditions when collecting those numbers (were the VMs off or
running?).

Tomas