On Thu, 26 Feb 2004, Dror Matalon wrote:
> On Thu, Feb 26, 2004 at 11:55:31AM -0700, scott.marlowe wrote:
> > On Thu, 26 Feb 2004, Dror Matalon wrote:
> >
> > > Hi,
> > >
> > > We have postgres running on freebsd 4.9 with 2 Gigs of memory. As per
> > > repeated advice on the mailing lists we configured effective_cache_size
> > > = 25520 which you get by doing `sysctl -n vfs.hibufspace` / 8192
> > >
> > > Which results in using 200Megs for disk caching.
> > >
> > > Is there a reason not to increase the hibufspace beyond the 200 megs and
> > > provide a bigger cache to postgres? I looked both on the postgres and
> > > freebsd mailing lists and couldn't find a good answer to this.
> >
> > Actually, I think you're confusing effective_cache_size with
> > shared_buffers.
>
> No, I'm not.
OK, sorry, I wasn't sure which you meant.
> > effective_cache_size changes no cache settings for postgresql, it simply
> > acts as a hint to the planner on about how much of the dataset your OS /
> > Kernel / Disk cache can hold.
>
> I understand that. The question is why have the OS, in this case FreeBsd
> use only 200 Megs for disk cache and not more. Why not double the
> vfs.hibufspace to 418119680 and double the effective_cache_size to 51040.
Doesn't the kernel just use the spare memory to buffer anyway?
I'd say if you got 2 megs memory and nothing else on the box, give a big
chunk (1 gig or so) to the kernel to manage. Unless large kernel caches
cause some issues in FreeBSD.
> > Making it bigger only tells the query planny it's more likely the data
> > it's looking for will be in cache.
> >
> > shared_buffers, OTOH, sets the amount of cache that postgresql uses. It's
> > generall considered that 256 Megs or 1/4 of memory, whichever is LESS, is
> > a good setting for production database servers.
> >
>
> Actually last I looked, I thought that the recommended max shared
> buffers was 10,000, 80MB, even on machines with large amounts of memory.
It really depends on what you're doing. For loads involving very large
data sets, up to 256 Megs has resulted in improvements, but anything after
that has only had advantages in very limited types of applications.
