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 that allows you to view the session information about a database instance and perform O&M operations on sessions. For example, you can terminate a session and perform 10-second SQL analysis, SQL throttling, and SQL optimization on the database instance.
Prerequisites
Your database instance is connected to DAS and is in the Normal Access state.
Note DAS does not provide the session management feature for ApsaraDB RDS for SQL Server instances.
Procedure
In this topic, an ApsaraDB RDS for MySQL instance is used to show how to use the session management feature.
Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side pane, click Instance Sessions.
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 exceptional sessions, active sessions, the longest execution duration, CPU utilization, and connection usage.
Click 10s SQL Analysis in the upper-right corner of the section. In the dialog box that appears, view the information about queries within 10 seconds, such as summary information, 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 to optimize sessions. For more information, see SQL optimization.
Export active sessions.
Terminate sessions.
To terminate a session, you must enter the account and password of the database in which the session is created. You can also use a database account that has the permissions to terminate the sessions that are created by using other database accounts. For example, you can use a privileged account.
Note In the User column of a session, you can view the database account that is used to create the session.
You can click End Session History to view the records of terminated sessions.
You can perform the following operations in the Session Statistics section:
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 ongoing sessions, and the longest session duration.
Export summary information and session statistics by user, access source, or database.
FAQ
Q: Why does a percent sign (%) appear in the Access Source column?
A: When you use a stored procedure, a percent sign (%) may appear in the Access Source column on the Source Statistics tab of the SQL Explorer tab. You can perform the following operations to reproduce this situation:
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.
Create a database and a standard account and grant permissions on the database to the standard account in the ApsaraDB RDS console. For more information, see Create accounts and databases.
Use the test_user account to connect to the 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 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;
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 for ApsaraDB RDS for MySQL instances and PolarDB for MySQL clusters?
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 the FLUSH TABLES WITH READ LOCK statement and DDL statements waiting for metadata locks due to pending transactions.
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 at the earliest opportunity.