Re: Optimize update query

Den 30/11/2012 kl. 17.06 skrev Shaun Thomas <sthomas(at)optionshouse(dot)com>:

> 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
Couldn't this be if you haven't changed these: http://www.postgresql.org/docs/9.2/static/kernel-resources.html ?
I have changed the following in my configuration:

kernel.shmmax = 8589934592 #(8GB)
kernel.shmall = 17179869184 #(16GB)