I have a machine with 4 CPU's and 2 gigabytes of physical ram.
 
I would like to get PostgreSQL to use as much memory as possible.  I
can't seem to get PostgreSQL to use more than 100 megabytes or so.
 
How can I optimize the use of PostgreSQL to get the maximum throughput
in a configuration like that?
 
Are there any memory usage/tuning documents I can read?
 

Message



I have a machine 
with 4 CPU's and 2 gigabytes of physical ram.
 
I would like to get 
PostgreSQL to use as much memory as possible.  I can't seem to get 
PostgreSQL to use more than 100 megabytes or so.
 
How can I optimize 
the use of PostgreSQL to get the maximum throughput in a configuration like 
that?
 
Are there any memory 
usage/tuning documents I can read?
Mon, 6 Jan 2003 19:16:24 -0800
"Dann Corbit" <DCorbit@connx.com>
PostgreSQL and memory usage
"Dann Corbit" <DCorbit@connx.com> writes:
> I have a machine with 4 CPU's and 2 gigabytes of physical ram.
> I would like to get PostgreSQL to use as much memory as possible.  I
> can't seem to get PostgreSQL to use more than 100 megabytes or so.

You should not assume that more is necessarily better.

In many practical situations, it's better to leave the majority of RAM
free for kernel disk caching.

			regards, tom lane

Mon, 06 Jan 2003 22:30:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PostgreSQL and memory usage 
Dann Corbit wrote:

> I have a machine with 4 CPU's and 2 gigabytes of physical ram.
>  
> I would like to get PostgreSQL to use as much memory as possible.  I 
> can't seem to get PostgreSQL to use more than 100 megabytes or so.
>  
> How can I optimize the use of PostgreSQL to get the maximum throughput 
> in a configuration like that?
>  
> Are there any memory usage/tuning documents I can read?
>  

I'm not sure if there is a document, but there are some things you can do.

First, size the shared memory pool. I've been told by Tom that too much 
is actually slower, but making it MUCH bigger than the default does help 
a lot.

shared_buffer=2048
(Maybe larger,  experiment)

Sort memory, this is useful for large queries that do sorting, it is 
allocated as needed on a per process basis. If you run large queries 
that sort, this can speed you up instead of sorting to disk.

sort_mem=16384


OK, lastly, do not dispare if PostgreSQL seems not to be using as much 
memory as is in your system. Don't forget the OS disk cache is important 
too.












Dann Corbit wrote:

Message


I have
a machine  with 4 CPU's and 2 gigabytes of physical ram.
 
I would
like to get  PostgreSQL to use as much memory as possible.  I can't seem
to get  PostgreSQL to use more than 100 megabytes or so.
 
How can
I optimize  the use of PostgreSQL to get the maximum throughput in a configuration
like  that?
 
Are there
any memory  usage/tuning documents I can read?
 

I'm not sure if there is a document, but there are some things you can do.

First, size the shared memory pool. I've been told by Tom that too much is
actually slower, but making it MUCH bigger than the default does help a lot.

shared_buffer=2048
(Maybe larger,  experiment)

Sort memory, this is useful for large queries that do sorting, it is allocated
as needed on a per process basis. If you run large queries that sort, this
can speed you up instead of sorting to disk.

sort_mem=16384


OK, lastly, do not dispare if PostgreSQL seems not to be using as much memory
as is in your system. Don't forget the OS disk cache is important too.
Tue, 07 Jan 2003 17:33:18 -0500
mlw <pgsql@mohawksoft.com>
Re: PostgreSQL and memory usage