X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1FD0F329E3C for ; Thu, 23 Sep 2004 04:41:42 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35690-01 for ; Thu, 23 Sep 2004 03:41:36 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 66FBF329E58 for ; Thu, 23 Sep 2004 04:41:34 +0100 (BST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CAKTx-0006Jn-00; Wed, 22 Sep 2004 23:41:21 -0400 To: Andrew Hammond Cc: Postgresql Performance , Rod Taylor Subject: Re: NAS, SAN or any alternate solution ? References: <1090344321.7056.105.camel@jester> <4151E1E7.2000009@ca.afilias.info> In-Reply-To: <4151E1E7.2000009@ca.afilias.info> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 22 Sep 2004 23:41:21 -0400 Message-ID: <87r7otbqji.fsf@stark.xeocode.com> Lines: 46 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/230 X-Sequence-Number: 8358 Andrew Hammond writes: > My goal is to tune the disk / filesystem on our prototype system. It's > an EMC disk array, so sectors on disk are 512 bytes of usable space. > We've decided to go with RAID 10 since the goal is to maximize > performance. Currently the raid element size is set at 16 sectors which > is 8192 bytes of payload. Do people find it works well to have a stripe size that small? It seems like it would be better to have it be at least a few filesystem/postgres blocks so that subsequent reads stand a chance of being sequential and not causing another spindle to have to seek. Does this depend on whether it's an DSS load vs an OLTP load? If it's a single query at a time DSS system perhaps small blocksizes work best to get maximum throughput? > I've got a sysadmin working on getting XFS going with 8192 byte blocks. Having your filesystem block size match postgres's block size is probably a good idea. So 8k blocks is good. > My next task will be to calculate the amount of space used by XFS for > headers etc. to find out how much of those 8192 bytes can be used for the > postgres payload. No filesystem that I know of uses up space in every block. The overhead is all stored elsewhere in blocks exclusively contain such overhead data. So just setting postgres to 8k which the default would work well. > Then configure postgres to use datablocks that size. So I'm looking for > details on how to manipulate the size of the datablock. Look in pg_config_manual.h in src/include. Postgres has to be recompiled to change it and the database has to be reinitialized. But it could be set to 16k or 32k. In which case you would probably want to adjust your filesystem to match. But unless you do experiments you won't know if it would be of any benefit to change. > I'm also not entirely sure how to make the datablocks line up with the > filesystem blocks. Any suggestions on this would be greatly appreciated. They just will. The files start on a block boundary, so every 8k is a new block. Postgres stores 8k at a time always. -- greg