Re: Proper Sizing of Shared Buffer Cache

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_pgsql_book/hw_performance/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

"Don Kelloway" <dkelloway(at)commodon(dot)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_pgsql_book/hw_performance/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.

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/GeneralBits/Tidbits/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