OceanBase学习记录14|查看 ODP 指定 Client Session 的内部统计项

原创没有名字 2023-01-17

155

本文介绍了查看 ODP 指定 Client Session 的内部统计项（包括：SQL 请求响应数量、SQL 请求响应大小等）的方法。

通过 SHOW PROXYSESSION STAT id [like 'xx'] 语句可以查看 ODP 指定 Client Session 的内部统计项。

参数说明：

id 既可以是 cs_id，也可以是 CONNECTION_ID，显示结果相同。
cs_id 为 ODP 内部标记的每个 Client 的 id 号，CONNECTION_ID 为整个 OceanBase 数据库标记的每个 Client 的 id 号。有关 CONNECTION_ID 的详细介绍，请参见 CONNECTION_ID。
like 模糊匹配，支持 '%' 和 '_' 。

通过 SHOW PROXYSESSION STAT 语句查看指定 Client Session 的内部统计项。示例如下：

obclient> SHOW PROXYSESSION STAT 3;
+--------------------------------------------------+-----------+
| stat_name                                        | value     |
+--------------------------------------------------+-----------+
| total_transaction_count                          |       100 |
| client_request_total_size                        |      2391 |
| client_response_total_size                       |     16968 |
| server_request_total_size                        |      2068 |
| server_response_total_size                       |      7676 |
| total_transactions_time                          | 406854594 |
| total_client_request_read_time                   |  10823905 |
| total_client_response_write_time                 |         0 |
| total_client_request_analyze_time                |   4147091 |
| total_server_process_request_time                | 244970812 |
| total_server_response_read_time                  |  33951745 |
| total_send_saved_login_time                      |  16869682 |
| total_send_all_session_vars_time                 |         0 |
| total_send_use_database_time                     |   3165510 |
| total_send_changed_session_vars_time             |         0 |
| total_send_last_insert_id_time                   |         0 |
| total_send_start_trans_time                      |         0 |
| total_pl_lookup_time                             |    756611 |
| total_server_connect_time                        |   3196171 |
| client_requests                                  |       100 |
| client_large_requests                            |         0 |
| client_internal_requests                         |        14 |
| client_missing_pk_requests                       |        85 |
| client_completed_requests                        |        99 |
| client_connection_abort_count                    |         0 |
| client_select_requests                           |         4 |
| client_insert_requests                           |         0 |
| client_update_requests                           |         0 |
| client_delete_requests                           |         0 |
| client_other_requests                            |         3 |
| request_size_100_count                           |        99 |
| request_size_1K_count                            |         0 |
| request_size_3K_count                            |         0 |
| request_size_5K_count                            |         0 |
| request_size_10K_count                           |         0 |
| request_size_1M_count                            |         0 |
| request_size_inf_count                           |         0 |
| response_size_100_count                          |        96 |
| response_size_1K_count                           |         1 |
| response_size_3K_count                           |         2 |
| response_size_5K_count                           |         0 |
| response_size_10K_count                          |         0 |
| response_size_1M_count                           |         0 |
| response_size_inf_count                          |         0 |
| client_speed_bytes_per_sec_100                   |         0 |
| client_speed_bytes_per_sec_1K                    |         0 |
| client_speed_bytes_per_sec_10K                   |         0 |
| client_speed_bytes_per_sec_100K                  |         0 |
| client_speed_bytes_per_sec_1M                    |         0 |
| client_speed_bytes_per_sec_10M                   |         0 |
| client_speed_bytes_per_sec_100M                  |         0 |
| server_speed_bytes_per_sec_100                   |         0 |
| server_speed_bytes_per_sec_1K                    |         0 |
| server_speed_bytes_per_sec_10K                   |         0 |
| server_speed_bytes_per_sec_100K                  |         2 |
| server_speed_bytes_per_sec_1M                    |        81 |
| server_speed_bytes_per_sec_10M                   |         2 |
| server_speed_bytes_per_sec_100M                  |         0 |
| server_connect_count                             |         4 |
| server_connect_retries                           |         0 |
| server_pl_lookup_count                           |         4 |
| server_pl_lookup_retries                         |         0 |
| broken_server_connections                        |         0 |
| server_requests                                  |        91 |
| server_responses                                 |        95 |
| server_error_responses                           |        77 |
| server_resultset_responses                       |         7 |
| server_ok_responses                              |         7 |
| server_other_responses                           |         4 |
| send_saved_login_requests                        |         3 |
| send_all_session_vars_requests                   |         0 |
| send_use_database_requests                       |         3 |
| send_changed_session_vars_requests               |         0 |
| send_last_insert_id_requests                     |         0 |
| send_start_trans_requests                        |         0 |
| vip_to_tenant_cache_hit                          |         0 |
| vip_to_tenant_cache_miss                         |         0 |
+--------------------------------------------------+-----------+
77 rows in set

obclient> SHOW PROXYSESSION STAT 2147549231;
+--------------------------------------------------+-----------+
| stat_name                                        | value     |
+--------------------------------------------------+-----------+
| total_transaction_count                          |       101 |
| client_request_total_size                        |      2420 |
| client_response_total_size                       |     20576 |
| server_request_total_size                        |      2068 |
| server_response_total_size                       |      7676 |
| total_transactions_time                          | 408855677 |
| total_client_request_read_time                   |  10987827 |
| total_client_response_write_time                 |         0 |
| total_client_request_analyze_time                |   4244033 |
| total_server_process_request_time                | 244970812 |
| total_server_response_read_time                  |  33951745 |
| total_send_saved_login_time                      |  16869682 |
| total_send_all_session_vars_time                 |         0 |
| total_send_use_database_time                     |   3165510 |
| total_send_changed_session_vars_time             |         0 |
| total_send_last_insert_id_time                   |         0 |
| total_send_start_trans_time                      |         0 |
| total_pl_lookup_time                             |    756611 |
| total_server_connect_time                        |   3196171 |
| client_requests                                  |       101 |
| client_large_requests                            |         0 |
| client_internal_requests                         |        15 |
| client_missing_pk_requests                       |        85 |
| client_completed_requests                        |       100 |
| client_connection_abort_count                    |         0 |
| client_select_requests                           |         4 |
| client_insert_requests                           |         0 |
| client_update_requests                           |         0 |
| client_delete_requests                           |         0 |
| client_other_requests                            |         3 |
| request_size_100_count                           |       100 |
| request_size_1K_count                            |         0 |
| request_size_3K_count                            |         0 |
| request_size_5K_count                            |         0 |
| request_size_10K_count                           |         0 |
| request_size_1M_count                            |         0 |
| request_size_inf_count                           |         0 |
| response_size_100_count                          |        97 |
| response_size_1K_count                           |         1 |
| response_size_3K_count                           |         2 |
| response_size_5K_count                           |         0 |
| response_size_10K_count                          |         0 |
| response_size_1M_count                           |         0 |
| response_size_inf_count                          |         0 |
| client_speed_bytes_per_sec_100                   |         0 |
| client_speed_bytes_per_sec_1K                    |         0 |
| client_speed_bytes_per_sec_10K                   |         0 |
| client_speed_bytes_per_sec_100K                  |         0 |
| client_speed_bytes_per_sec_1M                    |         0 |
| client_speed_bytes_per_sec_10M                   |         0 |
| client_speed_bytes_per_sec_100M                  |         0 |
| server_speed_bytes_per_sec_100                   |         0 |
| server_speed_bytes_per_sec_1K                    |         0 |
| server_speed_bytes_per_sec_10K                   |         0 |
| server_speed_bytes_per_sec_100K                  |         2 |
| server_speed_bytes_per_sec_1M                    |        81 |
| server_speed_bytes_per_sec_10M                   |         2 |
| server_speed_bytes_per_sec_100M                  |         0 |
| server_connect_count                             |         4 |
| server_connect_retries                           |         0 |
| server_pl_lookup_count                           |         4 |
| server_pl_lookup_retries                         |         0 |
| broken_server_connections                        |         0 |
| server_requests                                  |        91 |
| server_responses                                 |        95 |
| server_error_responses                           |        77 |
| server_resultset_responses                       |         7 |
| server_ok_responses                              |         7 |
| server_other_responses                           |         4 |
| send_saved_login_requests                        |         3 |
| send_all_session_vars_requests                   |         0 |
| send_use_database_requests                       |         3 |
| send_changed_session_vars_requests               |         0 |
| send_last_insert_id_requests                     |         0 |
| send_start_trans_requests                        |         0 |
| vip_to_tenant_cache_hit                          |         0 |
| vip_to_tenant_cache_miss                         |         0 |
+--------------------------------------------------+-----------+
77 rows in set

obclient> SHOW PROXYSESSION STAT 2147549231 like "%time%";
+--------------------------------------------------+-----------+
| stat_name                                        | value     |
+--------------------------------------------------+-----------+
| total_transactions_time                          | 410877642 |
| total_client_request_read_time                   |  11192818 |
| total_client_response_write_time                 |         0 |
| total_client_request_analyze_time                |   4355363 |
| total_server_process_request_time                | 244970812 |
| total_server_response_read_time                  |  33951745 |
| total_send_saved_login_time                      |  16869682 |
| total_send_all_session_vars_time                 |         0 |
| total_send_use_database_time                     |   3165510 |
| total_send_changed_session_vars_time             |         0 |
| total_send_last_insert_id_time                   |         0 |
| total_send_start_trans_time                      |         0 |
| total_pl_lookup_time                             |    756611 |
| total_server_connect_time                        |   3196171 |
+--------------------------------------------------+-----------+
14 rows in set
复制

各字段含义如下表所示：

字段	说明
stat_name	内部统计项的名称
value	内部统计项的取值

内部统计项及其说明说下：

字段	说明
total_transaction_count	事务总数
client_request_total_size	请求数据量总大小
client_response_total_size	返回数据量总大小
server_request_total_size	发送给 OBServer 的请求数据量总大小
server_response_total_size	OBServer 返回的数据量总大小
total_transactions_time	事务执行总耗时
total_client_request_read_time	客户端请求读取总时间
total_client_response_write_time	返回客户端数据发送总时间
total_client_request_analyze_time	解析客户端报文的总时间
total_server_process_request_time	数据库处理请求总时间
total_server_response_read_time	读取 OBServer 返回数据总时间
total_send_saved_login_time	发送给 OBServer LOGIN 报文的总时间
total_send_all_session_vars_time	发送给 OBServer Session 变量同步总时间
total_send_use_database_time	发送给 OBServer use database 命令总时间
total_send_changed_session_vars_time	发送给 OBServer Session 变量修改总时间
total_send_last_insert_id_time	发送给 OBServer last_insert_id 同步总时间
total_send_start_trans_time	发送给 OBServer 开启事务同步总时间
total_pl_lookup_time	路由总时间
total_server_connect_time	ODP 和 OBServer 建立连接的总时间
client_requests	总请求数
client_large_requests	大请求数
client_internal_requests	内部请求数
client_missing_pk_requests	统计本次请求不需要做路由处理，需要使用上个请求的连接的请求数量
client_completed_requests	已完成的请求数
client_connection_abort_count	连接及中止统计
client_select_requests	`SELECT` 请求总数
client_insert_requests	`INSERT` 请求总数
client_update_requests	`UPDATE` 请求总数
client_delete_requests	`DELETE` 请求总数
client_other_requests	其它请求总数
request_size_100_count	请求大小在 [0,100) 之间的数量
request_size_1K_count	请求大小在 [100,1K) 之间的数量
request_size_3K_count	请求大小在 [1K,3K) 之间的数量
request_size_5K_count	请求大小在 [3K,5K) 之间的数量
request_size_10K_count	请求大小在 [5K,10K) 之间的数量
request_size_1M_count	请求大小在 [10K,1M) 之间的数量
request_size_inf_count	请求大小在 [1M,+∞) 之间的数量
response_size_100_count	响应大小在 [0,100) 之间的数量
response_size_1K_count	响应大小在 [100,1K) 之间的数量
response_size_3K_count	响应大小在 [1K,3K) 之间的数量
response_size_5K_count	响应大小在 [3K,5K) 之间的数量
response_size_10K_count	响应大小在 [5K,10K) 之间的数量
response_size_1M_count	响应大小在 [10K,1M) 之间的数量
response_size_inf_count	响应大小在 [1M,+∞) 之间的数量
client_speed_bytes_per_sec_100	每秒的请求速度在 [0,100) 之间的数量
client_speed_bytes_per_sec_1K	每秒的请求速度在 [100,1K) 之间的数量
client_speed_bytes_per_sec_10K	每秒的请求速度在 [1K,10K) 之间的数量
client_speed_bytes_per_sec_100K	每秒的请求速度在 [10K,100K) 之间的数量
client_speed_bytes_per_sec_1M	每秒的请求速度在 [100K,1M) 之间的数量
client_speed_bytes_per_sec_10M	每秒的请求速度在 [1M,10M）之间的数量
client_speed_bytes_per_sec_100M	每秒的请求速度在 [10 M,+∞) 之间的数量
server_speed_bytes_per_sec_100	每秒的响应速度在 [0,100) 之间的数量
server_speed_bytes_per_sec_1K	每秒的响应速度在 [100,1K) 之间的数量
server_speed_bytes_per_sec_10K	每秒的响应速度在 [1K,10K) 之间的数量
server_speed_bytes_per_sec_100K	每秒的响应速度在 [10K,100K) 之间的数量
server_speed_bytes_per_sec_1M	每秒的响应速度在 [100K,1M) 之间的数量
server_speed_bytes_per_sec_10M	每秒的响应速度在 [1M,10M）之间的数量
server_speed_bytes_per_sec_100M	每秒的响应速度在 [10 M,+∞) 之间的数量
server_connect_count	连接 OBServer 总次数
server_connect_retries	连接 OBServer 重试总次数
server_pl_lookup_count	路由总次数
server_pl_lookup_retries	路由重试总次数
broken_server_connections	和 OBServer 断连总次数
server_requests	向 OBServer 发送请求总次数
server_responses	收到 OBServer 响应总次数
server_error_responses	收到 OBServer ERROR 类型响应总次数
server_resultset_responses	收到 OBServer resultset 类型响应总次数
server_ok_responses	收到 OBServer OK 类型响应总次数
server_other_responses	收到 OBServer 其它类型响应总次数
send_saved_login_requests	发送给 OBServer LOGIN 报文总次数
send_all_session_vars_requests	发送给 OBServer 全量 Session 变量同步总次数
send_use_database_requests	发送给 OBServer use database 总次数
send_changed_session_vars_requests	发送给 OBServer 部分 Session 变量总次数
send_last_insert_id_requests	发送给 OBServer last insert id 总次数
send_start_trans_requests	发送给 OBServer 开启事务总次数
vip_to_tenant_cache_hit	公有云上 VIP 信息查询命中次数
vip_to_tenant_cache_miss	公有云上 VIP 信息查询未命中次数

oceanbase

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

OceanBase学习记录14|查看 ODP 指定 Client Session 的内部统计项

评论

相关阅读