Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id EE212B5DBE6 for ; Thu, 26 May 2011 19:35:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 73954-03 for ; Thu, 26 May 2011 22:34:55 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com [209.85.216.46]) by mail.postgresql.org (Postfix) with ESMTP id C0420B5DBE0 for ; Thu, 26 May 2011 19:34:55 -0300 (ADT) Received: by qwk3 with SMTP id 3so609202qwk.19 for ; Thu, 26 May 2011 15:34:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:date:message-id:subject:from:to :content-type; bh=+Q9DfQWZlM4Nr0BK1Zd57e6dbIxrlZ2P6lb0m0XQMpw=; b=Ucl4GgJCQjjeUg1AQbsr2n/5g3mY7le8LPr1WO6ZEUw26BtE/sFo9Y5N0DDT5/FFr9 g/kqXbgoNQojyt36agrpeOKkAxrmuUWWcKjtA2AqHVJs52O/K4CeGTSAPIJZq+7ktdmp JxHUkTAgolQOLJyo2oUN8hPNrBuAvUKXWoSgI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=GqYp7bM86UUYonZFJfLM/LdZZwPi5799Asi2iEAJKEzGTmp5F7lbh26qt+lgUVbztW 8JoaOMvLF6xGK6TOXmDD8p5Mgv4FDKnQD6YHWPZlqOvGIR2puJ1Djeqjk2qor2GpD7Wh r1yXsWQHeF2vhYtB0tVFX7KVQn4tgHVu1apFA= MIME-Version: 1.0 Received: by 10.229.111.98 with SMTP id r34mr1158795qcp.3.1306449295755; Thu, 26 May 2011 15:34:55 -0700 (PDT) Received: by 10.229.238.79 with HTTP; Thu, 26 May 2011 15:34:55 -0700 (PDT) Date: Thu, 26 May 2011 15:34:55 -0700 Message-ID: Subject: Performance block size. From: Tory M Blue To: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.888 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RFC_ABUSE_POST=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201105/386 X-Sequence-Number: 43895 Working on some optimization as well as finally getting off my backside and moving us to 64bit (32gb+memory). I was reading and at some point it appears on freeBSD the Postgres block size was upped to 16kb, from 8kb. And on my fedora systems I believe the default build is 8kb. When we were using ext2/ext3 etc made not much of a difference as far as I can tell since one was limited to 4kb at the file system (so 2 disk access for every Postgres write/read ??). Now with ext4, we can set the block size, so would it make sense for larger data sets, that end up loading the entire 5 million row table into memory, to have a larger block size, or matching block size between postgres and the filesystem (given that raid is configured to optimize the writes over all the spindles in your storage?) (leaving that piece alone). I want to focus on the relation of Postgres default block size, and what the various issues/gains are with upping it , at the same time matching or doing some storage magic to figure out the optimum between Postgres/filesystem? Trying to gain some performance and wondered if any of this tuning even is something I should bother with. Fedora f12 (looking at CentOS) postgres 8.4.4 (probably time to start getting to 9.x) slon 1.2.20 (same, needs an update) But system tuning, 64 bit first.. Thanks Tory