Thread: Exploring memory usage

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>

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.)

On Tue, Dec 27, 2011 at 1:00 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> 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.)

I have it quite high, because I know the blend of queries going into
the server allows it.

But yes, it's not a sensible setting if you didn't analyze the
activity carefully.

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

On Tue, Dec 27, 2011 at 9:14 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> 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.

Exactly.  I've had an old Pentium4 machine that did reporting and only
had 2G RAM with a 256M work_mem setting, while the heavily loaded
machine I mentioned earlier handles something on the order of several
hundred concurrent users and thousands of queries a second, and 16Meg
was a pretty big setting on that machine, but since most of the
queries were of the select * from sometable where pkid=123456 it
wasn't too dangerous.

It's all about the workload.  For that, we need more info from the OP.

On Sat, Dec 24, 2011 at 12:22 PM, Michael Smolsky <sitrash@email.com> wrote:
> shared_buffers = 2GB (tried 8GB, didn't change anything)
> work_mem = 128MB (tried 257MB, didn't change anything)

As someone mentioned, lower is better here.  128M is quite high.

> effective_cache_size = 12GB (tried 2GB didn't change anything)

This doesn't affect memory usage.  It only tells the planner about how
big the OS and pg caches are for the db.  It's a very coarse
adjustment knob, so don't get too worried about it.

> In order to resolve my issue, I tried to search for postgres profiling tools
> and found no relevant ones. This is rather disappointing. That's what I
> expected to find:

Look for pg_buffercache.  I'm sue there's some others I'm forgetting.
Grab a copy of Greg Smith's Performance PostgreSQL, it's got a lot of
great info in it on handling heavy load servers.

> I realize that postgres is a free software and one cannot demand new
> features from people who invest their own free time in developing and
> maintaining it. I am hoping that my feedback could be useful for future
> development.

It's not just free as in beer.  It's free as in do what you will with
it.  So, if you whip out your checkbook and start waving it around,
you can certainly pay someone to write the code to instrument this
stuff.  Whether you release it back into the wild is up to you.  But
yea, first see if someone's already done some work on that, like the
pg_bufffercache modules before spending money reinventing the wheel.