We’ve checked out numerous areas to check for performance.Now its time to see how the application performance is doing within the DB2 system. During this post, I’ll talk about some of the other standard places to check for performance improvements.
Locking Tables and Lock Escalations
Looking into the DB2 system and application monitor showed huge spikes of CPU during the morning hours. These CPU spikes were also happening when there were a number of applications that are experiencing lock escalation in the system.Lock escalation can be caused by numerous situations and needs to be minimized for best processing throughput possible.For example, in a production performance report there were LOCK TABLES = 14,667 and LOCK ESCALATIONS = 25 during a given day.
The high number of LOCK TABLES is a major consideration because it prevents concurrent work.These situations need further research to determine whether they could be modified and eliminated.
The lock escalations were also troubling in terms of system performance. Twenty-five lock escalations during the daytime online window could cause throughput and CPU performance problems.These lock escalations needed to be further researched to determine their origin and the resources that were being locked.
Write Engine Not Available
Looking into the DB2 system and application monitor showed extended read and write times during peak processing.The disk configuration showed normal disk I/O rates and no particular data sets had particular bad read or write times or performance.
Looking further into the monitor statistic showed that in some of the buffer pools the amount of write activity was very robust during certain peak periods.During these extreme peak periods there were some instances where the write activity was so robust that all of DB2’s write engines assigned to that buffer pool were busy.
This situation is denoted in various performance monitor reports under each individual buffer pool report as Write Engine not available. The condition is caused by updated or dirty pages remaining in the buffer longer than necessary and reducing the number of buffer pages available for other processes.
Once the number of buffer pool pages update becomes too big the write engine try to write all the updated pages to the disk.Since the activity was so robust, DB2 could not keep up and all the write engines were busy trying to keep up.This situation can be fixed in a variety of ways depending on your existing configuration.The first method can be to expand buffer pool allowing more write cushion or changed the deferred write threshold setting to write more frequently or change the workload to spread out the peak timeframe.All will potentially improve the situation and allow DB2 to keep up with the write activity before it runs out of DB2 write engines.
In Summary
Do you have too many locks against your tables? How many lock escalations happened today? The answer to these questions lies in looking at the order of your application processing and your programming methodologies so that concurrent transactions can occur easily.
Check to see if there are lots of times that a write engine is not available.Look at the size of your buffer pools, look at the deferred write threshold and look at the timing of your jobs and limit the times write engines are not available.
See you next week when we continue on with our exploration of this fascinating case study.
________________________________________
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