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 tenant of OceanBase Database by using OBClient and Connect to an Oracle 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 mode and theSHOW FULL PROCESSLIST;statement in Oracle mode.The
LIKEoption supports fuzzy matches. Percent signs (%) and underscores (_) are supported.
Here is an example:
MySQL modeOracle 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.
FieldDescriptionstat_name The name of the internal metric. value The value of the internal metric. The following table describes the internal metrics.
FieldDescriptiontotal_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.