Managing Real-Time Sessions

Scenarios

You can query session snapshots of your instance while sorting, filtering, and displaying the snapshots as needed. You can also view session statistics by user, source, and database.

Sessions can be killed for urgent instance recovery to ensure database availability. If the maximum number of connections for an instance has been reached and the instance cannot be logged in to, you can view and kill unnecessary sessions.

Precautions

Killing a session may cause the application to disconnect from the instance. Your application should be able to reconnect to the instance.

Constraints

• Do not kill sessions unless you really need to. All your kill operations will be logged.
• Sessions cannot be killed and SQL throttling rules cannot be configured for sensitive users such as rdsAdmin, rdsBackup, rdsMetric, rdsRepl, and null.
• If your instance is overloaded, refreshing the session list may fail. Use as few resources as possible for real-time sessions. Wait a few seconds and try again.
• If the CPU is fully occupied, requests to kill sessions may fail. You may have to try more than once.

Procedure

1. Log in to the RDS console.
2. Click in the upper left corner and select a region.
3. On the Instances page, click the target instance name to go to the Summary page.
4. In the navigation pane, choose Sessions under DBA Assistant.
5. On the Sessions page, perform the following operations:
   • Viewing session statistics
     In the session statistics area, you can view the session summary and session statistics by user, source, and database.

     Figure 1 Sessions
     
   • Killing all sessions

     Above the session list, click Kill All Sessions. In the displayed dialog box, select a database or a user (except built-in users), or select both a database and a user, to kill all sessions that meet the conditions.

     After sessions are killed, you can view the operation history of these sessions. For details, see Viewing Killed Sessions.

     Figure 2 Killing all sessions
     
   • Kill Top 3

     In the session list, click Kill Top 3. The instance ends sessions based on the following rules.

     • To use SQL throttling, install rds_pg_sql_ccl first. It is a self-developed kernel extension provided by RDS for PostgreSQL.
     • SQL statements executed by built-in users (rdsAdmin, rdsMetric, rdsRepl, and rdsBackup) are not affected by SQL throttling rules.
     • SQL throttling supports DB engine versions 17.4, 16.2, 15.4, 14.8, 13.11, 12.15, 11.20, and later.
     • You can both enable SQL throttling and add rules only for PostgreSQL 14 or later.
     • Session killing rules:
       • top3_exec_time: Terminate the top 3 SQL statements with the longest execution time.
       • top3_tran_time: Terminate the top 3 SQL statements with the longest transaction execution time.
       • top3_count: Terminate the top 3 SQL statements with the highest concurrency. Statements with fewer than 3 concurrent executions will not be terminated.

     After you toggle on the Enable SQL Throttling and Add Rule switch, the system will add throttling rules for the top 3 SQL statements with the longest execution time, the top 3 SQL statements with the longest transaction execution time, and the top 3 SQL statements with the highest concurrency and set Max. Concurrent Requests to 10 and Max. Wait Time (s) to 0. If a SQL statement has been throttled, throttling rules will not apply to it again. After sessions are killed, the system removes these throttling rules automatically.

     After Kill Top 3 is configured, you can view the sessions that have been killed by this function. For details, see Viewing Killed Sessions.

     Figure 3 Kill Top 3
     
   • Killing abnormal sessions

     In the session list, select the abnormal sessions you want to end and click Kill Session to recover databases.

     After sessions are killed, you can view the operation history of sessions that have been killed manually. For details, see Viewing Killed Sessions.

   • Configuring SQL throttling

     In the session list, click SQL Throttling and set the SQL type and keyword to match SQL statements. When the number of matched SQL statements exceeds the configured upper limit, the DB instance will refuse to execute the SQL statements. This prevents instance instability issues caused by excessive concurrent SQL statements.

     For details, see Creating a SQL Throttling Rule.

   • Viewing the CPU/memory usage of databases

     In the session list, click Statistics By Database to view the CPU/memory usage of databases in the instance.

Viewing Killed Sessions

• Viewing sessions killed manually

  On the Sessions page, click the Killed Sessions tab and select the Sessions Manually Killed option to view the operation history of manually killed sessions. You can also export the history.

  Figure 4 Sessions Manually Killed
  
• Viewing sessions killed by the Kill Top 3 function

  On the Sessions page, click the Killed Sessions tab and select the Killed Top 3 Sessions option to view the operation history of sessions killed by the Kill Top 3 function. You can also export the history.

  Figure 5 Killed Top 3 Sessions