From owner-freebsd-database@FreeBSD.ORG Thu Aug 28 11:16:45 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 6BA4016A4BF; Thu, 28 Aug 2003 11:16:45 -0700 (PDT) Received: from perrin.nxad.com (internal.ext.nxad.com [69.1.70.251]) by mx1.FreeBSD.org (Postfix) with ESMTP id B51B443F85; Thu, 28 Aug 2003 11:16:44 -0700 (PDT) (envelope-from sean@nxad.com) Received: by perrin.nxad.com (Postfix, from userid 1001) id E2D4D21058; Thu, 28 Aug 2003 10:19:55 -0700 (PDT) Date: Thu, 28 Aug 2003 10:19:55 -0700 From: Sean Chittenden To: Robert Watson , Bill Moran , freebsd-database@freebsd.org, freebsd-fs@freebsd.org, freebsd-performance@freebsd.org Message-ID: <20030828171955.GE83759@perrin.nxad.com> References: <3F4D5957.8000204@potentialtech.com> <20030828085947.GA41090@HAL9000.homeunix.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030828085947.GA41090@HAL9000.homeunix.com> 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 Subject: Re: Some additional tests run on my performance testing 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: Thu, 28 Aug 2003 18:16:45 -0000 > > 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. For the sake of eating my own advice and in an attempt to verify the numbers you suggest above, I loaded a DB with 8k and 16K blocks (translation: almost all write activities). With 8K blocks: 15.188u 3.404s 7:12.27 4.2% 209+340k 1251+0io 0pf+0w 14.867u 3.686s 7:32.54 4.0% 201+327k 1252+0io 0pf+0w avg wall clock sec to complete: 442 With 16K blocks: 15.192u 3.312s 6:44.43 4.5% 198+322k 1253+0io 0pf+0w 15.120u 3.330s 6:51.43 4.4% 205+334k 1254+0io 0pf+0w avg wall clock sec to complete: 407 Which is different than what your results suggest, but I'll take the 35sec/8% speedup any day of the week and twice on Sunday. Granted these tests were done on my laptop and were 100% write, I'd expect them to stay about the same across the board. If someone wants to do some good read tests, I'd be interested in those results. -sc -- Sean Chittenden