Thread: 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

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

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

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

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

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

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

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

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

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

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

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
>

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
journalingFS 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
forthe use of the individual or entity to whom they are addressed and may be subject to legal privilege.  If you have
receivedthis e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your
systemand 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
thoseof the company.  The recipient should check this e-mail and any attachments for the presence of viruses.  The
companyaccepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email. 
******************************************

* 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

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

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

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

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

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.