Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: What is DB2 disk IO performance benefit setting noatime in /etc/fstab Linux kernel

    Hi,
    I am using DB2 v9.5 FP2a on two different Linux distro Suse Linux 10 sp1 (IBM mainframe System z) and Ubuntu 8.10 (Intel Sytem x server).

    I have been reading some articles that suggest to set Linux kernel parameter noatime in /etc/fstab to increase read performance. Linux kernel by default makes writes! to disk for every file it reads! just to update last access timestamp of file it was accessed. This produces way to many disk IO operations totally unnecessary.

    There is article from IBM web page Tuning LAMP systems, Part 1: Understanding the LAMP architecture: "The first order of business is to ensure that atime logging is disabled on file systems. The atime is the last access time of a file, and each time a file is accessed, the underlying file system must record this timestamp. Because atime is rarely used by systems administrators, disabling it frees up some disk time. This is accomplished by adding the noatime option in the fourth column of /etc/fstab."

    More info on this topic:
    Reducing Disk IO By Mounting Partitions With noatime for Ubuntu
    The atime and noatime attribute
    Optimizing filesystem access
    Linux kernel developers

    I have checked on my Ubuntu 8.10 system and there is:
    Code:
    UUID=091c7637-f087-4e24-a1ec-dbb57224d5f3 / ext3 relatime,errors=remount-ro 0 1
    I have checked on Suse Linux 10 sp1 and there is /db2 disk partition with the following options:
    Code:
    /dev/db2vg/db2lv /db2 ext3 acl,user_xattr 1 2
    According to article there sould be set for Ubuntu:
    Code:
    UUID=091c7637-f087-4e24-a1ec-dbb57224d5f3 / ext3 noatime,errors=remount-ro 0 1
    and for Suse:
    Code:
    /dev/db2vg/db2lv /db2 ext3 acl,user_xattr,noatime 1 2
    I can't find any articles that recommends this settings for DB2. But if there is recommendation for MySQL it is probably as well suitable for DB2.

    Questions:
    1. How is this this noatime parameter set on your DB2 database Linux environment? Can you please check yours /etc/fstab file?
    2. Has anybody measured performance benefits using SQLs (especialy select statemsts) using this kernel settings?

    Regards,
    Grofaty
    Last edited by grofaty; 12-30-08 at 14:02.

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Hi Grofaty,

    Just went through different articles on this and what I could understand is whether to get the benefit or no-benefit by having "noatime" is best judged by the owner of that particular application/Database. In some cases it works great and in some other cases, it shows no improvement.

    This is from one of those different article "This option works the best if external storage is used, for example, SAN."

    In our system (Linux 2 version6 with 64 GB and 8 CPU), this is not being used but having no issues with I/O.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by JAYANTA_DATTA
    In some cases it works great and in some other cases, it shows no improvement.
    What gain attention to me was two things: (a) On IBM page was suggestion to set this setting for MySQL database and (b) is the sentence from Linux kernel developers debate: "Whenever you read from a file, even one in memory cache.... do a write!" So if I understand correctly even if data in database are in bufferpool every time that data are accessed from memory an update of timestamp for atime is given. If I understand correctly this could have an impact. And then few posts later: "I didnt mean to say that it is _always_ a big issue, but "only a small number of files are read" is a very, very small minority of even the database server world."

    Quote Originally Posted by JAYANTA_DATTA
    This is from one of those different article "This option works the best if external storage is used, for example, SAN."
    You probably read this from Linux Tuning Parameters web site. And yes, we are using external storage, Linux on "System z" we are using Enterprise Storage Server.

    Quote Originally Posted by JAYANTA_DATTA
    In our system (Linux 2 version6 with 64 GB and 8 CPU), this is not being used but having no issues with I/O.
    We also don't have a IO performance problem. But what I am wondering is: "Do we have no disk IO problems because we have a 'big machine' and if we can lower down disk IO, could we have a smaller machine?"
    Last edited by grofaty; 01-02-09 at 04:32.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The impact with external storage is bigger, of course. When you read from a file, you have 1 round-trip: send the request to the SAN and get the data back. With atime, you have another round-trip to update the atime on the storage system. While both round-trips could be combined on the initial read, this would not work when the data is in the file system cache, in which case, you just have the update for atime.

    What do you mean with "big machine"? The atime stuff is not CPU intensive - it causes I/O. So you could avoid this additional I/O by deactivating it. Whether this helps in your situation depends on the workload you process. For example, if you have lots of table scans and a not-so-good BPHR, you have more disk I/O, which would be less expensive with atime. On the other side, the Linux kernel developers try to reduce the impact of atime handling by batching-up such updates. (At least, that's what I read the other day.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by stolze
    For example, if you have lots of table scans and a not-so-good BPHR...
    What is BPHR?

    Has anyone tested this atime settings and impact it has on DB2 performance on yours system? By the way, I have also find the same parameter on Windows, which is also not turned on by default. So it could also benefit on performance on DB2 on Windows.

    But what is my point: What is IBM recommendation for setting atime when using DB2 on Linux? I can't find any article to recommend this settings for DB2, but I could find an IBM article for suggesting this settings for LAMP.
    Regards
    Last edited by grofaty; 01-02-09 at 09:16.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    BPHR = buffer pool hit ratio

    On Linux, it may heavily depend on the file system you are using.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    I use ext3 on both Linux systems (System z and System x).

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I will not give an "IBM recommendation" beyond the traditional "it depends". Personally, I would turn off atime unless you run applications that need it.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    I only use DB2 on this Linux computer. What I meant with "IBM recommendation" is: "Does DB2 uses atime to work properly?" Probably not and because there is only DB2 on this Linux I could turn this parameter off.
    Regards

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My guess is that DB2 doesn't have a dependency on atime. Usually, you are only interested in the mtime anyway.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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