Database Autonomy Service (DAS) provides the session management feature for ApsaraDB RDS for MySQL. This feature allows you to view and export the statistics of the sessions on your ApsaraDB RDS for MySQL instance and terminate the sessions on your RDS instance. This feature also allows you to analyze SQL statements that are executed within a 10-second time window on your RDS instance and throttle and optimize SQL statements.

Prerequisites

Your RDS instance runs one of the following MySQL versions and RDS editions:
  • MySQL 8.0 on RDS High-availability Edition or RDS Enterprise Edition
  • MySQL 5.7 on RDS High-availability Edition or RDS Enterprise Edition
  • MySQL 5.6 on RDS High-availability Edition
  • MySQL 5.5 on RDS High-availability Edition

Procedure

  1. Access RDS Instances, select a region at the top, and then click the ID of the target RDS instance.
  2. In the left-side navigation pane, choose Autonomy Service > Diagnostics.
  3. Click the Session Management tab.
  4. On the Session Management tab, view the session information about the RDS instance in the Instance Sessions and Session Statistics sections. Session Management
    • 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 in the upper-right corner and paste the optimized SQL statements to the database client or Data Management (DMS) for execution. If you do not accept the suggestions, click OK.

        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.
        Note 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 runtime.
      • 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 control the number of access requests and the number of concurrent SQL queries on your RDS instance. This ensures 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, an ApsaraDB RDS for MySQL instance is used, the test account is named test_user, and the test database is named testdb.
  1. 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 databases and accounts for an ApsaraDB RDS for MySQL instance.
  2. Use the test_user account to connect to the RDS instance by using a CLI. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.
  3. Switch to the testdb database and execute the following statements to create a stored procedure: 
    -- 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;
  4. Use a privileged account to connect to the RDS instance. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.
  5. 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() |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+