Message-ID: <50B8D98C.8050507@optionshouse.com>
Date: Fri, 30 Nov 2012 10:06:36 -0600
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121028 Thunderbird/16.0.2
MIME-Version: 1.0
To: =?ISO-8859-1?Q?Niels_Kristian_Schj=F8dt?= <nielskristian@autouncle.com>
CC: Kevin Grittner <kgrittn@mail.com>, Willem Leenen
	<willem_leenen@hotmail.com>, <pgsql-performance@postgresql.org>
Subject: Re: Optimize update query
References: <20121130012426.69300@gmx.com>
	<C5E296A1-F820-4B04-9DBD-E78380BD3300@autouncle.com>
	<855F8817-6C9A-4EFB-BB80-752BF7B73B56@autouncle.com>
	<50B8BC7F.4000005@optionshouse.com>
	<5F52712E-C339-4DF2-9EE5-A26643CEA2C2@autouncle.com>
	<50B8CA01.5040405@optionshouse.com>
	<CD7CE75B-B6F1-4F3E-A2F3-08BF859604A8@autouncle.com>
In-Reply-To: <CD7CE75B-B6F1-4F3E-A2F3-08BF859604A8@autouncle.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Length: 1493
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 11/30/2012 09:44 AM, Niels Kristian Schj=F8dt wrote:

Just a note on your iostat numbers. The first reading is actually just
a summary. You want the subsequent readings.

> The pgsql_tmp dir is not changing at all it's constantly empty (a size
> of 4.0K).

Good.

> Filesystem     1K-blocks    Used Available Use% Mounted on
> /dev/md3       230619228 5483796 213420620   3% /ssd

Good.

You could just be seeing lots of genuine activity. But going back on the
thread, I remember seeing this in your postgresql.conf:

shared_buffers =3D 7680MB

Change this to:

shared_buffers =3D 4GB

I say that because you mentioned you're using Ubuntu 12.04, and we were
having some problems with PG on that platform. With shared_buffers over
4GB, it starts doing really weird things to the memory subsystem.
Whatever it does causes the kernel to purge cache rather aggressively.
We saw a 60% reduction in read IO by reducing shared_buffers to 4GB.
Without as many reads, your writes should be much less disruptive.

You'll need to restart PG to adopt that change.

But I encourage you to keep iostat running in a terminal window so you
can watch it for a while. It's very revealing.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related=
 to this email


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance