Troubleshooting
Problem
Slow performance during online backup may be experienced due to fragmentation.
Symptom
- Online backup takes longer to complete
- Applications are slower while running concurrently with online backup
Determining if free space is fragmented:
The primary indicator that there is free extent fragmentation can be found using 'db2pd -db <dbname> -tablespaces'. For example:
db2pd -db sample -tablespaces
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x0A000305E86B3960 2 DMS Large 16384 4 No 8 2 2 Off 3 0 3 USERSPACE1
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers
PathsDropped
0x0A000305E86B3960 2 113024896 113024884 86395432 0 26629452 111916828 111916828 0x00000000 0 0 No
From this output the UsedPgs is significantly less than HWM ( 111,916,828 - 86,395,432 = 25,521,396 ). This means that there are many free extents below the high water mark.
For specific details about where the free extents exist in the tablespace, we must examine 'db2dart sample /dhwm' output. The /dhwm option requires inputting the tablespace number.
Example of non fragmented free space:
-------------------------------------
[1200] 1235 0x00 [1201] 1235 0x00 [1202] 902 0x00 [1203] 1235 0x00
[1204] 1235 0x00 [1205] 1235 0x00 [1206] 3646 0x00 [1207] 3010 0x00
[1208] 4555 0x00 [1209] 902 0x00 [1210] 3555 0x00 [1211] 7216 0x00
[1212] 7084 0x00 [1213] 3656 0x00 [1214] 2103 0x00 [1215] 2103 0x00
[1216] 3348 0x00 [1217] 3646 0x00 [1218] 2103 0x00 [1219] 2103 0x00
[1220] 1984 0x00 [1221] 3647 0x00 [1222] 902 0x00 [1223] 3011 0x00
Example of fragmented free space:
---------------------------------
[880700] 1235 0x00 [880701] 1235 0x00 [880702] 1235 0x00 [880703] 160 0x00
[880704] 7698 0x00 [880705] 3652 0x00 [880706] == EMPTY == [880707] 2522 0x00
[880708] 7592 0x00 [880709] 3656 0x00 [880710] 1979 0x00 [880711] 3640 0x00
[880712] 4495 0x00 [880713] == EMPTY == [880714] 160 0x00 [880715] 7698 0x00
[880716] == EMPTY == [880717] 1334 0x00 [880718] 160 0x00 [880719] 7592 0x00
[880720] 160 0x00 [880721] 7727 0x00 [880722] 7727 0x00 [880723] 2522 0x00
Whenever there are multiple EMPTY extents located near each other, the free space is fragmented.
It is very difficult to say how much free space fragmentation is required to encounter noticeable performance issues. The best measurement is to keep track of how much longer online backups are running compared to what is considered normal.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"Database Objects\/Config - Database","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5;11.1;11.5","Edition":"Advanced Enterprise Server;Advanced Workgroup Server;Enterprise Server;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Log InLog in to view more of this document
This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.
Was this topic helpful?
Document Information
Modified date:
30 April 2025
UID
swg21644638