Investigation of session_id source and bulk tenant connection termination in OceanBase via observer and obproxy

Background : After connecting to OceanBase (OB) through obproxy , it was observed that the kill command succeeds when using IDs from show processlist , but fails when using IDs from information_schema.processlist or the virtual table oceanbase.__all_virtual_processlist .

Test objectives :

Identify the source of the session_id used by the kill statement.

Determine whether all connections of a tenant can be terminated in a single operation.

Login commands used for the tests :

Observer:

mysql -uroot@sys -p -P2881 -h ${oberver_ip} -c -A oceanbase

Obproxy:

mysql -uroot@sys#yjn_test -p -P2883 -h ${obproxy_ip} -c -A oceanbase

Test cases :

1. Connect to an observer node and run:

show processlist ;
select * from information_schema.processlist ;
select id,user,host,db,command,time,state,info from oceanbase.__all_virtual_processlist ;

Result: All three statements return the same ID, confirming that the session ID can be obtained via any of the three methods when connected directly to an observer.

2. Connect to an obproxy node and run the same three statements.

show processlist ;
select * from information_schema.processlist ;
select id,user,host,db,command,time,state,info from oceanbase.__all_virtual_processlist ;

Result: IDs from information_schema.processlist and oceanbase.__all_virtual_processlist match, but the IDs shown by show processlist differ; the kill command uses the IDs from show processlist , which represent client‑to‑obproxy connections.

3. Log into different observer nodes within the same cluster.

Result: A session logged in on one observer can see sessions from other observers.

4. Log into different obproxy instances (all connecting to the same OB).

Result: Sessions visible on one obproxy cannot see sessions on another; each obproxy maintains independent connection information.

Test summary :

The view information_schema.processlist derives its data from the table oceanbase.__all_virtual_processlist (verified with show create table information_schema.processlist \G ).

The table oceanbase.__all_virtual_processlist records every connection to OB, regardless of whether the client connects directly to an observer or via an obproxy.

show processlist displays connections to the software component the client is directly connected to; when using obproxy, it shows connections to the obproxy, not to the underlying OB.

When connecting directly to an observer, show processlist shows connections to OB itself.

Obproxy acts as a client of the observer, so connections observed through different obproxies are isolated from each other.

Q&A

Question 1: Where does the session_id used in kill come from?

Answer 1: The kill command targets a session or the SQL statements belonging to that session. The session_id can be obtained either from show processlist (front‑side connections) or from oceanbase.__all_virtual_processlist (back‑side connections). Any client that can query these views can retrieve the appropriate ID for the kill operation.

Question 2: Can all connections of a tenant be terminated at once?

Answer 2: Front‑side connections visible via show processlist are limited to the current client’s view, so they cannot list connections from other obproxies. By connecting directly to an observer, you can generate kill statements for all sessions belonging to a tenant with the following SQL:

select concat('kill ',id,';') from oceanbase.__all_virtual_processlist where tenant='tenant_ys' ;

This command produces a series of kill statements that, when executed, terminate every connection of the specified tenant.