Re: Tuning for PostGreSQL Database

From: Jim C. Nasby (jim_at_nasby.net)
Date: 07/21/03

  • Next message: Jason Stone: "Re: Tuning for PostGreSQL Database" 
    Date: Mon, 21 Jul 2003 15:24:00 -0500
To: Sean Chittenden <sean@chittenden.org>

    On Mon, Jul 21, 2003 at 01:16:45PM -0700, Sean Chittenden wrote:
    > > > > > Softupdates on, async off. Softupdates is just a better
    > > > > > async.
    > > > >
    > > > > postgresql fsync's all its files before returning from a commit
    > > > > in order to ensure durability, right? Does softupdates
    > > > > interfere with the functioning of sync(2)/fsync(2)?
    > > >
    > > > It can, yes, but that's the risk of soft updates. From tuning(7):
    > > >
    > > > Softupdates drastically improves meta-data performance,
    > > > mainly file creation and deletion. We recommend enabling
    > > > softupdates on most file systems; however, there are two
    > > > limitations to softupdates that you should be aware of when
    > > > determining whether to use it on a file system. First,
    > > > softupdates guarantees file system consistency in the case of
    > > > a crash but could very easily be several seconds (even a
    > > > minute!) behind on pending write to the physical disk. If
    > > > you crash you may lose more work than otherwise. Secondly,
    > > > softupdates delays the freeing of file system blocks. If you
    > > > have a file system (such as the root file system) which is
    > > > close to full, doing a major update of it, e.g. ``make
    > > > installworld'', can run it out of space and cause the update
    > > > to fail. For this reason, softupdates will not be enabled on
    > > > the root file system during a typical install.
    > >
    > > So are you saying that softupdates on whatever partition contains
    > > the database transaction logs is dangerous?
    >
    > Well, think about it. What happens when you pull the plug while the
    > OS is sitting on a pile of data that it's waiting to flush to disk?
    > The problem isn't so much with the WAL logs, so much as it is with the
    > actual files that contain the databases/table's data. Any kind of
    > volatle backed buffer increases the danger of data loss.
     
    If you pull the plug you're going to lose in-flight transactions no
    matter what (or at least you better). Once you commit, it should hit
    disk, at least in the WAL. Shouldn't sync/fsync ensure this even with
    softupdates?

    Of course the *real* issue is ensuring that no matter what, the WAL hits
    the disk before the main table data does, so that you can do proper
    recovery (though I'm not as clear on how this works with MVCC). If the
    issue is only a matter of delayed writes across the board, I don't see
    that it should really matter... it's just as if the plug got pulled a
    little earlier. 

    -- 
Jim C. Nasby, Database Consultant                  jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.org"
  • Next message: Jason Stone: "Re: Tuning for PostGreSQL Database"

    Relevant Pages

    • Re: Tuning for PostGreSQL Database
      ... >> determining whether to use it on a file system. ... >> softupdates guarantees file system consistency in the case of ... > the database transaction logs is dangerous? ... volatle backed buffer increases the danger of data loss. ...
      (freebsd-performance)
    • Re: ISA Bollox
      ... was no danger to children. ... People who know about the crime will never judge me at face value. ... their life. ... It doesn't matter what I am like now, it doesn't matter that I pose no ...
      (uk.legal)
    • Re: Heres a condensed and refined version of my theory
      ... :>: scenario, but if, as I think, it does matter and she does realise ... :>: the danger, then we are not going to see such a resolution. ... Attacking Rowling for justifying slavery ... However, insofar as the usage predates, or refers to concepts which ...
      (alt.fan.harry-potter)
    • Re: signalling the nature of conflict
      ... or losing his soul and underlines the danger of either option. ... And Shien had students now as well. ... to put him off in this matter even while Shien was ...
      (rec.arts.sf.composition)
    • Re: Heres a condensed and refined version of my theory
      ... scenario, but if, as I think, it does matter and she does realise ... the danger, then we are not going to see such a resolution. ... > justifies their lives even starting.Perhaps that analogy would ... Attacking Rowling for justifying slavery ...
      (alt.fan.harry-potter)