pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Fred Moyer <fred@digicamp.com>
To: scott.marlowe@ihs.com
Cc: DCorbit@connx.com
Cc: tgl@sss.pgh.pa.us
Cc: pgsql-general@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL and memory usage
Date: Tue, 7 Jan 2003 11:46:22 -0800 (PST)
Message-ID: <36045.168.103.211.137.1041968782.squirrel@mail.digicamp.com> (raw)
In-Reply-To: <Pine.LNX.4.33.0301070943390.30228-100000@css120.ihs.com>
References: <D90A5A6C612A39408103E6ECDD77B8294CD794@voyager.corporate.connx.com>
	<Pine.LNX.4.33.0301070943390.30228-100000@css120.ihs.com>

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







view thread (5+ messages)  latest in thread

Message-ID: <36045.168.103.211.137.1041968782.squirrel@mail.digicamp.com>
Permalink:  ../36045.168.103.211.137.1041968782.squirrel@mail.digicamp.com/
Also on:    postgresql.org/message-id/36045.168.103.211.137.1041968782.squirrel@mail.digicamp.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: fred@digicamp.com, scott.marlowe@ihs.com, DCorbit@connx.com, tgl@sss.pgh.pa.us, pgsql-general@postgresql.org
  Subject: Re: [PERFORM] PostgreSQL and memory usage
  In-Reply-To: <36045.168.103.211.137.1041968782.squirrel@mail.digicamp.com>
  References: <D90A5A6C612A39408103E6ECDD77B8294CD794@voyager.corporate.connx.com> <Pine.LNX.4.33.0301070943390.30228-100000@css120.ihs.com> <36045.168.103.211.137.1041968782.squirrel@mail.digicamp.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox