From: Shaun Thomas <sthomas@optionshouse.com>
To: Niels Kristian Schjødt <nielskristian@autouncle.com>
Cc: Kevin Grittner <kgrittn@mail.com>
Cc: Willem Leenen <willem_leenen@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimize update query
Date: Fri, 30 Nov 2012 10:06:36 -0600
Message-ID: <50B8D98C.8050507@optionshouse.com> (raw)
In-Reply-To: <CD7CE75B-B6F1-4F3E-A2F3-08BF859604A8@autouncle.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 11/30/2012 09:44 AM, Niels Kristian Schjødt 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 = 7680MB
Change this to:
shared_buffers = 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@optionshouse.com, nielskristian@autouncle.com, kgrittn@mail.com, willem_leenen@hotmail.com
Subject: Re: Optimize update query
In-Reply-To: <50B8D98C.8050507@optionshouse.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox