Обсуждение: FW: XFS filessystem for Datawarehousing -2

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

Best  Regards.
Milen Kulev


-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Milen Kulev
Sent: Tuesday, August 01, 2006 11:50 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] XFS filessystem for Datawarehousing


I intend to test  Postgres/Bizgres for DWH use. I want to use XFS filesystem to get the best possible performance at FS
level(correct me if I am wrong !).

Is anyone using XFS for storing/retrieving relatively large amount of data  (~ 200GB)?

If yes, what about the performance and stability of  XFS.
I am especially interested in recommendations about XFS mount options and mkfs.xfs options. My setup will be roughly
this:
1) 4  SCSI HDD , 128GB each,
2) RAID 0 on the four SCSI HDD disks using LVM (software RAID)

There are two other SATA HDD in the server.  Server has 2 physical CPUs (XEON at 3 GHz),  4 Logical CPUs, 8 GB RAM,  OS
= SLES9 SP3

My questions:
1) Should I place external XFS journal on separate device ?
2) What  should be the journal buffer size (logbsize) ?
3)  How many journal buffers (logbufs) should I configure ?
4) How many allocations groups  (for mkfs.xfs) should I  configure
5)  Is it wortj settion noatime ?
6) What I/O scheduler(elevators) should I use (massive sequencial reads)
7) What is the ideal stripe unit and width (for a RAID device) ?

I will appreciate any options, suggestions, pointers.

Best  Regards.
Milen Kulev


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

               http://archives.postgresql.org

Milen,

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

> Sorry, forgot to ask:
> What is the recommended/best  PG block size for DWH  database?  16k, 32k, 64k
> ?
> 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
maintenance tasks improving in performance, but I haven't seen it.

- Luke

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.

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).

-----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.

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).

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

Milen,

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

> Sorry, forgot to ask:
> What is the recommended/best  PG block size for DWH  database?  16k, 32k, 64k
> ?
> 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
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

denisl@enterprisedb.com ("Denis Lussier") writes:
> 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).

I have been curious about OCFS for some time; it sounded like a case
where there could possibly be some useful semantic changes to
filesystem functionality, notably that:

 - atime is pretty irrelevant;
 - it might try to work with pretty fixed block sizes (8K, perhaps?)
   rather than try to be efficient at handling tiny files

It sounds like it ought to be able to be a good fit.

Of course, with a big warning sticker of "what is required for Oracle
to work properly is implemented, anything more is not a guarantee" on
it, who's going to trust it?
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://www.ntlug.org/~cbbrowne/oses.html
"There  isn't  any  reason  why  Linux  can't  be  implemented  as  an
enterprise  computing solution.   Find  out what  you've been  missing
while you've been rebooting Windows NT." - Infoworld

I agree that OCFS 2.0 is NOT a general purpose PG (or any other) solution.  My recollection is that OCFS gave about 15% performance improvements (same as setting some aggressive switches on ext3).   I assume OCFS has excellent crash safety with its default settings but we did not test this as of yet.  OCFS now ships as one of the optional FS's that ship with Suse.   That takes care of some of the FUD created by Oracle's disclaimer below.  

OCFS 2 is much more POSIX compliant than OCFS 1.  The BenchmarkSQL, DBT2, & Regression tests we ran on OCFS 2 all worked well.  The lack of full Posix compliance did cause some problems for configuring PITR.

--Denis       http://www.enterprisedb.com