Re: memory strangeness (fwd)

Curt Sampson <cjs(at)cynic(dot)net> writes:
>> I still cannot set PG's shared_buffers higher than 20000 (160 MB):

> Shared memory pages, IIRC, are locked, meaning that they cannot be
> swapped.

Is that really how it works on *BSD? That's great if so --- it's
exactly what Postgres wants --- but you'll pardon my paranoia about
assuming that any part of the universe works the way I want it to...

There are Unixen that will happily swap shared-memory segments just
like anything else. You don't have to think hard to see that this
would be a dead loss for Postgres' shared disk buffers. For a clean
buffer page, if we don't have room for it we can:
(a) drop it from shared mem, and re-read it from the database file
next time we need it. Cost: one disk read.
(b) swap it out to swap area, and swap it in next time we need it.
Cost: one disk write and one read.
For a dirty page that we no longer have room for, we can:
(a) write it out to the database file and then drop it from shmem.
Cost: one disk write, and possibly a disk read if we need
the page again later.
(b) swap it out to swap area, then sometime later swap it in so
we can write it out to the database file. Cost: two writes
and a read.
So a swappable shared buffer always comes out behind.

The prospect of this effect is one of the reasons that I discourage
people from setting shared_buffers really high. If their kernel
will let them set shared_buffers to a very large fraction of RAM,
it probably means that the kernel is willing to swap shared memory,
and so they are going to end up behind not ahead.

What's more, exactly the same analysis applies to other chunks of
memory that the kernel might choose to swap out, if it's forced to
because it's under memory pressure due to PG shared_buffers chewing
too much of system RAM. Unless your swap disk is way faster than
your database disk, it's never a win to swap instead of sending
pages to their final resting place on the database disk.

Bottom line: you don't ever want to have PG shmem large enough that
it pushes the kernel into swapping. Even if the kernel will lock
shmem into RAM, you can still lose due to swapping other stuff.
So I'm pretty conservative about how much of physical RAM to assign
to shared buffers.

regards, tom lane