On Mon, 2005-10-31 at 09:35 -0500, Tom Lane wrote: > Simon Riggs <simon@xxxxxxxxxxxxxxx> writes: > > On Mon, 2005-10-31 at 14:14 +0100, Martijn van Oosterhout wrote: > >> On Mon, Oct 31, 2005 at 12:16:59PM +0000, Simon Riggs wrote: > >>> I'm not sure we have any good tests of that either way, do we? I'm not > >>> certain why we would trust OS cache any more than we could trust the > >>> shared buffers. But setting it too high would probably overuse backend > >>> memory for most variable query workloads. > >> > >> Well, it comes down to a thought experiment. Any disk blocks you have in > >> the shared buffers will also be in the system cache. > > > Each have different and independent cache replacement... > > The real point is that RAM dedicated to shared buffers can't be used for > anything else [1], whereas letting the kernel manage it gives you some > flexibility (for instance, to deal with transient large memory demands > by individual backends, or from stuff unrelated to Postgres). A system > configured to give most of RAM to shared buffers might look good on > sufficiently narrow test cases, but its performance will be horribly > brittle: it will go into swap thrashing on any small provocation. The > extra 50usec or whatever to get stuff from a kernel disk buffer instead > of our own shared buffer is a good tradeoff to get flexibility in the > amount of stuff actually buffered at any one instant. Agreed. But that is an argument in favour of more easily controllable server memory management, not a definitive argument against setting shared_ buffers higher. > [1] unless you are on a platform where the kernel doesn't think SysV > shared memory should be locked in RAM. In that case, what you have is a > large arena that is subject to being swapped out ... and a disk buffer > that's been swapped to disk is demonstrably worse than no buffer at all. > (Hint: count the I/Os involved, especially when the page is dirty.) This is a disaster for any database, not just PostgreSQL. But most other DBMS do something about this, for example on Linux, Solaris, HP/UX, AIX a certain orange DBMS provides additional support for making shared memory non-swappable. Have other people used lock_sga = true in Oracle? Or do we think this is a benchmark gimmic that should never be used in production? We would need to issue a shmctl() with SHM_LOCK, which requires enabling the CAP_IPC_LOCK capability. Best Regards, Simon Riggs ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
