Re: Optimize update query

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(at)optionshouse(dot)com

______________________________________________

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