Re: optimizing selects on time-series data in Pg

On Fri, Aug 01, 2003 at 11:42:22AM -0700, Josh Berkus wrote:
> > last pid: 36063; load averages: 0.38, 0.56, 0.39 up 453+17:25:41 11:25:25
> > 68 processes: 1 running, 65 sleeping, 2 stopped
> > CPU states: 8.9% user, 0.0% nice, 16.3% system, 16.0% interrupt, 58.8%
> > idle Mem: 95M Active, 297M Inact, 79M Wired, 25M Cache, 61M Buf, 2456K Free
> > Swap: 1024M Total, 388K Used, 1023M Free
>
> From the look of things, NFS isn't actually eating a lot of memory. So you
> actually have a relatively clear field for Postgres. For the first test,
> let's assume that Postgres and the Kernel cache will have 256mb available
> most of the time:
>
> set shared_buffers to 8% of the kernel cache: 256*1024/8*0.08 = about 2600
> set effective_cache_size to the expected available kernel cache, say 192mb =
> 24576
>
> Also, I'd lower your random_page_cost to 2.5 for your relatively unburdened
> CPU.
>
> These settings should make Postgres more likely to cache a large portion of
> your table in memory.

I'll try these this weekend and let you know the results.

Thanks,
Adi