Re: tuning questions

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