PolarDB:Long-running transaction logs

In PolarDB-X, slow query logs record SQL statements whose execution time exceeds a specified threshold. Long-running transaction logs record transactions whose execution time exceeds a specified threshold. The default threshold is 3 seconds. Long-running transactions can occupy connection and lock resources for extended periods, which negatively impacts the concurrent processing capabilities of the database. To address this issue, PolarDB-X provides the long-running transaction log monitoring feature.

Prerequisites

• The compute node version must be 5.4.17-16859297 or later. For more information about how to view and upgrade the instance version, see View and update the version of an instance.

• This feature is available only for Enterprise Edition instances.

View long-running transaction logs

1. Log on to the PolarDB-X console.

2. In the upper-left corner of the page, select the region where the target instance is located.

3. On the Instances page, click the PolarDB-X 2.0 tab.

4. Find the target instance and click the instance ID.

5. In the navigation pane on the left, click Diagnostics and Optimization > Long-running Transaction Log.

   Item	Description
   Trend of Long-running Transaction Logs	Displays the trends in the number of long-running transaction logs and CPU utilization over time to identify correlations between them.
   Node List	Displays the number of long-running transaction logs generated on each compute node within the specified time range. Click a node to go to the long-running transaction log page for that node.
   Details of Long-running Transaction Logs	The details of each long-running transaction log.
   Statistics of Long-running Transaction Logs	The statistics about long-running transaction logs.

Statistics of long-running transaction logs

Statistics for long-running transaction logs within a specified time range are collected based on transaction templates.

Transaction templates

Each transaction consists of a sequence of SQL statements, and each SQL statement has a corresponding SQL template. A transaction template is a sequence of these SQL templates that represents a specific type of business operation. Because variables such as query parameters are parameterized, different transactions that perform the same operation are grouped under a single transaction template.

Example

The following example shows a money transfer transaction.

Transaction 1: Mike transfers 1 CNY to John.

begin;
update account set balance = balance + 1 where name = 'Mike';
update account set balance = balance - 1 where name = 'John';
commit;

Transaction 2: Mike transfers 10 CNY to John.

begin;
update account set balance = balance + 10 where name = 'Mike';
update account set balance = balance - 10 where name = 'John';
commit;

Transaction 1 and Transaction 2 share the following transaction template, which represents the business logic of transferring an amount from one account to another.

update account set balance = balance + ? where name = ?;
update account set balance = balance - ? where name = ?;

If this type of transfer occurs N times within a specified time range, the execution count for this long-running transaction template is N.

Fields

Details of long-running transaction logs

To view the details of long-running transaction logs for a specific node, select the node from the drop-down list.

Fields