Re: was there a change in FreeBSD SHM implementation from

On Thu, 11 Jul 2002, Bruce Momjian wrote:

> Curt, I haven't heard anyone else who agrees with your idea of making
> PostgreSQL shared buffers smaller and kernel cache bigger.

Well, I seem to recall that Tom Lane agrees with me. The archives server
is still generating broken HTML (no </table> tag), though, so it's
difficult for me to find messages.

But I've seen nobody explain just how giving more buffers to postgres
is going to be better than less, in the general case.

> Now, you have potent argument that having 3X of kernel buffer and 1X of
> PostgreSQL buffers is better than having 2X of kernel buffers and 2X of
> PostgreSQL buffers.  However, you are assuming the extra 1X of kernel
> buffer size will be used often _and_ that the copying from kernel buffer to
> PostgreSQL is minimal.

I'm not assuming that copying is minimal. Copying could be very
heavy; it's still much, much, much cheaper than disk I/O.

But yes, I am assuming that the working data set is not significantly
smaller than the amount of memory in your machine. Thank you to the
person who pointed this out to me.

> I understand your idea that the disk I/O is clearly slower than the copy
> from kernel to PostgreSQL buffer, but with the 3X/1X solution, is moving
> more buffers from kernel to PostgreSQL slower than the number of times
> you are doing more I/O in the 2X/2X case.  My guess is that the 2X/2X
> case is the winner, but it is an interesting idea.

I'm working on the basis that you can do several hundred copies of a
block in memory in the time it would take to do a single disk I/O. Do
you disagree? What figure are you using? Why?

Also, note that I am not advocating the very minimal number of buffers;
you do want enough to ensure that, say, a bunch of simultaneous update
requests that touch various data and index pages several times during
the update can have all of those buffers remain in postgres' shared
memory. At a rough go, I'd say off-hand you probably want 30-50 shared
memory buffers per simultanous active query. So given, say, 200
connections, of which 20% will be active simultaneously, 2000 buffers
(16 MB) seems reasonable.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC