[Babase] PG server shared_buffers

Karl O. Pinc babase@www.eco.princeton.edu
Wed, 13 Oct 2004 00:21:45 -0500


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