Re: optimizing selects on time-series data in Pg

Aditya,

> > Mind checking TOP to see how much RAM your other processes on this server
> > eat? That will give us a good baseline for figuring out how much memory
> > to give Postgres.
>
> I do have some stats collection turned on in postgresql.conf, if that makes
> any difference:

No, it just uses a little memory and CPU time.

> 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.

--
Josh Berkus
Aglio Database Solutions
San Francisco