pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBest Linux filesystem for Postgres data store ?
8+ messages / 8 participants
[nested] [flat]
* Best Linux filesystem for Postgres data store ?
@ 2010-11-30 10:17 Lello, Nick <nick.lello@rentrakmail.com>
2010-12-01 15:54 ` Re: Best Linux filesystem for Postgres data store ? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-12-01 19:07 ` Re: Best Linux filesystem for Postgres data store ? Chris Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 8+ messages in thread
From: Lello, Nick @ 2010-11-30 10:17 UTC (permalink / raw)
To: pgsql-admin
What is considered the best filesystem to use for postgres data stores ?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Linux filesystem for Postgres data store ?
2010-11-30 10:17 Best Linux filesystem for Postgres data store ? Lello, Nick <nick.lello@rentrakmail.com>
@ 2010-12-01 15:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-12-01 16:05 ` Re: Best Linux filesystem for Postgres data store ? Steve Francis <sfrancis@logicmonitor.com>
2010-12-01 16:08 ` Re: Best Linux filesystem for Postgres data store ? Ray Stell <stellr@cns.vt.edu>
2010-12-01 17:58 ` Re: Best Linux filesystem for Postgres data store ? Craig James <craig_james@emolecules.com>
1 sibling, 3 replies; 8+ messages in thread
From: Kevin Grittner @ 2010-12-01 15:54 UTC (permalink / raw)
To: pgsql-admin; Nick Lello <nick.lello@rentrakmail.com>
"Lello, Nick" <nick.lello@rentrakmail.com> wrote:
> What is considered the best filesystem to use for postgres data
> stores ?
I held off for a bit to see if someone else would jump in with a
comparison of filesystems, but so far nobody has taken the bait.
The reason I held off is that the only reasonable answer I can think
of is, "It depends."
Personally, I would stay away from any filesystem without journaling
for allocations; which, for example, leaves out ext2. For various
reasons I would also avoid ReiserFS. We've had good luck with xfs
in our shop.
There are some issues with recoverability of some file systems in
some circumstances. There are often important configuration or
mount option issues. What choices you make there will depend
heavily on your usage. (A social networking site with data
replicated to multiple servers may prefer speed over recoverability
of any single server; most installations will not.)
Your RAID configuration and controllers will likely make a bigger
difference than your filesystem choices. Be sure to get good RAID
controllers with battery-backed (BBU) cache, configured for
write-back.
If you post with more particulars, you will probably get more people
jumping in with more specific advice.
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Linux filesystem for Postgres data store ?
2010-11-30 10:17 Best Linux filesystem for Postgres data store ? Lello, Nick <nick.lello@rentrakmail.com>
2010-12-01 15:54 ` Re: Best Linux filesystem for Postgres data store ? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-12-01 16:05 ` Steve Francis <sfrancis@logicmonitor.com>
2 siblings, 0 replies; 8+ messages in thread
From: Steve Francis @ 2010-12-01 16:05 UTC (permalink / raw)
To: pgsql-admin
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
On 12/1/2010 7:54 AM, Kevin Grittner wrote:
<blockquote cite="mid:4CF61B430200002500038009@gw.wicourts.gov"
type="cite"><br>
<pre wrap="">
Your RAID configuration and controllers will likely make a bigger
difference than your filesystem choices. Be sure to get good RAID
controllers with battery-backed (BBU) cache, configured for
write-back.
</pre>
</blockquote>
I agree that storage makes a bigger difference than filesystems in
most cases.<br>
Just wanted to jump in and say that testing with your workload is
really the only way to know things for sure. (Obviously, more
spindles implies more IOps, more cache implies faster writes...)<br>
But I have seen cases where a dell PERC RAID controller (not sure
that qualifies as "good") slowed things down when configured for
writeback with a write heavy workload. We noticed very spiky disk IO
rates on some database servers (we are monitoring company, so have
lots and lots of pretty graphs.) The controller seemed to accept a
bunch of writes quickly, then when full then try to pump them all to
disk, which took a non-trivial number of seconds. So we'd see the
interesting state of iostat reporting zero disk transactions per
second, but busy time = 100% and service time being hundreds of
millseconds.<br>
Switching to write through made things much faster overall. Not what
I expected.<br>
<br>
This was a PERC 5, so a few years ago. No doubt things have
improved, but the point is to test and monitor.<br>
<br>
<div class="moz-signature">-- <br>
<style type="text/css"> .sig, .sig a, .sig td { font: 12px Arial; color: DarkBlue; white-space: nowrap; vertical-align: top; text-align: left; } .sig img { border: 0; } .sig a { font: 11px Arial; } .sig .back { color: #ffffff; background-color: DarkBlue; text-decoration: none; } .sig .right { text-align: right; } .sig .border { border: 1px solid DarkBlue; margin:5px; border-collapse: collapse; } .sig .bottom { vertical-align:bottom } .sig p { clear:both; margin:2px; } </style>
<div class="sig">
<table class="border">
<tbody>
<tr>
<td class="back"> <b>Steve Francis</b></td>
<td class="back" style="width: 30px;"><br>
</td>
<td class="back" style="text-align: right;"><b>LogicMonitor
LLC</b></td>
</tr>
<tr>
<td><br>
</td>
<td><br>
</td>
<td class="right"><br>
</td>
</tr>
<tr>
<td class="bottom"><a class="moz-txt-link-abbreviated" href="mailto:sfrancis@logicmonitor.com">sfrancis@logicmonitor.com</a><br>
<a target="_blank" href="http://www.logicmonitor.com">www.logicmonitor.com</a></td;
<td><br>
</td>
<td class="bottom" style="text-align: right;"> Ph: 1 888
41 LOGIC x500<br>
Ph: 1 805 698 0770</td>
</tr>
</tbody>
</table>
<p> <br>
</p>
</div>
</div>
</body>
</html>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Linux filesystem for Postgres data store ?
2010-11-30 10:17 Best Linux filesystem for Postgres data store ? Lello, Nick <nick.lello@rentrakmail.com>
2010-12-01 15:54 ` Re: Best Linux filesystem for Postgres data store ? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-12-01 16:08 ` Ray Stell <stellr@cns.vt.edu>
2 siblings, 0 replies; 8+ messages in thread
From: Ray Stell @ 2010-12-01 16:08 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin@postgresql.org, Nick Lello <nick.lello@rentrakmail.com>
On Wed, Dec 01, 2010 at 09:54:11AM -0600, Kevin Grittner wrote:
> "Lello, Nick" <nick.lello@rentrakmail.com> wrote:
>
> > What is considered the best filesystem to use for postgres data
> > stores ?
>
Greg dedicates a chapter in his book on the topic:
https://www.packtpub.com/postgresql-9-0-high-performance/book
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Linux filesystem for Postgres data store ?
2010-11-30 10:17 Best Linux filesystem for Postgres data store ? Lello, Nick <nick.lello@rentrakmail.com>
2010-12-01 15:54 ` Re: Best Linux filesystem for Postgres data store ? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-12-01 17:58 ` Craig James <craig_james@emolecules.com>
2 siblings, 0 replies; 8+ messages in thread
From: Craig James @ 2010-12-01 17:58 UTC (permalink / raw)
To: pgsql-admin
On 12/1/10 7:54 AM, Kevin Grittner wrote:
> "Lello, Nick"<nick.lello@rentrakmail.com> wrote:
>
>> What is considered the best filesystem to use for postgres data
>> stores ?
>
> I held off for a bit to see if someone else would jump in with a
> comparison of filesystems, but so far nobody has taken the bait.
> The reason I held off is that the only reasonable answer I can think
> of is, "It depends."
>
> Personally, I would stay away from any filesystem without journaling
> for allocations; which, for example, leaves out ext2. For various
> reasons I would also avoid ReiserFS. We've had good luck with xfs
> in our shop.
We used xfs with good results for a couple years. We recently reinstalled the latest Linux and Postgres on the same hardware, and our tests showed ext4 gave slightly better performance than xfs so we switched to ext4. This is on a BBU 8-disk RAID10 array with 10K SATA disks. Just one data point...
Craig
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Linux filesystem for Postgres data store ?
2010-11-30 10:17 Best Linux filesystem for Postgres data store ? Lello, Nick <nick.lello@rentrakmail.com>
@ 2010-12-01 19:07 ` Chris Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 8+ messages in thread
From: Chris Browne @ 2010-12-01 19:07 UTC (permalink / raw)
To: pgsql-admin
nick.lello@rentrakmail.com ("Lello, Nick") writes:
> What is considered the best filesystem to use for postgres data stores ?
What is your metric for "best"?
Several are plausible:
- Fastest, for write workload (read performance doesn't usually vary
much based on filesystem)
- Fastest, for recovery from problems (e.g. - spontaneous reboot, where
journalled options are generally preferred)
- Best supported by some vendor (which historically tends to point
towards ext3, away from JFS and ReiserFS, with ambiguous results for
XFS)
- Most reliable (under some defined set of conditions, which tend not to
be well-defined)
- Perhaps some other metric, such as ability to support atomic backups
(where interesting options include ZFS, or running other filesystems
layered atop LVM)
I could see there being five different answers.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://linuxdatabases.info/info/fs.html
He doesn't have much of a reputation, or so I've heard.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Linux filesystem for Postgres data store ?
@ 2010-12-01 18:28 Lou Picciano <loupicciano@comcast.net>
2010-12-01 21:31 ` Re: Best Linux filesystem for Postgres data store ? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Lou Picciano @ 2010-12-01 18:28 UTC (permalink / raw)
To: pgsql-admin; +Cc: Craig James <craig_james@emolecules.com>
OK, weighing in here...
We use ZFS on Solaris for our data stores. Can't beat ZFS for its failsafe features, filesystem portability, etc.
Having said that, there is that school of thought which would hold that journaling may not be all that indicated anymore, given the quality of recovery possible from the binary logs. In fact, this would suggest that journaling may even be counterproductive - effectively redundant - in a transaction-heavy environment.
Lou
----- Original Message -----
From: "Craig James" <craig_james@emolecules.com>
To: pgsql-admin@postgresql.org
Sent: Wednesday, December 1, 2010 12:58:42 PM
Subject: Re: [ADMIN] Best Linux filesystem for Postgres data store ?
On 12/1/10 7:54 AM, Kevin Grittner wrote:
> "Lello, Nick"<nick.lello@rentrakmail.com> wrote:
>
>> What is considered the best filesystem to use for postgres data
>> stores ?
>
> I held off for a bit to see if someone else would jump in with a
> comparison of filesystems, but so far nobody has taken the bait.
> The reason I held off is that the only reasonable answer I can think
> of is, "It depends."
>
> Personally, I would stay away from any filesystem without journaling
> for allocations; which, for example, leaves out ext2. For various
> reasons I would also avoid ReiserFS. We've had good luck with xfs
> in our shop.
We used xfs with good results for a couple years. We recently reinstalled the latest Linux and Postgres on the same hardware, and our tests showed ext4 gave slightly better performance than xfs so we switched to ext4. This is on a BBU 8-disk RAID10 array with 10K SATA disks. Just one data point...
Craig
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Linux filesystem for Postgres data store ?
2010-12-01 18:28 Re: Best Linux filesystem for Postgres data store ? Lou Picciano <loupicciano@comcast.net>
@ 2010-12-01 21:31 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2010-12-01 21:31 UTC (permalink / raw)
To: Lou Picciano <loupicciano@comcast.net>; +Cc: pgsql-admin; Craig James <craig_james@emolecules.com>
Lou Picciano <loupicciano@comcast.net> writes:
> We use ZFS on Solaris for our data stores. Can't beat ZFS for its failsafe features, filesystem portability, etc.
> Having said that, there is that school of thought which would hold that journaling may not be all that indicated anymore, given the quality of recovery possible from the binary logs. In fact, this would suggest that journaling may even be counterproductive - effectively redundant - in a transaction-heavy environment.
Well, the conventional wisdom is that you want metadata journaling, but
*not* data journaling, at the filesystem level. Postgres can protect
its data just fine, but it can't recover if the filesystem goes insane.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-12-01 21:31 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-30 10:17 Best Linux filesystem for Postgres data store ? Lello, Nick <nick.lello@rentrakmail.com>
2010-12-01 15:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-12-01 16:05 ` Steve Francis <sfrancis@logicmonitor.com>
2010-12-01 16:08 ` Ray Stell <stellr@cns.vt.edu>
2010-12-01 17:58 ` Craig James <craig_james@emolecules.com>
2010-12-01 19:07 ` Chris Browne <cbbrowne@acm.org>
2010-12-01 18:28 Re: Best Linux filesystem for Postgres data store ? Lou Picciano <loupicciano@comcast.net>
2010-12-01 21:31 ` Tom Lane <tgl@sss.pgh.pa.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