buffer hit ratio with db2 8.2 and CIO/DIO (AIX 5.3 64 bits)

harby ariza

buffer hit ratio with db2 8.2 and CIO/DIO (AIX 5.3 64 bits)
_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________



Hi Guys,

I'm running db2 fixpak 12 64 bits on AIX 64 bits. We mounted the
filesystem to enable the Concurrent I/O and Direct I/O. We did all this
in order to avoid the double copying of data that results in additional
CPU consumption and reduce the duplication of application data within
the filesystem buffer cache.Also According to IBM this is the closest
that you can get to a raw devices using a JFS filesystem. Now looking at
the db2 snapshots the counter "Direct reads" according to the System
Monitor manual says that this counter contains "The number of read
operations that do not use the buffer pool" . It means that for instance
even if we have room to accommodate heaps of pages in bufferpool they
won't be kept. Can someone explain whether if using Direct I/O it'll
never use the Bufferpools at all. thanks.

harby


_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in reliance
upon this information, by persons or entities other than the intended recipient is
prohibited.

If you have received this in error, please contact the sender and delete this e-mail
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute
the information contained in this e-mail and any attached files, with the permission
of the sender.

This message has been scanned for viruses with Symantec Scan Engine and cleared by
MailMarshal.
_______________________________________________________________________________________

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Gunning

Re: buffer hit ratio with db2 8.2 and CIO/DIO (AIX 5.3 64 bits)
(in response to harby ariza)
Harbry, with CIO/DIO DB2 still uses the buffer pool, just not the OS
filesystem cache. However, LOBs/Long VARCHAR will not use the buffer pool
(never did, not related to CIO/DIO) and will use direct reads. Based on
the changes you made, you should see pretty good improvement with the
exceptions as stated. HTH Phil

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

harby ariza

Re: buffer hit ratio with db2 8.2 and CIO/DIO (AIX 5.3 64 bits)
(in response to Philip Gunning)
_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________



Hi Phil,

Thanks for your reply. I was wondering if now that we are avoiding the
filesystem buffer cache and most of that memory is now given back to the
apps I was expecting/hoping a behaviour change in terms of direct I/O.
For instance I upgraded db2 from 32 bits to 64 bits and with the 32 bits
instance I was using only a 800 megs bufferpool. Since we upgraded to
DB2 64 bits I increased the size of that bufferpool to 4 Gigs because
the memory was always there available but with 32 bits I was already
hitting the roof getting the ADM11000E error. Also I have one particular
system where we have a bunch of tables with clobs/varchar columns use as
a template for PDF documents. I would imagine If I can create a
dedicated buffepool for those tables with clob/varchar columns the
performance would be much better. But considering that using Direct I/O
regardless that the same row could be read 100 times I suppose each time
db2 will have to go to disk. The performance seems to be much better.
Now I'm willing to implement this in our DSS enviroemnts. Thanks again.

Harby



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Phil Gunning
Sent: Tuesday, 12 December 2006 1:28 PM
To: [login to unmask email]
Subject: Re: [DB2-L] buffer hit ratio with db2 8.2 and CIO/DIO (AIX 5.3
64 bits)

Harbry, with CIO/DIO DB2 still uses the buffer pool, just not the OS
filesystem cache. However, LOBs/Long VARCHAR will not use the buffer
pool
(never did, not related to CIO/DIO) and will use direct reads. Based on
the changes you made, you should see pretty good improvement with the
exceptions as stated. HTH Phil

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in reliance
upon this information, by persons or entities other than the intended recipient is
prohibited.

If you have received this in error, please contact the sender and delete this e-mail
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute
the information contained in this e-mail and any attached files, with the permission
of the sender.

This message has been scanned for viruses with Symantec Scan Engine and cleared by
MailMarshal.
_______________________________________________________________________________________

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm