Managing Real-Time Sessions

Scenarios

In database management, if system performance becomes abnormal, administrators need to quickly locate the issue. TaurusDB allows you to set a slow session threshold, view session statistics, and identify abnormal sessions on the Sessions page. You can manually kill sessions, intelligently kill sessions based on rules, or perform root cause analysis on sessions to identify blocked sessions, thereby improving system performance and management efficiency.

Prerequisites

To use root cause analysis, ensure that the performance_schema parameter is set to ON. For details, see Modifying Parameters of a DB Instance. Once the parameter is enabled, root cause analysis is available in the session list.

Constraints

After modifying performance_schema, you must reboot the instance. The reboot will terminate all current sessions. Therefore, the system can diagnose only blocked sessions that are generated after root cause analysis is enabled. Blocked sessions (sessions that cannot continue to execute due to lock waiting) that existed before the feature was enabled cannot be analyzed.

Differences Between Manual, Intelligent, and Automatic Session Kill

Setting a Slow Session Threshold

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, click the target TaurusDB instance name to go to the Basic Information page.
4. In the navigation pane, choose Sessions under DBA Assistant.
5. In the session statistics area, you can view statistics on slow sessions, active sessions, total sessions, long transaction sessions by user, access host, and database.
   Figure 1 Real-time sessions
   

6. Click next to the Slow Session Threshold field. In the displayed dialog box, set a slow session threshold and click OK. Sessions whose execution durations exceed this threshold are automatically displayed.
   Figure 2 Setting a slow session threshold
   

7. In the session list, view session details.

Managing Sessions

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, click the target TaurusDB instance name to go to the Basic Information page.
4. In the navigation pane, choose Sessions under DBA Assistant.
5. On the Sessions tab page, you can perform the following operations:
   • Manual session kill

     To end a single session, click Kill Session in the Operation column to restore the database.

     To end multiple sessions, select up to 20 sessions and click Kill Session above the list.

     Figure 3 Manually killing sessions
     

   • Root cause analysis

     Click Root Cause Analysis in the Operation column. The displayed dialog box shows details about blocked sessions and their root cause SQL statements.

     If the command for the blocking session is Sleep, the system traces back to the performance_schema.events_statements_history table and displays the historical SQL statements. Otherwise, it displays the SQL statement that is blocking the session.

     Figure 4 Root cause analysis
     
     Figure 5 Root cause analysis result
     

Intelligent Session Kill

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, click the target TaurusDB instance name to go to the Basic Information page.
4. In the navigation pane, choose Sessions under DBA Assistant.
5. Above the session list, click Kill Top 3. In the displayed dialog box, click Show killed sessions to view the IDs, SQL keywords, and other information of the sessions that meet the kill criteria at the current moment. The preview is not the final result and it only reflects the statistics at the current time.

   Intelligent session kill rules are as follows:

   The system retrieves the top 1,000 sessions by execution duration in descending order and filters for SELECT statements only. It extracts keywords from each session and groups sessions with identical keywords. It evaluates whether each group meets the top3_time, top3_count, and top3_avg_time rules. The top three groups that meet the rules are selected and the sessions in these groups are automatically killed.

   • top3_time: Sessions in the top three groups with the longest execution duration will be killed.
   • top3_count: Sessions in the top three groups with the largest session count will be killed.
   • top3_avg_time: Sessions in the top three groups with the longest average execution duration will be killed.

   All sessions in a group will be killed at a time. If a session meets multiple rules (top3_time, top3_count, and top3_avg_time), it will only be killed once.

   Figure 6 Kill Top 3
   

6. Confirm the information and click OK. The system then kills the sessions that meet the rules.
7. To view the intelligent session kill history, click Killed Top 3 Sessions. You can view the sessions that meet the kill rules and have been killed.
   You can query up to 30 days of intelligent session kill history. Data older than 30 days are automatically cleared.

   Figure 7 Killed Top 3 Sessions
   

   To download a history record, click Download in the Operation column. The downloaded package contains three files: killed_sessions_xxxx, rules_xxxx, and sessions_xxxx.

   • killed_sessions_xxxx stores information about killed sessions.
   • rules_xxxx stores intelligent session kill rules.
   • sessions_xxxx stores information about the top 1,000 sessions, filtered for SELECT statements and sorted by execution duration in descending order.

Exporting Session Information

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, click the target TaurusDB instance name to go to the Basic Information page.
4. In the navigation pane, choose Sessions under DBA Assistant.
5. In the session list, export some or all sessions.
   Figure 8 Exporting session information
   
   • Exporting some sessions

     Select sessions and click Export Selected above the list to export session information to a table. After the export is complete, a CSV file is generated on the local PC.

   • Exporting all sessions

     Choose Export All > Collect above the list. After all session information is collected, choose Export All > Download to export all the information to a table. After the export is complete, a CSV file is generated on the local PC.

     A maximum of 1,000,000 sessions can be exported at a time.