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

Re: optimizing selects on time-series data in Pg

From: Aditya <aditya(at)grot(dot)org>
To: sfpug(at)postgresql(dot)org
Subject: Re: optimizing selects on time-series data in Pg
Date: 2003-08-01 19:31:13
Message-ID: 20030801193113.GA29677@mighty.grot.org (view raw or flat)
Thread:
Lists: sfpug
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

In response to

sfpug by date

Next:From: Stephan SzaboDate: 2003-08-05 19:25:24
Subject: Re: Next Meeting -- Buca di Beppo Wed. Aug. 6
Previous:From: AdityaDate: 2003-08-01 19:29:24
Subject: Re: optimizing selects on time-series data in Pg

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