[Babase] Re: PG server shared_buffers

Karl O. Pinc babase@www.eco.princeton.edu
Wed, 13 Oct 2004 13:57:05 -0500


Date: Wed, 13 Oct 2004 11:54:44 -0500
From: "Karl O. Pinc" <kop@meme.com>
To: Hunter Matthews <thm@duke.edu>
Cc: babase@bwf-master.princton.edu
Subject: Re: PG server shared_buffers


On 2004.10.13 10:00 Hunter Matthews wrote:
> I'll review this Karl, but
> 
> 1) thats also the departmental web server

(I don't pay attention, does this really use much

> 2) thats also the aces database server, which is a pig

Databases are pigs.  That's the point.  < 450MB of RAM
does not sound like an excessive request.

> 3) we've got an influx of cgi users of late I have to work around.
> 
> Let me review some of the other ram uses and dig around and see if
> I can upgrade that to something bigger.

Anything you can do right now would help a lot as now
is when we need to run the conversion program,
which is the big pig.  Just giving me 10MB  made the
first 0.3% of the conversion happen almost instantly,
before it slowed to it's usual crawl.

I can't believe that me hosing the disks is doing the
rest of the applications on the box any good.  You might get
better overall performance by granting babase the
RAM and then living with whatever swapping occurs.
That would at least let the conversion finish quickly,
as I'm sure load varies and there wouldn't be swapping
all the time.

> 
> 
> On Wed, 2004-10-13 at 01:21, Karl O. Pinc wrote:
> > I see you've set shared_buffers to 1280.
> >
> > That can't be right.
> >
> > Do you really have only 10MB of RAM to devote
> > to the database?  I'd have thought you'd have
> > 10 or 50 times that at least.
> >
> > This is where the db caches it's disk pages,
> > you want as much RAM allocated to this as is
> > possible without swapping, leaving aside some
> > kernel disk buffers for write-ahead disk caching
> > (wal_buffers) and some RAM for pg sort operations.
> >
> > See:
> >
> http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node3.html
> >
> > So, you also need to set effective_cache_size to something
> > more than shared_buffers, basically to total RAM minus
> > all RAM used by other programs on the system minus RAM
> > pg is expected to use for sorting.
> >
> > Ah, here's a good reference:
> >
> http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node8.html
> >
> > "As a start for tuning, use 25% of RAM for cache size, and 2-4% for
> > sort size. Increase if no swapping, and decrease to prevent
> swapping.
> > Of course, if the frequently accessed tables already fit in the
> cache,
> > continuing to increase the cache size no longer dramatically
> improves
> > performance."
> >
> > I suspect we can do much better than 25% if the server's devoted
> > to running the database.
> >
> > I strongly suspect you will need to increase
> > the kernel limits, at least SHMMAX.  See:
> > http://www.postgresql.org/docs/7.3/static/kernel-resources.html
> >
> > See also:
> >
> http://archives.postgresql.org/pgsql-performance/2004-10/msg00068.php
> >
> > I'm sure you want at least 10MB for sort_mem.  Let's start with that
> > sort_mem=10240
> > (its in 1K units)
> > Or go by the above guideline as I've no clue how much RAM you've
> > got.  Sort_mem is per user, pretty much.   You don't have so
> > many db users so much RAM can
> > go to shared_buffers because there's little concurrent activity
> > using up sort_mem units of RAM.
> >
> > For our conversion I'd like to increase wal_buffers and
> > checkpoint_segments to anyhow 8388608 (16MB assuming 2K
> > Linux kernel page sizes -- I forget) and 24 respectively.
> > But this is a lesser tweak and should be reverted anyhow
> > when we're done converting.
> >
> > (Wal_buffers does require pg be restarted.)
> >
> > http://www.postgresql.org/docs/7.3/static/wal-configuration.html
> >
> > (Sure you don't want to reconsider that vmstat?  ;-)
> >
> > I don't suppose the box has multiple spindles?
> >
> > Karl <kop@meme.com>
> > Free Software:  "You don't pay back, you pay forward."
> >                   -- Robert A. Heinlein
> --
> Hunter Matthews                          Unix / Network Administrator
> Office: BioScience 145/244               Duke Univ. Biology Department
> Key: F0F88438 / FFB5 34C0 B350 99A4 BB02  9779 A5DB 8B09 F0F8 8438
> Never take candy from strangers. Especially on the internet.
> 
> 

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                   -- Robert A. Heinlein