HomeMogDBMogDB StackUqbar
v5.0

Documentation:v5.0

Supported Versions:

Other Versions:

WDR

Availability

This feature is available since MogDB 1.1.0.

Introduction

The workload diagnosis report (WDR) provides database performance diagnosis reports based on the baseline performance and incremental data that reflects performance changes.

Benefits

  • The WDR is the main method for diagnosing long-term performance problems. Based on the performance baseline of a snapshot, performance analysis is performed from multiple dimensions, helping DBAs understand the system load, performance of each component, and performance bottlenecks.
  • Snapshots are also an important data source for self-diagnosis and self-optimization suggestions on subsequent performance problems.

Description

The WDR generates a performance report between two different time points based on the system performance snapshot data at these time points. The report is used to diagnose database kernel performance faults.

You can use generate_wdr_report(…) to generate a performance report based on two performance snapshots.

The WDR depends on the following two components:

  • Snapshot: The performance snapshot can be configured to collect a certain amount of performance data from the kernel at a specified interval and store the data in the user tablespace. Any snapshot can be used as a performance baseline for comparison with other snapshots.
  • WDR Reporter: This tool analyzes the overall system performance based on two snapshots, calculates the changes of more specific performance indicators between the two time points, and generates summarized and detailed performance data. For details, see Table 1 and Table 2.

Table 1 Summarized diagnosis report

Diagnosis Type Description
Database Stat Evaluates the load and I/O status of the current database. Load and I/O are the most important indicators of the TP system.
The statistics include the number of sessions connected to the database, number of committed and rolled back transactions, number of read disk blocks, number of disk blocks found in the cache, number of rows returned, captured, inserted, updated, and deleted through database query, number of conflicts and deadlocks, usage of temporary files, and I/O read/write time.
Load Profile Evaluates the current system load from the time, I/O, transaction, and SQL dimensions.
The statistics include the job running elapse time, CPU time, daily transaction quality, logical and physical read volume, read and write I/O times and size, login and logout times, SQL, transaction execution volume, and SQL P80 and P95 response time.
Instance Efficiency Percentages Evaluates the cache efficiency of the current system.
The statistics include the database cache hit ratio.
Events Evaluates the performance of key system kernel resources and key events.
The statistics include the number of times that the key events of the database kernel occur and the waiting time.
Wait Classes Evaluates the performance of key events in the system.
The statistics include the release of the data kernel in the main types of wait events, such as STATUS, LWLOCK_EVENT, LOCK_EVENT, and IO_EVENT.
CPU Includes time release of the CPU in user mode, kernel mode, I/O wait mode, or idle mode.
IO Profile Includes the number of database I/O times, database I/O data volume, number of redo I/O times, and redo I/O volume.
Memory Statistics Includes maximum process memory, used process memory, maximum shared memory, and used shared memory.

Table 2 Detailed diagnosis report

Diagnosis Type Description
Time Model Evaluates the performance of the current system in the time dimension.
The statistics include time consumed by the system in each phase, including the kernel time, CPU time, execution time, parsing time, compilation time, query rewriting time, plan generation time, network time, and I/O time.
SQL Statistics Diagnoses SQL statement performance problems.
The statistics include normalized SQL performance indicators in multiple dimensions: elapsed time, CPU time, rows returned, tuple reads, executions, physical reads, and logical reads. The indicators can be classified into execution time, number of execution times, row activity, and cache I/O.
Wait Events Diagnoses performance of key system resources and key time in detail.
The statistics include the performance of all key events in a period of time, including the number of events and the time consumed.
Cache IO Stats Diagnoses the performance of user tables and indexes.
The statistics include read and write operations on all user tables and indexes, and the cache hit ratio.
Utility status Diagnoses the background task performance.
The statistics include the performance of background tasks such as replication.
Object stats Diagnoses the performance of database objects.
The statistics include user tables, tables on indexes, index scan activities, as well as insert, update, and delete activities, number of valid rows, and table maintenance status.
Configuration settings Determines whether the configuration is changed.
It is a snapshot that contains all current configuration parameters.
SQL detail Displays information about unique query text.

Enhancements

None.

Constraints

  • The WDR snapshot collects performance data of different databases. If there are a large number of databases or tables in the database instance, it takes a long time to create a WDR snapshot.
  • If WDR snapshot is performed when a large number of DDL statements are executed, WDR snapshot may fail.
  • When the database is dropped, WDR snapshot may fail.

Dependencies

None.

WDR Snapshot

Copyright © 2011-2024 www.enmotech.com All rights reserved.