From: Andrew Dunstan <andrew@dunslane.net>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: Michael Smolsky <sitrash@email.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Exploring memory usage
Date: Tue, 27 Dec 2011 11:14:40 -0500
Message-ID: <4EF9EEF0.1070207@dunslane.net> (raw)
In-Reply-To: <CAOR=d=3z0BaSQjD0oZYemDScvnD1qLvYq5FU8ruDSbQq=Reu-Q@mail.gmail.com>
References: <20111224192239.284500@gmx.com>
<CAGTBQpZtS-2cTWV9meyimTtbzm-UkPAaeaM2vyt=h-GO-9-6nQ@mail.gmail.com>
<CAOR=d=3z0BaSQjD0oZYemDScvnD1qLvYq5FU8ruDSbQq=Reu-Q@mail.gmail.com>
On 12/27/2011 11:00 AM, Scott Marlowe wrote:
> On Tue, Dec 27, 2011 at 8:33 AM, Claudio Freire<klaussfreire@gmail.com> wrote:
>> On Sat, Dec 24, 2011 at 4:22 PM, Michael Smolsky<sitrash@email.com> 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
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: andrew@dunslane.net, scott.marlowe@gmail.com, klaussfreire@gmail.com, sitrash@email.com
Subject: Re: Exploring memory usage
In-Reply-To: <4EF9EEF0.1070207@dunslane.net>
* 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