Re: Exploring memory usage

On 12/27/2011 11:00 AM, Scott Marlowe wrote:
> On Tue, Dec 27, 2011 at 8:33 AM, Claudio Freire<klaussfreire(at)gmail(dot)com> wrote:
>> On Sat, Dec 24, 2011 at 4:22 PM, Michael Smolsky<sitrash(at)email(dot)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