Thread: Memory usage question

I have just run a seqscan query on a fairly large table
(1.8Mrows, ca 40MB). I have a 256MB machine, so I expected
the postgres process to take up approximately that amount of
memory. On the other hand, observing top, I noticed postgres
was consistently below 4000KB. What does that mean? Isn't
postgres supposed to cache most of the database in memory,
so as to limit disk access?

Alex

Hi Alex,


> Isn't postgres supposed to cache most of the database in memory, so as
> to limit disk access?

Check your settings for shared memory buffers in postgresql.conf and read
the appropriate manpage in the documentation.


Kind regards
... Ralph ...

Ralph Graulich wrote:
>>Isn't postgres supposed to cache most of the database in memory, so as
>>to limit disk access?
> Check your settings for shared memory buffers in postgresql.conf and read
> the appropriate manpage in the documentation.

Specifically see:
http://www.postgresql.org/idocs/index.php?kernel-resources.html
and
http://www.postgresql.org/idocs/index.php?runtime-config.html

In postgresql.conf consider changing shared_buffers to something like:
    shared_buffers = 8192 (in 8K pages, which is equal to 64MB, the oft
    recommended 1/4 total RAM; play around, YMMV).

You may also want to set:

- sort_mem (I use 4096, but that isn't based on much in the way of
   empirical testing)
- wal_sync_method (I did enough testing with this to convince myself
   that on Linux with ext3, this is the best choice)

and possibly

- max_fsm_relations = 100
- max_fsm_pages = 10000 (search the archives for discussions on the free
   space map)

On all of this stuff, best advice is to play around to obtain the best
performance on your hardware with your data and queries.

HTH,

Joe

Joe Conway wrote:
> Ralph Graulich wrote:
>
>>
>> Check your settings for shared memory buffers in postgresql.conf and read
>> the appropriate manpage in the documentation.
>
>
> Specifically see:
> http://www.postgresql.org/idocs/index.php?kernel-resources.html
> and
> http://www.postgresql.org/idocs/index.php?runtime-config.html
>
> [lots of advice on how to configure pgsql]
>
> On all of this stuff, best advice is to play around to obtain the best
> performance on your hardware with your data and queries.
>
> HTH,
>
> Joe

Joe and Ralph,

Thank you very much for your help. I did not realize that
memory was statically assigned to postgres. I thought it
would allocate it on the fly, so I thought a big query would
cause postgres to grow more than a minuscule query.

Anyhow, I had already read (most of) the docs and not
noticed these facts.

Thanks again.
Alex