ext3 data journaling - Setup

This is a discussion on ext3 data journaling - Setup ; I'm installing Suse 11 for a database server, and need to set up the data file system to be as reliable as possible. It's going to be ext3 mounted with the sync flag. I was looking at the journaling options, ...

+ Reply to Thread
Results 1 to 8 of 8

Thread: ext3 data journaling

  1. ext3 data journaling

    I'm installing Suse 11 for a database server, and need to set up the data file
    system to be as reliable as possible.
    It's going to be ext3 mounted with the sync flag. I was looking at the
    journaling options, and was wondering if journaling the data was worth it. From
    a data safety perspective, the data is on the disk whether it writes it to the
    data journal or directly to the file. While a partial write to a file would
    normally cause problems, in a transactional database the incomplete writes would
    just be rolled back.
    I can see a performance advantage if the journal is on different hardware, so as
    to handle burst conditions.
    Am I missing something? Does anyone have any recommendations?

    Thanks, Rick DeBay


  2. Re: ext3 data journaling

    Rick DeBay wrote:
    > I'm installing Suse 11 for a database server, and need to set up the data
    > file system to be as reliable as possible. It's going to be ext3 mounted
    > with the sync flag. I was looking at the journaling options, and was
    > wondering if journaling the data was worth it. From a data safety
    > perspective, the data is on the disk whether it writes it to the data
    > journal or directly to the file. While a partial write to a file would
    > normally cause problems, in a transactional database the incomplete
    > writes would just be rolled back. I can see a performance advantage if
    > the journal is on different hardware, so as to handle burst conditions.
    > Am I missing something? Does anyone have any recommendations?
    >

    I recommend you get on a mailing list for the dbms you are using. You want
    one thing for IBM's DB2, another for postgreSQL, and I understand yet
    another thing for MySql. For DB2, on writes on the equivalent of a raw file
    system (i.e., a disk partition) and DB2 manages everything. For postgreSQL,
    you might as well use ext2, since it does its own journalling. I do not know
    about MySql.


    --
    .~. Jean-David Beyer Registered Linux User 85642.
    /V\ PGP-Key: 9A2FC99A Registered Machine 241939.
    /( )\ Shrewsbury, New Jersey http://counter.li.org
    ^^-^^ 23:15:01 up 21 days, 17 min, 3 users, load average: 4.30, 4.11, 4.03

  3. Re: ext3 data journaling

    In article <2qyJk.1408$r_3.1043@nwrddc02.gnilink.net>, Jean-David Beyer says...
    >
    >Rick DeBay wrote:
    >> I'm installing Suse 11 for a database server, and need to set up the data
    >> file system to be as reliable as possible. It's going to be ext3 mounted
    >> with the sync flag. I was looking at the journaling options, and was
    >> wondering if journaling the data was worth it. From a data safety
    >> perspective, the data is on the disk whether it writes it to the data
    >> journal or directly to the file. While a partial write to a file would
    >> normally cause problems, in a transactional database the incomplete
    >> writes would just be rolled back. I can see a performance advantage if
    >> the journal is on different hardware, so as to handle burst conditions.
    >> Am I missing something? Does anyone have any recommendations?
    >>

    >I recommend you get on a mailing list for the dbms you are using. You want
    >one thing for IBM's DB2, another for postgreSQL, and I understand yet
    >another thing for MySql. For DB2, on writes on the equivalent of a raw file
    >system (i.e., a disk partition) and DB2 manages everything. For postgreSQL,
    >you might as well use ext2, since it does its own journalling. I do not know
    >about MySql.


    I'm using Firebird. For it, all I need to worry about is that when the OS
    replies that a write is complete, it had better be. Hence the use of sync, and
    my exploring ext3's journaling of data.
    For the general case, does journaling the data offer anything beyond the two
    cases (partial file appends and burst performance) mentioned?


  4. Re: ext3 data journaling

    Am 16.10.2008, 05:02 Uhr, schrieb Rick DeBay :

    > I'm installing Suse 11 for a database server, and need to set up the
    > data file
    > system to be as reliable as possible.
    > It's going to be ext3 mounted with the sync flag. I was looking at the
    > journaling options, and was wondering if journaling the data was worth
    > it. From
    > a data safety perspective, the data is on the disk whether it writes it
    > to the
    > data journal or directly to the file. While a partial write to a file
    > would
    > normally cause problems, in a transactional database the incomplete
    > writes would
    > just be rolled back.
    > I can see a performance advantage if the journal is on different
    > hardware, so as
    > to handle burst conditions.
    > Am I missing something? Does anyone have any recommendations?


    The main advantage of journalling is the possibility to bring the
    filesystem into a consistent state without the need for an extensive
    consistency check after a crash. This does not mean that loss of data
    (especially those held in the various write caches during the crash) can
    be reliably prevented in any case. However, it increases convenience and
    adds a bit of security margin at little tradeoff.
    For your purpose, you should strongly consider the use of a RAID system,
    plus an adequate backup strategy.

    Ansgar

    --
    Mails an die angegebene Adresse erreichen mich - oder auch nicht.
    Nützliche Adresse gibt's bei Bedarf!
    Mail to the given address may or may not reach me - useful address will be
    given when required!

  5. Re: ext3 data journaling

    Rick DeBay wrote:
    > In article <2qyJk.1408$r_3.1043@nwrddc02.gnilink.net>, Jean-David Beyer says...
    >> Rick DeBay wrote:
    >>> I'm installing Suse 11 for a database server, and need to set up the data
    >>> file system to be as reliable as possible. It's going to be ext3 mounted
    >>> with the sync flag. I was looking at the journaling options, and was
    >>> wondering if journaling the data was worth it. From a data safety
    >>> perspective, the data is on the disk whether it writes it to the data
    >>> journal or directly to the file. While a partial write to a file would
    >>> normally cause problems, in a transactional database the incomplete
    >>> writes would just be rolled back. I can see a performance advantage if
    >>> the journal is on different hardware, so as to handle burst conditions.
    >>> Am I missing something? Does anyone have any recommendations?
    >>>

    >> I recommend you get on a mailing list for the dbms you are using. You want
    >> one thing for IBM's DB2, another for postgreSQL, and I understand yet
    >> another thing for MySql. For DB2, on writes on the equivalent of a raw file
    >> system (i.e., a disk partition) and DB2 manages everything. For postgreSQL,
    >> you might as well use ext2, since it does its own journalling. I do not know
    >> about MySql.

    >
    > I'm using Firebird.


    I do not know anything about that one, nor how it works inside.

    > For it, all I need to worry about is that when the OS
    > replies that a write is complete, it had better be.


    I do not think you can ever know that. The best you can hope for, I believe,
    is that it made it to the cache in disk controller and perhaps all the way
    tot he cache in the hard drive.

    > Hence the use of sync,


    In the old days, this merely scheduled that the buffers would be written as
    soon as the system could get to it. Now days, in Linux, it waits until the
    buffers have been written to the hard drive's controller (that may contain a
    cache) or to the hard drive's cache. But in neither case does it guarantee
    that the data have been written from the hard drive's cache to the
    platter(s). So to be sure, you would have to disable the write caching
    altogether in the hard drive. I do not know if you can do that with all drives.

    > and
    > my exploring ext3's journaling of data.


    On my machine, I notice a noticeable, though small, slowdown when using ext3
    instead of ext2, for the database partitions on my system.

    > For the general case, does journaling the data offer anything beyond the two
    > cases (partial file appends and burst performance) mentioned?
    >

    It depends on the dbms. For DB2, you do not get the choice, since it uses
    raw (unmounted) partitions and does its own journaling. For postgreSQL, it
    offers very little, other than slowing you down a little, because postgreSQL
    does its own journaling anyway. I do not know about Firebird.

    --
    .~. Jean-David Beyer Registered Linux User 85642.
    /V\ PGP-Key: 9A2FC99A Registered Machine 241939.
    /( )\ Shrewsbury, New Jersey http://counter.li.org
    ^^-^^ 07:55:01 up 21 days, 8:57, 3 users, load average: 4.29, 4.10, 4.02

  6. Re: ext3 data journaling

    Ansgar Strickerschmidt wrote:
    > Am 16.10.2008, 05:02 Uhr, schrieb Rick DeBay :
    >
    >> I'm installing Suse 11 for a database server, and need to set up the
    >> data file system to be as reliable as possible. It's going to be ext3
    >> mounted with the sync flag. I was looking at the journaling options,
    >> and was wondering if journaling the data was worth it. From a data
    >> safety perspective, the data is on the disk whether it writes it to the
    >> data journal or directly to the file. While a partial write to a file
    >> would normally cause problems, in a transactional database the
    >> incomplete writes would just be rolled back. I can see a performance
    >> advantage if the journal is on different hardware, so as to handle
    >> burst conditions. Am I missing something? Does anyone have any
    >> recommendations?

    >
    > The main advantage of journalling is the possibility to bring the
    > filesystem into a consistent state without the need for an extensive
    > consistency check after a crash. This does not mean that loss of data
    > (especially those held in the various write caches during the crash) can
    > be reliably prevented in any case. However, it increases convenience and
    > adds a bit of security margin at little tradeoff. For your purpose, you
    > should strongly consider the use of a RAID system, plus an adequate
    > backup strategy.
    >

    In a RAID system, you will typically need a battery backup (internal to the
    RAID controller) to be sure the RAID will get the writes done in the event
    of a power failure.

    --
    .~. Jean-David Beyer Registered Linux User 85642.
    /V\ PGP-Key: 9A2FC99A Registered Machine 241939.
    /( )\ Shrewsbury, New Jersey http://counter.li.org
    ^^-^^ 08:10:01 up 21 days, 9:12, 4 users, load average: 5.23, 4.66, 4.28

  7. Re: ext3 data journaling

    Rick DeBay wrote:
    > I'm installing Suse 11 for a database server, and need to set up the data file
    > system to be as reliable as possible.
    > It's going to be ext3 mounted with the sync flag. I was looking at the
    > journaling options, and was wondering if journaling the data was worth it. From
    > a data safety perspective, the data is on the disk whether it writes it to the
    > data journal or directly to the file. While a partial write to a file would
    > normally cause problems, in a transactional database the incomplete writes would
    > just be rolled back.
    > I can see a performance advantage if the journal is on different hardware, so as
    > to handle burst conditions.
    > Am I missing something? Does anyone have any recommendations?
    >
    > Thanks, Rick DeBay
    >

    Its a hell of a performance hit to force writes with sync.

    What IS worth doing is to write the transaction logs to different
    hardware than the database. On the basis that a periodic backup of the
    database itself, plus the live transaction log files, enables you to
    roll forward from a trashed database with no data loss, and if you back
    up the transaction logs nightly, a complete machine crash only loses
    less than days data and by and large, if the transaction log alone gets
    trashed, you lose no data.

    I.e. if you put database and log on different disks, and back te
    database up and clear the logs periodically, you can survive the loss of
    either disk with no data loss.

    If you back up the log nightly, you can survive total machine loss with
    only the last 24 hours lost.






  8. Re: ext3 data journaling

    The Natural Philosopher wrote:

    > Its a hell of a performance hit to force writes with sync.


    True.
    >
    > What IS worth doing is to write the transaction logs to different
    > hardware than the database.


    It is extremely important to do that, at least with postgreSQL, but I would
    imagine for any database. I have 6 hard drives on my machine, with 4 of them
    having one partition each, just for the database. For any particular table,
    I put the index on a different hard drive from the associated data to
    minimize seek contention while loading the database, something I do often.

    I put the Write-Ahead-Log as they call it on a separate hard drive of its
    own. Even then, a bulk database load that does mainly INSERTs (don't ask) is
    seek-limited on the WAL drive. Not IOWAIT: seek!

    --
    .~. Jean-David Beyer Registered Linux User 85642.
    /V\ PGP-Key: 9A2FC99A Registered Machine 241939.
    /( )\ Shrewsbury, New Jersey http://counter.li.org
    ^^-^^ 12:30:01 up 21 days, 13:32, 3 users, load average: 4.41, 4.40, 4.30

+ Reply to Thread