Deletes go very slow if the log buffer size is too small and we delete lots of data from big tables. It is very important to have the right settings for log buffer size to avoid having to frequent writes to the disk, writes that cause a high IO traffic that will slow down the DELETE.
STEP 1. Check the size of the log buffer size.
This parameter allows you to specify the amount of the database heap (defined by the DBHEAP parameter) to use as a buffer for log records before writing these records to disk.
[db2inst1@localhost ~]$ db2 get db cfg for MyDB | grep LOGBUFSZ Log buffer size (4KB) (LOGBUFSZ) = 4096
STEP 2. Check the size of Database heap
[db2inst1@localhost ~]$ db2 get db cfg for MMS | grep DBHEAP Database heap (4KB) (DBHEAP) = AUTOMATIC(4546)
Note that to increase LOGBUFSZ you must increase also DBHEAP (LOGBUFSZ is allocated from DBHEAP).
STEP 3. Increase DBHEAP
[db2inst1@localhost ~]$ db2 -v update database cfg for MMS using DBHEAP 9092 [db2inst1@localhost ~]$ db2 -v terminate
STEP 4. Increase LOGBUFSZ
[db2inst1@localhost ~]$ db2 -v update database cfg for MMS using LOGBUFSZ 8196 [db2inst1@localhost ~]$ db2 -v terminate
STEP 5. Test
Several tests with different values have to be made to ensure that just the right size is set. It is important to note that a value too big will start to affect other things sooner or latter.
Contribute to this site maintenance !
This is a self hosted site, on own hardware and Internet connection. The old, down to earth way 🙂. If you think that you found something useful here please contribute. Choose the form below (default 1 EUR) or donate using Bitcoin (default 0.0001 BTC) using the QR code. Thank you !