Message-ID: <b949a5ed6b99535e70d3c603b063bab4.squirrel@sq.gransy.com>
In-Reply-To: 
 <CAPVp=ga6CkO8vXJJs5PswKv0Cf3R3SpUK+KmQXY8edpc84CddA@mail.gmail.com>
References: 
 <CAPVp=gYs7uYF_hy0MDBkpRCxNbWQHPSyP6sXO-6SJZ_TvaU=XQ@mail.gmail.com>
	<87e450fd65045c328f41e1b99a4029d5.squirrel@sq.gransy.com>
	<CAPVp=ga6CkO8vXJJs5PswKv0Cf3R3SpUK+KmQXY8edpc84CddA@mail.gmail.com>
Date: Mon, 14 Nov 2011 23:57:49 +0100
Subject: Re: Slow queries / commits, mis-configuration or hardware
 issues?
From: "Tomas Vondra" <tv@fuzzy.cz>
To: "Cody Caughlan" <toolbag@gmail.com>
Cc: "Tomas Vondra" <tv@fuzzy.cz>,
 pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Importance: Normal
Content-Transfer-Encoding: quoted-printable

On 14 Listopad 2011, 22:58, Cody Caughlan wrote:
>> Seems reasonable, although I'd bump up the checkpoint_timeout (the 5m =
is
>> usually too low).
>
> Ok, will do.

Yes, but find out what that means and think about the possible impact
first. It usually improves the checkpoint behaviour but increases the
recovery time and you may need more checkpoint segments. And I doubt this
will fix the issue you've described.

> I ran bonnie++ on a slave node, doing active streaming replication but
> otherwise idle:
> http://batch-files-test.s3.amazonaws.com/sql03.prod.html
>
> bonnie++ on the master node:
> http://batch-files-test.s3.amazonaws.com/sql01.prod.html
>
> If I am reading this right, this is my first time using it, the
> numbers dont look too good.

Are those instances equal, i.e. use tha same RAID10 config etc.? It
surprises me a bit that the slave performs much better than the master,
for example the sequential reads are much faster (210MB/s vs. 60MB/s) and
it handles about twice the number of seeks (345 vs. 170). But this may be
skewed because of the workload.

> Heres a gist of running "iostat -x 3" for about a few minutes:
>
> https://gist.github.com/f94d98f2ef498a522ac2
>
> Indeed, the %iowat and await values can spike up drastically.

OK, so xvdb-xvde are individual drives and dm-0 is the RAID10 device,
right? According to the log_checkpoint info, you're writing about 15000
(120MB) buffers in 270s, i.e. about 440kB/s. But according to the iostat
you're writing up to 4MB/s, so it's not just about the checkpoints.

What else is going on there? How much WAL do you write?

Do you have iotop installed? That might give you a hint what processes ar=
e
writing data etc.

I'm a bit confused that the w/s don't add up while r/s do.

Tomas