SEO

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

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

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
>

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

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend