Performance tests I did with FreeBSD, Linux and PostgreSQL

[Sean Chittenden]

> > > 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 wonder if it would benefit us to do some tweaks to Postgres to
> tune the pgsql block size to the file system it's running on, and
> get it integrated back into the distribution....  As disks get
> bigger, a 16k block size is going to become more common, so this
> won't just hit FreeBSD, I expect.

Hrm, that sounds like a good idea to me.  The appropriate patch is
included (src/include/pg_config_manual.h).  I'll bounce this by the
PostgreSQL guys to see what their reaction is, but I'm inclined to
include this patch in the postgresql-devel port for now, and if things
go well there, possibly in the main postgresql7 port (pending
feedback).  I haven't ever played with this setting before and would
welcome feedback from anyone who has (rergardless of their
results). -sc

PS You have to re-initdb/reload your data after applying this patch
   because the size of data on disk has changed.

-- 
Sean Chittenden
-------------- next part --------------
Index: src/include/pg_config_manual.h
===================================================================
RCS file: /home/ncvs/pgsql/pgsql-server/src/include/pg_config_manual.h,v
retrieving revision 1.5
diff -u -r1.5 pg_config_manual.h
--- src/include/pg_config_manual.h	4 Aug 2003 00:43:29 -0000	1.5
+++ src/include/pg_config_manual.h	27 Aug 2003 17:40:12 -0000
@@ -23,7 +23,7 @@
  *
  * Changing BLCKSZ requires an initdb.
  */
-#define BLCKSZ	8192
+#define BLCKSZ	16384
 
 /*
  * RELSEG_SIZE is the maximum number of blocks allowed in one disk