Physical connections|V4.3.3| docs|Distributed Database

Physical connections

Last Updated：2024-11-11 07:37:14 Updated

This topic describes how to query the number of sessions in the current tenant and view the internal attributes of all network connections on an OceanBase Database Proxy (ODP).

Query the number of sessions in the current tenant and the session IDs

Note

This topic takes OceanBase Database V4.3.0 and ODP V4.2.3 as an example to describe the query methods and output information.

Connect to OceanBase Database by using ODP. In this example, the root@sys user is used.
```
obclient -h10.10.10.1 -P2883 -uroot@sys -p -Doceanbase -A
```

Execute the SHOW PROCESSLIST statement to query the number of sessions of the current tenant and the session IDs.

obclient> SHOW PROCESSLIST;

The result of the statement varies with the value of client_session_id_version.

Note

The client_session_id_version parameter specifies the computing logic for generating client session IDs. We recommend that you set the parameter to 2. This value indicates using the new computing logic, which ensures that the generated client session IDs are globally unique.

When the client_session_id_version parameter is set to 2 on the ODP, the ODP uses the new computing logic to generate globally unique client session IDs. In this case, the result of the statement shows the information about the sessions on the OBServer node of the corresponding tenant. A sample 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 following table describes the fields in the returned result.

Field	Description
Id	The ID of the session, which is equivalent to the `cs id` field.
User	The user to which the session belongs.
Host	The IP address and port number of the client initiating the session.
db	The database to which the session is currently connected. In Oracle mode, it is the schema name that is the same as the username.
Command	The type of command that the session is executing.
Time	The execution duration of the current command, in seconds. If the command is retried, the system resets and recalculates the execution duration.
State	The current status of the session.
Info	The statement that the session is executing.

When the client_session_id_version parameter is set to 1 on the ODP, the ODP uses the original computing logic to generate client session IDs. The information about the corresponding ODP is displayed in the query result. A sample 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 following table describes the fields in the returned result.

Field	Description
Id	The ID of the client session on the ODP. This parameter is equivalent to the `cs_id` parameter.
Tenant	The tenant to which the session belongs.
User	The user to which the session belongs.
Host	The IP address and port number of the client initiating the session.
db	The database to which the session is currently connected. In Oracle mode, it is the schema name that is the same as the username.
trans_count	The number of transactions transmitted by the ODP in the session.
svr_session_count	The number of sessions.
state	The status of the session. Valid values: `MCS_INIT`: being initialized `MCS_ACTIVE_READER`: active `MCS_KEEP_ALIVE`: alive `MCS_HALF_CLOSE`: half-closed `MCS_CLOSED`: closed
tid	The thread ID.
pid	The process ID.

View the internal attributes of all network connections on the ODP

Execute the SHOW PROXYNET CONNECTION statement to query the detailed internal attributes of network connections on the ODP.

SHOW PROXYNET CONNECTION [thread_id [LIMIT xx]]

where

If thread_id is not specified, the detailed internal attributes of all network connections on ODP are returned.
If thread_id is specified, the detailed internal attributes of network connections on the specified thread are returned. You can also specify LIMIT [offset,] rows or LIMIT rows OFFSET offset. The formats of these two parameters are fully compatible with those of MySQL. If you specify rows == -1, all rows are returned.

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 following table describes the fields in the returned result.

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 when the connection is 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.