Some additional tests run on my performance testing

[David Schultz]

On Wed, Aug 27, 2003, Robert Watson wrote:
> On Wed, 27 Aug 2003, Bill Moran wrote:
> 
> > Unfortunately (as you'll see) the results were _worse_ than with FreeBSD
> > 5.1. 
> ..
> > ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
> > ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
> > ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
> > ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) falling back to PIO mode

If your drives are using PIO in FreeBSD and DMA under other
operating systems, that would certainly explain the problem.  Are
the drives also in PIO mode under Linux?  Does this message pop up
with 4.8, 5.1, or both?  I understand that the usual cause is bad
cabling.

> Did you look at any of the blocksize-related patches that have been
> floating around? 

I tried his tests on a stock pgsql 7.3.4, twice with an 8K block
filesystem and twice with a 16K block UFS2 filesystem and measured
an improvement of about 4% for the 8K filesystem.  (Take this cum
grano salis though, since this was an informal test and I don't
have enough data to draw a statistically significant conclusion.)
It turns out that the tables in Bill's tests have no indices, so
pgsql winds up doing practically nothing but sequential reads and
sequential writes of entire tables.  A more typical database load
would probably be characterized by mostly random access patterns
and possibly more synchronous writes to the WAL log.