All Products
Search

This Product

    Database Autonomy Service:Transaction analysis

    Document Center

    Database Autonomy Service:Transaction analysis

    Last Updated:Aug 08, 2024

    You can use the transaction analysis feature of Database Autonomy Service (DAS) to obtain the transaction type, number of transactions, and transaction details of a thread within the specified period. This allows you to understand, analyze, and optimize database performance at the transaction level.

    Feature description

    Based on the data of DAS Enterprise Edition V3 stored in hot storage mode, the transaction analysis feature of DAS analyzes the transaction details of a specific thread within the specified period, collects statistics, and generates a chart that shows the trends in the quantity of transactions of different types.

    Note

    Some special transactions may be of different types at the same time. For example, a transaction can be an implicitly submitted transaction and a long transaction at the same time.

    The following types of transactions are supported by the transaction analysis feature:

    • Normal transaction.

    • Implicitly submitted transaction.

    • Large transaction: a transaction that updates more than 10,000 rows of data.

    • Long transaction: a transaction that lasts more than 10 minutes.

    • Transaction with a long pause: a transaction that is paused for more than 1 minute.

    • Transactions with excessive statements: a transaction that contains more than 100 SQL statements. Only SQL statements that involve updates are counted, including INSERT, UPDATE, DELETE, MERGE, CREATE, ALTER, DROP, SET, REPLACE, and RENAME.

    • Boundary transaction during the analysis period: a transaction that does not contain a transaction end statement during the specified analysis period.

    Prerequisites

    • Your database instance runs one of the following database engines:

      • ApsaraDB RDS for MySQL

      • PolarDB for MySQL

    • The database instance is connected to DAS and is in the Accessed state.

    • The version and region of the database instance support DAS Enterprise Edition V3. For more information, see Editions and supported features.

    • DAS Enterprise Edition V3 is enabled for the database instance. For more information, see Overview of DAS Enterprise Edition.

    Scenarios

    • Analyze the transactions that cause deadlocks to effectively resolve deadlocks and improve database performance based on the deadlock analysis information provided by the lock analysis feature.

    • Analyze the transactions that cause transaction blocking to help resolve and mitigate the lock wait problem based on the transaction blocking analysis information provided by the lock analysis feature.

    • Analyze the transaction information of the thread to which a SQL statement belongs based on audit logs.

    Limits

    • You can use the transaction analysis feature to analyze only the SQL data that is stored in hot storage of DAS Enterprise Edition V3.

      Note

      • To modify the storage period of such data, click Service Settings in the upper-right corner of the SQL Explorer and Audit page.

      • For information about the fees incurred by hot storage, see Billing details.

    • Only one running transaction analysis task is allowed for a database instance.

    • A transaction analysis task supports up to two threads for transaction analysis.

    • A transaction analysis task supports up to 30 minutes of data for transaction analysis.

    Procedure

    1. Log on to the DAS console.

    2. In the left-side navigation pane, click Instance Monitoring.

    3. On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.

    4. In the left-side navigation pane, choose Request Analysis > SQL Explorer and Audit. On the page that appears, click the SQL Explorer tab. Then, click the Transaction Analysis tab.

      • Create a transaction analysis task

        Click Create Analysis Task. In the Create Analysis Task dialog box, select a period, and specify a thread ID to create a transaction analysis task. You can view the created transaction analysis tasks in the transaction analysis task list.

        image

        Note

        You must specify a valid thread ID. You can obtain thread IDs from the pages for the lock analysis, audit log, and instance session features.

      • View the transaction analysis results

        You can view the transaction analysis results within the specified period.

        • In the transaction analysis task list, find the transaction analysis task that you want to view and click View Details in the Actions column. A trend chart is displayed, which involves different types of transactions and shows the trends in the quantity of transactions of different types.

        • In the Transaction Overview section, click a bar to view the statistics on the transactions within the specified minute. You can also click View Details in the Transaction Details column to view the details about the SQL statements involved in the transactions.

        Important

        Only the SQL data stored in hot storage within the specified period can be queried.