pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jack Coates <jack@lyris.com>
To: Richard Huxton <dev@archonet.com>
Cc: josh@agliodbs.com, pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: tuning questions
Date: Thu, 04 Dec 2003 16:32:06 -0800
Message-ID: <1070584326.18838.235.camel@cletus.lyris.com> (raw)
In-Reply-To: <200312042347.54461.dev@archonet.com>
References: <1070553983.6498.52.camel@cletus.lyris.com>
	<200312041324.37889.josh@agliodbs.com>
	<1070579771.18838.187.camel@cletus.lyris.com>
	<200312042347.54461.dev@archonet.com>

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@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
				--Olivier Fourdan





view thread (32+ messages)  latest in thread

Message-ID: <1070584326.18838.235.camel@cletus.lyris.com>
Permalink:  ../1070584326.18838.235.camel@cletus.lyris.com/
Also on:    postgresql.org/message-id/1070584326.18838.235.camel@cletus.lyris.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jack@lyris.com, dev@archonet.com
  Subject: Re: tuning questions
  In-Reply-To: <1070584326.18838.235.camel@cletus.lyris.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox