Re: Tuning for PostGreSQL Database

From: Sean Chittenden (sean_at_chittenden.org)
Date: 07/21/03

  • Next message: Jim C. Nasby: "Re: Tuning for PostGreSQL Database" 
    Date: Mon, 21 Jul 2003 13:16:45 -0700
To: "Jim C. Nasby" <jim@nasby.net>

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

    Good solution for these concerns? Get a UPS.

    > Luckily, the metadata for these files is changed very infrequently
    > (normally), so this shouldn't prove to be much of a performance
    > impact.

    Correct.

    -sc 

    -- 
Sean Chittenden
_______________________________________________
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: Jim C. Nasby: "Re: Tuning for PostGreSQL Database"

    Relevant Pages

    • Re: Tuning for PostGreSQL Database
      ... >> So are you saying that softupdates on whatever partition contains ... >> the database transaction logs is dangerous? ... > volatle backed buffer increases the danger of data loss. ... Of course the *real* issue is ensuring that no matter what, ...
      (freebsd-performance)
    • Re: Tuning for PostGreSQL Database
      ... > Softupdates drastically improves meta-data performance, ... If you crash you may lose more work than ... If you have a file system (such as the root file ... > on the root file system during a typical install. ...
      (freebsd-performance)
    • Re: Tuning for PostGreSQL Database
      ... Softupdates is just a better async. ... If you have a file system (such as the root file ...
      (freebsd-performance)
    • Re: Disabling background fsck; disabling softupdates; defaulting to "fsck -y"
      ... I have encountered problems where key system services ... start reading garbage from the file system before fsck completes. ... corruption, should I turn off softupdates? ...
      (comp.unix.bsd.freebsd.misc)