Re: Setting Shared Buffers , Effective Cache, Sort Mem

On Thu, 22 Apr 2004, Pallav Kalva wrote:

> Hi
>
> We are in the process of building a new machine for our production
> database. Below you will see some of the harware specs for the machine.
> I need some help with setting these parameters (shared buffers,
> effective cache, sort mem) in the pg_conf file. Also can anyone explain
> the difference between shared buffers and effective cache , how these
> are allocated in the main memory (the docs are not clear on this).
>
> Here are the Hardware details:
> Operating System: Red Hat 9
> Database Ver: Postgres 7.4
> CPU'S : 4
> RAM : 4 gig
> Datafile layout : RAID 1+0
> Transaction log : on different RAID1 Array
> RAID Stripe Size: 8k

Read this first:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

Basically shared buffers are the "play area" for the database backends to
toss data in the air and munge it together. The effective cache size
reflects the approximate amount of space your operating system is using to
buffer Postgresql data. On a dedicated database machine this is about the
same as the size of the kernel buffer shown in top. On a mixed machine,
you'll have to see how much of what data is getting buffered to get a
guesstimate of how much kernel cache is being used for pgsql and how much
for other processes. Then divide that number in bytes by 8192, the
default block size. On a machine with 1.2 gigs of kernel cache, that'd be
about 150,000 blocks.

Buffer sizes from 1000 to 10000 blocks are common. Block sizes from 10000
to 50000 can somtimes increase performance, but those sizes only really
make sense for machines with lots of ram, and very large datasets being
operated on.