Guru: Understanding Database Performance Using The Performance Data Investigator, Part 1
June 21, 2021 Dawn May
This is a two-part series on how to use the Performance Data Investigator (PDI) for investigating performance of Db2 for i. While the SQL Performance Center in Access Client Solutions is the tool of choice for Db2 performance analysis, there is valuable information that you can get from using PDI. PDI is often a useful starting point to help guide you in the correct direction when trying to identify performance issues. In my experience, PDI is under-utilized for Db2 investigation. For those not familiar with PDI, you may want to read the article, Getting Started with the Performance Data Investigator, before reading the rest of this tip.
The Investigate Data perspectives are organized into content packages. A content package is simply a group of charts (perspectives) based upon common underlying data. For example, the Collection Services content package features charts based upon Collection Services data.
For this article, we are going to review the Database content package, which is included with the Performance Tools license program product (5770-PT1, option 1). Most shops have this product. If you do not have 5770-PT1 installed, you will not see the Database content package.
Expand the Database content package to reveal many perspectives. The three at the top of the navigation tree are typically referred to as starting points, since they provide a system-wide overview that is helpful to determine where to get started. When you select a chart for review, you must also select the collection with the data used to build the charts. Most of the charts in the Database content package are based upon Collection Services data.
I/O Reads and Writes
The I/O Reads and Writes chart is a system-wide overview of database reads and writes, and shows physical vs logical I/O and synchronous vs asynchronous I/O. In general, it is good to know what your reads and writes base-line signature looks like; if it changes significantly, it can be a sign that something has gone wrong.
Physical I/O means that the I/O request, whether it is a read or a write, has to access data on disk to complete the operation. Logical I/O occurs when the data is already in memory and simply needs to be located for your application to access it. Synchronous I/O occurs when the read or write must complete before control is returned; asynchronous I/O allows other work to be done while waiting for the I/O operation to complete. Physical and synchronous I/O operations are generally of greater concern from a performance perspective.
The screen capture below is an example of what the I/O Reads and Writes chart looks like. Assuming your business has a daily structure of processes that are run, the signature should be similar from day to day. You can drill down from this chart, which leads you to the overview charts found in the Database I/O folder, or you can explore the additional charts in the Database I/O folder directly; these additional charts allow you to review more detailed information regarding database I/O, including the ability to review the I/O based upon jobs, generic jobs, current user profile, subsystems, etc. The blog post, Viewing Job Level SQL Metrics with the Performance Data Investigator, also listed in the references below, has more information on the database I/O charts.
SQL CPU Utilization Overview
The SQL CPU Utilization Overview chart is a great place to start when you have an issue with CPU utilization, but are unsure whether it is related to SQL usage or some other reason. This chart is a system-wide overview that shows whether CPU is used for SQL or non-SQL workloads.
In the screen capture below, I see the CPU utilization over the course of the day, and it shows me that the CPU utilization was generally not related to SQL. In the second screen capture, I removed the non-SQL CPU Utilization metric to more closely review my SQL CPU Utilization, where I can see my SQL CPU utilization signature throughout the day.
This chart is a great starting point when you have a CPU issue as it helps you determine what tools to use. If SQL CPU Utilization shows up as a major contributor, you know you need to use the SQL Performance Center to dig deeper.
Database Locks Overview
The Database Locks Overview chart is a system-wide chart that shows time spent waiting for database record locks. Database record lock contention can be a normal situation as the system must hold a lock when a record is being added, updated, or deleted to order to ensure data integrity. However, unusually large amounts of record lock contention often indicate application issues. Resolving contention generally improves performance. You can drill down into Collection Services data to identify the impacted jobs. Those jobs are victims wanting the lock, but unable to get it.
If you have an issue with database record lock contention, you will most likely need to collect Job Watcher data to identify the root cause. Job Watcher data can identify the thread that holds the lock. Job Watcher also collects call stacks and SQL statements, which are very valuable in resolving contention issues. Refer to The basics of IBM i wait accounting for more information.
I plan to provide an overview of remaining perspectives in the Database content package in part 2.
Dawn May is a leading authority on work management, systems management, performance, and diagnostics, with intimate knowledge of the IBM i operating system developed through her distinguished career with IBM. She focuses her skills on helping companies troubleshoot issues and plan for the future while teaching them how to get the most out of their IBM i systems. To learn more about PDI and how to use it, or for assistance with performance issues, visit her Website, dawnmayi.com to review her offerings.
RELATED STORIES
Getting Started With The IBM i Performance Data Investigator
