pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Setting Shared Buffers , Effective Cache, Sort Mem Parameters
7+ messages / 6 participants
[nested] [flat]

* Setting Shared Buffers , Effective Cache, Sort Mem Parameters
@ 2004-04-22 17:51 Pallav Kalva <pkalva@deg.cc>
  2004-04-22 21:16 ` Re: Setting Shared Buffers , Effective Cache, Sort Mem scott.marlowe <scott.marlowe@ihs.com>
  2004-04-22 22:50 ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 2 replies; 7+ messages in thread

From: Pallav Kalva @ 2004-04-22 17:51 UTC (permalink / raw)
  To: pgsql-performance

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


Thanks!
Pallav




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Setting Shared Buffers , Effective Cache, Sort Mem
  2004-04-22 17:51 Setting Shared Buffers , Effective Cache, Sort Mem Parameters Pallav Kalva <pkalva@deg.cc>
@ 2004-04-22 21:16 ` scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 7+ messages in thread

From: scott.marlowe @ 2004-04-22 21:16 UTC (permalink / raw)
  To: Pallav Kalva <pkalva@deg.cc>; +Cc: 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.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters
  2004-04-22 17:51 Setting Shared Buffers , Effective Cache, Sort Mem Parameters Pallav Kalva <pkalva@deg.cc>
@ 2004-04-22 22:50 ` Manfred Koizar <mkoi-pg@aon.at>
  2004-04-23 21:44   ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters Manfred Koizar <mkoi-pg@aon.at>
  1 sibling, 1 reply; 7+ messages in thread

From: Manfred Koizar @ 2004-04-22 22:50 UTC (permalink / raw)
  To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance

On Thu, 22 Apr 2004 13:51:42 -0400, Pallav Kalva <pkalva@deg.cc> wrote:
>I need some help with setting these parameters (shared buffers, 
>effective cache, sort mem) in the pg_conf file.

It really depends on the kind of queries you intend to run, the number
of concurrent active connections, the size of the working set (active
part of the database), what else is running on the machine, and and and
...

Setting shared_buffers to 10000, effective_cache_size to 400000 (80% of
installed RAM), and sort_mem to a few thousand might be a good start.

>  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).

Shared_buffers directly controls how many pages are allocated as
internal cache.  Effective_cache_size doesn't allocate anything, it is
just a hint to the planner how much cache is available on the system
level.

Servus
 Manfred



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters
  2004-04-22 17:51 Setting Shared Buffers , Effective Cache, Sort Mem Parameters Pallav Kalva <pkalva@deg.cc>
  2004-04-22 22:50 ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters Manfred Koizar <mkoi-pg@aon.at>
@ 2004-04-23 21:44   ` Manfred Koizar <mkoi-pg@aon.at>
  2004-04-23 22:10     ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Ron Mayer <rm_pg@cheapcomplexdevices.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Manfred Koizar @ 2004-04-23 21:44 UTC (permalink / raw)
  To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance

On Fri, 23 Apr 2004 10:20:10 -0400, Pallav Kalva <pkalva@deg.cc> wrote:
> the database sizes is around 2- 4 gig and 
>there are 5 of them. this machine is
>     mainly for the databases and nothing is running on them.

Did I understand correctly that you run (or plan to run) five
postmasters?  Is there a special reason that you cannot put all your
tables into one database?

>    setting shared buffers to 10000 allocates (81Mb)  and effective 
>cache to 400000 would be around (3gig)
>    does this means that if all of the 81mb of the shared memory gets 
>allocated it will use rest from the effective
>    cache of (3g-81mb) ?

Simply said, if Postgres wants to access a block, it first looks whether
this block is already in shared buffers which should be the case, if the
block is one of the last 10000 blocks accessed.  Otherwise the block has
to be read in.  If the OS has the block in its cache, reading it is just
a (fast) memory operation, else it involves a (slow) physical disk read.

The number of database pages residing in the OS cache is totally out of
control of Postgres.  Effective_cache_size tells the query planner how
many database pages can be *expected* to be present in the OS cache.

>increasing the shared buffers space to 2g

Setting shared_buffers to half your available memory is the worst thing
you can do.  You would end up caching exactly the same set of blocks in
the internal buffers and in the OS cache, thus effectively making one of
the caches useless.

Better keep shared_buffers low and let the OS do its job.

Servus
 Manfred



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Setting Shared Buffers , Effective Cache, Sort Mem
  2004-04-22 17:51 Setting Shared Buffers , Effective Cache, Sort Mem Parameters Pallav Kalva <pkalva@deg.cc>
  2004-04-22 22:50 ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters Manfred Koizar <mkoi-pg@aon.at>
  2004-04-23 21:44   ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters Manfred Koizar <mkoi-pg@aon.at>
@ 2004-04-23 22:10     ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
  2004-04-24 02:50       ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Ron Mayer @ 2004-04-23 22:10 UTC (permalink / raw)
  To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: Pallav Kalva <pkalva@deg.cc>; pgsql-performance


On Fri, 23 Apr 2004, Manfred Koizar wrote:
>
> Setting shared_buffers to half your available memory is the worst thing
> you can do.  You would end up caching exactly the same set of blocks in
> the internal buffers and in the OS cache, thus effectively making one of
> the caches useless.

One minor detail...  You wouldn't really cache the _exact_ same blocks
because cache-hits in shared-buffers (on the most frequently accessed
pages) would let the OS cache some other pages in it's cache.

But in my experience Manfred's right that there's no benefit and
some penalty to making shared_buffers so large it takes a significant
piece away from the OS's caching.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Setting Shared Buffers , Effective Cache, Sort Mem
  2004-04-22 17:51 Setting Shared Buffers , Effective Cache, Sort Mem Parameters Pallav Kalva <pkalva@deg.cc>
  2004-04-22 22:50 ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters Manfred Koizar <mkoi-pg@aon.at>
  2004-04-23 21:44   ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters Manfred Koizar <mkoi-pg@aon.at>
  2004-04-23 22:10     ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Ron Mayer <rm_pg@cheapcomplexdevices.com>
@ 2004-04-24 02:50       ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-24 15:37         ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2004-04-24 02:50 UTC (permalink / raw)
  To: Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: Manfred Koizar <mkoi-pg@aon.at>; Pallav Kalva <pkalva@deg.cc>; pgsql-performance

Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> [ on setting shared_buffers = half of RAM ]

> One minor detail...  You wouldn't really cache the _exact_ same blocks
> because cache-hits in shared-buffers (on the most frequently accessed
> pages) would let the OS cache some other pages in it's cache.

> But in my experience Manfred's right that there's no benefit and
> some penalty to making shared_buffers so large it takes a significant
> piece away from the OS's caching.

True, it'd probably not be the *exact* worst case.  But it'd be a good
approximation.  In practice you should either bet on the kernel doing
most of the caching (in which case you set shared_buffers pretty low)
or bet on Postgres doing most of the caching (in which case you set
shared_buffers to eat most of RAM).

The conventional wisdom at this point is to bet the first way; no one
has shown performance benefits from setting shared_buffers higher than
the low tens of thousands.  (Most of the mail list traffic on this
predates the existence of pgsql-performance, so check the other list
archives too if you go looking for discussion.)

It's possible that Jan's recent buffer-management improvements will
change the story as of 7.5.  I kinda doubt it myself, but it'd be worth
re-running any experiments you've done when you start working with 7.5.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Setting Shared Buffers , Effective Cache, Sort Mem
  2004-04-22 17:51 Setting Shared Buffers , Effective Cache, Sort Mem Parameters Pallav Kalva <pkalva@deg.cc>
  2004-04-22 22:50 ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters Manfred Koizar <mkoi-pg@aon.at>
  2004-04-23 21:44   ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters Manfred Koizar <mkoi-pg@aon.at>
  2004-04-23 22:10     ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Ron Mayer <rm_pg@cheapcomplexdevices.com>
  2004-04-24 02:50       ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-24 15:37         ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2004-04-24 15:37 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: Manfred Koizar <mkoi-pg@aon.at>; Pallav Kalva <pkalva@deg.cc>; pgsql-performance

Tom,

> It's possible that Jan's recent buffer-management improvements will
> change the story as of 7.5.  I kinda doubt it myself, but it'd be worth
> re-running any experiments you've done when you start working with 7.5.

Yes, Jan has indicated to me that he expects to make much heavier use of 
shared buffers under ARC.    But 7.5 still seems to be too unstable for me to 
test this assertion on a large database.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2004-04-24 15:37 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-22 17:51 Setting Shared Buffers , Effective Cache, Sort Mem Parameters Pallav Kalva <pkalva@deg.cc>
2004-04-22 21:16 ` Re: Setting Shared Buffers , Effective Cache, Sort Mem scott.marlowe <scott.marlowe@ihs.com>
2004-04-22 22:50 ` Manfred Koizar <mkoi-pg@aon.at>
2004-04-23 21:44   ` Manfred Koizar <mkoi-pg@aon.at>
2004-04-23 22:10     ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Ron Mayer <rm_pg@cheapcomplexdevices.com>
2004-04-24 02:50       ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Tom Lane <tgl@sss.pgh.pa.us>
2004-04-24 15:37         ` Re: Setting Shared Buffers , Effective Cache, Sort Mem Josh Berkus <josh@agliodbs.com>

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