db2_mmap vs create tablespace no file system cache option (concurrent i/o)

[sethwai]

Hi,

I've read everything I can get my hands on and am still very
confused about the similarities and differences between
db2_mmap_read/write and concurrent i/o. It seems to me at this point
that they are virtually identical except that db2_mmap_read/write
applies at an instance level and concurrent i/o can be aplied at a
tablespace level. It seems that they both bypass the AIX file cache
and eliminate i-node locking. Is this correct?

Thanks.

Lew

[Liam Finnie]

se*****@yahoo.com wrote:

Hi,

I've read everything I can get my hands on and am still very
confused about the similarities and differences between
db2_mmap_read/write and concurrent i/o. It seems to me at this point
that they are virtually identical except that db2_mmap_read/write
applies at an instance level and concurrent i/o can be aplied at a
tablespace level. It seems that they both bypass the AIX file cache
and eliminate i-node locking. Is this correct?

Thanks.

Lew

Hi Lew,

Both these forms of I/O bypass the i-node locking. However,
db2_mmap_read/write does not bypass the file cache, so you could get
double buffering of data (one copy in DB2's buffer pools, one in the
file cache). Concurrent I/O bypasses the file cache.

Cheers,
Liam.

[Ian]

Liam Finnie wrote:

se*****@yahoo.com wrote:

>Hi,

I've read everything I can get my hands on and am still very
confused about the similarities and differences between
db2_mmap_read/write and concurrent i/o. It seems to me at this point
that they are virtually identical except that db2_mmap_read/write
applies at an instance level and concurrent i/o can be aplied at a
tablespace level. It seems that they both bypass the AIX file cache
and eliminate i-node locking. Is this correct?

Thanks.

Lew

Hi Lew,

Both these forms of I/O bypass the i-node locking. However,
db2_mmap_read/write does not bypass the file cache, so you could get
double buffering of data (one copy in DB2's buffer pools, one in the
file cache). Concurrent I/O bypasses the file cache.

Furthermore: If you are using "no file system caching" at the DB2
level, and have a 32 bit instance, turning MMAP read/write off will
free up an extra shared memory segment that you can use for other,
more useful consumers.

Right, Liam?

[Liam Finnie]

Furthermore: If you are using "no file system caching" at the DB2
level, and have a 32 bit instance, turning MMAP read/write off will
free up an extra shared memory segment that you can use for other,
more useful consumers.

Right, Liam?

You got it. Of course, if you're worried about being able to squeeze
out one extra 256MB segment, then you should move up to a 64-bit
instance anyways :-)

Cheers,
Liam.

[sethwai]

Hi Liam,

Thanks for your response. I too thought that db2_mmap_read/write
didn't bypass the file system cache until I read this about
db2_mmap_read in the V8 performance tuning guide:

This variable is used in conjunction with DB2_MMAP_WRITE to allow DB2
UDB to use mmap as an alternate method of I/O. In most environments,
mmap should be used to avoid operating system locks when multiple
processes are writing to different sections of the same file.

When these variables are set to ON, data that is read to and written
from the DB2 buffer pools bypasses the AIX memory cache. If you have a
relatively small DB2 buffer pool, and you cannot or choose not to
increase the size of this buffer pool, you should take advantage of AIX
memory caching by setting DB2_MMAP_READ and DB2_MMAP_WRITE to OFF.

Lew

Liam Finnie wrote:

se*****@yahoo.com wrote:

Hi,

I've read everything I can get my hands on and am still very
confused about the similarities and differences between
db2_mmap_read/write and concurrent i/o. It seems to me at this point
that they are virtually identical except that db2_mmap_read/write
applies at an instance level and concurrent i/o can be aplied at a
tablespace level. It seems that they both bypass the AIX file cache
and eliminate i-node locking. Is this correct?

Thanks.

Lew

Hi Lew,

Both these forms of I/O bypass the i-node locking. However,
db2_mmap_read/write does not bypass the file cache, so you could get
double buffering of data (one copy in DB2's buffer pools, one in the
file cache). Concurrent I/O bypasses the file cache.

Cheers,
Liam.

[Liam Finnie]

se*****@yahoo.com wrote:

Hi Liam,

Thanks for your response. I too thought that db2_mmap_read/write
didn't bypass the file system cache until I read this about
db2_mmap_read in the V8 performance tuning guide:

This variable is used in conjunction with DB2_MMAP_WRITE to allow DB2
UDB to use mmap as an alternate method of I/O. In most environments,
mmap should be used to avoid operating system locks when multiple
processes are writing to different sections of the same file.

When these variables are set to ON, data that is read to and written
from the DB2 buffer pools bypasses the AIX memory cache. If you have a
relatively small DB2 buffer pool, and you cannot or choose not to
increase the size of this buffer pool, you should take advantage of AIX
memory caching by setting DB2_MMAP_READ and DB2_MMAP_WRITE to OFF.

Lew

Hi Lew,

That second paragraph is incorrect. DB2_MMAP_READ/DB2_MMAP_WRITE will
hit the AIX file cache - the only thing it does is avoid i-node
locking. To avoid the AIX file cache, you need to enable Concurrent
I/O, either through mount options, or by using the 'NO FILE SYSTEM
CACHING' clause when creating/altering your tablespaces.

Cheers,
Liam.