pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedXFS filessystem for Datawarehousing
19+ messages / 11 participants
[nested] [flat]
* XFS filessystem for Datawarehousing
@ 2006-08-01 21:49 Milen Kulev <makulev@gmx.net>
2006-08-01 22:46 ` Re: XFS filessystem for Datawarehousing J. Andrew Rogers <jrogers@neopolitan.com>
2006-08-02 02:06 ` Re: XFS filessystem for Datawarehousing Mark Kirkwood <markir@paradise.net.nz>
2006-08-02 02:42 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
0 siblings, 3 replies; 19+ messages in thread
From: Milen Kulev @ 2006-08-01 21:49 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
@ 2006-08-01 22:46 ` J. Andrew Rogers <jrogers@neopolitan.com>
2006-08-01 22:59 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 00:42 ` Re: XFS filessystem for Datawarehousing Alvaro Herrera <alvherre@commandprompt.com>
2 siblings, 2 replies; 19+ messages in thread
From: J. Andrew Rogers @ 2006-08-01 22:46 UTC (permalink / raw)
To: Milen Kulev <makulev@gmx.net>; +Cc: pgsql-performance
On Aug 1, 2006, at 2:49 PM, Milen Kulev wrote:
> Is anyone using XFS for storing/retrieving relatively large amount
> of data (~ 200GB)?
Yes, we've been using it on Linux since v2.4 (currently v2.6) and it
has been rock solid on our database servers (Opterons, running in
both 32-bit and 64-bit mode). Our databases are not quite 200GB
(maybe 75GB for a big one currently), but ballpark enough that the
experience is probably valid. We also have a few terabyte+ non-
database XFS file servers too.
Performance has been very good even with nearly full file systems,
and reliability has been perfect so far. Some of those file systems
get used pretty hard for months or years non-stop. Comparatively, I
can only tell you that XFS tends to be significantly faster than
Ext3, but we never did any serious file system tuning either.
Knowing nothing else, my experience would suggest that XFS is a fine
and safe choice for your application.
J. Andrew Rogers
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-01 22:46 ` Re: XFS filessystem for Datawarehousing J. Andrew Rogers <jrogers@neopolitan.com>
@ 2006-08-01 22:59 ` Milen Kulev <makulev@gmx.net>
1 sibling, 0 replies; 19+ messages in thread
From: Milen Kulev @ 2006-08-01 22:59 UTC (permalink / raw)
To: 'J. Andrew Rogers' <jrogers@neopolitan.com>; +Cc: pgsql-performance
Hi Andrew,
Thank you for your prompt reply.
Are you using some special XFS options ?
I mean special values for logbuffers bufferiosize , extent size preallocations etc ?
I will have only 6 big tables and about 20 other relatively small (fact aggregation) tables (~ 10-20 GB each).
I believe it should be a a good idea to use as much contigious chunks of space (from OS point of view) as possible in
order to make full table scans as fast as possible.
Best Regards,
Milen Kulev
-----Original Message-----
From: J. Andrew Rogers [mailto:jrogers@neopolitan.com]
Sent: Wednesday, August 02, 2006 12:47 AM
To: Milen Kulev
Cc: Pgsql-Performance ((E-mail))
Subject: Re: [PERFORM] XFS filessystem for Datawarehousing
On Aug 1, 2006, at 2:49 PM, Milen Kulev wrote:
> Is anyone using XFS for storing/retrieving relatively large amount
> of data (~ 200GB)?
Yes, we've been using it on Linux since v2.4 (currently v2.6) and it
has been rock solid on our database servers (Opterons, running in
both 32-bit and 64-bit mode). Our databases are not quite 200GB
(maybe 75GB for a big one currently), but ballpark enough that the
experience is probably valid. We also have a few terabyte+ non-
database XFS file servers too.
Performance has been very good even with nearly full file systems,
and reliability has been perfect so far. Some of those file systems
get used pretty hard for months or years non-stop. Comparatively, I
can only tell you that XFS tends to be significantly faster than
Ext3, but we never did any serious file system tuning either.
Knowing nothing else, my experience would suggest that XFS is a fine
and safe choice for your application.
J. Andrew Rogers
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-01 22:46 ` Re: XFS filessystem for Datawarehousing J. Andrew Rogers <jrogers@neopolitan.com>
@ 2006-08-02 00:42 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-08-04 20:44 ` Re: XFS filessystem for Datawarehousing Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 1 reply; 19+ messages in thread
From: Alvaro Herrera @ 2006-08-02 00:42 UTC (permalink / raw)
To: J. Andrew Rogers <jrogers@neopolitan.com>; +Cc: Milen Kulev <makulev@gmx.net>; pgsql-performance
J. Andrew Rogers wrote:
>
> On Aug 1, 2006, at 2:49 PM, Milen Kulev wrote:
> >Is anyone using XFS for storing/retrieving relatively large amount
> >of data (~ 200GB)?
>
>
> Yes, we've been using it on Linux since v2.4 (currently v2.6) and it
> has been rock solid on our database servers (Opterons, running in
> both 32-bit and 64-bit mode). Our databases are not quite 200GB
> (maybe 75GB for a big one currently), but ballpark enough that the
> experience is probably valid. We also have a few terabyte+ non-
> database XFS file servers too.
>
> Performance has been very good even with nearly full file systems,
> and reliability has been perfect so far. Some of those file systems
> get used pretty hard for months or years non-stop. Comparatively, I
> can only tell you that XFS tends to be significantly faster than
> Ext3, but we never did any serious file system tuning either.
Most likely ext3 was used on the default configuration, which logs data
operations as well as metadata, which is what XFS logs. I don't think
I've seen any credible comparison between XFS and ext3 with the
metadata-only journal option.
On the other hand I don't think it makes sense to journal data on a
PostgreSQL environment. Metadata is enough, given that we log data on
WAL anyway.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-01 22:46 ` Re: XFS filessystem for Datawarehousing J. Andrew Rogers <jrogers@neopolitan.com>
2006-08-02 00:42 ` Re: XFS filessystem for Datawarehousing Alvaro Herrera <alvherre@commandprompt.com>
@ 2006-08-04 20:44 ` Jim C. Nasby <jnasby@pervasive.com>
2006-08-07 16:55 ` Re: XFS filessystem for Datawarehousing Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jim C. Nasby @ 2006-08-04 20:44 UTC (permalink / raw)
To: J. Andrew Rogers <jrogers@neopolitan.com>; Milen Kulev <makulev@gmx.net>; pgsql-performance
On Tue, Aug 01, 2006 at 08:42:23PM -0400, Alvaro Herrera wrote:
> J. Andrew Rogers wrote:
> >
> > On Aug 1, 2006, at 2:49 PM, Milen Kulev wrote:
> > >Is anyone using XFS for storing/retrieving relatively large amount
> > >of data (~ 200GB)?
> >
> >
> > Yes, we've been using it on Linux since v2.4 (currently v2.6) and it
> > has been rock solid on our database servers (Opterons, running in
> > both 32-bit and 64-bit mode). Our databases are not quite 200GB
> > (maybe 75GB for a big one currently), but ballpark enough that the
> > experience is probably valid. We also have a few terabyte+ non-
> > database XFS file servers too.
> >
> > Performance has been very good even with nearly full file systems,
> > and reliability has been perfect so far. Some of those file systems
> > get used pretty hard for months or years non-stop. Comparatively, I
> > can only tell you that XFS tends to be significantly faster than
> > Ext3, but we never did any serious file system tuning either.
>
> Most likely ext3 was used on the default configuration, which logs data
> operations as well as metadata, which is what XFS logs. I don't think
> I've seen any credible comparison between XFS and ext3 with the
> metadata-only journal option.
>
> On the other hand I don't think it makes sense to journal data on a
> PostgreSQL environment. Metadata is enough, given that we log data on
> WAL anyway.
Actually, according to http://en.wikipedia.org/wiki/Ext3 the default
journalling option for ext3 isn't to journal the data (which is actually
data=journal), but to wait until the data is written before considering
the metadata to be committed (data=ordered).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-01 22:46 ` Re: XFS filessystem for Datawarehousing J. Andrew Rogers <jrogers@neopolitan.com>
2006-08-02 00:42 ` Re: XFS filessystem for Datawarehousing Alvaro Herrera <alvherre@commandprompt.com>
2006-08-04 20:44 ` Re: XFS filessystem for Datawarehousing Jim C. Nasby <jnasby@pervasive.com>
@ 2006-08-07 16:55 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 19+ messages in thread
From: Alvaro Herrera @ 2006-08-07 16:55 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: J. Andrew Rogers <jrogers@neopolitan.com>; Milen Kulev <makulev@gmx.net>; pgsql-performance
Jim C. Nasby wrote:
> On Tue, Aug 01, 2006 at 08:42:23PM -0400, Alvaro Herrera wrote:
> > Most likely ext3 was used on the default configuration, which logs data
> > operations as well as metadata, which is what XFS logs. I don't think
> > I've seen any credible comparison between XFS and ext3 with the
> > metadata-only journal option.
> >
> > On the other hand I don't think it makes sense to journal data on a
> > PostgreSQL environment. Metadata is enough, given that we log data on
> > WAL anyway.
>
> Actually, according to http://en.wikipedia.org/wiki/Ext3 the default
> journalling option for ext3 isn't to journal the data (which is actually
> data=journal), but to wait until the data is written before considering
> the metadata to be committed (data=ordered).
Well, we don't need the data to be written before considering metadata
committed. data=writeback is enough for partitions to be dedicated to
PGDATA. Not sure what other FSs do on this front but the ext3 default
leans towards safe rather than speedy.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
@ 2006-08-02 02:06 ` Mark Kirkwood <markir@paradise.net.nz>
2 siblings, 0 replies; 19+ messages in thread
From: Mark Kirkwood @ 2006-08-02 02:06 UTC (permalink / raw)
To: Milen Kulev <makulev@gmx.net>; +Cc: pgsql-performance
Milen Kulev wrote:
> Is anyone using XFS for storing/retrieving relatively large amount of data (~ 200GB)?
>
Yes, but not for that large - only about 40-50 GB of database data.
> If yes, what about the performance and stability of XFS.
I'm pretty happy with the performance, particularly read (get 215MB/s
sequential 8K reads from 4 (P)ATA drives setup as software RAID 0). I
have always found XFS very stable (used it on servers for several years).
> 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)
>
>
> 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) ?
>
>
1-3) I have not done any experimentation with where to put the journal,
or its buffer size / number of them (well worth doing I suspect tho).
4) I left it at the default.
5) I use noatime, but have not measured if there is any impact if I
leave it off.
6) deadline scheduler seemed to give slightly better performance for
sequential performance.
7) I tried out stripe width 2,4 (with 4 disks), and they seemed to give
the same results. Stripe unit of 256K (tested 32K, 64K, 128K) seemed to
give the best sequential performance. My software raid stripe size was
matched to this in each case.
I'll be interested to hear what you discover :-)
Cheers
Mark
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
@ 2006-08-02 02:42 ` Luke Lonergan <llonergan@greenplum.com>
2006-08-02 20:59 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2 siblings, 1 reply; 19+ messages in thread
From: Luke Lonergan @ 2006-08-02 02:42 UTC (permalink / raw)
To: Milen Kulev <makulev@gmx.net>; pgsql-performance
Milen,
On 8/1/06 2:49 PM, "Milen Kulev" <makulev@gmx.net> wrote:
> Is anyone using XFS for storing/retrieving relatively large amount of data (~
> 200GB)?
I concur with the previous poster's experiences with one additional
observation:
We have had instabilities with XFS with software RAID (md) on 32-bit Xeons
running RedHat4 U3 with the Centos 4.3 unsupported SMP kernel. XFS would
occasionally kernel panic under load.
We have had no problems with XFS running on the same OS/kernel on 64-bit
under heavy workloads for weeks of continuous usage. Each server (of 16
total) had four XFS filesystems, each with 250GB of table data (no indexes)
on them, total of 16 Terabytes. We tested with the TPC-H schema and
queries.
We use the default settings for XFS.
Also - be aware that LVM has a serious performance bottleneck at about
600MB/s - if you are working below that threshold, you may not notice the
issue, maybe some increase in CPU consumption as you approach it.
- Luke
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 02:42 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
@ 2006-08-02 20:59 ` Milen Kulev <makulev@gmx.net>
2006-08-02 21:26 ` Re: XFS filessystem for Datawarehousing Steve Poe <steve.poe@gmail.com>
2006-08-02 22:43 ` Re: XFS filessystem for Datawarehousing Chris Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 19+ messages in thread
From: Milen Kulev @ 2006-08-02 20:59 UTC (permalink / raw)
To: 'Luke Lonergan' <llonergan@greenplum.com>; pgsql-performance
Hi Like, Mark , Alvaro and Andrew,
Thank you very much for sharing you experience with me.
I want to compare DHW performance of PG/Bizgres on different filesystems and difffrent
Block sizes.
The hardware will be free for me in a week or too (at a moment another project is running on it) and then I will test
diffrenet setups and will post the results.
I MUST (sorry, no other choice) use SLES6 R3, 64 bit. I am not sure at all that I will get enough budget to get
approapriate RAID controller, and that is why I intent to use software RAID.
I am pretty exited whether XFS will clearly outpertform ETX3 (no default setups for both are planned !). I am not sure
whether is it worth to include JFS in comparison too ...
Best Regards,
Milen Kulev
-----Original Message-----
From: Luke Lonergan [mailto:llonergan@greenplum.com]
Sent: Wednesday, August 02, 2006 4:43 AM
To: Milen Kulev; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] XFS filessystem for Datawarehousing
Milen,
On 8/1/06 2:49 PM, "Milen Kulev" <makulev@gmx.net> wrote:
> Is anyone using XFS for storing/retrieving relatively large amount of
> data (~ 200GB)?
I concur with the previous poster's experiences with one additional
observation:
We have had instabilities with XFS with software RAID (md) on 32-bit Xeons running RedHat4 U3 with the Centos 4.3
unsupported SMP kernel. XFS would occasionally kernel panic under load.
We have had no problems with XFS running on the same OS/kernel on 64-bit under heavy workloads for weeks of continuous
usage. Each server (of 16
total) had four XFS filesystems, each with 250GB of table data (no indexes) on them, total of 16 Terabytes. We tested
with the TPC-H schema and queries.
We use the default settings for XFS.
Also - be aware that LVM has a serious performance bottleneck at about 600MB/s - if you are working below that
threshold, you may not notice the issue, maybe some increase in CPU consumption as you approach it.
- Luke
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 02:42 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
2006-08-02 20:59 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
@ 2006-08-02 21:26 ` Steve Poe <steve.poe@gmail.com>
2006-08-02 21:44 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 23:33 ` Re: XFS filessystem for Datawarehousing Michael Stone <mstone+postgres@mathom.us>
1 sibling, 2 replies; 19+ messages in thread
From: Steve Poe @ 2006-08-02 21:26 UTC (permalink / raw)
To: Milen Kulev <makulev@gmx.net>; +Cc: pgsql-performance
Milen,
For the past year, I have been running odbc-bench on a dual-opteron with
4GB of RAM using a 8GB sample data. I found the performance difference
between EXT3, JFS, and XFS is +/- 5-8%. This could be written-off as
"noise" just for normal server performance flux. If you plan on using the
default kernel, ext3 will likely perform best (what I found). When I added
my own kernel, ext3 performed fair. What I've had to consider is what does
each file system offer me as far as data integrity goes.
You'll find greater ROI on performance by investing your time in other areas
than chasing down a few percentage point (like I have done). If you could
borrow more RAM and/or more discs for your tests, Testing newer kernels and
read-ahead patches may benefit you as well.
Best of luck.
Steve Poe
On 8/2/06, Milen Kulev <makulev@gmx.net> wrote:
>
> Hi Like, Mark , Alvaro and Andrew,
>
> Thank you very much for sharing you experience with me.
> I want to compare DHW performance of PG/Bizgres on different filesystems
> and difffrent
> Block sizes.
>
> The hardware will be free for me in a week or too (at a moment another
> project is running on it) and then I will test
> diffrenet setups and will post the results.
>
> I MUST (sorry, no other choice) use SLES6 R3, 64 bit. I am not sure at all
> that I will get enough budget to get
> approapriate RAID controller, and that is why I intent to use software
> RAID.
>
> I am pretty exited whether XFS will clearly outpertform ETX3 (no default
> setups for both are planned !). I am not sure
> whether is it worth to include JFS in comparison too ...
>
>
> Best Regards,
> Milen Kulev
>
> -----Original Message-----
> From: Luke Lonergan [mailto:llonergan@greenplum.com]
> Sent: Wednesday, August 02, 2006 4:43 AM
> To: Milen Kulev; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] XFS filessystem for Datawarehousing
>
>
> Milen,
>
> On 8/1/06 2:49 PM, "Milen Kulev" <makulev@gmx.net> wrote:
>
> > Is anyone using XFS for storing/retrieving relatively large amount of
> > data (~ 200GB)?
>
> I concur with the previous poster's experiences with one additional
> observation:
>
> We have had instabilities with XFS with software RAID (md) on 32-bit Xeons
> running RedHat4 U3 with the Centos 4.3
> unsupported SMP kernel. XFS would occasionally kernel panic under load.
>
> We have had no problems with XFS running on the same OS/kernel on 64-bit
> under heavy workloads for weeks of continuous
> usage. Each server (of 16
> total) had four XFS filesystems, each with 250GB of table data (no
> indexes) on them, total of 16 Terabytes. We tested
> with the TPC-H schema and queries.
>
> We use the default settings for XFS.
>
> Also - be aware that LVM has a serious performance bottleneck at about
> 600MB/s - if you are working below that
> threshold, you may not notice the issue, maybe some increase in CPU
> consumption as you approach it.
>
> - Luke
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 02:42 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
2006-08-02 20:59 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 21:26 ` Re: XFS filessystem for Datawarehousing Steve Poe <steve.poe@gmail.com>
@ 2006-08-02 21:44 ` Milen Kulev <makulev@gmx.net>
1 sibling, 0 replies; 19+ messages in thread
From: Milen Kulev @ 2006-08-02 21:44 UTC (permalink / raw)
To: 'Steve Poe' <steve.poe@gmail.com>; +Cc: pgsql-performance
Hi Steve,
I hope that performance between EXT3 and XFS is not only 5-8% . Such a small difference could be interpreted as
"noise", as you already mentioned.
I want to give many filesystem a try. Stability is also a concern, but I don't want to favour any FS over another .
Best Regards.
MIlen Kulev
-----Original Message-----
From: Steve Poe [mailto:steve.poe@gmail.com]
Sent: Wednesday, August 02, 2006 11:27 PM
To: Milen Kulev
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] XFS filessystem for Datawarehousing
Milen,
For the past year, I have been running odbc-bench on a dual-opteron with 4GB of RAM using a 8GB sample data. I found
the performance difference between EXT3, JFS, and XFS is +/- 5-8%. This could be written-off as "noise" just for normal
server performance flux. If you plan on using the default kernel, ext3 will likely perform best (what I found). When I
added my own kernel, ext3 performed fair. What I've had to consider is what does each file system offer me as far as
data integrity goes.
You'll find greater ROI on performance by investing your time in other areas than chasing down a few percentage point
(like I have done). If you could borrow more RAM and/or more discs for your tests, Testing newer kernels and
read-ahead patches may benefit you as well.
Best of luck.
Steve Poe
On 8/2/06, Milen Kulev <makulev@gmx.net> wrote:
Hi Like, Mark , Alvaro and Andrew,
Thank you very much for sharing you experience with me.
I want to compare DHW performance of PG/Bizgres on different filesystems and difffrent
Block sizes.
The hardware will be free for me in a week or too (at a moment another project is running on it) and then I will test
diffrenet setups and will post the results.
I MUST (sorry, no other choice) use SLES6 R3, 64 bit. I am not sure at all that I will get enough budget to get
approapriate RAID controller, and that is why I intent to use software RAID.
I am pretty exited whether XFS will clearly outpertform ETX3 (no default setups for both are planned !). I am not sure
whether is it worth to include JFS in comparison too ...
Best Regards,
Milen Kulev
-----Original Message-----
From: Luke Lonergan [mailto: llonergan@greenplum.com <mailto:llonergan@greenplum.com> ]
Sent: Wednesday, August 02, 2006 4:43 AM
To: Milen Kulev; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] XFS filessystem for Datawarehousing
Milen,
On 8/1/06 2:49 PM, "Milen Kulev" <makulev@gmx.net> wrote:
> Is anyone using XFS for storing/retrieving relatively large amount of
> data (~ 200GB)?
I concur with the previous poster's experiences with one additional
observation:
We have had instabilities with XFS with software RAID (md) on 32-bit Xeons running RedHat4 U3 with the Centos 4.3
unsupported SMP kernel. XFS would occasionally kernel panic under load.
We have had no problems with XFS running on the same OS/kernel on 64-bit under heavy workloads for weeks of continuous
usage. Each server (of 16
total) had four XFS filesystems, each with 250GB of table data (no indexes) on them, total of 16 Terabytes. We tested
with the TPC-H schema and queries.
We use the default settings for XFS.
Also - be aware that LVM has a serious performance bottleneck at about 600MB/s - if you are working below that
threshold, you may not notice the issue, maybe some increase in CPU consumption as you approach it.
- Luke
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 02:42 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
2006-08-02 20:59 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 21:26 ` Re: XFS filessystem for Datawarehousing Steve Poe <steve.poe@gmail.com>
@ 2006-08-02 23:33 ` Michael Stone <mstone+postgres@mathom.us>
2006-08-03 04:05 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
1 sibling, 1 reply; 19+ messages in thread
From: Michael Stone @ 2006-08-02 23:33 UTC (permalink / raw)
To: pgsql-performance
On Wed, Aug 02, 2006 at 02:26:39PM -0700, Steve Poe wrote:
>For the past year, I have been running odbc-bench on a dual-opteron with
>4GB of RAM using a 8GB sample data. I found the performance difference
>between EXT3, JFS, and XFS is +/- 5-8%.
That's not surprising when your db is only 2x your RAM. You'll find that
filesystem performance is much more important when your database is 10x+
your RAM (which is often the case once your database heads toward a TB).
>Testing newer kernels and read-ahead patches may benefit you as well.
I've been really impressed by the adaptive readahead patches with
postgres.
Mike Stone
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 02:42 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
2006-08-02 20:59 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 21:26 ` Re: XFS filessystem for Datawarehousing Steve Poe <steve.poe@gmail.com>
2006-08-02 23:33 ` Re: XFS filessystem for Datawarehousing Michael Stone <mstone+postgres@mathom.us>
@ 2006-08-03 04:05 ` Luke Lonergan <llonergan@greenplum.com>
2006-08-03 19:44 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
0 siblings, 1 reply; 19+ messages in thread
From: Luke Lonergan @ 2006-08-03 04:05 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; pgsql-performance
Again - the performance difference increases as the disk speed increases.
Our experience is that we went from 300MB/s to 475MB/s when moving from ext3
to xfs.
- Luke
On 8/2/06 4:33 PM, "Michael Stone" <mstone+postgres@mathom.us> wrote:
> On Wed, Aug 02, 2006 at 02:26:39PM -0700, Steve Poe wrote:
>> For the past year, I have been running odbc-bench on a dual-opteron with
>> 4GB of RAM using a 8GB sample data. I found the performance difference
>> between EXT3, JFS, and XFS is +/- 5-8%.
>
> That's not surprising when your db is only 2x your RAM. You'll find that
> filesystem performance is much more important when your database is 10x+
> your RAM (which is often the case once your database heads toward a TB).
>
>> Testing newer kernels and read-ahead patches may benefit you as well.
>
> I've been really impressed by the adaptive readahead patches with
> postgres.
>
> Mike Stone
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 02:42 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
2006-08-02 20:59 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 21:26 ` Re: XFS filessystem for Datawarehousing Steve Poe <steve.poe@gmail.com>
2006-08-02 23:33 ` Re: XFS filessystem for Datawarehousing Michael Stone <mstone+postgres@mathom.us>
2006-08-03 04:05 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
@ 2006-08-03 19:44 ` Milen Kulev <makulev@gmx.net>
2006-08-04 03:41 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 19+ messages in thread
From: Milen Kulev @ 2006-08-03 19:44 UTC (permalink / raw)
To: 'Luke Lonergan' <llonergan@greenplum.com>; 'Michael Stone' <mstone+postgres@mathom.us>; pgsql-performance
Hi Luke,
That is ~ 50% increase !! Amazing...
How many reader processes did you have to get this results ?
Regards. Milen
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Luke Lonergan
Sent: Thursday, August 03, 2006 6:05 AM
To: Michael Stone; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] XFS filessystem for Datawarehousing
Again - the performance difference increases as the disk speed increases.
Our experience is that we went from 300MB/s to 475MB/s when moving from ext3 to xfs.
- Luke
On 8/2/06 4:33 PM, "Michael Stone" <mstone+postgres@mathom.us> wrote:
> On Wed, Aug 02, 2006 at 02:26:39PM -0700, Steve Poe wrote:
>> For the past year, I have been running odbc-bench on a dual-opteron
>> with 4GB of RAM using a 8GB sample data. I found the performance
>> difference between EXT3, JFS, and XFS is +/- 5-8%.
>
> That's not surprising when your db is only 2x your RAM. You'll find
> that filesystem performance is much more important when your database
> is 10x+ your RAM (which is often the case once your database heads
> toward a TB).
>
>> Testing newer kernels and read-ahead patches may benefit you as well.
>
> I've been really impressed by the adaptive readahead patches with
> postgres.
>
> Mike Stone
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 02:42 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
2006-08-02 20:59 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 21:26 ` Re: XFS filessystem for Datawarehousing Steve Poe <steve.poe@gmail.com>
2006-08-02 23:33 ` Re: XFS filessystem for Datawarehousing Michael Stone <mstone+postgres@mathom.us>
2006-08-03 04:05 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
2006-08-03 19:44 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
@ 2006-08-04 03:41 ` Luke Lonergan <llonergan@greenplum.com>
0 siblings, 0 replies; 19+ messages in thread
From: Luke Lonergan @ 2006-08-04 03:41 UTC (permalink / raw)
To: Milen Kulev <makulev@gmx.net>; 'Michael Stone' <mstone+postgres@mathom.us>; pgsql-performance
Milen,
On 8/3/06 12:44 PM, "Milen Kulev" <makulev@gmx.net> wrote:
> Hi Luke,
> That is ~ 50% increase !! Amazing...
> How many reader processes did you have to get this results ?
Just one - I'll refresh the results sometime and post.
- Luke
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-02 02:42 ` Re: XFS filessystem for Datawarehousing Luke Lonergan <llonergan@greenplum.com>
2006-08-02 20:59 ` Re: XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
@ 2006-08-02 22:43 ` Chris Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 19+ messages in thread
From: Chris Browne @ 2006-08-02 22:43 UTC (permalink / raw)
To: pgsql-performance
makulev@gmx.net ("Milen Kulev") writes:
> I am pretty exited whether XFS will clearly outpertform ETX3 (no
> default setups for both are planned !). I am not sure whether is it
> worth to include JFS in comparison too ...
I did some benchmarking about 2 years ago, and found that JFS was a
few percent faster than XFS which was a few percent faster than ext3,
on a "huge amounts of writes" workload.
That the difference was only a few percent made us draw the conclusion
that FS performance was fairly much irrelevant. It is of *vastly*
more importance whether the filesystem will survive power outages and
the like, and, actually, Linux hasn't fared as well with that as I'd
like. :-(
The differences are small enough that what you should *actually* test
for is NOT PERFORMANCE.
You should instead test for reliability.
- Turn off the power when the DB is under load, and see how well it
survives.
- Pull the fibrechannel cable, and see if the filesystem (and
database) survives when under load.
If you find that XFS is 4% faster, that's likely a *terrible*
trade-off if it only survives power outage half as often as (say)
ext3.
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://cbbrowne.com/info/wp.html
"C combines the power of assembler language with the convenience of
assembler language." -- Unknown
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
@ 2006-08-03 07:10 Koth, Christian (DWBI) <Christian.Koth@smiths-heimann.com>
2006-08-03 07:17 ` Re: XFS filessystem for Datawarehousing Florian Weimer <fweimer@bfk.de>
2006-08-03 09:39 ` Re: XFS filessystem for Datawarehousing Michael Stone <mstone+postgres@mathom.us>
0 siblings, 2 replies; 19+ messages in thread
From: Koth, Christian (DWBI) @ 2006-08-03 07:10 UTC (permalink / raw)
To: Milen Kulev <makulev@gmx.net>; +Cc: pgsql-performance
Milen,
> XFS, EXT3, JFS
For what reason are you planning to use a journaling FS? I think using WAL, fsyncing every transaction and using a journaling FS is tautologous. And if you have problems using EXT2 you can just add the journal later without loosing data.
My tests using EXT2 showed a performance boost up to 50% on INSERTs.
Christian
> I am pretty exited whether XFS will clearly outpertform ETX3
> (no default setups for both are planned !). I am not sure
> whether is it worth to include JFS in comparison too ...
>
>
> Best Regards,
> Milen Kulev
>
******************************************
The information contained in, or attached to, this e-mail, may contain confidential information and is intended solely for the use of the individual or entity to whom they are addressed and may be subject to legal privilege. If you have received this e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your system and notify your system manager. Please do not copy it for any purpose, or disclose its contents to any other person. The views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of the company. The recipient should check this e-mail and any attachments for the presence of viruses. The company accepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email.
******************************************
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-03 07:10 Re: XFS filessystem for Datawarehousing Koth, Christian (DWBI) <Christian.Koth@smiths-heimann.com>
@ 2006-08-03 07:17 ` Florian Weimer <fweimer@bfk.de>
1 sibling, 0 replies; 19+ messages in thread
From: Florian Weimer @ 2006-08-03 07:17 UTC (permalink / raw)
To: Koth, Christian (DWBI) <Christian.Koth@smiths-heimann.com>; +Cc: Milen Kulev <makulev@gmx.net>; pgsql-performance
* Christian Koth:
> For what reason are you planning to use a journaling FS? I think
> using WAL, fsyncing every transaction and using a journaling FS is
> tautologous.
The journal is absolutely required to preserve the integrity of the
file system's own on-disk data structures after a crash. Even if
you've got a trustworthy file system checker (there are surprisingly
few of them, especially for advanced file systems without fixed data
structure locations), running it after a crash usually leads to
unacceptably high downtime.
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Durlacher Allee 47 tel: +49-721-96201-1
D-76131 Karlsruhe fax: +49-721-96201-99
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: XFS filessystem for Datawarehousing
2006-08-03 07:10 Re: XFS filessystem for Datawarehousing Koth, Christian (DWBI) <Christian.Koth@smiths-heimann.com>
@ 2006-08-03 09:39 ` Michael Stone <mstone+postgres@mathom.us>
1 sibling, 0 replies; 19+ messages in thread
From: Michael Stone @ 2006-08-03 09:39 UTC (permalink / raw)
To: pgsql-performance
On Thu, Aug 03, 2006 at 01:10:39AM -0600, Koth, Christian (DWBI) wrote:
>For what reason are you planning to use a journaling FS? I think using WAL, fsyncing every transaction and using a journaling FS is tautologous. And if you have problems using EXT2 you can just add the journal later without loosing data.
>My tests using EXT2 showed a performance boost up to 50% on INSERTs.
The requirements for the WAL filesystem and for the data filesystem are
different. Having the WAL on a small ext2 filesystem makes sense and is
good for performance. Having the data on a huge ext2 filesystem is a
horrible idea, because you'll fsck forever if there's a crash, and
because ext2 isn't a great performer for large filesystems. I typically
have a couple-gig ext2 WAL paired with a couple of couple-hundred-gig
xfs data & index partitions. Note that the guarantees of a journaling fs
like xfs have nothing to do with the kind of journaling done by the WAL,
and each has its place on a postgres system.
Mike Stone
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2006-08-07 16:55 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-01 21:49 XFS filessystem for Datawarehousing Milen Kulev <makulev@gmx.net>
2006-08-01 22:46 ` J. Andrew Rogers <jrogers@neopolitan.com>
2006-08-01 22:59 ` Milen Kulev <makulev@gmx.net>
2006-08-02 00:42 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-08-04 20:44 ` Jim C. Nasby <jnasby@pervasive.com>
2006-08-07 16:55 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-08-02 02:06 ` Mark Kirkwood <markir@paradise.net.nz>
2006-08-02 02:42 ` Luke Lonergan <llonergan@greenplum.com>
2006-08-02 20:59 ` Milen Kulev <makulev@gmx.net>
2006-08-02 21:26 ` Steve Poe <steve.poe@gmail.com>
2006-08-02 21:44 ` Milen Kulev <makulev@gmx.net>
2006-08-02 23:33 ` Michael Stone <mstone+postgres@mathom.us>
2006-08-03 04:05 ` Luke Lonergan <llonergan@greenplum.com>
2006-08-03 19:44 ` Milen Kulev <makulev@gmx.net>
2006-08-04 03:41 ` Luke Lonergan <llonergan@greenplum.com>
2006-08-02 22:43 ` Chris Browne <cbbrowne@acm.org>
2006-08-03 07:10 Re: XFS filessystem for Datawarehousing Koth, Christian (DWBI) <Christian.Koth@smiths-heimann.com>
2006-08-03 07:17 ` Florian Weimer <fweimer@bfk.de>
2006-08-03 09:39 ` Michael Stone <mstone+postgres@mathom.us>
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