IBM Support

Free extent fragmentation can lead to slow performance during online backup.

Troubleshooting


Problem

Slow performance during online backup may be experienced due to fragmentation.

Symptom

  1. Online backup takes longer to complete
  2. 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.

Cause

 
  1. Dropping one or more tables within a tablespace
  2. Moving one or more tables to another tablespace, using admin_move_table or other similar techniques
  3. Compressing one or more tables in the existing tablespace

Resolving The Problem

The following could be the potential solutions:
  1. If the tablespace was created in DB2 9.7 or higher and has reclaimable storage enabled, then the free space can be coalesced using the 'ALTER TABLESPACE' command, using either the 'REDUCE' or 'LOWER HIGH WATER MARK' clause. Review the 'ALTER TABLESPACE' command in the Info Center to determine which option is appropriate for your environment.

    To determine if the tablepsace is enabled for reclaimable storage, execute the MON_GET_TABLESPACE function and look for "Y" in RECLAIMABLE_SPACE_ENABLED column.

    db2 select TBSP_NAME, TBSP_ID, TBSP_TYPE, RECLAIMABLE_SPACE_ENABLED FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t
  2. If the tablespace is not enabled for reclaimable storage, then the only option is to add data to the tablespace to consume the existing free space below the high water mark. This can be done by adding data to existing tables or by creating new tables.

[{"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"}}]

Document Information

Modified date:
19 August 2019

UID

swg21644638