DB2 Version This Was Written For
9.7
Parameter Name
UTIL_HEAP_SZ
Where This Parameter Lives
DB CFG
How To Check Value
> db2 get db cfg for sample |grep UTIL_HEAP_SZ Utilities heap size (4KB) (UTIL_HEAP_SZ) = 70982
OR
> db2 "select name, substr(value,1,12) value, substr(deferred_value,1,12) deferred_value from sysibmadm.dbcfg where name='util_heap_sz' with ur" NAME VALUE DEFERRED_VALUE -------------------------------- ------------ -------------- util_heap_sz 70982 70982
Description
The utility heap is used by – surprise, surprise – utilities. Load, Backup, Restore, Redistribute, Compression Dictionary Creation, and Online Index reorg operations each use memory from this heap.
Impact
Can drastically impact the performance of utilities.
Default
5000
Range/Values
16-524,288
Recycle Required To Take Effect?
No
Can It Be Set To AUTOMATIC?
No, there is no AUTOMATIC option for this parameter.
How To Change It
db2 update db cfg for sample using UTIL_HEAP_SZ 7000
Rule of Thumb
Start with the default, and increase if you have performance problems with utilities or receive errors.
Tuning Considerations
You can track the allocated/used space using db2mtrk. In the example below, the utility heap is the first one reported in the upper left.
> db2mtrk -d Tracking Memory on: 2012/08/01 at 03:10:41 Memory for database: SAMPLE utilh pckcacheh other catcacheh bph (4) bph (3) 192.0K 36.1M 192.0K 18.2M 1.2G 54.1M bph (2) bph (1) bph (S32K) bph (S16K) bph (S8K) bph (S4K) 414.1M 1.4G 832.0K 576.0K 448.0K 384.0K lockh dbh apph (56234)apph (56209)apph (55221)apph (54716) 62.8M 35.9M 256.0K 64.0K 64.0K 192.0K apph (54205)apph (53697)apph (53195)apph (52173)apph (51151)apph (5977) 128.0K 128.0K 192.0K 128.0K 192.0K 128.0K apph (5599) apph (25678)apph (901) apph (832) apph (45) apph (44) 128.0K 128.0K 64.0K 128.0K 64.0K 64.0K apph (43) apph (42) apph (41) apph (40) apph (39) apph (38) 64.0K 64.0K 64.0K 512.0K 64.0K 64.0K apph (37) appshrh 64.0K 6.1M
You can also get this information through the SYSIBMADM views:
> db2 "select SNAPSHOT_TIMESTAMP, POOL_ID, POOL_CUR_SIZE, POOL_WATERMARK, POOL_CONFIG_SIZE, DBPARTITIONNUM from sysibmadm.snapdb_memory_pool where DB_NAME = 'WC005D01' and pool_id='UTILITY' with ur " SNAPSHOT_TIMESTAMP POOL_ID POOL_CUR_SIZE POOL_WATERMARK POOL_CONFIG_SIZE DBPARTITIONNUM -------------------------- -------------- -------------------- -------------------- -------------------- -------------- 2012-08-01-03.24.02.128583 UTILITY 196608 101253120 290783232 0
Note that while the pool size is specified in 4K pages, the numbers in the the above two commands are in Bytes/KB.
Now here’s part of the interesting part. The space from this heap is allocated as a percentage of the remaining heap in the following ways:
| Backup | 50% |
| Restore | 100% |
| Load | 25% |
| Redistribute | 50% |
Compression Dictionary creation takes only about 10MB to build, and online index reorganization uses this area to track transactions.
The important part of that is not the percentages – it’s what the percentages are of. They’re of the REMAINING available heap space – so each additional operation gets less space. This would especially show up if you were running a number of loads at the same time – you would see them get slower and slower with each additional load added.
There are some things you can do to get around these allocations. The data buffer option on the load or redistribute utilities would help you ensure consistent space. Similar ends could be achieved by the buffer size on backup and restore. But keep in mind that even if you use these methods to get around the allocations, all that memory still comes from the Utility heap, and if you exhaust it, you won’t have memory available for additional operations.
consider restore utility is overwriting an existing db, then i can update db cfg of existing db before starting restore utility there by getting increased performance.
how will you use this parameter(util heap) if restore is creating new database?
will restore utility refer db cfg inside the backup image during restore process?
thanks.
You can always create a database and set the configuration before restoring into it. Restore will use the cfg of the database it is restoring into, if there is one. If restoring to a new database, it will use what is in the backup image.
Hi,
Thanks for your inputs and suggestions.
Question in General with Restore Utility:
Will Restore Utility use the DB CFG parameters stored inside the backup image file during Restore process or will it apply the DB CFG after restoring complete at the end phase?
My Observation as a test case:
Test Case 1:
create data base db1, update db cfg with num_ioserver set to 6, activate and deactivate db
backup data base
drop data base
restore database db1 – utilizes 255 pre-fetchers during restore process and crashes the instance. (db2pd -edus reveals db2pfchr threads)
terminate all process,do instance restart.
create db1 update db cfg with num_ioservers 6
restore database db1 from backup image – utilizes only 6 pre-fetchers.(db2pd -edus reveals db2pfchr threads)
my observation from test case:
when restore process is creating a new database – it is not referring the DB CFG inside the backup image.
when restore process is overwriting an existing database – it is using the correct num_ioserver configuration, because the db is active with cfg parameters applied.
if restore utility creates database it is not referring DB CFG upfront,it is applying the DB cfg parameters after doing the restore process at the end phase?
Win 7,DB2 Express C 10.5,32 bit,1GB RAM,Intel Dual Core CPU 1.6GHz
Let me know your inputs.
Thanks,
Harish Pathangay
I don’t know when the db cfg is applied. From your tests, it appears to be at the end of the restore.
[…] be aware of the usage of the utility heap by other utilities – LOAD, REDISTRIBUTE, etc. See my entry on the utility heap for more […]
Hi Ember,
Noticed that if the backup utility is an online backup then 50% of UTIL_HEAP_SZ is used, where as when the backup is an offline backup then the amount of UTIL_HEAP_SZ is 90%.
ONLINE BACKUP – 50% of UTIL_HEAP_SZ
OFFLINE BACKUP – 90% of UTIL_HEAP_SZ
Interesting. It is supposed to be all of it for an offline backup, because all utilities are incompatible with offline backup.
Hi Ember,
Nice article. I have a 3 TB database and full online database backup to disk take 16 hours to complete. What would be your recommendations. UTIL_HEAP_SZ is at 262144(4K pages). Should I increase it ? I am also thinking of enabling TRACKMOD and doing incremental backup. Will that have an adverse performance impact considering it will track all page changes and its an highly active database.
Trackmod helps a lot with the write side of a backup, but not as much with the read side.
The biggest overall impact to backup performance (beyond ensuring the system is not constrained on disk speed or processing and reviewing BAR information in the diag log) is to ensure that the data is spread out across table spaces. Backup parallelism is done at the table space level, so if you have one or two really large table spaces, any other threads are just waiting on those large ones to complete. You can DRASTICALLY reduce your backup time by evenly spreading data across table spaces.
Trackmod sets a flag at the table space level to indicate whether anything in that table space has change. The backup then has to read the entire table space to determine what changed and backup only that which changed. So often, you still end up reading the entire table space, but writing out a smaller amount.
In a database that large, you may have some tablespaces that don’t change. In that case, trackmod may be more useful, or it may make sense to mostly do backups at the tablespace level for things that change in between full backups.
But look at the BAR information in the diag log. I’ve blogged on it. It can help you understand where your bottlenecks on backup really are.