From owner-freebsd-database@FreeBSD.ORG Wed Aug 27 10:44:52 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id C433716A4BF; Wed, 27 Aug 2003 10:44:52 -0700 (PDT) Received: from perrin.nxad.com (internal.ext.nxad.com [69.1.70.251]) by mx1.FreeBSD.org (Postfix) with ESMTP id 337A643FCB; Wed, 27 Aug 2003 10:44:49 -0700 (PDT) (envelope-from sean@nxad.com) Received: by perrin.nxad.com (Postfix, from userid 1001) id B27BC21058; Wed, 27 Aug 2003 10:44:48 -0700 (PDT) Date: Wed, 27 Aug 2003 10:44:48 -0700 From: Sean Chittenden To: Robert Watson Message-ID: <20030827174448.GB72378@perrin.nxad.com> References: <20030827060644.GA9321@HAL9000.homeunix.com> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="envbJBWh7q8WU6mo" Content-Disposition: inline In-Reply-To: X-PGP-Key: finger seanc@FreeBSD.org X-PGP-Fingerprint: 3849 3760 1AFE 7B17 11A0 83A6 DD99 E31F BC84 B341 X-Web-Homepage: http://sean.chittenden.org/ User-Agent: Mutt/1.5.4i cc: freebsd-database@freebsd.org cc: David Schultz cc: freebsd-performance@FreeBSD.ORG cc: Bill Moran Subject: Re: Performance tests I did with FreeBSD, Linux and PostgreSQL X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 27 Aug 2003 17:44:52 -0000 --envbJBWh7q8WU6mo Content-Type: text/plain; charset=us-ascii Content-Disposition: inline > > > 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 --envbJBWh7q8WU6mo Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=patch 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 --envbJBWh7q8WU6mo--