Concurrent IO and Direct IO?

[Former Member]

Hello,

we have upgraded a lot of databases to 9.5.2. I have some questions regarding CIO and DIO.

It seems, that there a two ways of activating this feature. I can turn off Filesystem caching on tablespace level and then the database is using CIO or DIO in dependency of the filesystem.

Alternative I can change the mount points on unix level and mount using DIO or CIO.

Is this so far correct?

We have for some databases deactivated the filesystem caching on tablespace level with alter tablespace.... for all DMS. What about SMS? In one documentation I have read for 8.2.2 that for SMS/DMS temporary tablespaces the filesystem caching shouldn't been deactivated. Is this still valid for 9.5.2?

Regards,

Alexander

[Former Member · Accepted solution]

here is a link that details db2 and cio

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/t0...

Also, on AIX using jfs2 you can mount your log_dir as CIO as well.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/t0...

Regards,

Dan Pamintuan

[Former Member · Accepted solution]

Hello Alexander,

enabling CIO/DIO with NO FILE SYSTEM CACHING is the recommended method.

Starting with DB2 9.5 CIO/DIO is the default for all DMS tablespaces, because you can expect better performance.

On SMS tablespaces, which are only used for TEMPSPACEs in SAP, CIO/DIO has not helped.

On SAP BW systems DMS tablespaces for TEMPSPACEs has shown good results.

On 9.5 FP1 you can use the registry variable DB2_LOGGER_NON_BUFFERED_IO to enable DIO on logging operations.

Please be aware of the following problems:

- backups can take longer especially if your tablespaces are configured with a small extent size. The extent size should be a mulitple of the physical stripe set. Often this is 64K. The SAP default 16K page size with extent size 2 is typically too small.

- LONG/LOB read operations have worse performance with CIO/DIO enabled, because the file system cache cannot help to buffer the values.

Best regards,

Jens

[Former Member]

Dear colleagues,

thank you all for your answers. We will deactivate File System Caching on db level, but we are still not sure, if we further should mount with specail options to deactivate file system caching on os level. Have anyone done some tests? Does it further improves the speed, it we deactivate also on os level? Any experiences?

regards,

alexander

[Frank-Martin]

Hi Alexander,

as Jens mentioned switching of file system caching completely on OS level may reduce read performance of LONG/LOB data since those are not buffered in the DB2 bufferpool. As a default SAP does not use seperate Tablespaces for LONG/LOB data.

Regards

Frank

[Former Member]

Hello Derek,

we are running on AIX 5.3.8.4

Regards,

Alexander

[former_member184709]

Hi Alexander,

You'll need to both turn off file system caching at the tablespace level, and also remount the filesystem using the CIO/DIO option, to get DIO to work. Which UNIX are you using?