From owner-freebsd-database@FreeBSD.ORG Fri Nov 11 18:24:07 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 9C6FD16A41F for ; Fri, 11 Nov 2005 18:24:07 +0000 (GMT) (envelope-from olli@lurza.secnetix.de) Received: from lurza.secnetix.de (lurza.secnetix.de [83.120.8.8]) by mx1.FreeBSD.org (Postfix) with ESMTP id 6E14D43D49 for ; Fri, 11 Nov 2005 18:24:06 +0000 (GMT) (envelope-from olli@lurza.secnetix.de) Received: from lurza.secnetix.de (cdsdyb@localhost [127.0.0.1]) by lurza.secnetix.de (8.13.1/8.13.1) with ESMTP id jABIO4wW074289 for ; Fri, 11 Nov 2005 19:24:05 +0100 (CET) (envelope-from oliver.fromme@secnetix.de) Received: (from olli@localhost) by lurza.secnetix.de (8.13.1/8.13.1/Submit) id jABIO4Sh074288; Fri, 11 Nov 2005 19:24:04 +0100 (CET) (envelope-from olli) Date: Fri, 11 Nov 2005 19:24:04 +0100 (CET) Message-Id: <200511111824.jABIO4Sh074288@lurza.secnetix.de> From: Oliver Fromme To: freebsd-database@FreeBSD.ORG In-Reply-To: <8664qzcz82.fsf@dryice.3322.org> X-Newsgroups: list.freebsd-database User-Agent: tin/1.5.4-20000523 ("1959") (UNIX) (FreeBSD/4.11-RELEASE (i386)) Cc: Subject: Re: suggested block size for a frequently updated postgresql? X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list Reply-To: freebsd-database@FreeBSD.ORG 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 18:24:07 -0000 Dryice Liu wrote: > 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. FreeBSD doesn't support bsize/fsize that large. In fact, I wouldn't trust anything other than 16K/2K and 8K/1K to work reliably under load. I also remember Matt Dillon mentioned that larger block sizes are less-than-optimal for the VM system. Besides, I don't think that PostgreSQL would benefit from such huge fragment and block sizes. Let me explain ... PostgreSQL does two things on the file system: First, it records all modifications to the WAL file. This is a sequential process, and I wouldn't expect the blocksize to have any significant effect. Second, it updates the actual table data in the data files. This consists of appending data to the files and random access inside the files, which would definitely not benefit from a larger block size. Also take into account that the regular "vacuum" process of postgresql is random access inside the data files. For more details on that I recommend you ask in the Post- greSQL list (pgsql-novice). There are very knowledgeable and responsive people there. So, the bottom line is: I recommend you leave the bsize and fsize at the default 16K/2K. However, you probably should reduce the inode density from the default, i.e. use the -i option with some high value such as 262144 (that's 2^18). But don't make this value too high either ... I remember someone tried to set it to 64 million or some- thing, which broke his FS. Best regards Oliver -- Oliver Fromme, secnetix GmbH & Co. KG, Marktplatz 29, 85567 Grafing Dienstleistungen mit Schwerpunkt FreeBSD: http://www.secnetix.de/bsd Any opinions expressed in this message may be personal to the author and may not necessarily reflect the opinions of secnetix in any way. "With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead." -- RFC 1925