What is RSS?
Re: Setting Shared Buffers , Effective Cache, Sort Mem
Hello Guest		   
  • Login
• Register…
• Start blog		   • Who, Where, When
• What can I do?
• What to Read?		   • Polls
• Avatars
• Interests		   • Cities and Countries
• Random blog
• Users search		   • Search
• Games
• Tests
• QAIX		   • Сообщества
• Talxy Chat
• Horoscope
• Online		  
Зарегистрируйся!
QAIX > PostgreSQL database development > Re: Setting Shared Buffers , Effective Cache, Sort Mem 24 April 2004 18:37:42
  Recent blog posts: 
Free iPod nano raffle for…
Aimersoft 2008 Christmas promotion: "Free iPod nano raffle for Chrismas" and "Buy 1 Get…

How to convert Rhapsody to…
Music purchased from Rhapsody is under DRM protection, which stops customers from playing the downloaded&helli­p;

PS3 firmware update adds full…
Eric Lempel, Sony's Director of PlayStation Network Operations, has posted on the official PlayStation 3…

Nintendo makes $6 profit on…
According to Macquarie Securities analyst David Gibson, Nintendo makes $6 USD operating profit on each…

Maintenance in winter
Skin for winter very easy to do, if does not supplement the moisture in time, like persimmons, flat and…

'Jasper' Xbox 360s finally…
'Jasper' Xbox 360s finally available According to a flooding of posts over at xbox-scene, it appears…

How to transfer Napster music…
Music purchased from Napster is under DRM protection, which stops customers from playing the downloaded&helli­p;

The song my brother taught me
Don't let your heart be hardened Don't let your love grow cold May it always stay so childlike May it…

My brother taught me this…
God grant me the serenity to accept the things I cannot change; courage to change the things I can;…

baz-baz-baz-baz pam-parabaram…
baz-baz-baz-baz pam-parabaram baz-baz-baz-baz pam-parabaram baz-baz-baz-baz pam-parabaram-baz ba-ra-ba…

  They have birthday today: 
xiongchong
henna diah
  Forums:   
    topic feeds

• QAIX  all topics, blogs and comments

• PHP web-programming

• Perl web-programming

• ASP web-programming

• Visual Basic Programming

• .Net Development

• C/C++ Programming

• Java Programming

• Javascript web-programming

• Apache HTTP Server

• Web-design

• Web-hosting, dedicated, colocation

• HTML web-programming

• XML development

• General Web-programming

• Content Management Systems

• ColdFusion

• Search Engine Optimization

• Hardware

• Software, Computer Help

• MySQL database development

• PostgreSQL database development

• MS Access database development

• Oracle database development

• MS SQL database development

• Informix database development

• Buy / Sell

• Open discussion

• Anonymous
  Discuss: 
plpgsql - Insert from a record…
Dear Postgresql experts, I'm writing a simple plpgsql function that selects a row from a table…

calling a pl/pgsql function…
Hello, i am tryin to pass an array like argument to a pl/pgsql function but i can not find the correct…

PgDatabase, on heap…
I am having a problem constructing a PgDatabase object. If I create the object on the stack (as a var…

Backup and restore through JDBC
Hello List I am trying to develop, a API to carry through backup and restore through JDBC. I think that…
  Recent forum topics: 
why infix:<div>:(Int, Int…
HaloO, I realized from the typed literal thread that S03 now explicitly states that div on two Ints…

[rt.cpan.org #41329] VMS…
<URL: http://rt.cpan.org/­Ticket/Display.html?­id=41329 > Thanks, applied

how to write literals of some…
Hello, I'm wondering how to write anonymous value literals of some Perl 6 basic types, and I didn't…
  Recent forum comments:
RE: Updating A Portion Of A Site
Thanks! I remembered just before I came back to check. It's amazing the things you forget. Thanks…

RE: Updating A Portion Of A Site
Use a FrontPage include file - see Help on Included Content. Or, if all pages are asp or PHP, use a…

RE: Index Files
What they are telling you is the home page of your site (before you publish it) must be named index.htm…

RE: Website diifers from local copy
I see it as a 40x68 icon (clear your cache) - as for the position check w/ Google API help Neither issue…

RE: Forced submit
"Ellie" <ellymay999nospam@n­ospamnetzero.com> wrote in message…

RE: access denied
IUSR account doesn't have access to cmd.exe by default (assuming you are using anonymous…

RE: access denied
Hey guys, I'm totally out of my league here, I don't even know how I got stuck with this problem, it's…

RE: Forced submit
Ellie wrote on 05 sep 2008 in microsoft.public.in­etserver.asp.general­: You cannot…

RE: [patch@34995] vms.c - Memory…
On Wed, Dec 3, 2008 at 7:38 PM, John E. Malmberg <wb8tyw@qsl.net> wrote: Thanks, applied as…

RE: why infix:<div>:(Int, Int…
On Thu, Dec 4, 2008 at 3:26 PM, David Green <david.green@telus.­net> wrote: Using…
  Moderators:
 Administrator
 Protorioris

Re: Setting Shared Buffers , Effective Cache, Sort Mem

Scott.Marlowe 24 April 2004 18:37:42
 On Thu, 22 Apr 2004, Pallav Kalva wrote:
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/GeneralB­its/Tidbits/perf.htm­l

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.


-------------------­--------(end of broadcast)---------­------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHer­e" to majordomo@postgresq­l.org)

Add comment
Ron Mayer 24 April 2004 01:10:20 [ permanent link ]
 
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.

-------------------­--------(end of broadcast)---------­------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresq­l.org

Add comment
Tom Lane 24 April 2004 05:50:14 [ permanent link ]
 Ron Mayer <rm_pg@cheapcomplex­devices.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

-------------------­--------(end of broadcast)---------­------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgres­ql.org/docs/faqs/FAQ­.html

Add comment
Josh Berkus 24 April 2004 18:37:42 [ permanent link ]
 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

-------------------­--------(end of broadcast)---------­------------------
TIP 4: Don't 'kill -9' the postmaster

Add comment
 

Add new comment

As:
Login:  Password:  
 
 
  
 
Пожалуйста, относитесь к собеседникам уважительно, не используйте нецензурные слова, не злоупотребляйте заглавными буквами, не публикуйте рекламу и объявления о купле/продаже, а также материалы нарушающие сетевой этикет или УК РФ.

QAIX > PostgreSQL database development > Re: Setting Shared Buffers , Effective Cache, Sort Mem 24 April 2004 18:37:42
see also:
AMD starts a rare reverse price-war…
Intel manager calls Pentium-D a donkey
Samsung countersuits Rambus
пройди тесты:
see also:
SOFTWARE POSITIONS WITH eFulgent
Cyber Data Recorder Client/Server V1.5
  Copyright © 2001—2008 QAIX
Idea: Miсhael Monashev
Помощь и задать вопросы можно в сообществе support.qaix.com.
Сообщения об ошибках оставляем в сообществе bugs.qaix.com.
Предложения и комментарии пишем в сообществе suggest.qaix.com.
Информация для родителей.
Write us at:
If you would like to report an abuse of our service, such as a spam message, please .