 | |
File System Caching - is it ever good ?
comp.databases.ibm-db2 comp.databases.ibm-db2
 |
| | Thread Tools | Display Modes |
#31
 
| |||
| |||
|
Re: File System Caching - is it ever good ? -
12-19-2010
, 09:22 AM
|
Please critique my settings as restated below, guys. FILE SYSTEM CACHING: ON all SMS tablespaces except tempspaces Syscat,Systool, So called "long" tablespaces which are BLOB containers bufferpool sizes: small-to-moderate, no bufferpool assigned to "long" because BLOBs do not use BP's, defaults to IBMDEFAULTBP but is not central to discussion FILE SYSTEM CACHING: OFF all DMS user tablespaces all Temp tablespaces bufferpool sizes: medium to big depending on how active Experts, what do you all think ? |
tablespaces. This recommendation may change in the future if IBM has fixes
(or has already fixed in latest release) the problem where DB2 has to access
the SMS file system synchronously even if data is in the bufferpool (at
least for some DML).
In most cases I use a 32K page size for (LARGE) tablespaces with LONG data,
so that precludes me from assigning it to IBMDEFAULTBP. Besides, I need a
32K bufferpool anyway because I always create a SYSTEM TEMP tablespace with
32K page size for when someone may join a lot of tables together with a
large row size being returned. Obviously this bufferpool can be quit small
if only used for this purpose or for LOB's.
#32
| |||
| |||
|
#33
| |||
| |||
|
|
Good stuffs. I really like the depth of discussion here. True knoweledge only comes from thorough and sometime painful (for me at least) investigation. At next maintenance I am turning on FS cache for temp space. But i really doubt the merit of it, even though I had opened a PMR on this subject and iBM recom tempspace to be cached. I mean you have only transient data there, not really usable to my mind. Also I appreciate the notion that if your app does huge scan, and will exceed bufferpool size, why waste the memory by assigning big pool size. I rather like to isolate my bufferpools by functions. Incidentally there is a camp that says one big tablespace and one big pool for pretty much all tables. Is this lazy man DBA talking ? I like to isolate tablespaces and pools by function. |
tablespaces. The reason SMS needs to be cached has nothing to do with
bufferpools, rather it has to do with the fact that DB2 has to access the
files synchronously even if the data is in the bufferpool. Obviously, this
has nothing to do with the data, but has something to do with some low level
file attributes that DB2 has to access on disk (or in file cache). I have
heard that IBM is making changes to SMS to fix this, but not sure if it has
been implemented in the latest 9.7 fixpack, or whether it will be in a later
fixpack or release.
#34
| |||
| |||
|
|
"bostonibd" <sq8... (AT) gmail (DOT) com> wrote in message news:60568b83-c0f9-4840-a172-be2f282eb4f6 (AT) p7g2000prb (DOT) googlegroups.com... Also re: Bernard's knowledge sharing on Bufferpool searches vs OS memory search. That's great stuff. Not until he tells us whether he was using SMS or DMS tablespaces when he did the test. |
Bernard
#35
| |||
| |||
|
#36
| |||
| |||
|
#37
| |||
| |||
|
|
Also I appreciate the notion that if your app does huge scan, and will exceed bufferpool size, why waste the memory by assigning big pool size. I rather like to isolate my bufferpools by functions. Incidentally there is a camp that says one big tablespace and one big pool for pretty much all tables. |
- Dedicated buffer pools if you have time and ability to constantly monitor the database
- Two or three pools (index + data, maybe + temp) otherwise
- One big pool if you're not exactly sure how a buffer pool works -- better than 20 table spaces + 20 buffer pools
#38
| ||||||||||||||||||||||||
| ||||||||||||||||||||||||
|
|
On Friday, December 10, 2010 10:00 AM bostonibd wrote: 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. |
Quote:
|
On Friday, December 10, 2010 10:28 AM Mark A wrote: I do not 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). |
Quote:
|
On Friday, December 10, 2010 11:02 AM Ian wrote: If you are 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 are just adding extra system CPU time that is 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? |
Quote:
|
On Friday, December 10, 2010 11:15 AM Ian wrote: I do not 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 is not 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 is not 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 is maximum value (61440?) you are still getting lots of files closed. But in either of these cases I would posit that it is more of a configuration problem than a problem with file system caching and SMS tablespaces. Ian |
Quote:
|
On Friday, December 10, 2010 12:06 PM bostonibd wrote: 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 would not 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. |
Quote:
|
On Friday, December 10, 2010 12:25 PM Ian wrote: Because if you use all of that same memory for the bufferpools you do not have to incur the extra CPU to double buffer it. Same effect, less system CPU overhead. |
Quote:
|
On Friday, December 10, 2010 2:06 PM bostonibd wrote: 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 is 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 ? |
Quote:
|
On Friday, December 10, 2010 2:17 PM Mark A wrote: It is always better to allocate that to DB2 bufferpools rather than file system caching. Huge difference in performance. |
Quote:
|
On Friday, December 10, 2010 2:32 PM Mark A 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 do not 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. |
Quote:
|
On Friday, December 10, 2010 2:47 PM bostonibd wrote: I will turn off FILE SYSTEM CACHING on our prod environment this sunday, and increase bufferpool sizes big time and see ! Thanks again. |
Quote:
|
On Saturday, December 11, 2010 12:02 PM Willem Fischer wrote: They switched off file system caching on one of our production servers (on Windows) only to find out that the performance of the application was much worse afterwards. When I checked the snapshots, I found that their average read time was like 0.1 milliseconds with FS caching on and the regular > 5ms with FS off (do not remember the values). They returned to FS caching on, because their application performance was "good enough" and they did not want to bother tuning buffer pools. By the way, when calculating average read times, would you calculate (bp read time/number of synchronous reads) or (bp read time/number of total reads)? (Synchronous reads being total reads minus asynchronous reads.) |
Quote:
|
On Monday, December 13, 2010 11:28 AM Mark A wrote: Was this with DMS or SMS tablespaces? |
Quote:
|
On Monday, December 13, 2010 11:51 AM bostonibd wrote: Thanks to Willem and everyone who provide input and please keeping your valuable comments coming ! Mark, the tablespaces are on SMS which by the way, is my major gripe IBM made a monumental blunder by allowing tablespace default to SMS and only corrected that with 9.5 when default was changed to DMS and Large Tablespace. When IBM created before was the situation when an unknowing user in charge of creating the DB environment, in my case, my previous admin was incompetent as DBA, created production environments all on SMS without data/index/BLOB separation. Performance was horrible, locking was constant until I began the long process of migrating to DMS with the separate tablespaces. IBM created a situation where a casual use environment was made for intended high intensity production use. IBM lets the unknowing user created a second-best database. Imagine Oracle or MSSQL building a casual demo environment right out of the box ? BUT I DIGRESS ! |
Quote:
|
On Monday, December 13, 2010 8:13 PM Mark A wrote: As I mentioned in my previous post, SMS tablespaces should have file caching on. Only DMS should have it off. Your database performance would be faster (probably noticeably faster) with DMS tablespaces and file system caching off, assuming you had larger bufferpools. Regarding the default of SMS, I have complained about defaults for a long time (but mostly memory allocations) , but it seems to me that people should obtain help from experienced DBA's to set up a database if they are concerned about performance. But one reason that the default was SMS, is that at one time only SMS had automatic resizing (up to the amount of available space on the mount point or drive). Now with auto-resizing and automatic storage of tablespaces, DMS is feasible to be the default for permanent tables. |
Quote:
|
On Thursday, December 16, 2010 9:54 AM Bernard wrote: Having big pools and very good hit ratio is not always a guarantee for performance improvements. It depends. It can even degrade performance. Suppose a system, AIX, and storage file system based JFS2 (no raw devices). For the 9.5 release DB2 defaults are DB2_MMAP_WRITE=3DOFF DB2_MMAP_READ=3DOFF, a user can always enforce it by using db2set (as in the past the default was ON). As result of these settings, DB2 when having a non-hit in his cache, will go to AIX and ask for the data. If AIX does not have the data in it is memory, it will go to persistent storage. Before knowing if the data is in his cache, DB2 has to search for it. As AIX has to search when the kernel receives the request from DB2 for the in case of DB2 cache miss. DB2 search is application level and is less performant than AIX JFS2 search, tuned at OS and hardware memory search level. So if DB2 has a big cache, it can take more time for the db2 software to search the data in it is cache than to search in a small DB2 cache and let AIX search in a big OS managed memory cache (and when found in memory or persistentent storage, taken account of memory transfer to the DB2 cache). So the more systems (and memory transfer performance) and memory size increase, the less DB2 cache and cache hit becomes critical, and having very big DB2 caches can indeed impact negatively performance (as tested I made confirm) Bernard Dhooghe |
Quote:
|
On Thursday, December 16, 2010 2:06 PM bostonibd wrote: I have turned off file caching on SMS tablespaces and watched PERFMON, the disk queue becomes slightly more rounded instead of sharp peaks. A sharp peak is when disk i/o happens but completes quickly, hence quick up and down, which is good. When it doesnt retract quickly you get more rounded peaks. I believe (free to refute me) that disk requests are not satisfied quickly because the pages are not stored in the filesystem cache and have to go to real disks to find them. On writes, because db2 is not writing to fs write cache but to disk directly, it also takes longer. In reality i/o goes to/from SAN controller cache. That's another level of abstraction. Slowly I am forming the opinion that Filesystem caching is good for SMS tablespaces. But why would not be good for DMS also ? |
Quote:
|
On Friday, December 17, 2010 6:46 AM Mark A wrote: You are repeating a statement I made previously about using file system caching for SMS. I got that suggestion from an IBM support person and posted it earlier in this thread. As I previously stated, when DB2 uses SMS tablespaces, there is some file system information it needs in addition to the actual data in those files (which may be in DB2 Bufferpools). So if file system caching is off for SMS, disk access is required even when the data is in the bufferpool, and this does not happen with DMS. Someone told me that the latest release/fixpack of DB2 9.7 "may" have fixed this problem to some degree (no longer need to access file system if data is in DB2 bufferpools for SMS), but not 100% sure about that. |
Quote:
|
On Friday, December 17, 2010 11:28 AM bostonibd wrote: Thanks. Here are the settings as they stand. File Caching ON = SMS user tablespaces, Long Tablespaces ( Long's are blob, neither SMS nor DMS technically ) Syscatspace ( small, might as well cache it) , Systoolspace File Caching OFF = DMS user tablespaces, All Temp tablespaces (temps are SMS by definition) As I mentioned when SMS user tablespaces are off, I am finding PERFMON disk queues more rounded, meaning the wait or the size of it got longer. When I turned it on, I got sharper peaks, means the disk queues got smaller or completes sooner. In other words, pages are found in cache. However that could also be the result of turning off DMS file caching too. I'd leave it to the experts to determine which contributed more. And I am seeing you do not get sharp peaks (good) on first day, but on second day I start to see sharper peaks. It could be that pages take time to accumulate in file cache. Once they accumulated you run good chance of finding it on cache than on disks. Am I making any sense ? |
Quote:
|
On Friday, December 17, 2010 12:45 PM bostonibd wrote: Also re: Bernard's knowledge sharing on Bufferpool searches vs OS memory search. That's great stuff. |
Quote:
|
On Saturday, December 18, 2010 12:04 AM Mark A wrote: Not until he tells us whether he was using SMS or DMS tablespaces when he did the test. |
Quote:
|
On Saturday, December 18, 2010 12:13 AM Mark A wrote: 1. There is no such thing as a LONG tablespace in DB2 LUW. There are the following: LARGE REGULAR SYSTEM TEMPORARY USER TEMPORARY When you create a table, you can specify "LONG IN" tablespace-name, but that is not a type of tablespace, it just means to put the LOB's in the LARGE or REGULAR tablespace that has already been defined. This could be a tablespace that has regular table or index data in it also. 2. TEMP Tablespaces are not SMS "by definition." One can easily create a SYSTEM TEMP or USER TEMP Tablespace as DMS. However, the default for a TEMP a tablespace is SMS if you do not specify the type. |
Quote:
|
On Monday, December 20, 2010 8:43 AM Bernard wrote: SMS, containers were JFS2 based directories. Bernard |
Quote:
|
On Monday, December 20, 2010 5:39 PM bostonibd wrote: Mark, thanks for explanation about SMS needing FS caching because of synchronous io. I think I get it. All my SMS is FS cached, including the temp which I will turn on as we speak because it is dynamic. But speaking of SMS, why should we have any SMS tablespace except for temp space? You can create database and specify database manage space for USERSPACE1, and SYSCATSPACE now. I am creating my databases that way now. IBM should stick to the gun and outright ban SMS except for temp's. Because the unknowing user will create a casual use environment that is second best. And it always seems the case the untrained user was the one who created the environment. I believe DB2 would have made greater gain on Oracle than it has had this been the case early on. I can attest to the fact that DB2 when properly set up runs like a screaming sports car. I have admin'd ORACLE RAC environments on similar hardware and similar load and it does not compare. I am in long process of remaking our production databases from SMS to DMS and my insert benchmarks (our app's mostly insert so that is a fair comparison) is 2 to 3 times sometimes 4 times faster depending on size of BLOB present compare to old SMS. The new structure will be DMS with data/index/long tablespace separation. Think about that. Four times faster. |
Quote:
|
On Tuesday, December 21, 2010 3:16 PM bostonibd wrote: I turned off tempspace file caching after it seems to be drainning memory faster. Now with it off, free RAM is more or less stable. I also assigned a bufferpool for tempspace. Strongly recommend it. Small bufferpool for syscat too. We still do twice a week WAS application client bounce that releases all RAM. So our RAM is in great shape. DB2 is hosting nation-wide clients - hundreds of stores - without problem. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.