Skip site navigation (1) Skip section navigation (2)

Re: optimizing selects on time-series data in Pg

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Aditya <aditya(at)grot(dot)org>, sfpug(at)postgresql(dot)org
Subject: Re: optimizing selects on time-series data in Pg
Date: 2003-08-01 18:42:22
Message-ID: 200308011142.22589.josh@agliodbs.com (view raw or flat)
Thread:
Lists: sfpug
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

In response to

Responses

sfpug by date

Next:From: Stephan SzaboDate: 2003-08-01 18:51:17
Subject: Re: optimizing selects on time-series data in Pg
Previous:From: AdityaDate: 2003-08-01 18:38:57
Subject: Re: optimizing selects on time-series data in Pg

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group