Session management allows you to view tenant sessions, perform session statistics, and detect deadlocks.
Background information
The connection between an application and a database is called a session. On the Session Management tab, you can view the Total Sessions, Active Sessions, and Idle Sessions.
Tenant sessions
Note
- An agent session is recorded on the database proxy server.
- A database session is recorded on an OceanBase server and identifies a unique database connection on the server.
- The Tenant Sessions tab displays only the sessions at the current time. One agent session corresponds to multiple database sessions, but only one database session is displayed. If there are active database sessions, they are displayed. Otherwise, a database session is displayed randomly.
Procedure
Log in to the OceanBase Cloud console.
In the left-side navigation pane, click Instances.
In the instance list, find the target instance, click the instance name, and go to the Overview page of the instance.
Click Diagnostics in the left-side navigation pane, and then select the Real-Time Diagnostics tab on the Diagnostics page.
Click the Session Management tab at the bottom of the page.
On the Session Management tab, click Tenant Session. You can search for sessions by proxy session ID, database session ID, SQL ID, or SQL statement.
Select View Only Active Sessions to view the information about active sessions in the session list. You can select multiple active sessions to set throttling limits in bulk.
Select Aggregate Sessions by SQL ID to summarize sessions with the same SQL ID.
Note
Aggregating sessions by SQL ID helps you analyze the traffic and consumption of different SQL IDs to identify the resource consumers.
Select Show Sessions by Aggregation in SQL to display sessions based on IN SQL statements without affecting the statistics. You can select multiple active sessions to set throttling limits in bulk.
Note
Aggregating sessions by IN SQL means ignoring the number of constant parameters in the IN clause of SQL statements. Instead, each IN clause is treated as having only one constant parameter. Based on this rule, SQL statements are parameterized, and SQL IDs are calculated. SQL statements with the same SQL ID belong to the same category and are aggregated.
The following table describes the columns in the tenant session list.
Column Description Proxy Session ID The session ID recorded by the database proxy server. Database Session ID The session ID recorded by the OceanBase server. This ID identifies a unique database connection on the OceanBase server. SQL ID The ID of the SQL statement that is being executed in the session. This ID uniquely identifies the SQL statement. SQL Text The content of the SQL statement that is being executed in the session. User The database username used to log in to the session. Source The address from which the session is initiated. It is usually the IP address of the client. Destination The address to which the session is sent. Database Node The database node that the session accesses. Database The database that the session accesses. Command The type of command being executed in the session. Execution Time (s) The time, in seconds, consumed by the SQL statement that is being executed in the session. You can sort sessions by Execution Time (s). CPU Time (s) The time, in seconds, that the CPU is occupied by the SQL statement that is being executed in the session. You can sort sessions by CPU Time (s). Status The status of the session. Valid values: CLOSED (closed) and ACTIVE (active). You can click Close Session in the action column of a session to delete the session. You can also select multiple sessions and click Close sessions in batches.
Note
After a session is closed, you can click View Optimization Records > Optimization History in the upper-right corner of the Session Management tab to view the task status.
Session statistics
On the Session Statistics tab, you can view or export the number of active sessions and the total number of sessions in the current cluster from the user, access source, and database dimensions.
Click 10s SQL Analysis in the upper-right corner to statistics SQL execution in the tenant within 10 seconds. You can view the statistics based on SQL type, User, Source, and Database.
Deadlock detection
OceanBase Database uses multi-version two-phase locking to maintain the correctness of its concurrency control model. The locking mechanism is crucial for ensuring data concurrency and consistency.
However, the locking mechanism can also lead to deadlocks, which occur when there is a cyclic dependency on resources. For example, if transaction A and transaction B both attempt to acquire resources 1 and 2, transaction A might first acquire resource 1 and then try to acquire resource 2, while transaction B might first acquire resource 2 and then try to acquire resource 1. In such a case, if no transaction is willing to release the resources it has acquired, no transaction can proceed and end normally.
Note
Deadlock detection is supported in OceanBase Database V3.2 and later.
Procedure
Note
After deadlock detection is enabled, OceanBase Database will real-time detect deadlock events and handle them, which will consume about 2% of performance. Please decide whether to enable it based on your actual situation.
For the first time you use deadlock detection, click Enable Automatic Deadlock Detection.
After deadlock detection is enabled, the system will diagnose whether there are deadlocks in the tenants of the cluster and display the diagnosis results on the Deadlock Details page, keeping the results for the past 7 days.
You can click Disable Automatic Deadlock Detection in the upper-right corner or refresh the page to stop automatic deadlock detection or view the deadlock detection details.