Use the quick diagnosis and deadlock analysis features - PolarDB

The quick diagnosis feature provided by PolarDB for MySQL integrates with some features of Database Autonomy Service (DAS) and allows you to use the deadlock analysis feature to analyze the most recent deadlock on a database and check the analysis details.

Overview

The deadlock analysis feature allows you to perform multi-dimensional analysis on deadlocks, transaction blocking, and metadata locks on a cluster.

Important

If you want to enable the full deadlock analysis and other deadlock analysis features, make sure that the following requirements are met:

The cluster is a PolarDB for MySQL cluster.
The DAS Cost-efficient Edition or DAS Enterprise Edition is enabled. DAS Cost-efficient Edition and DAS Enterprise Edition are available only in some regions. For more information, see Supported databases and regions. For information about how to enable DAS Cost-efficient Edition or DAS Enterprise Edition, see Enable DAS Cost-efficient Edition and DAS Enterprise Edition.

Recent deadlock analysis: DAS analyzes the most recent deadlock logs in the returned results of the SHOW ENGINE INNODB STATUS statement. If multiple deadlocks occur, DAS analyzes only the most recent deadlock.
Full deadlock analysis: DAS analyzes error logs at regular intervals, parses the deadlock information, and performs comprehensive deadlock analysis. DAS also allows you to view the deadlock trends within a specified time range and view details of each deadlock.
Other lock analysis: DAS analyzes the metadata lock and transaction blocking in the current sessions of your cluster in real time based on the data in information_schema and performance_schema.
- Metadata lock analysis: DAS deduces the lock wait relationships and generates the corresponding diagram based on the data in tables such as the information_schema.processlist table.
- Transaction blocking analysis: DAS analyzes the transaction blocking relationships and generates the corresponding diagram based on the data in the information_schema.processlist, information_schema.innodb_trx, and information_schema.innodb_lock_waits or performance_schema.data_lock_waits tables. Data in the information_schema.innodb_lock_waits table is used for clusters that run MySQL 5.6 or MySQL 5.7, and data in the performance_schema.data_lock_waits table is used for clusters that run MySQL 8.0.
  Note
  The transaction blocking analysis feature is not supported for PolarDB for MySQL clusters that run MySQL 5.6.

Parameter settings

To use the deadlock analysis feature on a cluster, you must configure the corresponding parameters of the cluster. The following table describes the parameter settings required for different deadlock analysis features.

Deadlock analysis feature	Required parameter setting
Recent deadlock analysis	Set the `innodb_deadlock_detect` parameter to ON.
Full deadlock analysis	Set the `innodb_deadlock_detect` parameter to ON. Set the `innodb_print_all_deadlocks` parameter to ON.
Transaction blocking analysis in other lock analysis	Set the `performance_schema` parameter to ON if your PolarDB for MySQL cluster runs MySQL 8.0.

For information about how to modify the parameters of a PolarDB for MySQL cluster, see Configure cluster and node parameters.

Procedure

Log on to the PolarDB console.
On the Clusters page, click the ID of the cluster.
In the upper-left corner, select the region in which the cluster is deployed.
In the left-side navigation pane, choose Diagnostics and Optimization > Quick Diagnostics.
Click the Deadlock Analysis tab.
On the Deadlock Analysis page, select the target cluster ID from the Current Node drop-down list and click Diagnose.
Click View Details in the Details column for the most recent deadlock that is detected.
Note
The View Details button is available only when the Deadlock Detected column for the same item appears in the Yes state.
In the Deadlock Analysis dialog box, view the details of the deadlock analysis result. You can click View Deadlock Log to view the details of the most recent deadlock log.