Purpose
You can use this statement to query information about OceanBase Database Proxy (ODP) and OceanBase Database.
Syntax
SHOW {
PROXYINFO BINARY
| PROXYINFO UPGRADE
| PROXYINFO IDC
| PROXYCLUSTER [IDC] [like 'cluster_name']
| PROXYCONFIG [diff] [user] [like 'config_name']
| PROXYCONGESTION [all] [cluster_name]
| PROXYMEMORY [objpool] [num]
| PROXYNET CONNECTION [thread_id [LIMIT xx]]
| PROXYNET THREAD
| PROXYROUTE [like 'cluster [tenant [db [table]]]']
| PROXYSESSION
| PROXYSESSION ATTRIBUTE [id [like 'xxx']]
| PROXYSESSION STAT id [like 'xx']
| PROXYSESSION VARIABLES [all] id [like 'xx']
| PROXYSM [sm_id]
| PROXYSTAT [refresh] [like 'name']
| WARNLOG [[[log_id], thread_id], time]
};
Parameters
Parameter |
Description |
|---|---|
| PROXYINFO BINARY | Returns binary ODP information in rich text, including the version, packaging time, and MD5 hash. |
| PROXYINFO UPGRADE | Returns ODP runtime and upgrade information in rich text. |
| PROXYINFO IDC | Returns information about the Internet Data Centers (IDCs) that match ODP in rich text. |
| PROXYCLUSTER [IDC] [like 'cluster_name'] | Returns details of the RootService list of the cluster, if IDC is specified. You can use the like clause for fuzzy search. Supported wildcard characters are the percent sign (%) and the underscore (_). |
| PROXYCONFIG [diff] [user] [like 'config_name'] | Returns ODP parameters. If diff is specified, only parameters with values different from the default values are returned. If diff user is specified, only user-level parameters with values different from the default values are returned. The like clause supports fuzzy search. Supported wildcard characters are the percent sign (%) and the underscore (_). |
| PROXYCONGESTION [all] [cluster_name] | Returns information about all clusters served by ODP. The returned information varies based on the following cases:
NoteIn ODP of a version earlier than V1.1.2, |
| PROXYMEMORY [objpool] [num] | Returns the memory usage information of ODP, including the size of allocated memory, size of used memory, and type of each module, to facilitate ODP performance analysis. You can specify the num option to view the memory allocation stack. A maximum of 10 layers can be displayed. You need to modify the mem_leak_check_mod_name and mem_leak_check_class_name parameters. |
| PROXYNET CONNECTION [thread_id [LIMIT xx]] | Returns the internal attributes of all network connections in ODP. You can specify thread_id to view the internal attributes of network connections on the specified thread. If thread_id is not specified, the internal attributes of all network connections in ODP are returned. If thread id is specified, you can also specify LIMIT [offset,] rows and LIMIT rows OFFSET offset. Their formats are fully compatible with those in MySQL. If rows is set to -1, all rows are returned.
NoteFor more information, see Network connection. |
| PROXYNET THREAD | Returns the attributes of worker threads in ODP. The number of worker threads of ODP is specified by work_thread_num. |
| PROXYROUTE [like 'cluster [tenant [db [table]]]'] | Returns the status information about table entries in ODP. By default, the information about all table entries is returned. The like clause supports fuzzy search. Supported wildcard characters are the percent sign (%) and the underscore (_). SQL routing address information in ODP is provided in the unit of table entry. Each table entry is comprised of the cluster name, tenant name, database name, and table name. An SQL query without a table name is marked with __all_dummy in ODP. |
| PROXYSESSION | Returns the internal status of all client sessions for current tenant connections in ODP. Unlike the SHOW PROCESSLIST statement, the SHOW PROXYSESSION statement can also show the cluster of each tenant. |
| PROXYSESSION ATTRIBUTE [id [like 'xxx']] | Returns the details of a client session, including the related server session of this client session.
|
| PROXYSESSION STAT id [like 'xx'] | Returns the internal statistical items of a specified client session in ODP, such as the SQL response count and SQL response size.
|
| PROXYSESSION VARIABLES [all] id [like 'xx'] | Returns the variables of a client session.
|
| PROXYSM [sm_id] | Returns the internal status of the state machines in ODP in rich text. You can also specify sm_id to return the internal status of the specified state machine. |
| PROXYSTAT [refresh] [like 'xx'] | Returns the internal statistical items of ODP.
|
| WARNLOG [[[log_id], thread_id], time] | Returns the WARN- and ERROR-level logs with the specified log ID generated within the specified period. You can specify tread_id to query the alert logs of the specified thread. If you are not concerned about the log ID or thread ID, you can set log_id or thread_id to -1, which specifies to obtain the alert logs for all log IDs or thread IDs. |
Examples
Query ODP information
Query the ODP version
show proxyinfo binary\GThe output is as follows:
*************************** 1. row *************************** name: binary info info: ObProxy-OceanBase 4.0.0-20221103165243.el7 version:RELEASE_7U MD5 REVISION:20221103165243-7c7e5821009b2d3924d1bc7a8165edf7384f6ed3 BUILD_TIME:Nov 3 2022 16:57:16Query ODP runtime and upgrade information
show proxyinfo upgrade\GThe output is as follows:
*************************** 1. row *************************** name: hot upgrade info info: {is_inited:true, proxy_port:13205, proxy_ip:"10.10.10.1", is_self_md5_available:false, proxy_self_md5:"", upgrade_failures:0, check_available_failures:0, is_timeout_rollback:false, timeout_rollback_timeout_at:0, wait_cr_finish_timeout_at:0, is_self_binary:false, last_new_binary_name:"", last_new_binary_md5:"", cmd:"", hu_cont:NULL, mysql_proxy:{this:0x1107a750, is_inited:true, stop:false, is_raw_execute:false, timeout_ms:5000, client_pool:{this:0x7fd04d55d280, is_inited:true, stop:false, mc_count:1, cluster_resource:0x7fd04d5a5f80}, raw_mysql_client:{is_inited:true, info:{has_passwd_scrambled:false, user_name:"admin", database_name:"oceanbase", request_sql:""}, svr_addr_[0]="0.0.0.0", svr_addr_[1]="0.0.0.0", svr_addr_[2]="0.0.0.0"}}, info:{is_inherited:false, upgrade_version:0, need_conn_accept:true, user_rejected:0, fd:23, sub_pid:-1, graceful_exit_end_time:0, graceful_exit_start_time:0, active_client_vc_count:-1, local_addr:"10.10.10.1:13205", rc_status:"", hu_cmd:"", state:"HU_STATE_WAIT_HU_CMD", hu_status:"", is_parent:true, sub_status:"", last_parent_status:"", last_sub_status:"", upgrade_version_buf:"", argc:1, argv[0]="./bin/obproxy", inherited_argv[0]="./bin/obproxy", inherited_argv[1]="(null)", inherited_argv[2]="(null)", inherited_argv[3]="(null)"}}Query information about IDCs matching ODP
show proxyinfo idc;The output is as follows:
+-----------------+--------------+----------------+--------------+--------------+-------------+--------------+ | global_idc_name | cluster_name | match_type | regions_name | same_idc | same_region | other_region | +-----------------+--------------+----------------+--------------+--------------+-------------+--------------+ | idc1 | MetaDataBase | MATCHED_BY_IDC | ["HZ"] | ["z1", "z2"] | [] | ["z3", "z4"] | +-----------------+--------------+----------------+--------------+--------------+-------------+--------------+
Query ODP connection information
Here is an example of querying ODP connection information. For more information, see Client session.
The following sample code queries attributes whose names contain id in the client session with the ID 2.
show proxysession attribute 2 like '%id%';
The output is as follows:
+------------------------+--------------------+----------------+
| attribute_name | value | info |
+------------------------+--------------------+----------------+
| proxy_sessid | 756006681247547396 | cs common |
| cs_id | 2 | cs common |
| tid | 2230520 | cs common |
| pid | 2230520 | cs common |
| last_insert_id_version | 0 | cs var version |
| server_sessid | 2147549201 | last used ss |
| ss_id | 4 | last used ss |
| last_insert_id_version | 0 | last used ss |
+------------------------+--------------------+----------------+
Query memory usage information of ODP
show proxymemory;
The output is as follows:
+-------------------------------+-----------+----------+----------+-------+----------+
| mod_name | mod_type | hold | used | count | avg_used |
+-------------------------------+-----------+----------+----------+-------+----------+
| OB_ALLOC_CHUNK | allocator | 29424512 | 29423792 | 15 | 1961586 |
| OB_ALLOC_BLOCK | allocator | 24137696 | 23371079 | 223 | 104803 |
| OB_ALLOC_OBJECT | allocator | 23260647 | 23253895 | 211 | 110208 |
| OB_MEMORY_STAT | user | 54336 | 54336 | 1 | 54336 |
| OB_TSI_FACTORY | user | 8757888 | 8757888 | 16 | 547368 |
| OB_PAGE_ARENA | user | 32640 | 32640 | 4 | 8160 |
| OB_FIXED_QUEUE | user | 13008 | 13008 | 2 | 6504 |
| TEST | user | 55400 | 55400 | 1 | 55400 |
| OB_HASH_BUCKET_CONF_CONTAINER | user | 24720 | 24720 | 1 | 24720 |
| OB_HASH_BUCKET_TASK_MAP | user | 37376 | 37376 | 1 | 37376 |
| OB_HASH_NODE_CONF_CONTAINER | user | 15792 | 15792 | 2 | 7896 |
| OB_CONCURRENCY_OBJ_POOL | user | 14183104 | 14183104 | 175 | 81046 |
| OB_CORE_LOCAL_STORAGE | user | 2048 | 2048 | 1 | 2048 |
| OB_PROXY_DEFAULT_SYS_VARIABLE | user | 32768 | 32768 | 2 | 16384 |
| OB_PROXY_SQL_PARSE | user | 24576 | 24576 | 3 | 8192 |
| OB_PROXY_COMMON_DQ | user | 8200 | 8200 | 1 | 8200 |
| OB_LARGE_IO_BUFFER | user | 12039 | 12039 | 1 | 12039 |
+-------------------------------+-----------+----------+----------+-------+----------+
Query cluster information
The following sample code queries information about OBServer nodes in a cluster:
show proxycongestion all "obcluster";
In this example, the cluster is named obcluster. The output is as follows. You must replace the cluster name with the actual one when you query information.
+-----------------+-----------+------------+--------------------+--------------+-----------------+----------------------+----------------+---------------------+---------------------+--------------------+---------------------+---------------------+----------------------+----------------------+-----------+
| cluster_name | zone_name | zone_state | server_ip | server_state | alive_congested | last_alive_congested | dead_congested | last_dead_congested | stat_alive_failures | stat_conn_failures | conn_last_fail_time | conn_failure_events | alive_last_fail_time | alive_failure_events | ref_count |
+-----------------+-----------+------------+--------------------+--------------+-----------------+----------------------+----------------+---------------------+---------------------+--------------------+---------------------+---------------------+----------------------+----------------------+-----------+
| obcluster | zone1 | ACTIVE | 10.10.10.1:13201 | ACTIVE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
+-----------------+-----------+------------+--------------------+--------------+-----------------+----------------------+----------------+---------------------+---------------------+--------------------+---------------------+---------------------+----------------------+----------------------+-----------+
Query internal statistical items of ODP
The following sample code queries statistical items whose names contain global in ODP.
show proxystat refresh like '%global%';
The output is as follows, where global_client_connections_currently_open indicates the number of client connections with ODP and global_connections_currently_open indicates the sum of the number of client connections with ODP and the number of ODP connections with OceanBase Database.
+---------------------------------------------+-------+--------------+
| stat_name | value | persist_type |
+---------------------------------------------+-------+--------------+
| get_congestion_from_global_cache_miss | 0 | PERSISTENT |
| get_congestion_from_global_cache_hit | 0 | PERSISTENT |
| kick_out_routine_entry_from_global_cache | 0 | PERSISTENT |
| gc_routine_entry_from_global_cache | 0 | PERSISTENT |
| get_routine_entry_from_global_cache_dirty | 0 | PERSISTENT |
| get_routine_entry_from_global_cache_hit | 0 | PERSISTENT |
| kick_out_partition_entry_from_global_cache | 0 | PERSISTENT |
| gc_partition_entry_from_global_cache | 0 | PERSISTENT |
| get_partition_entry_from_global_cache_dirty | 0 | PERSISTENT |
| get_partition_entry_from_global_cache_hit | 0 | PERSISTENT |
| kick_out_table_entry_from_global_cache | 0 | NULL |
| gc_table_entry_from_global_cache | 0 | NULL |
| get_pl_from_global_cache_dirty_stat | 0 | PERSISTENT |
| get_pl_from_global_cache_hit | 2 | PERSISTENT |
| global_accepts_currently_open | 4 | NULL |
| global_connections_currently_open | 1 | NULL |
| global_client_connections_currently_open | 1 | NULL |
+---------------------------------------------+-------+--------------+
Query alert logs
Query alert logs generated at 2016-09-13 11:20:52.251 or later.
show warnlog -1, -1, "2016-09-13 11:20:52.251";In this example, both
log_idandthread_idare set to-1to query all alert logs generated at the specified time or later.Query alert logs with a log ID equal to or greater than
1000that were generated by the thread with the ID36592at 2016-09-13 11:20:52.251 or later.show warnlog 1000, 36592, "2016-09-13 11:20:52.251";