Hi Guys,

We are migrating DB2 from v8.2 to v9.7(FP1) also we are moving from AIX 5.3 to AIX 6.1

We were glad initially with the DB2 v9.7 outputs, we were testing a lot and the output was good and we are all surprised. As and when days progressed, we observed LOAD utility and reorg on tables took longer time than the usual. While running LOAD utility, any other parallel jobs affected the server response and the server started to act slow.

We navigated the issue and found the server response were going down because of I/O issue. (LOAD & reog uses temporary tablespace which in turn uses high disk usage).

Since it was a testing server, i dropped the DB and planned to recreate it from scratch. Initial 5- 10 mins the server was still slow, after that 10 mins server was back to the stage it was one month back.

So logically dropping the DB, flushed something on the server and came back to normal. So we are not able to find out where the issue is, and am expecting the server to act slow after few weeks.

Are there any best practices for designing temporary tablespaces? Also we are suspecting the issue might be more on file system caching at file system level or SAN level. Are there any recommendations for file system caching on temporary tablespaces? and is it recommended to shut down file system caching on OS level for temporary tablespace file system?

All our eyes are now on file system caching for SMS, so any input on file system caching will be helpful.

Thanks,
Dharmaraj.G

Take a look at the following redbook:
http://www.redbooks.ibm.com/redbooks/pdfs/sg247821.pdf

In addition to caching recommendations, check VMM guidelines. You can also check the following technote:
IBM - Recommended AIX Virtual Memory Manager settings for DB2


I'd also recommend reviewing known issues for DB2 on AIX doc:
IBM - Known issues for DB2 on AIX 5.2, 5.3, and 6.1


Both load and (offline) reorg rebuild indexes and write to the history file. Large history file can slow down performance of utilities such as load/reorg so regular pruning is recommended. If you see high wait cpu / blocked queue in vmstat, this can indicate some kind of I/O issue, check busy disks in iostat. If you determined that the tempspace resides on those disks, then db2 is most likely spilling sorts. You can check the size of TDA files (sort temp files) in your tempspace. If sortheap is not large enough, db2 will spill to tempspace's bufferpool and then can go to tempspace on disk. It then has to read it back from disk again. This is all extra I/Os. If you see a lot of sort spills (load will allocate one sort per index), you may consider increasing sortheap / sheapthres_shr / sheapthres (if using private sorts) and increasing the bufferpool.

Please check the redbook for more info. To understand what's happening, you need to capture DB2/AIX info at regular intervals (when system is performing well and when it's slow) and then examine it.

Thanks bella for your link and information.. we are using that redbook as reference in our lab to set AIX parameters.

LOAD utility usually performs two phase
1) Loading data
2) Rebuilding indexes

Interestingly, at the stage of rebuilding indexes there are not much temporary tablespace usage, at the 1st phase its creating a lot of TDA files, that too 10 files of almost 1GB files each, thats were am worried.

Any clue, why its creating lot of TDA files at loading phase?

Thanks,
Dharmaraj.G

Load inserts index keys into sort during the load phase. When the number of keys exceed sortheap, it's sorted and then spilled. During the build phase, they're read and merged together, possibly spilling again. So, it's not unusual to see large TDA files created during the load phase. Increasing sortheap /sheapthres(_shr) will help minimize it.

Sure... will try to increase sorting parameters and give a try, my another concern is why its loading faster at early stages and loading time decreases gradually as the number of times the load operation are repeated.

Also why after dropping the DB and recreating from scratch making the load to run faster again? not only load, my whole server started responding faster as good as first day after DB recreation.

in V9.7, STMM parameters are quite well managed by DB2 as we have experienced compared to the previous versions, you can try AUTO for SORT and SHEAP if possible. Regarding the LOAD timing, you could have also tried, Dropping the INIDICES, do the LOAD and then recreate the INDEX everytime, if you are doing it in a batch mode.

What would you recommend for filesystem caching on SMS temporary tablespaces? OFF or ON?

As per IBM redbook, SMS temp tablespace must have file system caching ON on database side. Also those file systems must have 'rbrw' mount options.

Our SMS temp tablespace had file system caching ON, but the mount option we are still working on.

Is there any way to flush the file system cache of one particular file system at AIX level?

Can you provide a link and page number of the document where you saw this?

here is the link,

http://www.redbooks.ibm.com/redbooks/pdfs/sg247821.pdf

page no. 101

Its making a indirect talk of mounting file system "rbrw" for temporary tablespace which usually has file system caching ON.

correct me, if i understood wrongly....

Thanks,
Dharmaraj.G

I am assuming that is PDF page number 101, which is document page number 71 (bottom of page), but I think I found it.

"Mount option rbrw is recommended for specific file systems where FILE
SYSTEM CACHING is used to get better performance (such as temporary
table spaces, table spaces with LONG and LOB data, BACKUP file systems)."

I do see where they suggest that temporary tablespaces would have file system caching on, but it doesn't seem to me they are saying that "SMS temp tablespace must have file system caching ON."

1. Is there something different about SMS temp tablespaces than DMS temp tablespaces?
2. Why would temp spaces need file caching while it is clearly not recommended for data tablespaces?

It is clear why file system caching is recommended for tablespaces that primarily have LOB's, since they are not cached in bufferpools, but I don't understand why it is an issue for temp tablespaces (unless a lot of LOBs are ending up in temp tablespaces?).

I don't claim that I know the answer to these questions, but the Red Book you quoted does not seem to be definitive about using file system caching on temp tablespaces, or at least does not give an explanation as to why it recommends that.

Yes i do agree there are no precise documents for file system caching on temporary tablespace, since we are in the process of building new environment we wanted to design it well ahead. So trying out options.

We have a I/O disk issue on our existing environment because of certain flaws in the design, so we doesnt want those flaws again on new environment. But unfortunately we are ending into the same I/O disk issue on new environment too. Those I/O contentions are happening on temporary tablespace file system.

So only we need a best practice on temporary tablespace design. Still we are yet to try that mount option, hoping it will give us some good results.

For bella, logically i would recommend file system caching ON for SMS temporary tablespace.

Turn the FSC OFF for whether it is SMS or DMS. IBM strongly recommends to take advantage of BUFFERPOOL while managing the memory instead of allowing the Operating System to do it's job by turning Filesystem caching ON.

So whether it's SMS or DMS, let the FS Caching be OFF. You should tune all the available Registry Variables and DB/DBM CFG parameters alongwith Bufferpool to have lower I/O hit.

I too agree, DB2 strongly recommending FSC off.

But logically to avoid double buffering we are turning that off on DB2 side. When it comes to temporary tablespace, its all about your TDA files on disk. So it needs some kind of caching for those files probably thats the reason why FSC should be on for temp tablespace.

Hope it clears you why we need FSC on on temp tablespace. Think those TDA files are OS files created on the fly. Eventhough DB2 is responsible of those TDA files, still DB2 might be handling it has OS files, i suppose.

All the above are my understanding towards FSC, correct me if i was wrong anywhere.