Professional Web Applications Themes

Log directory & raw device - IBM DB2

Hi, From V7 (I believe) one can set the log directory for the database log files on a raw device. I have a few questions on that. 1) Would this indeed give a performance improvement? 2) Since the database only writes to 1 log file at a time and the OS prevents multiple processes to write to 1 file, will thus by putting the log files on a raw device allow for more log processes or would the log process use like asynchronous IO to write to a log file without having to wait till the response comes back that ...

  1. #1

    Default Log directory & raw device

    Hi,

    From V7 (I believe) one can set the log directory for the database log files
    on a raw device. I have a few questions on that.

    1) Would this indeed give a performance improvement?

    2) Since the database only writes to 1 log file at a time and the OS
    prevents multiple processes to write to 1 file, will thus by putting the log
    files on a raw device allow for more log processes or would the log process
    use like asynchronous IO to write to a log file without having to wait till
    the response comes back that it's been written and thus continue with
    writing the next block?

    3) Currently we have it on a filesystem with caching enabled (Veritas
    filesystem). Would disabling the filesystem cache give a performance
    improvement (so that it would come close to a raw device)?

    4) How can one test this? There is no information on how much time the log
    process has spent writing the log files like one has for the tablespaces. So
    how can one know if indeed we are doing faster IO now?

    Thank you.


    Erik Hendrix Guest

  2. #2

    Default Re: Log directory & raw device

    We added support for raw logs in v5, fp3 (March 1998). It was done to
    support some TPC benchamrks on Solaris, so its original intent was
    performance. I do not have any experience with customers doing this in
    production (although I'm sure some are). Evil things can happen if the
    device used for raw logging fills up.

    Erik Hendrix wrote:
    > Hi,
    >
    > From V7 (I believe) one can set the log directory for the database log files
    > on a raw device. I have a few questions on that.
    >
    > 1) Would this indeed give a performance improvement?
    >
    > 2) Since the database only writes to 1 log file at a time and the OS
    > prevents multiple processes to write to 1 file, will thus by putting the log
    > files on a raw device allow for more log processes or would the log process
    > use like asynchronous IO to write to a log file without having to wait till
    > the response comes back that it's been written and thus continue with
    > writing the next block?
    >
    > 3) Currently we have it on a filesystem with caching enabled (Veritas
    > filesystem). Would disabling the filesystem cache give a performance
    > improvement (so that it would come close to a raw device)?
    >
    > 4) How can one test this? There is no information on how much time the log
    > process has spent writing the log files like one has for the tablespaces. So
    > how can one know if indeed we are doing faster IO now?
    >
    > Thank you.
    >
    >
    Blair Adamache Guest

  3. #3

    Default Re: Log directory & raw device

    Pierre Saint-Jacques <sesconsnospam.attglobal.net> wrote in message news:<O6LPa.10764$ru2.1140185news20.bellglobal.co m>...
    > Here are my two cents worth from what other people have told me. It may
    > not be much but I hope it helps.
    > >
    > > 4) How can one test this? There is no information on how much time the log
    > > process has spent writing the log files like one has for the tablespaces. So
    > > how can one know if indeed we are doing faster IO now?
    > ###### The closest you can get to this info. is by monitoring direct
    > read/write requests and times out of your snapshot outputs.
    > >
    Can I butt in here and ask - is that correct about direct read/writes
    - I thought this was just LOB read write activity under Direct
    read/write?

    thanks
    Charles
    riehe Guest

  4. #4

    Default Re: Log directory & raw device

    Pierre Saint-Jacques <sesconsnospam.attglobal.net> wrote in message news:<O6LPa.10764$ru2.1140185news20.bellglobal.co m>...
    > Here are my two cents worth from what other people have told me. It may
    > not be much but I hope it helps.
    > >
    > > 4) How can one test this? There is no information on how much time the log
    > > process has spent writing the log files like one has for the tablespaces. So
    > > how can one know if indeed we are doing faster IO now?
    > ###### The closest you can get to this info. is by monitoring direct
    > read/write requests and times out of your snapshot outputs.
    > >
    Can I butt in here and ask - is that correct about direct read/writes
    - I thought this was just LOB read write activity under Direct
    read/write?

    thanks
    Charles
    riehe Guest

  5. #5

    Default Re: Log directory & raw device

    Wouldn't those evil things happen as well when the log_dir would fill up?

    Any idea on how one could monitor the performance or determine how wel/bad
    it is doing in regards with IO for the logging?

    Thanks.

    "Blair Adamache" <badamache2muchspam.> wrote in message
    news:ben4gm$7ta$1hanover.torolab.ibm.com...
    > We added support for raw logs in v5, fp3 (March 1998). It was done to
    > support some TPC benchamrks on Solaris, so its original intent was
    > performance. I do not have any experience with customers doing this in
    > production (although I'm sure some are). Evil things can happen if the
    > device used for raw logging fills up.
    >
    > Erik Hendrix wrote:
    >
    > > Hi,
    > >
    > > From V7 (I believe) one can set the log directory for the database log
    files
    > > on a raw device. I have a few questions on that.
    > >
    > > 1) Would this indeed give a performance improvement?
    > >
    > > 2) Since the database only writes to 1 log file at a time and the OS
    > > prevents multiple processes to write to 1 file, will thus by putting the
    log
    > > files on a raw device allow for more log processes or would the log
    process
    > > use like asynchronous IO to write to a log file without having to wait
    till
    > > the response comes back that it's been written and thus continue with
    > > writing the next block?
    > >
    > > 3) Currently we have it on a filesystem with caching enabled (Veritas
    > > filesystem). Would disabling the filesystem cache give a performance
    > > improvement (so that it would come close to a raw device)?
    > >
    > > 4) How can one test this? There is no information on how much time the
    log
    > > process has spent writing the log files like one has for the
    tablespaces. So
    > > how can one know if indeed we are doing faster IO now?
    > >
    > > Thank you.
    > >
    > >
    >

    Erik Hendrix Guest

  6. #6

    Default Re: Log directory & raw device

    Can this be confirmed that DB2 will force to write through the cache? When
    doing a truss the only thing I see happening is a pwrite. But I don't know
    if this could be set to go straight through the cache or not.

    On the direct read/write, I also thought this was for like LOB's and LONG's.
    If it also puts in there for the logger then would it be correct that if one
    adds up all the direct writes for the tablespaces and compare that to the
    database snapshot that the difference would be from the logger? Same for the
    direct write time?

    Thanks.


    "Pierre Saint-Jacques" <sesconsnospam.attglobal.net> wrote in message
    news:O6LPa.10764$ru2.1140185news20.bellglobal.com ...
    > Here are my two cents worth from what other people have told me. It may
    > not be much but I hope it helps.
    > See folded in your note.
    > HTH, Pierre.
    >
    > Erik Hendrix wrote:
    > > 3) Currently we have it on a filesystem with caching enabled (Veritas
    > > filesystem). Would disabling the filesystem cache give a performance
    > > improvement (so that it would come close to a raw device)?
    > ###### I believe that for writing to logs, essentially as results of
    > commits, DB2 will force writing thru thr cache to ensure physical
    > externalisation of the data and therefore recoverability. SO I don't
    > think that for those, DB2 will use the cache.
    > >
    > > 4) How can one test this? There is no information on how much time the
    log
    > > process has spent writing the log files like one has for the
    tablespaces. So
    > > how can one know if indeed we are doing faster IO now?
    > ###### The closest you can get to this info. is by monitoring direct
    > read/write requests and times out of your snapshot outputs.
    > >
    > > Thank you.
    > >
    > >
    >

    Erik Hendrix Guest

  7. #7

    Default Re: Log directory & raw device

    Presumably you're talking about Solaris. On Solaris, Db2 can do direct
    I/O support to avoid double buffering and virtual memory thrashing when
    regular files are used for data and logs.

    Erik Hendrix wrote:
    > Can this be confirmed that DB2 will force to write through the cache? When
    > doing a truss the only thing I see happening is a pwrite. But I don't know
    > if this could be set to go straight through the cache or not.
    >
    > On the direct read/write, I also thought this was for like LOB's and LONG's.
    > If it also puts in there for the logger then would it be correct that if one
    > adds up all the direct writes for the tablespaces and compare that to the
    > database snapshot that the difference would be from the logger? Same for the
    > direct write time?
    >
    > Thanks.
    >
    >
    > "Pierre Saint-Jacques" <sesconsnospam.attglobal.net> wrote in message
    > news:O6LPa.10764$ru2.1140185news20.bellglobal.com ...
    >
    >>Here are my two cents worth from what other people have told me. It may
    >>not be much but I hope it helps.
    >>See folded in your note.
    >>HTH, Pierre.
    >>
    >>Erik Hendrix wrote:
    >>
    >>>3) Currently we have it on a filesystem with caching enabled (Veritas
    >>>filesystem). Would disabling the filesystem cache give a performance
    >>>improvement (so that it would come close to a raw device)?
    >>
    >>###### I believe that for writing to logs, essentially as results of
    >>commits, DB2 will force writing thru thr cache to ensure physical
    >>externalisation of the data and therefore recoverability. SO I don't
    >>think that for those, DB2 will use the cache.
    >>
    >>>4) How can one test this? There is no information on how much time the
    >
    > log
    >
    >>>process has spent writing the log files like one has for the
    >
    > tablespaces. So
    >
    >>>how can one know if indeed we are doing faster IO now?
    >>
    >>###### The closest you can get to this info. is by monitoring direct
    >>read/write requests and times out of your snapshot outputs.
    >>
    >>>Thank you.
    >>>
    >>>
    >>
    >
    >
    Blair Adamache Guest

  8. #8

    Default Re: Log directory & raw device

    Hey Blair,

    ok, so it sounds to me that overall going to a raw device for log files is
    not suggested unless there is a absolute need for it in regards with
    performance.
    But how can I determine if there is a need for it? None of the snapshot data
    (as far as I can tell) will tell me how much time has been spent writing to
    the log files like we can see for tablespaces.
    I can only know how many log pages have been written/read, but now how much
    time was spent doing this.
    Since logging is so crucial to overall DB performance, it would be nice to
    find out if the IO the database is doing there is optimal or not.
    Do you know of any methods on how to determine this without starting to run
    benchmarks?
    We have nightly batch jobs and they use up a lot of log space. No problem
    there, but since they are using this amount of log space, the performance
    we're doing disk IO for the log files becomes more crucial I would think.
    But how can one thus detemine if we do not have a bottleneck there and if we
    do, if there is anything we can do about?

    Thanks.


    "Blair Adamache" <badamache2muchspam.> wrote in message
    news:bf1329$j32$1hanover.torolab.ibm.com...
    > Presumably you're talking about Solaris. On Solaris, Db2 can do direct
    > I/O support to avoid double buffering and virtual memory thrashing when
    > regular files are used for data and logs.
    >
    > Erik Hendrix wrote:
    >
    > > Can this be confirmed that DB2 will force to write through the cache?
    When
    > > doing a truss the only thing I see happening is a pwrite. But I don't
    know
    > > if this could be set to go straight through the cache or not.
    > >
    > > On the direct read/write, I also thought this was for like LOB's and
    LONG's.
    > > If it also puts in there for the logger then would it be correct that if
    one
    > > adds up all the direct writes for the tablespaces and compare that to
    the
    > > database snapshot that the difference would be from the logger? Same for
    the
    > > direct write time?
    > >
    > > Thanks.
    > >
    > >
    > > "Pierre Saint-Jacques" <sesconsnospam.attglobal.net> wrote in message
    > > news:O6LPa.10764$ru2.1140185news20.bellglobal.com ...
    > >
    > >>Here are my two cents worth from what other people have told me. It may
    > >>not be much but I hope it helps.
    > >>See folded in your note.
    > >>HTH, Pierre.
    > >>
    > >>Erik Hendrix wrote:
    > >>
    > >>>3) Currently we have it on a filesystem with caching enabled (Veritas
    > >>>filesystem). Would disabling the filesystem cache give a performance
    > >>>improvement (so that it would come close to a raw device)?
    > >>
    > >>###### I believe that for writing to logs, essentially as results of
    > >>commits, DB2 will force writing thru thr cache to ensure physical
    > >>externalisation of the data and therefore recoverability. SO I don't
    > >>think that for those, DB2 will use the cache.
    > >>
    > >>>4) How can one test this? There is no information on how much time the
    > >
    > > log
    > >
    > >>>process has spent writing the log files like one has for the
    > >
    > > tablespaces. So
    > >
    > >>>how can one know if indeed we are doing faster IO now?
    > >>
    > >>###### The closest you can get to this info. is by monitoring direct
    > >>read/write requests and times out of your snapshot outputs.
    > >>
    > >>>Thank you.
    > >>>
    > >>>
    > >>
    > >
    > >
    >

    Erik Hendrix Guest

Similar Threads

  1. Replies: 2
    Last Post: October 8th, 09:57 AM
  2. Replies: 1
    Last Post: July 4th, 12:23 AM
  3. Replies: 1
    Last Post: May 21st, 03:47 PM
  4. rsync exclude file - directory name without directory contents
    By emilio lazardo in forum Linux / Unix Administration
    Replies: 0
    Last Post: December 3rd, 10:28 PM
  5. wher is the Qt directory and KDE directory in Mandrake Linux 9.1?
    By Paul in forum Linux Setup, Configuration & Administration
    Replies: 0
    Last Post: July 24th, 01:16 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139