On 12/27/2011 11:00 AM, Scott Marlowe wrote:
On Tue, Dec 27, 2011 at 8:33 AM, Claudio Freire<klaussfreire@xxxxxxxxx> wrote:
On Sat, Dec 24, 2011 at 4:22 PM, Michael Smolsky<sitrash@xxxxxxxxx> wrote:
work_mem = 128MB (tried 257MB, didn't change anything)
This is probably your problem.
Without an EXPLAIN output, I cannot be sure, but 'work_mem' is not the
total amount of memory a query can use, it's the amount of memory it
can use for *one* sort/hash/whatever operation. A complex query can
have many of those, so your machine is probably swapping due to
excessive memory requirements.
Try *lowering* it. You can do so only for that query, by executing:
set work_mem = '8MB';<your query>
He can lower it for just that query but honestly, even on a machine
with much more memory I'd never set it as high as he has it. On a
busy machine with 128G RAM the max I ever had it set to was 16M, and
that was high enough I kept a close eye on it (well, nagios did
anway.)
It depends on the workload. Your 16M setting would make many of my
clients' systems slow to an absolute crawl for some queries, and they
don't run into swap issues, because we've made educated guesses about
usage patterns.
cheers
andrew
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
