Re: tuning questions - Mailing list pgsql-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@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
                --Olivier Fourdan