View the sessions on an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

When performance issues or operation exceptions occur on a database, you can troubleshoot the issues or exceptions based on the session information on the database. Database Autonomy Service (DAS) provides the session management feature for ApsaraDB RDS for MySQL instances. The feature allows you to view information such as the active sessions, abnormal sessions, access sources, and SQL statements about your database instance in real time. You can also perform O&M operations such as terminating sessions and performing SQL throttling, SQL diagnostics, and SQL optimization on your database instance.

Prerequisites

Your RDS instance runs one of the following MySQL versions and RDS editions:

MySQL 8.0 on RDS High-availability Edition, RDS Enterprise Edition, or RDS Cluster Edition
MySQL 5.7 on RDS High-availability Edition, RDS Enterprise Edition, or RDS Cluster Edition
MySQL 5.6 on RDS High-availability Edition
MySQL 5.5 on RDS High-availability Edition

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, choose Autonomy Services > Diagnostics.
Click the Session Management tab.
On the Session Management tab, view the session information about the database instance in the Instance Sessions and Session Statistics sections.
- In the Instance Sessions section, you can perform the following operations:
  - View information such as exceptions, active sessions, the longest execution duration, CPU utilization, and connection usage.
  - In the upper-right corner of the section, click 10s SQL Analysis. In the dialog box that appears, view the information about SQL statements that are executed within a 10-second time window, such as SQL statistics, slow query logs, and SQL overview. For more information, see 10-second SQL analysis.
  - Click SQL Throttling. In the SQL Throttling dialog box, configure the parameters to enable threshold-based SQL throttling on sessions. For more information, see SQL throttling.
  - Click Optimize. In the SQL Diagnostic Optimization dialog box, view the SQL diagnostic results.
    If you accept the SQL optimization suggestions, click Copy and paste the optimized SQL statement to the database client or Data Management (DMS) for execution. If you do not accept the SQL optimization suggestions, click Cancel.
    Note
    DAS performs SQL diagnostics based on the level of complexity of SQL statements, the amount of data in the table, and the database load. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostic engine provides diagnostic results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the provided information.
  - Export active sessions.
  - Terminate sessions.
    When you terminate the sessions, you must enter the username and password of the database account that is used to establish the sessions. You can also use a database account that has the permissions to terminate sessions established by other database accounts. For example, you can use a privileged account.
    Note
    In the User column of the session list, you can view the database account that is used to establish the sessions.
    You can click End Session History to view the records of terminated sessions.
- In the Session Statistics section, you can perform the following operations:
  - View summary information and session statistics by user, access source, or database. The summary information includes the total number of sessions, the total number of running sessions, and the longest session duration.
  - Export summary information and session statistics by user, access source, or database.
- You can click Enable Automatic Throttling to enable the automatic SQL throttling feature. After you enable the feature and the conditions that you configure for the feature are met, automatic SQL throttling is triggered to manage the number of access requests and the number of concurrent SQL requests on your database instance. This helps ensure the availability of your database service. For more information, see Automatic SQL throttling.

FAQ

Why does a percent sign (%) appear in the Access Source column?

When you use a stored procedure, a percent sign (%) may be displayed in the Access Source column. To reproduce this scenario, perform the following steps:

Note

In this example, the database instance is an ApsaraDB RDS for MySQL instance, the test account is test_user, and the test database is testdb.

In the ApsaraDB RDS console, create a database and a standard account and grant permissions on the database to the standard account. For more information, see Create accounts and databases.
Use the test_user account to connect to the database instance by using the CLI. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

Switch to the testdb database and create a stored procedure by executing the following statements:

-- Switch to the testdb database.
USE testdb;

-- Create a stored procedure.
DELIMITER $$
DROP PROCEDURE IF EXISTS `das` $$
CREATE DEFINER=`test_user`@`%` PROCEDURE `das`()
BEGIN
SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID();
END $$
DELIMITER;

Use a privileged account to connect to the database instance. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

Call the stored procedure that you created.

-- Switch to the testdb database.
USE testdb;

-- Call the stored procedure.
CALL das();

+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+
| ID     | USER      | HOST   | DB     | COMMAND | TIME | STATE     | INFO                                                                    |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+
| 487818 | test_user | %:2065 | testdb | Query   |    0 | executing | SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID() |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+

Q: What sessions are marked as abnormal?

A: The following sessions are marked as abnormal:

Sessions that contain blocking SQL statements such as a session that reports a "Waiting for table metadata lock" error. The execution duration of blocking SQL statements exceeds 30 seconds. Blocking SQL statements occupy resources for an extended period of time. As a result, other SQL statements may fail to be executed. Common blocking SQL statements include DDL statements and the FLUSH TABLES WITH READ LOCK statement.
Sessions that contain transactions whose duration exceeds 30 seconds.
Sessions that contain transactions that have not been committed for an extended period of time. If a transaction is started in a session but no new SQL statements are executed for more than 10 seconds, it is likely that the COMMIT command is omitted from the code. This may cause the transaction to occupy resources for an extended period of time and fail to release resources in a timely manner.

References

You can enable the autonomy features of DAS. DAS can automatically diagnose and optimize the SQL statements in the sessions on your database instance.