DB2 performance measurement and tuning hands on exercises

A common symptom of a performance issue in a database system is that a job runs slower than usual. For example, a report does not return as quickly as expected, an ETL job takes longer to complete. Sometimes, the performance issue is system wide in which the whole system is slow down but not specific to certain jobs. Performance problem may sound like less severe than a system down issue. However, it could cause direct business impact. For example, a faster response time is usually a decisive factor for people to choose the vendor of web applications. If an application constantly receives time out because of the slow database response time, and the application time out value reaches the maximum value, the direct outcome of this problem would be that no user can use the application. We have seen frustrated DBAs trying to get around with the application time out issue to bring their client live to the application.

It's known that performance issue in database system is usually not easy to diagnose. Under the common symptom of the slow performance, there could be many different factors have played a role in the problem. To determine which factor is in play or where the bottleneck is, a different set of diagnosis data would be needed for different nature of the performance issue. What data to collect is a challenge, how to capture the data so it's useful for diagnosis is another challenge. When facing such situation, how to resolve the case to avoid business impact? Calling DB2 support is an option, but the turn-around time of support may not be an immediate relief when situation is critical. To be a skillful DBA resolving the issue locally would be ideal. Typically performance issue requires skilled and experienced DBA.

When facing a performance issue, clarifying the problem scope is the first step for analysis. To clarify the scope, questions to be asked could be: is there a bottle neck in I/O, CPU, memory, or the network? Is the performance problem at the system level, database level, for certain activities or specific queries? Capturing data to find out where the bottle neck exists is very important. Once the bottle neck is identified, a solution will more naturally follow.

If you have handled DB2 performance issue, you probably already know snapshot tools used in DB2. Get snapshot command collects status information and formats the output for the user. The information returned represents a snapshot of the database manager operational status at the time the command was issued. Usually several snapshots need to be collected to get a picture of the system performance in a period of time. DB2 V9.7 has enhanced the existing monitor features in both functionality and usability. New SQL interfaces for monitor reports are more useful than the traditional snapshot interface. The monitoring elements are redefined for different dimensions and the system dimensions that monitor data is accessed through service class, workload, connections and units of work. The activity dimension monitor data is accessed through package cache, package cache event monitor and activity event monitor. For example, if an activity is running unusually long, how do we find out where the extra time was spent in DB2? Using snapshot, we could capture several snapshots and examine the data flow to identify what specifically is happening. The new monitor SQL interface in DB2 V9.7 will report the time matrix of the activity, for example through the total wait time. This way we can see if the slowdown is from DB2 processing the activity, or if DB2 is idle due to other problems such as poor I/O.

For example, for slow application response time, to determine if the slowness is specific to a query, user can query the new table function mon_get_pkg_cache_stmt to get the performance data of the query from the package cache. We can tell if the slowness is from the query compilation or from the runtime. The table function mon_get_pkg_cache_stmt reports total execution time and total wait time of the query. If the total wait time is taking most of the execution on time, we can further look at other data to see why query stayed idle, it could be physical data read due to low buffer pool hit ratio. If the slowness is not specific to a query, for example, all queries are running fast from DB2 CLP, but the application is slow. If the slowness is not specific to a query issued in the application, could it be related to other operations like commit? Or could the delay be from the network? Those type of questions can be answered using the new monitor view mon_connection_summary can be used to monitor application level metrics to determine where the bottleneck is. For example, the bottleneck could be in a commit or in the network.

This workshop was designed to help the workshop attendees to understand the nature of the performance issues, how to find the bottleneck of the problem and know what diagnostic data are necessary for different performance issues. The workshop was broken down into 3 sections. Each section covered a short presentation on a use case and relevant monitoring features for the case analysis, discussion and hands on exercise. Through the interactive class, workshop participants gained confidence for handling general database performance problems. This workshop also included some convenient SQL statements that will allow you to monitor the health and performance of DB2. The new monitor features of DB2 V9.7 was used to illustrate how a database performance is measured. The knowledge carried from the workshop is not just for DB2, but applicable to general database systems.