Performance boost through disabling file system caching

In a recent engagement to troubleshoot lousy performance, colleagues of mine were able to "fix it".

Usually it is a good thing, when an operating system (here: kernel and file system code) caches data in buffers at the file system level and flushes it out to disk when appropriate (and in the right chunks) to minimize the amount of physical I/O operations.

When placing database files on such filesystems however, the filesystem's caching algorithms can be extremely counterproductive.

In the case here, Solaris, the Veritas filesystem, and Oracle was involved. Re-mounting the filesystem with the parameter

mincache=direct

reduced the time for SQL inserts into the SIB tables from up to 11 seconds down to 0.03 seconds.

When dealing with the combination AIX, JFS2, and DB2, the DB2 command

db2 alter tablespace no file system caching

has more or less the same effect. Data caching on the filesystem level is disabled and the data is persisted as fast as possible. According to the AIX documentation the same effect can be reached on the file system level by using "mount ... -o dio". Read performance for non-DB files might suffer, because caching is reduced. DB-data will still be cached in the DB bufferpools.