Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    94

    Unanswered: moving transaction log to a dedicated array

    Hello Gurus,

    I had a question on the performance benefits of relocating the transaction log to a dedicated disk array.

    We have a BCU setup at our DB2 shop here - with 48 partitions on several LPARs on the same rack. I am told the services implement a "shared-nothing" inter-partition architecture.

    However, I also understand the database files for individual partitions are on the same physical array as the transaction log - my question: wouldnt that slow down logged operations (updates/deletes/logged inserts) significantly?

    Aren't there significant performance benefits to having the log on a seperate array?

    And besides, fundamentally, the log was also envisioned to help recover from problems such as disk crashes - if it is on the same disk as the database, wouldnt that defeat the purpose?

    I need to build a case - but I want to make sure I am doing it for the right reasons - could you help me out please? Would there be significant performance benefits? if so, would there be significant costs to this change? I know I am not giving you much of my environment to be very specific in your assessment - but just ballpark figures from your experience would be tremendously helpful!

    Thanks as always!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    In an OLTP database it is considered a good practice to define database log on a device separate from those used for data (and other things). However, in modern SAN environments this becomes 1) difficult and 2) unnecessary.

    From your mentioning BCU I suspect that you have a data warehouse. Normally you would not have many logged operations in such an environment.

    In any case, only the monitoring of I/O utilization can tell you whether you may achieve any benefits from separating logs from data.

  3. #3
    Join Date
    Oct 2010
    Posts
    94
    Thanks n_i for getting back!

    Yes we are running Warehouse on the BCU - however, most of the loads involve a logged operation since most loads apply incremental changes only to the Warehouse and marts.

    When you mention, relocating the t-log to a dedicated becomes unnecessary, are you suggesting, that in a SAN environment, it probably is already on a different array? Not sure i understand.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    For my BCU, I have log files going to a different array than the partitioned data (each partition has its own dedicated array for data, and a global shared array across all partitions for misc stuff like logs). Backups are on a totally different SAN.

    Even for OLTP, I recommend separate mount points for logs, which do not share any spindles (physical drives) with the data mount points. There are two reasons for this:

    1. So that even if there is an array failure for the data, I can recover the db with backup and logs on separate disk.

    2. Most data is stored on SAN's using RAID-5 or RAID-6 arrays, and logs should have the fastest disk possible which would be RAID 10 (which is basically stripped with mirroring). RAID-5/6 can be a bit slow for writes. This is more expensive than RAID-5/6, but since log space is relatively small compared to data, it should not be a big deal. DB2 transaction logging is the only place where synchronous I/O takes place (all other I/O is cached and deferred for better performance) except for LOB data, so that is why log write speed is important.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by getback0 View Post

    When you mention, relocating the t-log to a dedicated becomes unnecessary, are you suggesting, that in a SAN environment, it probably is already on a different array? Not sure i understand.
    It may be unnecessary, because it may not give you performance or availability benefits. Your log I/O requests will be going through the same FC interface as all other requests, and if the entire SAN array goes down it won't matter if logs were on a separate LUN (while for a modern enterprise-level storage system failure of a handful of disks is a non-issue).

    It may be impossible, because you cannot carve out a LUN that is not shared with anything else. Say, your log space requirement is 20 GB (too much anyway), while each storage LUN is a 12+2 array of 143 GB drives. You will never convince your storage admins to give you one LUN just for logs.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by n_i View Post
    You will never convince your storage admins to give you one LUN just for logs.
    We have separate LUNs for logs. I am not claiming that they don't share physical disks with someone else, but they don't share disks with the data for the database they belong to (and we have more than one cabinet in our SAN).

    For a data warehouse, on a BCU shipped from IBM, there is a separate array for misc stuff, separate from the data arrays. It is not dedicated to logs only, but logs and data do not share the same arrays. This one misc array can be spread across multiple physical servers in the BCU config. We did not purchase a BCU config from IBM, but we set our DPF system up the same way as an supplied BCU based on documentation provided to us by IBM.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Oct 2010
    Posts
    94
    Quote Originally Posted by Marcus_A View Post
    We have separate LUNs for logs. I am not claiming that they don't share physical disks with someone else, but they don't share disks with the data for the database they belong to (and we have more than one cabinet in our SAN).

    For a data warehouse, on a BCU shipped from IBM, there is a separate array for misc stuff, separate from the data arrays. It is not dedicated to logs only, but logs and data do not share the same arrays. This one misc array can be spread across multiple physical servers in the BCU config. We did not purchase a BCU config from IBM, but we set our DPF system up the same way as an supplied BCU based on documentation provided to us by IBM.
    Marcus - just wanted to get your thoughts again (sorry for the redundancy), doesnt separating logs from data to different disks result in significant throughput performance difference compared to when they are on the same disk?

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by getback0 View Post
    Marcus - just wanted to get your thoughts again (sorry for the redundancy), doesnt separating logs from data to different disks result in significant throughput performance difference compared to when they are on the same disk?
    It definitely would in a DPF (Data Partitioning Feature, or InfoSphere Data Warehouse as it is now called) configuration. In theory it would help for OLTP also, but large modern SAN's are typically spread across so many different disks it may not make as much difference. A BCU configuration shipped from IBM for DPF has dedicated disk arrays for each logical partition, and separate arrays for logs/misc files. Each array has its own disks.

    The other consideration is that one would not want DB2 transaction log files on the same physical disks as the data because of recovery issues if the disk array failed. Probably better to put at least the mirror log on local internal disk if possible.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Oct 2010
    Posts
    94
    SWWEEEETTTT!!!!!
    Quote Originally Posted by Marcus_A View Post
    It definitely would in a DPF (Data Partitioning Feature, or InfoSphere Data Warehouse as it is now called) configuration.
    Thanks for getting back!


    Its unfortunate that the DBA at my shop here fails to appreciate the benefit of this inspite of recognizing that most of the traffic in our DW environment is logged (which is another issue). And clearly logged transactions would benefit from having logs seggregated from data. Sure enough, the management has decided to spend more money and throw in more resources (memory and partitions) in the existing BCU.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •