DB2 and CIO DIO on AIX

Hi Guys,

I just wondering whether is really worthwhile enabling CIO for DB2. The reason behing my question is under JFS2 and file system buffering you can basically take advantage of readahead_max_pages which makes a huge difference when performing large read sequential (e.g database backup or even large joins suh as the one's common on a datawarehouse ). My impresion is that once CIO is enable it'll use DIO implicitly therefore all reads/writes will be done synchronously unless the data is already in the db2 bufferpool. I wonder if anyone else who has implemented CIO DIO for DB2 can share their experience regardless they were positive or negative. thanks in advance.

Harby.

re: DB2 and CIO DIO on AIX

G'day Mate,

Since Concurrent I/O implicitly invokes Direct I/O, all the performance considerations for Direct I/O hold for Concurrent I/O as well. Thus, applications that benefit from filesystem read-ahead, or have a high filesystem buffer cache hit rate, would probably see their performance deteriorate with Concurrent I/O, just as it would with Direct I/O. On AIX 5.2.10, Concurrent I/O is the preferred method since it is not subject to the limitations of buffer size and alignment that limit Direct I/O’s effectiveness on JFS and pre AIX 5.2.10 JFS2 file systems. The benefits of Direct I/O and Concurrent I/O with DB2 are evident in:

• Disk throughput
• System CPU utilization
• Memory usage

With Direct I/O and Concurrent I/O, overall performance improvements are largely a result of the freeing up system CPU cycles for use by the application. On I/O bound systems, the performance gain is less dramatic since the application is waiting on data from the disks to continue processing and any extra CPU cycles are wasted

You might have to look over this article for details.
http://www3.software.ibm.com/ibmdl/p...IO-article.pdf

Thanks,
Shashank K
IBM, DB2 for Linux, Unix & Windows -Information Management Software