As a database administrator (DBA), performance monitoring and tuning can be your most challenging business-critical task. You need comprehensive and proactive performance and tuning tools to help you to resolve immediate performance issues as well as to help avoid future performance issues. DB2 Performance Expert helps you optimize your company’s DB2 servers and DB2 applications, whether for data warehousing, for OLTP, or for mixed environments.
Performance Expert helps you quickly isolate current and potential performance problems. Its user interface makes performance and tuning simple so that both novice and expert database administrators can use it. Performance Expert can help you do the following:
- Optimize buffer pools
- Tune your database for maximum throughput
- Improve your database capacity and reliability
- Obtain a detailed analysis of system performance based on your DB2 for Linux®, UNIX®, and Windows® workload and operating system
You can use operating system counters to help you understand how components outside of the database engine affect your data server.
Performance Expert also offers multiple historical views of performance data. You can see current data, which can help you respond quickly to any emerging conditions, and you can see past performance data, both long-term and short-term. Short-term and long-term history, which is described in greater detail later in the article, can help you troubleshoot problems that occurred in the past, such as over the weekend. You can use the long-term data for trend analysis to help you see patterns of use that you can adjust to prevent problems in the future.
This series describes the top six actions (based on IBM's experiences working with customers) to get the most out of DB2 Performance Expert. This article addresses the first three actions:
- Change the default setting for server history configuration
- Set up alerts
- Use your System Health data views
Changing the default settings for server history configuration
A powerful feature of DB2 Performance Expert is its ability to provide key performance indicators (KPIs) and configuration data on past, present, and future events to help you understand how your DB2 data server performs today and in the future. To take advantage of Performance Expert’s historical capabilities, it helps to understand a bit about how it works.
Short-term history data
In order to understand your system, take a look at how your system has been running historically. Performance Expert history consists of both short-term and long-term history.
Short-term history provides high-level, detailed statistical counters regarding the DB2 applications, system health, database activity, SQL activity, locking conflicts, Workload Management, operating system (including process, memory, and file system), DB2 configuration parameters, containers, and more. Short-term history helps you troubleshoot problems that occurred during the evening or after end users complain about bad response times. You can also use short-term history data to create performance baselines from a test run and to compare those baselines with other test runs. You can read more about performance baselines later in this article.
Short-term history data is deleted automatically from the performance database after a specified retention time frame. Performance counters of short-term history are automatically aggregated and can be used for long-term trend analysis.
Long-term history data
The long-term history data is not as detailed as the short-term history data. Long-term history data is aggregated from short-term history data, and it resides much longer in the performance database of Performance Expert. Data remains in the performance database forever unless you delete it manually.
Performance Expert can provide a graphical view of your system’s long-term aggregated data, offer trend analysis of the past, and provide future predictions automatically. Reports on the long-term data can help you do the following:
- Identify long-running SQL by using reports that show which SQL statements are being executed and which can be sorted by differing cost values, including sort time or CPU.
- Prioritize workloads by storing DB2 Workload Management performance counters and using reports of histogram charts and summary data.
- Track the number of transactions per day grouped by database name, partition number, and timestamp to help with workload planning.
- Identify problems with memory, including low buffer pool hit ratios, catalog cache hit ratios, and package cache hit ratios.
- Identify changes in your dbm and db configuration parameters visually so you don’t need to keep track of those changes.
The reason for changing the default settings
Performance Expert collects performance counters and stores them in a DB2 database, which is called db2pe by default. For each monitored DB2 instance, change the default setting for history collection, because most likely each monitored DB2 instance’s workload is different.
The default setting for short-term history is 50 hours. In many cases, this is not long enough, and you should keep short-term history for 3 to 8 days. The 3-day minimum is to get you through a weekend. One week is recommended. For example, you might have a customer who is experiencing a performance problem on Friday in his development and production environments. On Friday, you focus on fixing his production environment. Then on Monday morning, you work with the DB2 application developers and use Performance Expert’s short-term historical data to pinpoint the performance problem in development. If you don't keep that data over the weekend, that detailed information is not available for analysis.
The drawback of keeping this much data is, of course, disk space, but the benefit of having this data available when needed outweighs the cost in most cases.
Changing the short-term history defaults
To change Performance Expert’s short-term history settings:
- Navigate to PE’s System Overview window.
- Log into the monitored DB2 instance you want to change.
- Right-click the monitored instance, and select Properties from the drop-down box.
- Select the History tab, as shown in Figure 1.
Figure 1. Changing the short-term history default settings
- Change the Timeframe value to at least 72 hours (for 3 days).
- After you make changes, click OK, and your changes are dynamically changed on the Performance Expert server for this monitored instance. You do not need to restart the Performance Expert server.
Here are more recommendations for short-term history settings:
- If you are monitoring a warehousing environment, you want Performance Expert to gather performance counters every 5 to 10 minutes. A data warehouse is often associated with an On-line Analytical Processing (OLAP) system that is designed for optimum read capability, which is why you can gather performance counters at longer intervals than you might for an OLTP system.
- You should also consider changing the Performance Expert recording interval to more than 60 seconds. The value you put in the recording interval is a multiplier for any component history setting (which you can find below the recording interval on the History properties page). For example, if your recording interval is 60, and you specify 5 as the System multiplier, you get 60 x 5, which is 300 seconds (5 min).
- You should also enable statistics for locking conflicts if locking problems might happen on your monitored system that you want to further investigate. By default, locking counters are not gathered. This interval value should be smaller than the LOCKTIMEOUT database configuration parameter.
Setting up alerts
When it comes to performance and tuning, you don’t have the time to constantly look for potential performance problems, so you need alerts to help you identify performance problems before they become severe. Performance Expert can send out both warning and error alerts using email or text message. Typically, you then go to the dashboard (System Overview) and drill through the highlighted items to find the source of the problem.
How alerts are displayed
When a warning level alert occurs, Performance Expert highlights it in yellow. Red is used to highlight those alerts that have gone beyond warnings to actual problems, as shown in Figure 2.
Figure 2. Alerts are displayed in red in the System Overview screen
You can double-click any of the key performance indicators from the Performance Expert System Overview window or dashboard to drill down into more key performance indicators. In Figure 2, the Buffer Hit Ratio for the FRUITS Buffer Pool is extremely low. By double clicking it, you go to more details, as shown in Figure 3. Figure 3 shows the hit ratio highlighted in red, which indicates a performance problem.
Figure 3. Key performance indicators that generated an alert
Click here for a larger view of Figure 3.
Types of alerts
Performance Expert provides two types of alerts:
- Deadlock event exception, which is used to help investigate when deadlocks have occurred
- Periodic exception processing alerts (threshold alerts), which are based on operating system and DB2 performance counters
It is easy to set up alerts by setting filters and qualifications on a data counter. For example, you can define a threshold alert to be triggered if your Buffer Pool Hit ratio drops to below 60% for a warning level or 55% for a problem level. If a warning is triggered, Performance Expert sends you an email and highlights the item on the System Overview in yellow. Then when the problem level is triggered, you receive another email, and the item is highlighted in red on the System Overview. You can filter on buffer pool name, partition number, and other properties.
The thresholds are defined in a threshold set, which is a list of Periodic Exceptions performance counters. You can find a list of these threshold sets in the Performance Expert Exception Processing wizard. You can use one of Performance Expert’s pre-defined threshold set templates for different workloads, such as OLTP, Content Manager, and Business Intelligence/Warehousing. You can modify and save these pre-defined threshold set templates, or you can create your own template from scratch.
Recommendations for defining thresholds
If you are new to DB2 or you are not sure what to define as warning and problem levels for counters or even what counters you should be looking at, use one of the predefined threshold templates. Then when an alert is triggered, you can examine what was triggered and why. You might need to modify the template based on your system resources.
Using filters (also called qualifiers) on thresholds is an excellent idea. You can put a filter on a particular threshold counter. For example, if you are monitoring a Content Manager system, you can define an alert to be triggered when the ICMFL32 TableSpace Disk Usage percentage is greater than 90% full for a warning level and 95% full for a problem level. Some DBAs prefer to keep their threshold sets generic or to limit the qualifiers they place on the counters because they use the same threshold set for multiple DB2 instances. You cannot assign two different threshold sets to run against the same DB2 instance.
If you’re running a mixed environment, then combine the OLTP and BI/Warehousing threshold sets, then edit or remove the additional counters. Follow these steps to combine the thresholds sets:
- From the PE System Overview window, click the Exception Processing icon (the first icon in the row of icons at the top left side of the screen).
- Right-click Threshold sets > New Threshold Set.
Mixed Envfor the name of your threshold set, enter your name as author, and click the Predefined radio button.
- Click Statistics BI for Threshold for BI/Warehousing, and click OK.
- When the threshold opens up, select Threshold set > Add, and select to add the OLTP threshold set, as shown in Figure 4.
Figure 4. Predefined threshold templates
Using your System Health data views
Performance Expert provides you graphical data views called System Health Data Views. These give you an overall understanding of the health of your system for performance and tuning. You know that old saying: "A picture is worth a thousand words." Performance Expert has system health data views for your DB2 and Operating System Performance Counters. You can use these system health data views to diagnose and tune performance problems.
Performance Expert provides 40 predefined data views, including:
- Average number of locks per application
- Average number of rows read per selected row
- Catalog and package cache hit ratio (%)
- Compare application hit ratio of buffer pools
- Compare application hit ratio of databases
You can also create customized data views. Performance Expert also includes Data View groups that ship with Performance Expert Client for the Warehousing environment, for the Business Intelligence environment, or for the Content Manager environment.
Note: You can look at both current and historical data in a data view, even one you just created.
Defining your system health charts using predefined data views
First, some terminology. A data view is a system health chart used to focus on a performance problem or issue. An example of a data view is a chart to display the SQL statement distribution. SQL statement distribution shows the distribution of dynamic and static SQL statements for a database.
Performance Expert enables you to create a group of different data views into data groups. Most DBAs create data groups around a specific performance counter. For example, you might create a group for sorts and include data views related to sorting. Some key data groups include:
- Page I/O
- System statistics, including CPU, memory, and disk space
Recommendations for using a predefined data view
Here’s how to configure a data group called SQL that uses two predefined data views to see SQL statement throughput and to sort overflows.
- From the System Overview window, click the DB2 instance, and click the System Health button on the toolbar at the top of the window, as shown in Figure 5.
Figure 5. Performance Expert System Overview window icons
- From the toolbar, click Selected > New, as shown in Figure 6.
Figure 6. Create a System Health data group
Click here for a larger view of Figure 6.
- For the Data Group name, enter
- After you create the new SQL data groups, navigate to the new group from the tree of the System Health window.
- Right-click that group (SQL) and select Open Predefined Data View, as shown in Figure 7.
Figure 7. Create a System Health predefined data view
Here’s how to create a data view for Sorts, which is in the example SQLdata group.
- From the right side of the System Health window, right-click SQL, and select Rows read vs Selected.
- Select Sort overflows [%], and click OK.
- Select Sort overflows [%] Data View, then click OK, as shown in Figure 8.
Figure 8. Select from a predefined System Health data view
- Repeat steps 1 and 2, but select SQL stmt throughput, as shown in Figure 9.
Figure 9. Select the SQL statement throughput predefined data view
Notice that the Predefined Data View window includes a brief description to help novice DB2 users understand how this will help them.
Create a customized data view
If you don’t want to use one of the predefined data views, you can create your own. Here is how to create a customized System Health data view that compares the number of rows read to the number of rows selected. This customized data view is added to the SQL Data Group you created in the previous section.
- Navigate to the SQL Data Group you created, and click New, as shown in Figure 10.
Figure 10. Create a customized System Health data view
- On the Category tab, enter
Rows Read vs Rows Selected, and click Statistic Details, Databases, as shown in Figure 11.
Figure 11. Customize your System Health data view
- Click Next to go to the Counters tab.
- Select the database in which you want to compare counters.
- Scroll down in the Select counters section to select Rows read and Rows selected, as shown in Figure 12.
Figure 12. Select the counters
- Click Next to continue to the Thresholds tab.
- Select the Dynamic scale radio button, as shown in Figure 13. In this window, you can define a threshold for this chart. This threshold definition draws a line across the chart to highlight that a threshold has been reached. Some people confuse the threshold displayed on this chart with the thresholds defined in Exception Processing. The threshold on this chart does not trigger an alert. Only the counters defined in the Exception Process can trigger an alert.
Figure 13. Customize your System Health data view by defining a threshold
- Select the Graphics tab.
- Select the bar chart radio button, and choose the colors and patterns you want to use, as shown in Figure 14.
Figure 14. Customize your System Health data view by creating a bar chart
- Click Finish. You’ll see in the next section what the final result looks like.
See Resources for more information about creating customized data views.
Using the DB2 Performance Expert history function to go back in time
- From the System Health screen, click History.
- Click the right arrow key to step back through past events. Even though you just created this chart by following the steps in this article, you can still use the Performance Expert history to go back in time to examine data for Rows Read vs Rows Selected.
- Position your mouse pointer over the Data View chart, and hold down your left mouse button to display the actual values, as shown in Figure 15.
Figure 15. System Health data view customized chart
Click here for a larger view of Figure 15.
Creating data view reports in HTML format
DBAs wonder whether it’s possible to generate reports of the Performance Expert System health. The answer is “Yes, it’s possible to generate reports.” To generate the report, right-click the chart, and select Print from the pop-up window, as shown in Figure 16.
Figure 16. Create a report
A browser opens displaying the chart legend, the report, and the graph in an HTML format.
Importing System Health data groups for a warehousing environment
To import predefined data groups, follow these steps:
- Right-click Data Groups from the System Health window, and select Import, as shown in Figure 17.
Figure 17. Importing System Health data group
- Notice the variety of System Health data groups that have been created specifically for warehousing and business intelligence, as shown in Figure 18.
Figure 18. Select one of many System Health predefined data groups and show charts
- Select BI_DataViews_Workload.xml from the window, and click Open.
The predefined charts open, as shown in Figure 19.
Figure 19. Predefined warehousing/BI charts
This first part of the series introduced you to DB2 Performance Expert for Linux, UNIX, and Windows. It explained how to get the most out of Performance Expert by changing your default settings for server history configuration, setting up alerts, and using your System Health data views. Stay tuned for Part 2, where you’ll learn to take performance baselines, to take advantage of DB2 Workload Management, and to configure monitoring in large data partitioning environments.
- Use an RSS feed to request notification for the upcoming articles in this series. (Find out more about RSS feeds of developerWorks content.)
- Refer to the Integrated Data Management Information Center for more information on creating customized data views.
- Check out the Performance Expert web page.
- Explore the material available on Video Channel DB2, including Performance Expert Overview, Performance Expert Warehouse, and Performance Expert Extended Insight Feature.
- Look at the IBM Data Studio Web page.
- Find the IBM Data Studio community space for more Data Studio information.
- Read the Data Studio/Integrated Data Management blog for more Data Studio scoop.
- Review the IBM Redbook®.
- Watch the demo A Day in the Life of a DBA to observe a DBA use DB2 Performance Expert and Extended Insight Feature to solve typical problems.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.