Skip site navigation (1) Skip section navigation (2)

Re: Setting Shared Buffers , Effective Cache, Sort Mem

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Pallav Kalva <pkalva(at)deg(dot)cc>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Setting Shared Buffers , Effective Cache, Sort Mem
Date: 2004-04-22 21:16:41
Message-ID: Pine.LNX.4.33.0404221510180.24882-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.


In response to

pgsql-performance by date

Next:From: Magnus HaganderDate: 2004-04-22 21:21:33
Subject: Re: Wierd context-switching issue on Xeon patch for 7.4.1
Previous:From: Tom LaneDate: 2004-04-22 19:22:51
Subject: Re: [pgsql-advocacy] MySQL vs PG TPC-H benchmarks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group