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

Re: tuning questions

From: Jack Coates <jack(at)lyris(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: josh(at)agliodbs(dot)com,pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: tuning questions
Date: 2003-12-05 00:32:06
Message-ID: 1070584326.18838.235.camel@cletus.lyris.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Thu, 2003-12-04 at 15:47, Richard Huxton wrote:
> On Thursday 04 December 2003 23:16, Jack Coates wrote:
> >
> > > > effective_cache_size = 10000
> > >
> > > This is way the heck too low.  it's supposed to be the size of all
> > > available RAM; I'd set it to 2GB*65% as a start.
> >
> > This makes a little bit of difference. I set it to 65% (15869 pages).
> 
> That's still only about 127MB (15869 * 8KB).

yeah, missed the final digit when I copied it into the postgresql.conf
:-( Just reloaded with 158691 pages.
> 
> > Now we have some real disk IO:
> >    procs                      memory    swap          io
> > system         cpu
> >  r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us
> > sy  id
> >  0  3  1   2804  10740  40808 1899856   0   0 26624     0  941  4144
> 
> According to this your cache is currently 1,899,856 KB which in 8KB blocks is 
> 237,482 - be frugal and say effective_cache_size = 200000 (or even 150000 if 
> the trace above isn't typical).

d'oh, just realized what you're telling me here. /me smacks forehead.
Let's try effective_cache of 183105... (75%). Starting both servers,
waiting for big fetch to start, and...

   procs                      memory    swap          io    
system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us 
sy  id
 0  0  0   2800  11920  40532 1906516   0   0     0     0  521     8  
0   0 100
 0  1  0   2800  11920  40532 1906440   0   0   356    52  611   113  
1   3  97
 0  1  0   2800  11920  40532 1906424   0   0 20604     0  897   808  
1  18  81
 0  1  0   2800  11920  40532 1906400   0   0 26112     0  927   820  
1  13  87
 0  1  0   2800  11920  40532 1906384   0   0 26112     0  923   812  
1  12  87
 0  1  0   2800  11920  40532 1906372   0   0 24592     0  921   805  
1  13  87
 0  1  0   2800  11920  40532 1906368   0   0  3248    48  961  1209  
0   4  96
 0  1  0   2800  11920  40532 1906368   0   0  2600     0  845  1631  
0   2  98
 0  1  0   2800  11920  40532 1906364   0   0  2728     0  871  1714  
0   2  98

better in vmstat... but the query doesn't work any better unfortunately.

The frustrating thing is, we also have a UP P3-500 with 512M RAM and two
IDE drives with the same PG install which is doing okay with this load
-- still half the speed of MS-SQL2K, but usable. I'm at a loss.
-- 
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack(at)lyris(dot)com
"Interoperability is the keyword, uniformity is a dead end."
				--Olivier Fourdan



In response to

Responses

pgsql-performance by date

Next:From: Ivar ZaransDate: 2003-12-05 01:45:14
Subject: Re: Slow UPADTE, compared to INSERT
Previous:From: Richard HuxtonDate: 2003-12-04 23:47:54
Subject: Re: tuning questions

pgsql-hackers by date

Next:From: Eric SoroosDate: 2003-12-05 04:52:22
Subject: Re: tuning questions
Previous:From: Richard HuxtonDate: 2003-12-04 23:47:54
Subject: Re: tuning questions

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