Re: tuning questions

On Fri, 2003-12-05 at 17:22, Jack Coates wrote:
...
> That's it, I'm throwing out this whole test series and starting over
> with different hardware. Database server is now a dual 2GHz Xeon with
> 2GB RAM & 2940UW SCSI, OS and PG's logs on 36G drive, PG data on 9GB
> drive. Data is importing now and I'll restart the tests tonight.

Sorry to reply at myself, but thought I'd note that the performance is
practically unchanged by moving to better hardware and separating logs
and data onto different spindles. Although the disks are twice as fast
by hdparm -Tt, their behavior as shown by iostat and vmstat is little
different between dual and dev (single P4-2GHz/512MB/(2)IDE drives).
Dual is moderately faster than my first, IDE-based testbed (about 8%),
but still only 30% as fast as the low-powered dev.

I've been running vacuumdb --analyze and/or vaccuumdb --full between
each config change, and I also let the job run all weekend. Saturday it
got --analyze every three hours or so, Sunday it got --analyze once in
the morning. None of these vacuumdb's are making any difference.

Theories at this point, in no particular order:

a) major differences between my 7.3.4 from source (compiled with no
options) and dev's 7.3.2-1PGDG RPMs. Looking at the spec file doesn't
reveal anything glaring to me, but is there something I'm missing?

b) major differences between my kernel 2.4.18-14smp (RH8) and dev's
kernel 2.4.18-3 (RH7.3).

c) phase of the moon.

While SQL optimization is likely to improve performance across the
board, it doesn't explain the differences between these two systems and
I'd like to avoid it as a theory until the fast box can perform as well
as the slow box.

Any ideas? Thanks in advance,
--
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