Sequential Writes (t-logs) on a Separate Volume ? - Storage

This is a discussion on Sequential Writes (t-logs) on a Separate Volume ? - Storage ; I made a choice recently and that choice cost me more than I wanted it to. Now I need to understand WHY I made the choice. I configured my SQL Server database in such a way that the transaction logs ...

+ Reply to Thread
Results 1 to 5 of 5

Thread: Sequential Writes (t-logs) on a Separate Volume ?

  1. Sequential Writes (t-logs) on a Separate Volume ?

    I made a choice recently and that choice cost me more than I wanted it
    to. Now I need to understand WHY I made the choice.

    I configured my SQL Server database in such a way that the transaction
    logs are placed on a separate RAID 1 volume. I've scoured the SQL newsgroup
    and I think I understand the rationale behind this: Transaction logs are
    written are sequentially. Placing the transaction log on the same volume as
    the data would cause the sequential writes of the transaction log to be
    interrupted by the random read/writes of the data file. This would be
    inefficient.

    Here's my follow-up question: Let's say I have a single RAID 0+1 volume
    that allows for 500 IOs/second. Let's say that my database uses 300
    IOs/second including both read/writes of data and writes to the transaction
    log. In this case, even if I place both the data file and the transaction
    file on the same volume, I'm still not using all of the IOs/second that are
    available to me. Why then would it make sense to place my transaction log on
    a separate RAID 1 volume given that I'm not even making full use of my MAIN
    volume?

    Please show me the path.

    Thank you.



  2. Re: Sequential Writes (t-logs) on a Separate Volume ?

    The Oracle wrote:
    > I made a choice recently and that choice cost me more than I wanted it
    > to. Now I need to understand WHY I made the choice.
    >
    > I configured my SQL Server database in such a way that the transaction
    > logs are placed on a separate RAID 1 volume. I've scoured the SQL newsgroup
    > and I think I understand the rationale behind this: Transaction logs are
    > written are sequentially. Placing the transaction log on the same volume as
    > the data would cause the sequential writes of the transaction log to be
    > interrupted by the random read/writes of the data file. This would be
    > inefficient.
    >
    > Here's my follow-up question: Let's say I have a single RAID 0+1 volume
    > that allows for 500 IOs/second. Let's say that my database uses 300
    > IOs/second including both read/writes of data and writes to the transaction
    > log. In this case, even if I place both the data file and the transaction
    > file on the same volume, I'm still not using all of the IOs/second that are
    > available to me. Why then would it make sense to place my transaction log on
    > a separate RAID 1 volume given that I'm not even making full use of my MAIN
    > volume?


    If you're using a hardware controller with its own stable write-back
    cache (though it really should be a mirrored cache if you want the same
    reliability guarantees you get with the mirrored disks), it might not
    make any sense: the cache firmware should be smart enough to gather up
    the sequential log writes and batch them out lazily to the platters.

    Otherwise, the advantage of isolating the transaction logs is that
    you'll avoid any seek overhead when writing to them: the head will
    already be on-cylinder, and it'll just have to wait for the target disk
    location to arrive under it. This will cut your log-write latency by as
    much as 2/3 (actually, it could be more if the database doesn't
    prioritize its log writes over other writes that the log write might
    then queue behind), and since a competent database will perform almost
    all its other writes lazily you could conceivably see as much as a 3x
    performance improvement (though in practical terms I'd guess you'd never
    see more than a doubling in performance - e.g., because if your activity
    is that hot you'll be seeing a complete disk revolution's worth of
    log-write latency rather than the half-rev which is nominally 'average';
    ; even that level of improvement might be rare, given the way a good
    database 'batches' up new log writes while waiting for the previous
    write to complete, since long disk latencies then just allow more log
    records to be included in the next log write and transfer overheads
    start to make seek latency less important).

    - bill

  3. Re: Sequential Writes (t-logs) on a Separate Volume ?

    Bill Todd wrote:

    [something twice]

    Apologies: Firefox has an annoying habit of bringing up a second copy
    of itself when one double-clicks a folder (and my mouse is getting old
    and bouncy). If one does not notice this apparently both copies submit
    the post...

    - bill

  4. Re: Sequential Writes (t-logs) on a Separate Volume ?

    On Wed, 30 Mar 2005 09:17:51 -0800, "The Oracle"
    <9thFloor@Building502.TheMatrix.com> wrote:

    > I made a choice recently and that choice cost me more than I wanted it
    >to. Now I need to understand WHY I made the choice.
    >
    > I configured my SQL Server database in such a way that the transaction
    >logs are placed on a separate RAID 1 volume. I've scoured the SQL newsgroup
    >and I think I understand the rationale behind this: Transaction logs are
    >written are sequentially. Placing the transaction log on the same volume as
    >the data would cause the sequential writes of the transaction log to be
    >interrupted by the random read/writes of the data file. This would be
    >inefficient.
    >
    > Here's my follow-up question: Let's say I have a single RAID 0+1 volume
    >that allows for 500 IOs/second. Let's say that my database uses 300
    >IOs/second including both read/writes of data and writes to the transaction
    >log. In this case, even if I place both the data file and the transaction
    >file on the same volume, I'm still not using all of the IOs/second that are
    >available to me. Why then would it make sense to place my transaction log on
    >a separate RAID 1 volume given that I'm not even making full use of my MAIN
    >volume?
    >
    > Please show me the path.
    >
    >Thank you.
    >



    As Bill points out the biggest issue at that point is seek time of
    drive heads. The IO's for drives are under optimal conditions, db
    workload is not an optimal condition.

    The other reason for the segregation is the possibility of
    snapshot'ing or cloning. You don't want your transaction logs to be
    recovered from such a mechanism along with the data files. you'd lose
    the ability to recover to the last transaction.
    Of course if you don't do any of those techniques then it just falls
    back to seek time...

    ~F

  5. Re: Sequential Writes (t-logs) on a Separate Volume ?

    On Wed, 30 Mar 2005 09:17:51 -0800, "The Oracle"
    <9thFloor@Building502.TheMatrix.com> wrote:

    > I made a choice recently and that choice cost me more than I wanted it
    >to. Now I need to understand WHY I made the choice.
    >
    > I configured my SQL Server database in such a way that the transaction
    >logs are placed on a separate RAID 1 volume. I've scoured the SQL newsgroup
    >and I think I understand the rationale behind this: Transaction logs are
    >written are sequentially. Placing the transaction log on the same volume as
    >the data would cause the sequential writes of the transaction log to be
    >interrupted by the random read/writes of the data file. This would be
    >inefficient.
    >
    > Here's my follow-up question: Let's say I have a single RAID 0+1 volume
    >that allows for 500 IOs/second. Let's say that my database uses 300
    >IOs/second including both read/writes of data and writes to the transaction
    >log. In this case, even if I place both the data file and the transaction
    >file on the same volume, I'm still not using all of the IOs/second that are
    >available to me. Why then would it make sense to place my transaction log on
    >a separate RAID 1 volume given that I'm not even making full use of my MAIN
    >volume?
    >
    > Please show me the path.
    >
    >Thank you.
    >


    It is not a great idea to have the logs on the same LUN as the data
    for the basic reason that with a LUN - even with RAID 1+0 (hopefully
    you're striping your mirrored sets) - there are failure scenarios that
    can take the LUN out of service. There are OS failures, software
    failures, etc. that may affect a LUN, but are less likely to affect
    multiple LUNs.

    (And you should have your backups to take care of multiple LUN
    failures - and of course, these should save the logs with the data in
    an appropriate manner as well, just in case.)

    There may be some folks who think that striping mirrored sets will
    provide ultimate availability enough to keep them both on the same
    LUN. However, for folks who make a living in the High
    Availability/Disaster Recovery/ Business Continuity biz, this is not
    recommended. Experience is often the best teacher in this case.

    --- jls
    The preceding message was personal opinion only.
    I do not speak in any authorized capacity for anyone,
    and certainly not my employer.

+ Reply to Thread