X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 848499FB26F for ; Thu, 3 Aug 2006 16:56:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 41610-01 for ; Thu, 3 Aug 2006 19:56:27 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from mail.gmx.net (mail.gmx.de [213.165.64.21]) by postgresql.org (Postfix) with SMTP id AF6919FB259 for ; Thu, 3 Aug 2006 16:56:24 -0300 (ADT) Received: (qmail invoked by alias); 03 Aug 2006 19:56:22 -0000 Received: from dslb-084-057-048-060.pools.arcor-ip.net (EHLO ltmku) [84.57.48.60] by mail.gmx.net (mp035) with SMTP; 03 Aug 2006 21:56:22 +0200 X-Authenticated: #23352258 From: "Milen Kulev" To: "'Denis Lussier'" , "'Luke Lonergan'" Cc: Subject: Re: XFS filessystem for Datawarehousing -2 Date: Thu, 3 Aug 2006 21:56:19 +0200 Message-ID: <024801c6b736$e3505ed0$0a00a8c0@trivadis.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0249_01C6B747.A6D92ED0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: <5f820f750608022236yd8dd6escce3bb849b91bddb@mail.gmail.com> X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.269 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/48 X-Sequence-Number: 20233 This is a multi-part message in MIME format. ------=_NextPart_000_0249_01C6B747.A6D92ED0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Dennis,=20 I am just cusrios to try PG with different block sizes ;) I don't know = how much performance the bigger block size will bring (I mean 32k or 64k , for example, for DWH applikations). I am surprised to hear that OCFS2.0 (or any her FS usind direct I/O) = performs well with PG. A month ago I have performed a simple test with Veritas FS, with and than without cache = (e.g. direct I/O). I have started 1 , then 2, , then 3, then 4 parallel INSERT processes.=20 Veritas FS WITH FS cache outperformed the direct I/O version by factor = 2-2.5 ! I haven't tested woth OCFS2.0 though. I am not sure that OCFS2.0 is the = good choice for PG data and index filesystems. For WAL -> perhaps. =20 Best Regards. Milen=20 -----Original Message----- From: pgsql-performance-owner@postgresql.org = [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Denis = Lussier Sent: Thursday, August 03, 2006 7:36 AM To: Luke Lonergan Cc: Milen Kulev; pgsql-performance@postgresql.org Subject: Re: [PERFORM] XFS filessystem for Datawarehousing -2 I was kinda thinking that making the Block Size configurable at InitDB = time would be a nice & simple enhancement for PG 8.3. My own personal rule of thumb for sizing is 8k for OLTP, 16k for = mixed use, & 32k for DWH.=20 I have no personal experience with XFS, but, I've seen numerous internal = edb-postgres test results that show that of all file systems... OCFS 2.0 seems to be quite good for PG update intensive = apps (especially on 64 bit machines).=20 On 8/1/06, Luke Lonergan wrote:=20 Milen, On 8/1/06 3:19 PM, "Milen Kulev" wrote: > Sorry, forgot to ask: > What is the recommended/best PG block size for DWH database? 16k, = 32k, 64k=20 > ? > What hsould be the relation between XFS/RAID stripe size and PG block = size ? We have found that the page size in PG starts to matter only at very = high disk performance levels around 1000MB/s. Other posters have talked = about=20 maintenance tasks improving in performance, but I haven't seen it. - Luke ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org ------=_NextPart_000_0249_01C6B747.A6D92ED0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Nachricht
Hi=20 Dennis,
I am=20 just cusrios to try PG with different block sizes ;)  I don't=20 know how much performance the bigger block size will bring (I = mean 32k=20 or 64k , for example, for DWH applikations).
I am=20 surprised to hear that OCFS2.0 (or any her FS usind direct I/O) = performs=20 well  with PG. A month ago I have performed a simple = test=20 with Veritas FS, with and than without cache (e.g. direct=20 I/O).  I have started  1  , then 2, , = then 3,=20 then 4 parallel INSERT processes. 
Veritas FS WITH FS cache outperformed the direct I/O = version by=20 factor 2-2.5 !
I=20 haven't tested woth OCFS2.0 though. I am not sure that OCFS2.0 is  = the good=20 choice  for  PG data and index=20 filesystems.
For=20 WAL -> perhaps.
 
Best=20 Regards. Milen
-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of = Denis=20 Lussier
Sent: Thursday, August 03, 2006 7:36 = AM
To: Luke=20 Lonergan
Cc: Milen Kulev;=20 pgsql-performance@postgresql.org
Subject: Re: [PERFORM] XFS=20 filessystem for Datawarehousing -2


I was kinda = thinking that making the Block Size configurable at InitDB time would = be a=20 nice & simple enhancement for PG 8.3.  My own personal rule = of thumb=20 for sizing is 8k for OLTP, 16k for mixed use, & 32k for DWH. =

I=20 have no personal experience with XFS, but, I've seen numerous internal = edb-postgres test results that show that of all file systems... OCFS = 2.0 seems=20 to be quite good for PG update intensive apps (especially on 64 bit = machines).=20

On 8/1/06, Luke=20 Lonergan <llonergan@greenplum.com>=20 wrote:
Milen,

On=20 8/1/06 3:19 PM, "Milen Kulev" <makulev@gmx.net> = wrote:

>=20 Sorry, forgot to ask:
> What is the = recommended/best  PG=20 block size for DWH  database?  16k, 32k, 64k =
>=20 ?
> What hsould be the relation  between XFS/RAID = stripe=20 size and PG block size ?

We have found that the page size in = PG=20 starts to matter only at very high
disk performance levels around = 1000MB/s.  Other posters have talked about
maintenance = tasks=20 improving in performance, but I haven't seen it.

-=20 Luke



---------------------------(end of=20 broadcast)---------------------------
TIP 4: Have you searched = our list=20 = archives?

         &n= bsp;    =20 http://archives.postgresql.org

------=_NextPart_000_0249_01C6B747.A6D92ED0--