Обсуждение: Best Linux filesystem for Postgres data store ?

"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

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

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
jumpin and say that testing with your workload is really the only way to know things for sure. (Obviously, more
spindlesimplies 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
ofpretty graphs.)  The controller seemed to accept a bunch of writes quickly, then when full then try to pump them all
todisk, which took a non-trivial number of seconds. So we'd see the interesting state of iostat reporting zero disk
transactionsper second, but busy time = 100% and service time being hundreds of millseconds.<br /> Switching to write
throughmade things much faster overall. Not what I expected.<br /><br /> This was a PERC 5, so a few years ago. No
doubtthings 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><divclass="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 href="http://www.logicmonitor.com"
target="_blank">www.logicmonitor.com</a></td><td><br/></td><td class="bottom" style="text-align: right;"> Ph: 1 888 41
LOGICx500<br /> Ph: 1 805 698 0770</td></tr></tbody></table><p>  <br /></div></div> 

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-diskRAID10 array with 10K SATA disks.  Just one data point... 

Craig

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.

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