On 27 Listopad 2011, 10:45, Gaëtan Allart wrote:
> Actually, this is because I changed sort_mem to 4 Mb as asked by Robert.
>
> I removed this setting..
This is exactly the reason why it's recommended not to do any changes
until it's reasonably certain what is the caause.
Did increasing the work_mem again solved the issues? Is the database
running fine?
If no, post the iotop output again, so we can see what was the impact. And
post the EXPLAIN ANALYZE of the query, so we can see if the estimates are
OK.
Anyway, Rober was right - the work_mem=128MB is rather large and should be
decreased. Use something like a binary search to find the right value -
use 64MB and watch the log for temp file messages. If there's a lot of
messages, choose the upper interval and set (64+128)/2 = 96MB, otherwise
32MB. And so on. That should lead you to the minimal work_mem value.
Tomas