Last time I wrote about some of the application performance aspects. This week we’ll delve deeper into DB2 Checkpoint aspects for recoveries and index definitions over your busiest tables and their potential performance impact.
DB2 Checkpoint System Frequency
The DB2 system runs transactions and takes system wide recovery checkpoints for system consistency and integrity.These system wide checkpoints synchronize the system across all applications and can be disruptive because they hold up transactions to get a synchronization point.It is a common industry rule-of-thumb to take these checkpoints every ten to fifteen minutes to have good recovery points and not hold up processing.
Within the examined system these checkpoints were being taken very infrequently because the active log data sets were very large.This frequency, while good for processing through put, exposes the DB2 system to having an elongated recovery window should a disaster happen.The system checkpoint frequency needed to be evaluated and adjusted to provide better recoverability.
This situation also can cause issues during the batch cycle as programs write over 250,000 or 500,000 log records without the system taking a checkpoint.
Too Many Indexes Defined on Busy Tables
Looking at the workload for the overall system found that one of the main insert, update and delete processing tables also had a large number of indexes defined on it.Querying the DB2 catalog, found 11 indexes defined on this very busy table.Analysis also showed that the majority of the processing was inserts, updates and deletes as opposed to select activity.
Other very busy tables also had 7 indexes.These tables and each of their indexes needed to be analyzed for further usage versus their overhead.
Too Many Indexes Not Being Used
Looking further into the indexes that were defined on the application database also showed many indexes that were not being used.Querying the DB2 catalog I often find a large number of indexes defined that are not being used.Sometimes these indexes that aren’t being used are even on the largest or the busiest tables.
Further research showed that some of these indexes were defined for one time processes and weren’t deleted after the processing was complete.One other index was being used and was critical for the dynamic SQL workload.So just because you queried the DB2 Catalog with the query below to find the index plan and package dependencies does not mean that you can delete the index.
SELECT DISTINCT TBNAME,NAME AS INDEXNAME
FROM SYSIBM.SYSINDEXES
WHERE DBNAME LIKE ‘<database-name>%’
ANDNAME NOT IN
(SELECT BNAME
FROM SYSIBM.SYSPACKDEP
UNION
SELECT BNAME
FROM SYSIBM.SYSPLANDEP)
Run the query and use the indexes listed as your starting point for further analysis. Each index listed could be critical for dynamic SQL performance so be very careful before deleted any indexes from your system.
In Summary
Verify the checkpoint frequency of your systems.Having the correct checkpoint frequency can provide the proper checkpoints for disaster and regular recoveries of your databases and system. The recommended checkpoint frequency is about every 10 to 15 minutes.So depending on how busy your system is adjust the number of log records accordingly.
Having extra indexes over busy tables can be a huge performance overhead.Deleting two or three extra indexes can sometimes cut one-quarter or one-half of a second off your transaction response time.So make sure you only have the indexes that are being used defined on your tables.
________________________________________
Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.
Leave a Reply