This topic describes how to query the number of sessions in the current tenant and view the internal attributes of all network connections on ODP.
Query the number of sessions and session IDs for the current tenant
Note
This document uses ODP V4.2.3 as an example to describe the query method and output information.
Connect to OceanBase AI Database through ODP. The following example uses the
root@sysuser.obclient -h10.10.10.1 -P2883 -uroot@sys -p -Doceanbase -AUse the
SHOW PROCESSLISTstatement to query the number of sessions and session IDs for the current tenant.obclient> SHOW PROCESSLIST;The output varies depending on the value of the ODP parameter
client_session_id_version.Note
The
client_session_id_versionparameter specifies the computing logic for generating client session IDs. We recommend that you set the parameter to2to use the new computing logic, which ensures that the generated client session IDs are globally unique.When the ODP parameter
client_session_id_versionis set to2, ODP uses the new computing logic to generate globally unique client session IDs. In this case, the output displays session information on the OBServer node corresponding to the tenant. The query result is as follows:+------------+---------+---------------------+-----------+---------+------+--------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------------+---------+---------------------+-----------+---------+------+--------+------------------+ | 1 | root | 10.10.10.1:39512 | oceanbase | Query | 0 | ACTIVE | SHOW PROCESSLIST | | 3221501386 | proxyro | 10.10.10.1:37728 | oceanbase | Sleep | 13 | SLEEP | NULL | +------------+---------+---------------------+-----------+---------+------+--------+------------------+The meaning of each field is described in the following table:
FieldDescriptionId The ID of the session, which is the cs id.User The user to which the session belongs. Host The client IP address and port number that initiated the session. db The name of the database to which the session is currently connected. Command The type of command the session is currently executing. Time The time taken to execute the current command, in seconds. If a command is retried, the system resets and recalculates. State The current state of the session. Info The statement being executed by the session. When the ODP parameter
client_session_id_versionis set to1, ODP uses the original computing logic to generate client session IDs. In this case, the output displays session information on the ODP node. The query result is as follows:+------+--------+------+---------------------+-----------+-------------+-------------------+-------------------+-------+-------+ | Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | +------+--------+------+---------------------+-----------+-------------+-------------------+-------------------+-------+-------+ | 9 | sys | root | 10.10.10.1:17890 | oceanbase | 0 | 1 | MCS_ACTIVE_READER | 48243 | 48243 | +------+--------+------+---------------------+-----------+-------------+-------------------+-------------------+-------+-------+The meaning of each field is described in the following table:
FieldDescriptionId The idnumber assigned to each client session by ODP, that is, thecs id.Tenant Indicates the tenant to which the session belongs. User Indicates the user to whom the session belongs. Host Indicates the client IP address and port number that initiated the session. db Indicates the name of the database currently connected by the session. trans_count Indicates the number of transactions transmitted by ODP in the session. svr_session_count The number of sessions. state The status of the client session. Valid values: - MCS_INIT (initializing)
- MCS_ACTIVE_READER (active)
- MCS_KEEP_ALIVE (keep-alive)
- MCS_HALF_CLOSE (half-closed)
- MCS_CLOSED (closed)
tid The thread ID. pid The process ID.
View the internal attribute status of all network connections on ODP
Use the SHOW PROXYNET CONNECTION statement to view the internal attribute status of all network connections on ODP.
SHOW PROXYNET CONNECTION [thread_id [LIMIT xx]]
The parameters are described as follows:
If
thread_idis not specified, the internal attribute status of all network connections on ODP is displayed.If
thread_idis specified, the connection status on the specified thread is displayed. You can specify theLIMIT [offset,] rowsandLIMIT rows OFFSET offsetparameters, which are fully compatible with MySQL format. Whenrows == -1, all rows are displayed.
Here is an example:
obclient> SHOW PROXYNET CONNECTION\G
*************************** 1. row ***************************
thread_id: 0
connect_id: 1
socket_fd: 4
type: inner connected
src_ip: XXX.XXX.XXX.XXX
src_port: 43051
dst_ip: XXX.XXX.XXX.XXX
dst_port: 13203
virtual_ip: *Not IP address [0]*
virtual_port: 0
bind_style: any
read_enabled: 1
read_nbytes: 9223372036854775807
read_ndone: 0
write_enabled: 0
write_nbyte: 0
write_ndone: 0
alive_time(sec): 2864
activity_timeout_in(sec): 0
inactivity_timeout_in(sec): 28800
timeout_close_in(sec): 28791
last_error_no: 0
shutdown: 0
comments: not closed
*************************** 2. row ***************************
thread_id: 0
connect_id: 3
socket_fd: 24
type: accepted
src_ip: XXX.XXX.XXX.XXX
src_port: 48292
dst_ip: XXX.XXX.XXX.XXX
dst_port: 13205
virtual_ip: *Not IP address [0]*
virtual_port: 0
bind_style: any
read_enabled: 1
read_nbytes: 9223372036854775807
read_ndone: 0
write_enabled: 0
write_nbyte: 0
write_ndone: 0
alive_time(sec): 1214
activity_timeout_in(sec): 0
inactivity_timeout_in(sec): 30
timeout_close_in(sec): 29
last_error_no: 0
shutdown: 0
comments: not closed
*************************** 3. row ***************************
thread_id: 0
connect_id: 4
socket_fd: 28
type: connected
src_ip: XXX.XXX.XXX.XXX
src_port: 43154
dst_ip: XXX.XXX.XXX.XXX
dst_port: 13203
virtual_ip: *Not IP address [0]*
virtual_port: 0
bind_style: any
read_enabled: 1
read_nbytes: 9223372036854775807
read_ndone: 0
write_enabled: 0
write_nbyte: 0
write_ndone: 0
alive_time(sec): 1214
activity_timeout_in(sec): 0
inactivity_timeout_in(sec): 28800
timeout_close_in(sec): 28788
last_error_no: 0
shutdown: 0
comments: not closed
*************************** 4. row ***************************
thread_id: 1
connect_id: 5
socket_fd: 29
type: inner connected
src_ip: XXX.XXX.XXX.XXX
src_port: 43156
dst_ip: XXX.XXX.XXX.XXX
dst_port: 13203
virtual_ip: *Not IP address [0]*
virtual_port: 0
bind_style: any
read_enabled: 1
read_nbytes: 9223372036854775807
read_ndone: 0
write_enabled: 0
write_nbyte: 0
write_ndone: 0
alive_time(sec): 1194
activity_timeout_in(sec): 0
inactivity_timeout_in(sec): 28800
timeout_close_in(sec): 28791
last_error_no: 0
shutdown: 0
comments: not closed
*************************** 5. row ***************************
thread_id: 3
connect_id: 2
socket_fd: 25
type: inner connected
src_ip: XXX.XXX.XXX.XXX
src_port: 43052
dst_ip: XXX.XXX.XXX.XXX
dst_port: 13203
virtual_ip: *Not IP address [0]*
virtual_port: 0
bind_style: any
read_enabled: 1
read_nbytes: 9223372036854775807
read_ndone: 0
write_enabled: 0
write_nbyte: 0
write_ndone: 0
alive_time(sec): 2853
activity_timeout_in(sec): 0
inactivity_timeout_in(sec): 28800
timeout_close_in(sec): 28799
last_error_no: 0
shutdown: 0
comments: not closed
5 rows in set
obclient> SHOW PROXYNET CONNECTION 0\G
*************************** 1. row ***************************
thread_id: 0
connect_id: 1
socket_fd: 4
type: inner connected
src_ip: XXX.XXX.XXX.XXX
src_port: 43051
dst_ip: XXX.XXX.XXX.XXX
dst_port: 13203
virtual_ip: *Not IP address [0]*
virtual_port: 0
bind_style: any
read_enabled: 1
read_nbytes: 9223372036854775807
read_ndone: 0
write_enabled: 0
write_nbyte: 11958
write_ndone: 11958
alive_time(sec): 2919
activity_timeout_in(sec): 0
inactivity_timeout_in(sec): 30
timeout_close_in(sec): 30
last_error_no: 0
shutdown: 0
comments: not closed
*************************** 2. row ***************************
thread_id: 0
connect_id: 3
socket_fd: 24
type: accepted
src_ip: XXX.XXX.XXX.XXX
src_port: 48292
dst_ip: XXX.XXX.XXX.XXX
dst_port: 13205
virtual_ip: *Not IP address [0]*
virtual_port: 0
bind_style: any
read_enabled: 1
read_nbytes: 9223372036854775807
read_ndone: 0
write_enabled: 0
write_nbyte: 0
write_ndone: 0
alive_time(sec): 1269
activity_timeout_in(sec): 0
inactivity_timeout_in(sec): 30
timeout_close_in(sec): 29
last_error_no: 0
shutdown: 0
comments: not closed
*************************** 3. row ***************************
thread_id: 0
connect_id: 4
socket_fd: 28
type: connected
src_ip: XXX.XXX.XXX.XXX
src_port: 43154
dst_ip: XXX.XXX.XXX.XXX
dst_port: 13203
virtual_ip: *Not IP address [0]*
virtual_port: 0
bind_style: any
read_enabled: 1
read_nbytes: 9223372036854775807
read_ndone: 0
write_enabled: 0
write_nbyte: 0
write_ndone: 0
alive_time(sec): 1269
activity_timeout_in(sec): 0
inactivity_timeout_in(sec): 28800
timeout_close_in(sec): 28733
last_error_no: 0
shutdown: 0
comments: not closed
3 rows in set
obclient> SHOW PROXYNET CONNECTION 0 limit 2 offset 1\G
*************************** 1. row ***************************
thread_id: 0
connect_id: 3
socket_fd: 24
type: accepted
src_ip: XXX.XXX.XXX.XXX
src_port: 48292
dst_ip: XXX.XXX.XXX.XXX
dst_port: 13205
virtual_ip: *Not IP address [0]*
virtual_port: 0
bind_style: any
read_enabled: 1
read_nbytes: 9223372036854775807
read_ndone: 0
write_enabled: 0
write_nbyte: 0
write_ndone: 0
alive_time(sec): 1299
activity_timeout_in(sec): 0
inactivity_timeout_in(sec): 30
timeout_close_in(sec): 29
last_error_no: 0
shutdown: 0
comments: not closed
*************************** 2. row ***************************
thread_id: 0
connect_id: 4
socket_fd: 28
type: connected
src_ip: XXX.XXX.XXX.XXX
src_port: 43154
dst_ip: XXX.XXX.XXX.XXX
dst_port: 13203
virtual_ip: *Not IP address [0]*
virtual_port: 0
bind_style: any
read_enabled: 1
read_nbytes: 9223372036854775807
read_ndone: 0
write_enabled: 0
write_nbyte: 0
write_ndone: 0
alive_time(sec): 1299
activity_timeout_in(sec): 0
inactivity_timeout_in(sec): 28800
timeout_close_in(sec): 28703
last_error_no: 0
shutdown: 0
comments: not closed
2 rows in set
The meaning of each field is described in the following table:
Field |
Description |
|---|---|
| thread_id | The thread ID. |
| connect_id | The network connection ID. |
| socket_fd | The socket descriptor. |
| type | The connection status. |
| src_ip | The source IP address. |
| src_port | The source port number. |
| dst_ip | The destination IP address. |
| dst_port | The destination port number. |
| virtual_ip | The virtual IP address. |
| virtual_port | The virtual port number. |
| bind_style | The binding type. |
| read_enabled | Indicates whether the connection supports read operations. |
| read_nbytes | The number of bytes to be read. |
| read_ndone | The number of bytes that have been read. |
| write_enabled | Indicates whether the connection supports write operations. |
| write_nbyte | The number of bytes to be sent. |
| write_ndone | The number of bytes that have been sent. |
| alive_time(sec) | The duration for which the connection has been alive. |
| activity_timeout_in(sec) | The timeout period of the active state. |
| inactivity_timeout_in(sec) | The timeout period of the inactive state. |
| timeout_close_in(sec) | The timeout period after which the connection is closed. |
| last_error_no | The last returned error code. |
| shutdown | The number of closed network connections. |
| comments | The comments. |