OceanBase通过视图结合SQL运维查看集群信息

原创尚雷 2023-02-03

2855

一、前言

部署OceanBase集群后，可通过OB提供的一些SQL语句来查看集群以及集群资源等使用信息。

OceanBase提供了Oracle模式和MySQL两种，本篇一些查看集群信息的SQL仅限在OceanBase的MySQL模式下，另外这些SQL在因为>OceanBase 社区版 4.0未提供相应视图故在其上也无法执行。

如：以下是在企业版OceanBase数据库Oracle模式下，无法像MySQL方式去查询数据库相关信息。

[admin@obnode1 ~]$ obclient -h10.110.xxx.xxx -P2883 -uSYS@xxx#obtest -pxxx
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 783285
Server version: OceanBase 3.2.4.0 (r100000072022102819-2a28da9e758e2d232c41fa1a1b0070a08b77dd7d) (Built Oct 28 2022 19:46:38)
 
Copyright (c) 2000, 2018, OB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
obclient [SYS]> show databases;
ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'databases' at line 1

如：以下是在社区版Oceanbase 4.0 下，无法通过vob_cluster去查询集群信息,因为社区版不提供vob_cluster这视图。

[admin@obproxy-node ~]$ obclient -h 10.110.xxx.xxx -uroot@sys#obcluster -pxxxx -P2883 -c
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 1048586
Server version: OceanBase_CE 4.0.0.0 (r102000032022120718-58fdb0ef1a9b589ef05e56c1ede65e951986d996) (Built Dec  7 2022 18:32:31)
 
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
obclient [(none)]> use oceanbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
obclient [oceanbase]> SELECT * FROM v$ob_cluster\G
ERROR 1146 (42S02): Table 'oceanbase.v$ob_cluster' doesn't exist

二、通过视图查询集群名称及创建时间等信息

[admin@obnode1 ~]$ mysql -h10.110.xxx.xxx -P2883 -uroot@sys#obtest -pxxxx
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 886414
Server version: 5.6.25 OceanBase 3.2.4.0 (r100000072022102819-2a28da9e758e2d232c41fa1a1b0070a08b77dd7d) (Built Oct 28 2022 19:46:38)
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> use oceanbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
MySQL [oceanbase]> select * from v$ob_cluster\G
*************************** 1. row ***************************
                cluster_id: 1668483228
              cluster_name: obtest
                   created: 2022-11-15 16:47:51.160058
              cluster_role: PRIMARY
            cluster_status: VALID
               switchover#: 0
         switchover_status: NOT ALLOWED
           switchover_info: NONE SYNCED STANDBY CLUSTER
               current_scn: 1672300887141389
standby_became_primary_scn: 0
        primary_cluster_id: NULL
           protection_mode: MAXIMUM PERFORMANCE
          protection_level: MAXIMUM PERFORMANCE
    redo_transport_options: ASYNC NET_TIMEOUT = 30000000
1 row in set (0.01 sec)

通过select * from v$ob_cluster\G可以格式化清晰的查询到集群的名称，集群ID，集群创建时间等信息。

三、通过__all_zone视图查询集群Zone相关信息

MySQL [oceanbase]> select * from __all_zone where name='idc';
+----------------------------+----------------------------+-------+------+-------+------+
| gmt_create                 | gmt_modified               | zone  | name | value | info |
+----------------------------+----------------------------+-------+------+-------+------+
| 2022-11-15 16:48:03.323946 | 2022-11-15 16:48:35.941123 | zone1 | idc  |     0 | NJ   |
| 2022-11-15 16:48:03.325001 | 2022-11-15 16:48:35.959077 | zone2 | idc  |     0 | NJ   |
| 2022-11-15 16:48:03.326055 | 2022-11-15 16:48:35.969648 | zone3 | idc  |     0 | NJ   |
+----------------------------+----------------------------+-------+------+-------+------+
3 rows in set (0.00 sec)

__all_zone视图可以在OceanBase企业版和社区版MySQL模式下都可以查看。

通过__all_zone视图查询到的一些字段信息含义如下：

字段名称	信息描述
gmt_create	创建时间
gmt_modified	更新时间
zone	zone 名称
name	信息名称
value	信息值
info	信息值字符串表示

四、通过__all_server 视图查询集群信息

MySQL [oceanbase]> select id,svr_ip,svr_port,zone,inner_port,with_rootserver,status,start_service_time,last_offline_time from  __all_server\G
*************************** 1. row ***************************
                id: 1
            svr_ip: 10.110.xxx.xxx
          svr_port: 2882
              zone: zone1
        inner_port: 2881
   with_rootserver: 1
            status: active
start_service_time: 1670405645954160
 last_offline_time: 0
*************************** 2. row ***************************
                id: 2
            svr_ip: 10.110.xxx.xxx
          svr_port: 2882
              zone: zone2
        inner_port: 2881
   with_rootserver: 0
            status: active
start_service_time: 1670405517050184
 last_offline_time: 0
*************************** 3. row ***************************
                id: 3
            svr_ip: 10.110.xxx.xxx
          svr_port: 2882
              zone: zone3
        inner_port: 2881
   with_rootserver: 0
            status: active
start_service_time: 1670997805487941
 last_offline_time: 0
3 rows in set (0.01 sec)
 
MySQL [oceanbase]> select id,svr_ip,svr_port,zone,inner_port,with_rootserver,status,start_service_time,last_offline_time from  __all_server;
+----+-------------+----------+-------+------------+-----------------+--------+--------------------+-------------------+
| id | svr_ip      | svr_port | zone  | inner_port | with_rootserver | status | start_service_time | last_offline_time |
+----+-------------+----------+-------+------------+-----------------+--------+--------------------+-------------------+
|  1 | 10.110.xxx.xxx |     2882 | zone1 |       2881 |               1 | active |   1670405645954160 |                 0 |
|  2 | 10.110.xxx.xxx |     2882 | zone2 |       2881 |               0 | active |   1670405517050184 |                 0 |
|  3 | 10.110.xxx.xxx |     2882 | zone3 |       2881 |               0 | active |   1670997805487941 |                 0 |
+----+-------------+----------+-------+------------+-----------------+--------+--------------------+-------------------+
3 rows in set (0.00 sec)

__all_server视图里有些字段last_offline_time是在社区OceanBase里不存在的。
通过__all_server视图查询到一些字段含义如下：

字段名称	信息描述
id	服务器 ID
srv_ip	IP 地址
srv_port	端口
zone	zone 名称
inner_port	sql 执行端口
with_rootserver	是否RS 所在服务器
status	observer 状态。有以下取值：* active server 运行正常*inactive：server 异常。deleting：delete server 进行中。
start_service_time	开始服务的时间
last_offline_time	最近下线时间

五、通过__all_virtual_server_stat视图查询集群资源使用情况

__all_virtual_server_stat视图也是在社区版OceanBase上是不不存在的。

MySQL [oceanbase]> select
    ->  zone,
    ->  concat(svr_ip, ':', svr_port) observer,
    ->  cpu_capacity,
    ->  cpu_total,
    ->  cpu_assigned,
    ->  cpu_assigned_percent,
    ->  mem_capacity,
    ->  mem_total,
    ->  mem_assigned,
    ->  mem_assigned_percent,
    ->  unit_num,
    ->  round(`load`, 2) `load`,
    ->  round(cpu_weight, 2) cpu_weight,
    ->  round(memory_weight, 2) mem_weight,
    ->  leader_count
    ->  from __all_virtual_server_stat
    ->  order by zone,svr_ip\G
*************************** 1. row ***************************
                zone: zone1
            observer: 10.110.xxx.xxx:2882
        cpu_capacity: 10
           cpu_total: 10
        cpu_assigned: 8.5
cpu_assigned_percent: 85
        mem_capacity: 43093875097
           mem_total: 43093875097
        mem_assigned: 40802189312
mem_assigned_percent: 94
            unit_num: 2
                load: 0.90
          cpu_weight: 0.47
          mem_weight: 0.53
        leader_count: 1655
*************************** 2. row ***************************
                zone: zone2
            observer: 10.110.xxx.xxx:2882
        cpu_capacity: 10
           cpu_total: 10
        cpu_assigned: 8.5
cpu_assigned_percent: 85
        mem_capacity: 43093878374
           mem_total: 43093878374
        mem_assigned: 40802189312
mem_assigned_percent: 94
            unit_num: 2
                load: 0.90
          cpu_weight: 0.47
          mem_weight: 0.53
        leader_count: 515
*************************** 3. row ***************************
                zone: zone3
            observer: 10.110.xxx.xxx:2882
        cpu_capacity: 10
           cpu_total: 10
        cpu_assigned: 8.5
cpu_assigned_percent: 85
        mem_capacity: 43093878374
           mem_total: 43093878374
        mem_assigned: 40802189312
mem_assigned_percent: 94
            unit_num: 2
                load: 0.90
          cpu_weight: 0.47
          mem_weight: 0.53
        leader_count: 362
3 rows in set (0.00 sec)
 
MySQL [oceanbase]> select
    ->  zone,
    ->  concat(svr_ip, ':', svr_port) observer,
    ->  cpu_capacity,
    ->  cpu_total,
    ->  cpu_assigned,
    ->  cpu_assigned_percent,
    ->  mem_capacity,
    ->  mem_total,
    ->  mem_assigned,
    ->  mem_assigned_percent,
    ->  unit_num,
    ->  round(`load`, 2) `load`,
    ->  round(cpu_weight, 2) cpu_weight,
    ->  round(memory_weight, 2) mem_weight,
    ->  leader_count
    ->  from __all_virtual_server_stat
    ->  order by zone,svr_ip;
+-------+------------------+--------------+-----------+--------------+----------------------+--------------+-------------+--------------+----------------------+----------+------+------------+------------+--------------+
| zone  | observer         | cpu_capacity | cpu_total | cpu_assigned | cpu_assigned_percent | mem_capacity | mem_total   | mem_assigned | mem_assigned_percent | unit_num | load | cpu_weight | mem_weight | leader_count |
+-------+------------------+--------------+-----------+--------------+----------------------+--------------+-------------+--------------+----------------------+----------+------+------------+------------+--------------+
| zone1 | 10.110.xxx.xxx:2882 |           10 |        10 |          8.5 |                   85 |  43093875097 | 43093875097 |  40802189312 |                   94 |        2 | 0.90 |       0.47 |       0.53 |         1655 |
| zone2 | 10.110.xxx.xxx:2882 |           10 |        10 |          8.5 |                   85 |  43093878374 | 43093878374 |  40802189312 |                   94 |        2 | 0.90 |       0.47 |       0.53 |          515 |
| zone3 | 10.110.xxx.xxx:2882 |           10 |        10 |          8.5 |                   85 |  43093878374 | 43093878374 |  40802189312 |                   94 |        2 | 0.90 |       0.47 |       0.53 |          362 |
+-------+------------------+--------------+-----------+--------------+----------------------+--------------+-------------+--------------+----------------------+----------+------+------------+------------+--------------+
3 rows in set (0.00 sec)

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

关注作者

文章被以下合辑收录

OceanBase（共13篇）

OceanBase合集

OceanBase通过视图结合SQL运维查看集群信息

一、前言

二、通过视图查询集群名称及创建时间等信息

三、通过__all_zone视图查询集群Zone相关信息

四、通过__all_server 视图查询集群信息

五、通过__all_virtual_server_stat视图查询集群资源使用情况

文章被以下合辑收录

评论