I know that beginning 9.5, file system caching is turned off by
default. Our software-app platform is at db2 9.1 windows 2003 due to
installed app requirement. My question is: is FILE SYSTEM CACHING ever
good ?

I found I have near instant response when I run full table scan the
2nd time if I leave file system caching on. And it even survives
instance bounce. So in effect, OS file cache acts as a secondary
memory disk. So I can leave bufferpool size moderate or even small and
let OS cache feeds me the data.

We have 32 G of RAM and we bounce the app twice a week to killed the
websphere connections thereby releasing the memory holds. In a typical
week, db2 will start out with 27 GB free (out 32 GB) after fresh
websphere restart, and drips down to 14-15 GB free. Each day it begins
with like 19 GB and regains it to 21 GB, but next day it begins at 17
GB, and regains to 20 GB, so the watermark is lowered over time, but
an application restart after 3 or days restore the starting point back
up to 27GB.
So we are (cross fingers) not memory constrained.

This question also leads into even more technical discussion,like OS
caching and Read,write caching enablement on the SAN controller.

Any insight will be appreciated. Thank you in Advance.

"bostonibd" <sq8188 (AT) gmail (DOT) com> wrote

I don't know about Windows, but on Linux/UNIX file system caching for DB2 is
off by default on DMS tablespaces because of kernel changes in newer
versions of those operating systems which allows it to be turned off. Not
sure about Windows 2003.

File system caching should be kept on for SMS tablespaces.

There is no comparison in performance between DB2 bufferpools and file
system caching. Large DB2 bufferpools are always better than file system
caching. So not sure about your "test." Did you try it with file system
caching off?

Keep in mind that most OS's will use any free memory for file system
caching, but will give it back to applications that requests it, so you
probably have more free memory than you realize. I would lean to larger
bufferpools and not worry about file system caching (except on SMS
tablespaces).

On Dec 10, 8:00*am, bostonibd <sq8... (AT) gmail (DOT) com> wrote:
If you're using LOBs, these do not go through the bufferpool (assuming
you are not using inline LOBs). File system caching can be useful in
a case where you have large LOBs and have set up table to use a
separate tablespace for LOB data.

This is not a good thing -- you should just increase the size of your
bufferpool.

By leaving file system caching on, you're just adding extra system CPU
time that's required for the OS to read the data from disk and write
it to the file system cache.

This is curious behavior. If you have many hundreds of connections
coming in to the database this may make sense, but I wonder if the
self tuning memory manager is turned on?

Are you seeing performance effects (either negative or positive) as
the memory utilization changes?

On Dec 10, 8:28*am, "Mark A" <no... (AT) nowhere (DOT) com> wrote:

I don't agree -- what is your reasoning behind this?

When DB2 reads a table located in a DMS file tablespace, it opens the
file (assuming the file isn't already open) and reads a specific
length of data (i.e. 1 or more pages) starting at a specific offset
within the file.

When DB2 reads a table located in an SMS tablespace, it opens the
specific file holding the table (again, assuming the file isn't
already open) and then reads a specific amount of data (i.e. 1 or more
pages) starting at a specific offset within that table's file.

While you can certainly assert that SMS is less efficient than DMS
(because DB2 has to keep track of many more files in the file system),
the actual reads from the files (once open) are the same.

Perhaps file system caching might be useful if MAXFILOP is not tuned
properly and DB2 is constantly opening and closing files. Or, if you
have so many active objects in your database that even with MAXFILOP
set to it's maximum value (61440?) you are still getting lots of files
closed. But in either of these cases I would posit that it's more of
a configuration problem than a problem with file system caching and
SMS tablespaces.



Ian

Thank you for excellent feedbacks so far. Please keep in coming !

One thing I forgot to mention is: self-tuning mem is off in my
production environments. In fact I found it to be never good but thats
a discussion for another time. I just use STMM for a time to see what
the thresholds are, but config db according to the max plus a little
bit extra.

Back to FILE SYSTEM CACHING. Why wouldn't I use OS memory ( of which
I have whopping 27 GB free after initial app connects) as a memory
disk by leaving File system Caching ON ?

Sure it incurs double cpu cycles when it pulls it from SAN disks, or
writes double to disk, but I am getting repeated payoffs when the
application re-reads the same info, which happens multiple times and
shares among multiple read requests. Will someone convince me this is
a bad thing ?

Thank you in advance for more advice.

On Dec 10, 10:06*am, bostonibd <sq8... (AT) gmail (DOT) com> wrote:
Because if you use all of that same memory for the bufferpools you
don't have to incur the extra CPU to double buffer it.
Same effect, less system CPU overhead.

Oh, by the way, my envionment is regular OLTP but each time an
internal user logons, the system scans entire db's to try to push to
him/her whatever open tasks are in-waiting. Then he will pick and
choose and drills down on specific tasks.

The difference between db2 buffers and OS caching maybe difference in
Aging Mechanisms. Something like keeping the most recently used pages.
At OS, Windows 2003 (which I found is surprisingly robust), aging
mechanism works differently. Even beyond the OS, there's controller
cache on the SAN. They all have different aging mechanism/periods.

Maybe someone can speak to that, or comment on existing ?

Also, whats your experience like before and after turning off FILE
SYSTEM CACHING ?

"bostonibd" <sq8188 (AT) gmail (DOT) com> wrote

It is always better to allocate that to DB2 bufferpools rather than file
system caching. Huge difference in performance.

"Ian" <ianbjor (AT) mobileaudio (DOT) com> wrote

I got some information from an IBM person who did some testing and some
investigation with people inside the DB2 Lab. At least in version 9.1, there
is some activity that DB2 does to the SMS file system even if the data is in
the bufferpool, so if that file system is not cached, performance suffers.

The analysis I saw was based on actual tests performed with, and without,
file system caching, and based on an explanation of why it happening. My
recollection is that problem has been largely mitigated in the newest DB2
releases (but don't recall if the problem applies to 9.5 also). However,
even with DB2 9.7.3a, the default during database creation for TEMPSPACE1 is
SMS with file system caching ON. File system caching is OFF by default for
DMS tablespaces automatically created at db creation time.

I will turn off FILE SYSTEM CACHING on our prod environment this
sunday, and increase bufferpool sizes big time and see !

Thanks again.