Re: [PERFORM] PostgreSQL and memory usage - Mailing list pgsql-general

To put this usage of shared buffers in perspective would you mind kindly
let us know your total amount of system ram?  Without hearing what
percentage of memory used as shared buffers (assuming is the primary
application being using here)

I have always taken the 'more is better' approach with shared buffers but
would like to know what in terms of percentages other people are using.  I
have been using 50% of system ram (2 out of 4 gigs) for shared buffers
(and corresponding shmmax values) and it has been working great.  I
haven't tweaked the kernel yet to get more than 2 gigs shmmax so I can't
speak for a setup using over 50%.  I've been using between 256 and 512
megs sort memory which sounds like a little much from what I'm hearing
here.

Thanks

Fred

>
> Hi Dann, I took hackers out of the list as this isn't really a hacking
> issue, but I added in performance as this definitely applies there.
>
> There are generally two areas of a database server you have to
> reconfigure  to use that extra memory.  The first is the kernel's shared
> memory  settings.
>
> On a linux box that has sysconf installed this is quite easy.  If it
> isn't  installed, install it, as it's much easier to manipulate your
> kernel's  settings using sysctl than it is with editing rc.local.
>
> First, get root.  Then, use 'sysctl -a|grep shm' to get a list of all
> the  shared memory settings handled by sysctl.
>
> On a default redhat install, we'll get something like this:
>
> kernel.shmmni = 4096
> kernel.shmall = 2097152
> kernel.shmmax = 33554432
>
> On my bigger box, it's been setup to have this:
>
> kernel.shmmni = 4096
> kernel.shmall = 32000000
> kernel.shmmax = 256000000
>
> To make changes that stick around, edit the /etc/sysctl.conf file to
> have  lines that look kinda like those above.  To make the changes to
> the  /etc/sysctl.conf file take effect, use 'sysctl -p'.
>
> Next, as the postgres user, edit $PGDATA/postgresql.conf and increase
> the  number of shared buffers.  On most postgresql installations this
> number is  multiplied by 8k to get the amount of ram being allocated,
> since
> postgresql allocates share buffers in blocks the same size as what it
> uses  on the dataset.  To allocate 256 Megs of buffers (that's what I
> use, seems  like a nice large chunk, but doesn't starve my other
> processes or system  file cache) set it to 32768.
>
> Be careful how big you make your sort size.  I haven't seen a great
> increase in speed on anything over 8 or 16 megs, while memory usage can
> skyrocket under heavy parallel load with lots of sorts, since sort
> memory  is PER SORT maximum.
>
> Then do the old pg_ctl reload and you should be cooking with gas.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly