DB2 Linux - AIO and DIO questions

[mairhtin o'feannag]

Hello,

After reading the fine article on the Stinger enhancements for Linux, I
have two questions. First, our application is in essence a warehouse,
with 100-400 million rows for various tables.

Having said that, the AIO enhancements seem to be geared to an OLTP
environment. Our environment is that we load MASSIVE amounts of data
every couple of months, and then run various queries against this massive
influx. Seems to me that our environment would not make that much use of
AIO? Most of the document that I read -
ftp://ftp.software.ibm.com/software/...ingerlinux.pdf
seems to indicate that I/O cleaners are made more efficient by the use of
AIO. Which, since we read but do not write, appears to be obviated.
Second : DIO. Again, some mention of OLTP workload was referenced, but
I wanted to know if the avoidance of file system caching would aid in our
throughput, since we are doing massive reads, but no writes. The article
didn't seem too clear on the nature of the benefits - i.e. read vs write.

Any help, discussion, etc. would be appreciated. We are somewhat limited
in our budgets, so any improvements would be looked upon with great
favor!

Thanks,

Mairhtin

[Haider Rizvi]

Don't see a response to this post, so here are my thoughts.

As you are probably aware, DB2 does its own "asynchronous" i/o using the
prefetchers (or io servers); "asynchronous" meaning separately from the
agent that needs the page. Currently db2 doesn't use the asynchronous
i/o api provided in linux for prefetching data.

Regarding direct i/o, the correct answer is it depends on the details of
the workload and your configuration. If you have set up DB2 such that a
large part of the memory is being utilized in bufferpool / sort areas
then perhaps filesystem caching doesn't give you anything. In other
words, turning it off may be useful.

Here is a paper on using direct i/o on aix, db2 usage is exactly the
same on linux.

http://www-128.ibm.com/developerwork...lee/index.html

"mairhtin o'feannag" <ir**********@rocketmaildot.com> writes:

Hello,

After reading the fine article on the Stinger enhancements for Linux, I have two
questions. First, our application is in essence a warehouse, with 100-400
million rows for various tables.

Having said that, the AIO enhancements seem to be geared to an OLTP environment.
Our environment is that we load MASSIVE amounts of data every couple of months,
and then run various queries against this massive influx. Seems to me that our
environment would not make that much use of AIO? Most of the document that I
read - ftp://ftp.software.ibm.com/software/...ingerlinux.pdf
seems to indicate that I/O cleaners are made more efficient by the use of AIO.
Which, since we read but do not write, appears to be obviated.
Second : DIO. Again, some mention of OLTP workload was referenced, but I wanted
to know if the avoidance of file system caching would aid in our throughput,
since we are doing massive reads, but no writes. The article didn't seem too
clear on the nature of the benefits - i.e. read vs write.

Any help, discussion, etc. would be appreciated. We are somewhat limited in our
budgets, so any improvements would be looked upon with great favor!

Thanks,

Mairhtin

--
Regards,
--
Haider