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:
I have checked on Suse Linux 10 sp1 and there is /db2 disk partition with the following options:
According to article there sould be set for Ubuntu:
and for Suse:
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

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.

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."

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.

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?"

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.)

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

BPHR = buffer pool hit ratio

On Linux, it may heavily depend on the file system you are using.

I use ext3 on both Linux systems (System z and System x).

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.

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

My guess is that DB2 doesn't have a dependency on atime. Usually, you are only interested in the mtime anyway.