This topic describes how to query the internal metrics, such as the number of SQL request responses and size of an SQL request response, of a specified session on OceanBase Database Proxy (ODP).
Procedure
You can use the SHOW PROXYSESSION STAT statement in the sys tenant or a user tenant to query the internal metrics of a specified client session on ODP.
Connect to OceanBase Database by using ODP.
Here is an example:
obclient -h10.xx.xx.xx -uusername@obtenant#obdemo -P2883 -p****** -c -A oceanbaseFor more information about how to connect to OceanBase Database by using ODP, see Connect to a MySQL-compatible tenant of OceanBase Database by using OBClient and Connect to an Oracle-compatible tenant of OceanBase Database by using OBClient.
Query the internal metrics of the specified client session on ODP.
SHOW PROXYSESSION STAT id LIKE 'xx';The parameters are described as follows:
In the statement,
idcan be replaced withcs_idorconnection_id. These parameters are equivalentcs_idindicates the ID of a client session in ODP.connection_idindicates the ID of a client session in OceanBase Database.You can obtain the
connection_idvalue by using theSELECT CONNECTION_ID();statement in MySQL-compatible mode and theSHOW FULL PROCESSLIST;statement in Oracle-compatible mode.The
LIKEoption supports fuzzy matches. Percent signs (%) and underscores (_) are supported.
Here is an example:
MySQL-compatible modeOracle-compatible modeQuery
cs_idorconnection_id.Execute the following statement to query
cs_id:SHOW PROXYSESSION;In the following query result,
Idis equivalent tocs_id.+----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl | +----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | 12402504630519660562 | 11 | test420 | mysql001 | root | 100.xx.xx.xx:48056 | NULL | 0 | 1 | MCS_ACTIVE_READER | 76286 | 76286 | 0 | +----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ 1 row in setExecute the following statement to query
connection_id:SELECT CONNECTION_ID();The result is as follows:
+-----------------+ | CONNECTION_ID() | +-----------------+ | 3221597019 | +-----------------+ 1 row in setQuery the internal metrics.
SHOW PROXYSESSION STAT 11 LIKE '%time%';or
SHOW PROXYSESSION STAT 3221597019 LIKE '%time%';The return result is as follows:
+-------------------------------------------+-------+ | stat_name | value | +-------------------------------------------+-------+ | total_transactions_time | 0 | | total_user_transactions_time | 0 | | total_client_request_read_time | 0 | | total_client_response_write_time | 0 | | total_client_request_analyze_time | 0 | | total_client_transaction_idle_time | 0 | | total_ok_packet_trim_time | 0 | | total_server_process_request_time | 0 | | total_server_response_read_time | 0 | | total_server_response_analyze_time | 0 | | total_send_saved_login_time | 0 | | total_send_all_session_vars_time | 0 | | total_send_use_database_time | 0 | | total_send_changed_session_vars_time | 0 | | total_send_changed_session_user_vars_time | 0 | | total_send_last_insert_id_time | 0 | | total_send_start_trans_time | 0 | | total_pl_lookup_time | 0 | | total_congestion_control_time | 0 | | total_server_connect_time | 0 | +-------------------------------------------+-------+ 20 rows in set
Query
cs_idorconnection_id.Execute the following statement to query
cs_id:SHOW PROXYSESSION;In the following query result,
Idis equivalent tocs_id.+----------------------+-------+----------+-----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl | +----------------------+-------+----------+-----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | 12402504630519660558 | 65543 | test420 | oracle001 | sys | 100.xx.xx.xx:59648 | SYS | 0 | 1 | MCS_ACTIVE_READER | 76286 | 76286 | 0 | +----------------------+-------+----------+-----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ 1 row in setExecute the following statement to query
connection_id:SHOW FULL PROCESSLIST;In the following query result,
idis equivalent toconnection_id.+------------+------+-----------+---------------------+------+---------+------+--------+-----------------------+---------------+------+ | ID | USER | TENANT | HOST | DB | COMMAND | TIME | STATE | INFO | IP | PORT | +------------+------+-----------+---------------------+------+---------+------+--------+-----------------------+---------------+------+ | 3221552558 | SYS | oracle001 | 172.xx.xx.xx:45343 | SYS | Query | 0 | ACTIVE | SHOW FULL PROCESSLIST | 172.xx.xx.64 | 2881 | +------------+------+-----------+---------------------+------+---------+------+--------+-----------------------+---------------+------+ 1 row in setQuery the internal metrics.
SHOW PROXYSESSION STAT 65543 LIKE '%time%';or
SHOW PROXYSESSION STAT 3221552558 LIKE '%time%';The return result is as follows:
+-------------------------------------------+-------+ | stat_name | value | +-------------------------------------------+-------+ | total_transactions_time | 0 | | total_user_transactions_time | 0 | | total_client_request_read_time | 0 | | total_client_response_write_time | 0 | | total_client_request_analyze_time | 0 | | total_client_transaction_idle_time | 0 | | total_ok_packet_trim_time | 0 | | total_server_process_request_time | 0 | | total_server_response_read_time | 0 | | total_server_response_analyze_time | 0 | | total_send_saved_login_time | 0 | | total_send_all_session_vars_time | 0 | | total_send_use_database_time | 0 | | total_send_changed_session_vars_time | 0 | | total_send_changed_session_user_vars_time | 0 | | total_send_last_insert_id_time | 0 | | total_send_start_trans_time | 0 | | total_pl_lookup_time | 0 | | total_congestion_control_time | 0 | | total_server_connect_time | 0 | +-------------------------------------------+-------+ 20 rows in set
The following table describes the fields in the returned result.
Field Description stat_name The name of the internal metric. value The value of the internal metric. The following table describes the internal metrics.
Field Description total_transaction_count The total number of transactions. total_user_transaction_count The number of transactions executed on ODP. total_query_count The number of user requests executed. total_client_request_reread_count The number of user requests read by ODP. total_server_response_reread_count The number of times that ODP reads data from OBServer nodes. client_request_total_size The total size of data requested by the client. client_response_total_size The total size of data returned by the client. server_request_total_size The total size of the request data sent to OBServer nodes. server_response_total_size The total size of the response data returned by OBServer nodes. total_transactions_time The total amount of time consumed to execute transactions. total_user_transactions_time The total amount of time consumed to execute transactions. total_client_request_read_time The total amount of time consumed to read client requests. total_client_response_write_time The total amount of time consumed to return data to the client. total_client_request_analyze_time The total amount of time consumed to parse client packets. total_client_transaction_idle_time The total amount of idle time in executing transactions. total_server_process_request_time The total amount of time consumed by the database to process requests. total_server_response_read_time The total amount of time consumed to read data from OBServer nodes. total_server_response_analyze_time The total amount of time consumed by ODP to parse data returned from the server. total_send_saved_login_time The total amount of time consumed to send LOGIN packets to OBServer nodes. total_send_all_session_vars_time The total amount of time consumed to synchronize session variables to OBServer nodes. total_send_use_database_time The total amount of time consumed to send the use databasecommand to OBServer nodes.total_send_changed_session_vars_time The total amount of time consumed to send session variable modifications to OBServer nodes. total_send_last_insert_id_time The total amount of time consumed to synchronize values of last_insert_id to OBServer nodes. total_send_start_trans_time The total amount of time consumed for sending the request that starts transactions to OBServer nodes. total_pl_lookup_time The total amount of routing time. total_server_connect_time The total amount of time consumed for creating connections between the ODP and OBServer nodes. client_requests The total number of requests. client_large_requests The number of large requests. client_internal_requests The number of internal requests. local_session_state_requests The number of internal requests of ODP. client_missing_pk_requests The number of requests that do not need to be routed but need to use the connection of the last request. client_completed_requests The number of completed requests. client_connection_abort_count The number of aborted connections. client_select_requests The total number of SELECTrequests.client_insert_requests The total number of INSERTrequests.client_update_requests The total number of UPDATErequests.client_delete_requests The total number of DELETErequests.client_other_requests The total number of other requests. request_size_100_count The number of requests within the size range of [0,100). request_size_1K_count The number of requests within the size range of [100,1000). request_size_3K_count The number of requests within the size range of [1000,3000). request_size_5K_count The number of requests within the size range of [3000,5000). request_size_10K_count The number of requests within the size range of [5000,10000). request_size_1M_count The number of requests within the size range of [10000,1000000). request_size_inf_count The number of requests within the size range of [1000000,+∞). response_size_100_count The number of responses within the size range of [0,100). response_size_1K_count The number of responses within the size range of [100,1000). response_size_3K_count The number of responses within the size range of [1000,3000). response_size_5K_count The number of responses within the size range of [3000,5000). response_size_10K_count The number of responses within the size range of [5000,10000). response_size_1M_count The number of responses within the size range of [10000,1000000). response_size_inf_count The number of responses within the size range of [1000000,+∞). client_speed_bytes_per_sec_100 The number of clients whose size of request data sent per second is within the range of [0,100). client_speed_bytes_per_sec_1K The number of clients whose size of request data sent per second is within the range of [100,1000). client_speed_bytes_per_sec_10K The number of clients whose size of request data sent per second is within the range of [1000,10000). client_speed_bytes_per_sec_100K The number of clients whose size of request data sent per second is within the range of [10000,100000). client_speed_bytes_per_sec_1M The number of clients whose size of request data sent per second is within the range of [100000,1000000). client_speed_bytes_per_sec_10M The number of clients whose size of request data sent per second is within the range of [1000000,10000000). client_speed_bytes_per_sec_100M The number of clients whose size of request data sent per second is within the range of [10000000,+∞). server_speed_bytes_per_sec_100 The number of OBServer nodes whose size of response data sent per second is within the range of [0,100). server_speed_bytes_per_sec_1K The number of OBServer nodes whose size of response data sent per second is within the range of [100,1000). server_speed_bytes_per_sec_10K The number of OBServer nodes whose size of response data sent per second is within the range of [1000,10000). server_speed_bytes_per_sec_100K The number of OBServer nodes whose size of response data sent per second is within the range of [10000,100000). server_speed_bytes_per_sec_1M The number of OBServer nodes whose size of response data sent per second is within the range of [100000,1000000). server_speed_bytes_per_sec_10M The number of OBServer nodes whose size of response data sent per second is within the range of [1000000,10000000). server_speed_bytes_per_sec_100M The number of OBServer nodes whose size of response data sent per second is within the range of [10000000,+∞). server_connect_count The total number of connections to OBServer nodes. server_connect_retries The total number of reconnections to OBServer nodes. server_pl_lookup_count The total number of times routing is performed. server_pl_lookup_retries The total number of routing retries. broken_server_connections The total number of disconnections from OBServer nodes. server_requests The total number of requests sent to OBServer nodes. server_responses The total number of responses received from OBServer nodes. server_error_responses The total number of ERROR responses received from OBServer nodes. server_resultset_responses The total number of resultset responses received from OBServer nodes. server_ok_responses The total number of OK responses received from OBServer nodes. server_other_responses The total number of other responses received from OBServer nodes. send_saved_login_requests The total number of LOGIN packets sent to OBServer nodes. send_all_session_vars_requests The total number of times that all session variables are sent to OBServer nodes. send_use_database_requests The total number of times that the use databasecommand is sent to OBServer nodes.send_changed_session_vars_requests The total number of times that part of session variables are sent to OBServer nodes. send_last_insert_id_requests The total number of times that the values of last insert idare sent to OBServer nodes.send_start_trans_requests The total number of times that the request for starting transactions is sent to OBServer nodes. vip_to_tenant_cache_hit The number of hits in queries of VIP information in the public cloud. vip_to_tenant_cache_miss The number of cache misses for VIP information queries in the public cloud.