This topic describes how to query the internal statistics of a specified client session on ODP, including the number of SQL request responses and the size of SQL request responses.
Procedure
You can query the internal statistics of a specified client session on ODP by executing the SHOW PROXYSESSION STAT statement in the sys tenant or a user tenant.
Connect to OceanBase AI 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 a database by using ODP, see Connect to OceanBase AI Database by using OBClient.
Query the internal statistics 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, and the query results are the same.cs_idindicates the ID of a client session in ODP.connection_idindicates the ID of a client session in OceanBase AI Database.You can obtain the
connection_idvalue by using theSELECT CONNECTION_ID();statement.The
LIKEoption supports fuzzy matches. Percent signs (%) and underscores (_) are supported.
Here is an example:
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 | +----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | 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 query result is as follows:
+-----------------+ | CONNECTION_ID() | +-----------------+ | 3221597019 | +-----------------+ 1 row in setQuery the internal statistics.
SHOW PROXYSESSION STAT 11 LIKE '%time%';Alternatively, use the following syntax:
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
The following table describes the fields in the returned result.
FieldDescriptionstat_name The name of the internal statistic. value The value of the internal statistic. The following table describes the internal statistics.
FieldDescriptiontotal_transaction_count Total number of transactions total_user_transaction_count Number of user transactions executed by ODP total_query_count Number of requests executed by users total_client_request_reread_count Number of times ODP reads user requests total_server_response_reread_count Number of times ODP reads data returned by OBServer nodes client_request_total_size Total size of data requested by clients client_response_total_size Total size of data returned to clients server_request_total_size Total size of data requested to OBServer nodes server_response_total_size Total size of data returned by OBServer nodes total_transactions_time Total time consumed for transaction execution total_user_transactions_time Total time spent on executing user transactions total_client_request_read_time Total time spent on reading requests from clients total_client_response_write_time Total time spent on sending data back to clients total_client_request_analyze_time Total time spent on parsing client messages total_client_transaction_idle_time Total idle time during user transaction execution total_server_process_request_time Total time spent on processing requests by the database total_server_response_read_time Total time spent on reading data returned by OBServer nodes total_server_response_analyze_time Total time spent by ODP on parsing data returned by OBServer nodes total_send_saved_login_time Total time spent on sending LOGIN messages to OBServer nodes total_send_all_session_vars_time Total time spent on sending session variable synchronization messages to OBServer nodes total_send_use_database_time Total time spent on sending use database commands to OBServer nodes total_send_changed_session_vars_time Total time spent on sending session variable modification messages to OBServer nodes total_send_last_insert_id_time Total time spent on sending last_insert_id synchronization messages to OBServer nodes total_send_start_trans_time Total time spent on initiating transaction synchronization to OBServer nodes total_pl_lookup_time Total routing time total_server_connect_time Total time spent on establishing connections between ODP and OBServer nodes client_requests Total number of requests client_large_requests Number of large requests client_internal_requests Number of internal requests local_session_state_requests Number of internal requests processed by ODP client_missing_pk_requests Number of requests that do not require routing but need to use the connection from the previous request client_completed_requests Number of completed requests client_connection_abort_count Total number of connections and aborts client_select_requests Total number of SELECTrequestsclient_insert_requests Total number of INSERTrequestsclient_update_requests Total number of UPDATErequestsclient_delete_requests Total number of DELETErequestsclient_other_requests Total number of other requests request_size_100_count Number of requests with sizes between [0,100) request_size_1K_count Number of requests with sizes between [100,1K) request_size_3K_count Number of requests with sizes between [1K,3K) request_size_5K_count Number of requests with sizes between [3K,5K) request_size_10K_count Number of requests with sizes between [5K,10K) request_size_1M_count Number of requests with sizes between [10K,1M) request_size_inf_count Number of requests with sizes between [1M,+∞) response_size_100_count Number of responses with sizes between [0,100) response_size_1K_count Number of responses with sizes between [100,1K) response_size_3K_count Number of responses with sizes between [1K,3K) response_size_5K_count Number of responses with size in the range of [3K,5K) response_size_10K_count Number of responses with size in the range of [5K,10K) response_size_1M_count Number of responses with size in the range of [10K,1M) response_size_inf_count Number of responses with size in the range of [1M,+∞) client_speed_bytes_per_sec_100 Number of requests with a per-second speed in the range of [0,100) client_speed_bytes_per_sec_1K Number of requests with a per-second speed in the range of [100,1K) client_speed_bytes_per_sec_10K Number of requests with a per-second speed in the range of [1K,10K) client_speed_bytes_per_sec_100K Number of requests with a per-second speed in the range of [10K,100K) client_speed_bytes_per_sec_1M Number of requests with a per-second speed in the range of [100K,1M) client_speed_bytes_per_sec_10M Number of requests with a per-second speed in the range of [1M,10M) client_speed_bytes_per_sec_100M Number of requests with a per-second speed in the range of [10 M,+∞) server_speed_bytes_per_sec_100 Number of responses with a per-second speed in the range of [0,100) server_speed_bytes_per_sec_1K Number of responses with a per-second speed in the range of [100,1K) server_speed_bytes_per_sec_10K Number of responses with a per-second speed in the range of [1K,10K) server_speed_bytes_per_sec_100K Number of responses with a per-second speed in the range of [10K,100K) server_speed_bytes_per_sec_1M Number of responses with a per-second speed in the range of [100K,1M) server_speed_bytes_per_sec_10M Number of responses with a per-second speed in the range of [1M,10M) server_speed_bytes_per_sec_100M Number of responses with a per-second speed in the range of [10 M,+∞) server_connect_count Total number of connections to OBServer nodes server_connect_retries Total number of retries for connecting to OBServer nodes server_pl_lookup_count Total number of route lookups server_pl_lookup_retries Total number of route lookup retries broken_server_connections Total number of disconnections from OBServer nodes server_requests Total number of requests sent to OBServer nodes server_responses Total number of responses received from OBServer nodes server_error_responses Total number of ERROR responses received from OBServer nodes server_resultset_responses Total number of resultset-type responses received from OBServer nodes server_ok_responses Total number of OK-type responses received from OBServer nodes server_other_responses Total number of other-type responses received from OBServer nodes send_saved_login_requests Total number of LOGIN messages sent to OBServer nodes send_all_session_vars_requests Total number of full session variable synchronization requests sent to OBServer nodes send_use_database_requests Total number of use database requests sent to OBServer nodes send_changed_session_vars_requests Total number of partial session variable requests sent to OBServer nodes send_last_insert_id_requests Total number of last insert id requests sent to OBServer nodes send_start_trans_requests Total number of transaction start requests sent to OBServer nodes vip_to_tenant_cache_hit Number of hits for VIP information queries on the public cloud vip_to_tenant_cache_miss Number of misses for VIP information queries on the public cloud