Performance tests I did with FreeBSD, Linux and PostgreSQL

[David Schultz]

On Tue, Aug 26, 2003, Bill Moran wrote:
> Like all benchmarks, I doubt these are perfect (or even close) examples
> of real-world use.
> 
> However, in the hopes that they will be useful for improving FreeBSD, I
> present them to the community.
> 
> http://www.potentialtech.com/wmoran/postgresql.php
> 
> Intelligent comments are welcome.

Well, a little trussing revealed that pgsql uses a block size of
8K, which can be a significant pessimization when the filesystem
block size is 16K, which is the default in FreeBSD!  ext2fs uses
4K blocks and reiserfs uses extents, so those filesystems would
not suffer from that problem.  You might consider trying your test
on a UFS filesystem with 8K blocks, or you could tune pgsql to use
a different block size.  (For pgsql, I think the appropriate
tunable is BLCKSZ in include/pg_config.h.  You'll have to
recompile it and create a new database.)

I used to think that the lack of fdatasync() in FreeBSD would
significantly pessimize databases such as pgsql, but after running
your script, I discovered that the fsync() overhead was only about
1% of the total execution time.  Maybe that would be more
significant if you tried to execute lots of little transactions,
rather than a few large ones.  That kind of workload is probably
more common these days anyway.

As previously mentioned, it would also be interesting to see what
you get out of FreeBSD 4.8+softupdates, just in case the
performance problems you're seeing are a result of known (or
unknown) regressions in 5.X.  You might also consider running your
results by the pgsql people, since they understand how the
database works. ;-)