pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFW: XFS filessystem for Datawarehousing -2
6+ messages / 4 participants
[nested] [flat]
* FW: XFS filessystem for Datawarehousing -2
@ 2006-08-01 22:19 Milen Kulev <makulev@gmx.net>
2006-08-02 02:44 ` Re: XFS filessystem for Datawarehousing -2 Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 6+ messages in thread
From: Milen Kulev @ 2006-08-01 22:19 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: XFS filessystem for Datawarehousing -2
2006-08-01 22:19 FW: XFS filessystem for Datawarehousing -2 Milen Kulev <makulev@gmx.net>
@ 2006-08-02 02:44 ` Luke Lonergan <llonergan@greenplum.com>
2006-08-03 05:36 ` Re: XFS filessystem for Datawarehousing -2 Denis Lussier <denisl@enterprisedb.com>
0 siblings, 1 reply; 6+ messages in thread
From: Luke Lonergan @ 2006-08-02 02:44 UTC (permalink / raw)
To: Milen Kulev <makulev@gmx.net>; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: XFS filessystem for Datawarehousing -2
2006-08-01 22:19 FW: XFS filessystem for Datawarehousing -2 Milen Kulev <makulev@gmx.net>
2006-08-02 02:44 ` Re: XFS filessystem for Datawarehousing -2 Luke Lonergan <llonergan@greenplum.com>
@ 2006-08-03 05:36 ` Denis Lussier <denisl@enterprisedb.com>
2006-08-03 19:56 ` Re: XFS filessystem for Datawarehousing -2 Milen Kulev <makulev@gmx.net>
2006-08-03 21:00 ` Re: XFS filessystem for Datawarehousing -2 Chris Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 6+ messages in thread
From: Denis Lussier @ 2006-08-03 05:36 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Milen Kulev <makulev@gmx.net>; pgsql-performance
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.0seems 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: XFS filessystem for Datawarehousing -2
2006-08-01 22:19 FW: XFS filessystem for Datawarehousing -2 Milen Kulev <makulev@gmx.net>
2006-08-02 02:44 ` Re: XFS filessystem for Datawarehousing -2 Luke Lonergan <llonergan@greenplum.com>
2006-08-03 05:36 ` Re: XFS filessystem for Datawarehousing -2 Denis Lussier <denisl@enterprisedb.com>
@ 2006-08-03 19:56 ` Milen Kulev <makulev@gmx.net>
1 sibling, 0 replies; 6+ messages in thread
From: Milen Kulev @ 2006-08-03 19:56 UTC (permalink / raw)
To: 'Denis Lussier' <denisl@enterprisedb.com>; 'Luke Lonergan' <llonergan@greenplum.com>; +Cc: pgsql-performance
Hi Dennis,
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.
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.
Best Regards. Milen
-----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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: XFS filessystem for Datawarehousing -2
2006-08-01 22:19 FW: XFS filessystem for Datawarehousing -2 Milen Kulev <makulev@gmx.net>
2006-08-02 02:44 ` Re: XFS filessystem for Datawarehousing -2 Luke Lonergan <llonergan@greenplum.com>
2006-08-03 05:36 ` Re: XFS filessystem for Datawarehousing -2 Denis Lussier <denisl@enterprisedb.com>
@ 2006-08-03 21:00 ` Chris Browne <cbbrowne@acm.org>
2006-08-04 20:13 ` Re: XFS filessystem for Datawarehousing -2 Denis Lussier <denisl@enterprisedb.com>
1 sibling, 1 reply; 6+ messages in thread
From: Chris Browne @ 2006-08-03 21:00 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: XFS filessystem for Datawarehousing -2
2006-08-01 22:19 FW: XFS filessystem for Datawarehousing -2 Milen Kulev <makulev@gmx.net>
2006-08-02 02:44 ` Re: XFS filessystem for Datawarehousing -2 Luke Lonergan <llonergan@greenplum.com>
2006-08-03 05:36 ` Re: XFS filessystem for Datawarehousing -2 Denis Lussier <denisl@enterprisedb.com>
2006-08-03 21:00 ` Re: XFS filessystem for Datawarehousing -2 Chris Browne <cbbrowne@acm.org>
@ 2006-08-04 20:13 ` Denis Lussier <denisl@enterprisedb.com>
0 siblings, 0 replies; 6+ messages in thread
From: Denis Lussier @ 2006-08-04 20:13 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
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
On 8/3/06, Chris Browne <cbbrowne@acm.org> wrote:
>
>
> 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?
> --
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-08-04 20:13 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-01 22:19 FW: XFS filessystem for Datawarehousing -2 Milen Kulev <makulev@gmx.net>
2006-08-02 02:44 ` Luke Lonergan <llonergan@greenplum.com>
2006-08-03 05:36 ` Denis Lussier <denisl@enterprisedb.com>
2006-08-03 19:56 ` Milen Kulev <makulev@gmx.net>
2006-08-03 21:00 ` Chris Browne <cbbrowne@acm.org>
2006-08-04 20:13 ` Denis Lussier <denisl@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox