From owner-freebsd-database@FreeBSD.ORG Fri Nov 11 09:33:25 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org 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 29E5816A41F for ; Fri, 11 Nov 2005 09:33:25 +0000 (GMT) (envelope-from dryice@hotpop.com) Received: from smtp-out.hotpop.com (smtp-out.hotpop.com [38.113.3.61]) by mx1.FreeBSD.org (Postfix) with ESMTP id C930643D45 for ; Fri, 11 Nov 2005 09:33:24 +0000 (GMT) (envelope-from dryice@hotpop.com) Received: from hotpop.com (kubrick.hotpop.com [38.113.3.105]) by smtp-out.hotpop.com (Postfix) with SMTP id E38E41CCAE0F for ; Fri, 11 Nov 2005 09:33:14 +0000 (UTC) Received: from dryice.3322.org (unknown [221.0.236.221]) by smtp-2.hotpop.com (Postfix) with ESMTP id 73B481C8443B for ; Fri, 11 Nov 2005 09:26:34 +0000 (UTC) To: freebsd-database@freebsd.org From: Dryice Liu Date: Fri, 11 Nov 2005 17:26:05 +0800 Message-ID: <8664qzcz82.fsf@dryice.3322.org> User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/23.0.0 (berkeley-unix) MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha1; protocol="application/pgp-signature" X-HotPOP: ----------------------------------------------- Sent By HotPOP.com FREE Email Get your FREE POP email at www.HotPOP.com ----------------------------------------------- Subject: suggested block size for a frequently updated postgresql? X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 11 Nov 2005 09:33:25 -0000 --=-=-= Content-Transfer-Encoding: quoted-printable I'm planning on setting up a postgresql server, the database data files are on their own slice so I can tune the file system for pgsql. The database is pretty large. On my test server, some data files get larger than 1G and is splitted by pgsql. Also the database will be updated frequently. I'm planning on setting the slice with a bigger block size/fragment size but not sure if that's a good idea. I know the default on FreeBSD is 16K/2K, I'm planning on something like 1M/128K. The most concern is how the database files get updated? I heard that most database do update/delete by appending a mark and new record at the end of the data file but can't find any evidence for that. If that's true, then the thing looks good. But if the database update the data file by changing the original file block, setting a bigger block size may bring extra effort for the drive, especially when it's a RAID5 and a checksum build is required. Can someone shine some light on this? Thanks! =2D-=20 Dryice @ http://dryice.3322.org Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/sylvester-response.html --=-=-= Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (FreeBSD) iD8DBQBDdGO3a1t4qHe2eHQRAkszAJ0bclnu1HRXwVrXmeeFulgjmMK99gCeK3Yl X2zpG6s0vLUKfrbLX5LjPEM= =UVJp -----END PGP SIGNATURE----- --=-=-=--