Help DBAs identify the root cause of the problem through several simple steps in DB2

Performance problems may be difficult to diagnose and fix. Problems often occur in the most unexpected situations. In general, problems may evolve and sometimes lead to database application interruptions. From the DBA's perspective, it is important to be aware of taking proactive measures to solve the problem before the performance begins to cause damage to the business.

Performance problems may be difficult to diagnose and fix. Problems often occur in the most unexpected situations. In general, problems may evolve and sometimes lead to database application interruptions. From the DBA's perspective, it is important to be aware of taking proactive measures to solve the problem before the performance begins to cause damage to the business.

This article will discuss an analysis process to help DBAs identify the root cause of the problem through several simple steps.

In order to better diagnose performance problems, this article discusses a systematic process to help determine whether there are performance problems and to develop remedial measures. When DB2®Or when the performance of e-commerce applications fails to reach the expected goal, the entire Organization and financial bottom line may be affected.

This article aims to guide DBA in Windows®Define and diagnose DB2 performance problems. The content of this article is as follows:

• Information collected using Windows tools
• Information collected using DB2 tools
• Problem investigation and Solution

Hypothesis

This document assumes that you have a basic understanding of databases, SQL, and DB2 LUW. For UNIX®You may have a basic understanding of performance tuning and monitoring.

Performance Problems and Their Association with system resources

Performance issues cover a wide range of scenarios:

• SQL query execution speed is slower than expected
• The workload or batch processing job was not completed within the expected time, or the transaction rate and throughput gradually declined over a period of time
• Overall system speed reduction

In most cases, performance problems occur because of improper use of system resources or excessive use of resources such as CPU, IO, and memory, which often reveals bottlenecks in these system resources. In an environment with proper optimization, system resources will be used best and will not be overly dependent on any of these resources.

The first step to diagnose performance problems is to identify all resource bottlenecks. Windows provides some tools to help identify these bottlenecks.

CPU bottleneck

If one or more CPUs on the system consistently display a usage rate of more than 90%, this usually means that the system has a CPU bottleneck. Task Manager helps you identify whether the system has a CPU bottleneck. Other tools (such as perfmon.exe and Resource Monitor) will display CPU utilization and help identify CPU bottlenecks.

Memory bottleneck

Memory bottlenecks are not very common, mainly because the database heap and parameters are usually configured based on the available memory. However, if you see very low available memory in perfmon and Resource Monitor, this may indicate a memory bottleneck. Sometimes, when using STMM, the available memory on the system may be very low, but this does not always mean that the system has a memory bottleneck.

Network bottleneck

If you see high network utilization in the Resource Monitor, this may indicate a network bottleneck. The resource monitor displays network utilization in percentages, which helps to quickly identify network bottlenecks. If the resource monitor shows that the network usage is above 80%, this usually indicates a network bottleneck.

I/O bottleneck

If one or more disks remain busy for more than 90% of the time, or the disk queue length is constantly increasing, this usually means that the system has an I/O disk bottleneck. Windows tools such as resource monitor and perfmon can help identify I/O bottlenecks. The task manager can indeed display I/O activities, but the Resource Monitor and perfmon can display the I/O details of each disk and the percentage of activity time, this helps identify bottlenecks on any specific disk.

There are a variety of Windows tools to help determine whether the system has one or more resource bottlenecks.