Re: Exploring memory usage

On Sat, Dec 24, 2011 at 12:22 PM, Michael Smolsky <sitrash(at)email(dot)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.