Re: optimizing selects on time-series data in Pg

On Mon, Aug 11, 2003 at 08:50:32PM -0700, Sean Chittenden wrote:
> > > shared_buffers = 128 # 2*max_connections, min 16 (default 64)
> > > ...
> > > #effective_cache_size = 1000 # default in 8k pages
> >
> > You could certainly stand to raise these, unless your system is low
> > on RAM ...
>
> Using /bin/sh, plop the following in to determine your
> effective_cache_size:
>
> echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))"

Thanks for that -- it shows:

effective_cache_size = 7752

when I run it. That's a third smaller than what Josh recommended:

set effective_cache_size to the expected available kernel cache, say 192mb =
24576

> > > PIII-800/512MB RAM running FreeBSD 4-STABLE with the database data
> > > files NFS mounted via a 100Mbps/full-duplex private network from a
> > > lightly-loaded Network Appliance F87 fileserver (I'm pretty
> > > certain that we're not IO bound).
>
> FWIW, NFS + DB server is a bad idea for file locking reasons. -sc

even if there is only one database reader/writer? I've been using this setup
for a couple of years now without any untoward effects...

Thanks,
Adi