如何查看某张表的主在哪个节点？

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB 】
【 使用版本 】4.0
【问题描述】请问老师，如何查看某张表的主在哪台OBSERVER？

#3

查看某个实例（租户）下库表分区主副本的位置和大小

SELECT t.tenant_id, a.tenant_name, t.table_name, d.database_name, tg.tablegroup_name , t.part_num , t2.partition_id, t2.ZONE, t2.svr_ip , round(t2.data_size/1024/1024/1024) data_size_gb
, a.primary_zone , IF(t.locality = ‘’ OR t.locality IS NULL, a.locality, t.locality) AS locality
FROM oceanbase.__all_tenant AS a
JOIN oceanbase.__all_virtual_database AS d ON ( a.tenant_id = d.tenant_id )
JOIN oceanbase.__all_virtual_table AS t ON (t.tenant_id = d.tenant_id AND t.database_id = d.database_id)
JOIN oceanbase.__all_virtual_meta_table t2 ON (t.tenant_id = t2.tenant_id AND (t.table_id=t2.table_id OR t.tablegroup_id=t2.table_id) AND t2.ROLE IN (1) )
LEFT JOIN oceanbase.__all_virtual_tablegroup AS tg ON (t.tenant_id = tg.tenant_id and t.tablegroup_id = tg.tablegroup_id)
WHERE a.tenant_id IN (1006 ) AND t.table_type IN (3)
– AND d.database_name = ‘T_FUND60PUB’
– and table_name in (‘BMSQL_HISTORY’)
ORDER BY t.tenant_id, tg.tablegroup_name, d.database_name, t.table_name, t2.partition_id
;
备注：

role : 1 表示 主副本； 2 表示备副本
tablegroup_name 为空，表示没有用到表分组。

#4 
[root@172-16-11-134 ~]# obclient -h172.16.11.134 -P2883 -uroot@sys -Doceanbase -A
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 10
Server version: OceanBase_CE 4.0.0.0 (r100000282022112511-dd289d2407609a88b1fcdf2be9e7c384cb8e19d0) (Built Nov 25 2022 11:58:08)

Copyright (c) 2000, 2018, OB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [oceanbase]> SELECT 
    ->     t.tenant_id, a.tenant_name, t.table_name, d.database_name, tg.tablegroup_name , 
    ->     t.part_num , t2.partition_id, t2.ZONE, t2.svr_ip , round(t2.data_size/1024/1024/1024) data_size_gb
    ->     , a.primary_zone , IF(t.locality = '' OR t.locality IS NULL, a.locality, t.locality) AS locality
    -> FROM 
    ->     oceanbase.__all_tenant AS a
    ->     JOIN oceanbase.__all_virtual_database AS d ON ( a.tenant_id = d.tenant_id )
    ->     JOIN oceanbase.__all_virtual_table AS t ON (t.tenant_id = d.tenant_id AND t.database_id = d.database_id)
    ->     JOIN oceanbase.__all_virtual_meta_table t2 ON (t.tenant_id = t2.tenant_id AND (t.table_id=t2.table_id OR t.tablegroup_id=t2.table_id) AND t2.ROLE IN (1) )
    ->     LEFT JOIN oceanbase.__all_virtual_tablegroup AS tg ON (t.tenant_id = tg.tenant_id and t.tablegroup_id = tg.tablegroup_id)
    -> WHERE 
    ->     a.tenant_id IN (1002 ) AND t.table_type IN (3)
    ->     AND d.database_name = 'test'
    ->     and table_name in ('sbtest8')
    -> ORDER BY t.tenant_id, tg.tablegroup_name, d.database_name, t.table_name, t2.partition_id
    -> ;
ERROR 1146 (42S02): Table 'oceanbase.__all_virtual_meta_table' doesn't exist
#5

老师，表不存在

#6

你的是4.0，我给的是3.x版本。

#7

老师，4.0版本的怎么查呢？

#8

4.0 主副本位置。看下是否符合需求 select tenant_name,concat(svr_ip,svr_port) as node,role,count(*) as cnt from __all_ls_meta_table t1,__all_tenant t2 where t1.role=1 and t1.tenant_id=t2.tenant_id group by t2.tenant_name,concat(t1.svr_ip,t1.svr_port),t1.role;

#9

老师，我需要看表级别的

#10

image
image1919×362 13.5 KB

每张表不是都有个的 leader吗？

#11

我没有4.0环境，你可以先show create table __all_virtual_ls_meta_table .
有表字段，就直接修改样例中的SQL就可以看到表级别

资源分配与部署

查看租户资源分配

OceanBase(root@oceanbase)>select MAX_CPU, MIN_CPU, MEMORY_SIZE/1024/1024/1024 from gv$ob_units where tenant_id=1002;
+---------+---------+----------------------------+
| MAX_CPU | MIN_CPU | MEMORY_SIZE/1024/1024/1024 |
+---------+---------+----------------------------+
|       2 |       2 |             3.000000000000 |
+---------+---------+----------------------------+
1 row in set (0.00 sec)

对于 16 核的物理资源，除去 OBProxy 和 sys 租户的 cpu 分配，该租户分配 11 个 CPU 是合理的；租户整体内存 75G，对于小租户而言，也是可以的。

分区分布

--1002租户分区分配情况：
MySQL [oceanbase]> select svr_ip, svr_port, count(1) from __all_virtual_ls_meta_table where tenant_id=1002 group by svr_port;
+----------------+----------+----------+
| svr_ip         | svr_port | count(1) |
+----------------+----------+----------+
| 100.88.105.197 |    40000 |      130 |
| 100.88.105.197 |    40001 |      130 |
| 100.88.105.197 |    40002 |      130 |
+----------------+----------+----------+
3 rows in set (0.00 sec)

--所有租户分区分配情况：
MySQL [oceanbase]> select svr_ip, svr_port, count(1) from __all_virtual_ls_meta_table group by svr_port;
+----------------+----------+----------+
| svr_ip         | svr_port | count(1) |
+----------------+----------+----------+
| 100.88.105.197 |    40000 |      135 |
| 100.88.105.197 |    40001 |      135 |
| 100.88.105.197 |    40002 |      135 |
+----------------+----------+----------+
3 rows in set (0.01 sec)

对于 1002 租户而言，单机上的分配是均衡的，分别为 130 个。整个集群三个机器分区总分布初步看是均衡的。

Leader 分布

MySQL [oceanbase]> select svr_ip, svr_port, count(1) from __all_virtual_ls_meta_table where role=1 and tenant_id=1002 group by svr_port;
+----------------+----------+----------+
| svr_ip         | svr_port | count(1) |
+----------------+----------+----------+
| 100.88.105.197 |    40000 |       43 |
| 100.88.105.197 |    40001 |       44 |
| 100.88.105.197 |    40002 |       43 |
+----------------+----------+----------+
3 rows in set (0.01 sec)

Leader 分布基本是均衡的，符合预期。

https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-0000000001421697

#12

https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000885753
DBA_OB_TABLE_LOCATIONS这个表能看到

#13

可以了，非常感谢

obclient [oceanbase]> select * from DBA_OB_TABLE_LOCATIONS where DATABASE_NAME='test'and ROLE<>'FOLLOWER' and TABLE_NAME like '%sbtest8%'\G
*************************** 1. row ***************************
    DATABASE_NAME: test
       TABLE_NAME: sbtest8
         TABLE_ID: 500008
       TABLE_TYPE: USER TABLE
   PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
       INDEX_NAME: NULL
    DATA_TABLE_ID: NULL
        TABLET_ID: 200007
            LS_ID: 1001
             ZONE: zone1
           SVR_IP: 172.16.11.134
         SVR_PORT: 2882
             ROLE: LEADER
     REPLICA_TYPE: FULL
1 row in set (0.011 sec)
1 个赞