Re: [pgsql-cluster-hackers][performance] fast reads on a busy server

[Willy-Bas Loos <willybas@xxxxxxxxx>]

On Wed, Jun 27, 2012 at 9:34 AM, Hannu Krosing <hannu@xxxxxxxxxxxxxxx> wrote:

Check if you are CPU-bound. On a database which fits fully you may
already be.

Being CPU-bound is my goal.
That makes your answer a yes to me.

Only i'm afraid that this solution is not optimal. 
Because i am stealing more resopurces from the (already busy) rest of the server than necessary. That's because the file-cache will also be filled (partially) with data that this cluster uses, unnecessarily. I'm not going to read from the file cache, because the data will be in the shared_buffers as soon as they have been read from disk.

So then, would it be better to use 80% of memory for the shared buffers of the combined clusters?
I've read that 25% is good and 40% is max because of the file cache, but it doesn't make much sense..

Greg Smith writes (here, page 12):
* PostgreSQL is designed to rely heavily on the operating
system cache, because portable sotware like PostgreSQL can’t
know enough about the filesystem or disk layout to make
optimal decisions about how to read and write files
* The shared buffer cache is really duplicating what the
operating system is already doing: caching popular file blocks
* In many cases, you’ll find exactly the same blocks cached by
both the buffer cache and the OS page cache
* This makes is a bad idea to give PostgreSQL too much
memory to manage

I cannot follow that reasoning completely. Who needs OS level file cache when postgres' shared_buffers is better? The efficiency should go up again after passing 50% of shared buffers, where you would be caching everything twice.
The only problem i see is that work_mem and such will end up in SWAP if there isn't enough memory left over to allocate.

Cheers,

WBL

-- 
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth