By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

Proper Sizing of Shared Buffer Cache

P: n/a
I'm a first-time user with PostgreSQL so please forgive my ignorance.

I've purchased (and read) Practical PostgreSQL (O'Reilly) and PostgreSQL
Essential Reference (New Riders). So far, so good. I think learning
PostgreSQL will not be as difficult as I thought it would be. I've also
been googling for the last few days, but I have a question in regards to
determining the proper size of the buffer cache parameter.

http://www.postgresql.org/docs/aw_pg...nce/node6.html

The above webpage states that ideally, the POSTGRESQL shared buffer cache
will be:

- Large enough to hold most commonly-accessed tables
- Small enough to avoid swap pagein activity

My question is how do you determine how large the most commonly-accessed
table(s) are? I thought maybe I could view the pg_stat_database, but I
don't think that provides the answer I'm seeking. Can someone point me in
the right direction? It would be very much appreciated.

Best regards,
Don Kelloway
Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Don Kelloway" <dk*******@commodon.com> writes:
I'm a first-time user with PostgreSQL so please forgive my ignorance.

I've purchased (and read) Practical PostgreSQL (O'Reilly) and
PostgreSQL Essential Reference (New Riders). So far, so good. I
think learning PostgreSQL will not be as difficult as I thought it
would be. I've also been googling for the last few days, but I have
a question in regards to determining the proper size of the buffer
cache parameter.

http://www.postgresql.org/docs/aw_pg...nce/node6.html

The above webpage states that ideally, the POSTGRESQL shared buffer cache
will be:

- Large enough to hold most commonly-accessed tables
- Small enough to avoid swap pagein activity

My question is how do you determine how large the most
commonly-accessed table(s) are? I thought maybe I could view the
pg_stat_database, but I don't think that provides the answer I'm
seeking. Can someone point me in the right direction? It would be
very much appreciated.


Alas, the slickest book in this regard is Douglas & Douglas (New
Riders), which has a section that can guide you through how PostgreSQL
arranges its filesystem usage, which is kind of what you _really_ need
for this.

Although that may be a bit of a red herring.

The "rule of thumb" is that you should devote about 10% of available
memory (on a dedicated DBMS server, that would presumably be 10% of
the memory on the machine; on a machine doing other things, scale it
down...) to shared buffer cache.

If 10% is much more than 82MB, then you can pretty safely limit
yourself to about 10000-15000 as the # of 8K blocks. There isn't
evidence available to establish that having much more buffer cache
than that is particularly helpful.

The problem with having a larger buffer cache is twofold:

1. It will compete with the OS file cache. Data loaded into the
buffer cache firstly has to be read by the OS, which is therefore
in the OS file cache already. The bigger the buffer cache, the
more redundant cacheing takes place.

2. Backends need to scan through the buffer cache to look for data;
the bigger the cache, the more that scan costs.
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.
Nov 23 '05 #2

P: n/a
Don Kelloway wrote:
I'm a first-time user with PostgreSQL so please forgive my ignorance.

I've purchased (and read) Practical PostgreSQL (O'Reilly) and PostgreSQL
Essential Reference (New Riders). So far, so good. I think learning
PostgreSQL will not be as difficult as I thought it would be. I've also
been googling for the last few days, but I have a question in regards to
determining the proper size of the buffer cache parameter.


Another good reference point is
http://www.varlena.com/varlena/Gener...bits/perf.html

The only real way to find the best value is by testing different values
against actual usage.

There's also a performance mailing list you might find useful. Archive
available on the website.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.