ob上表的主

【 使用环境 】测试环境
【 OB 】
【 使用版本 】4.0.0.0
【问题描述】
±------------------------------------------------+
| observer |
±--------------±--------±-----±------±-------+
| ip | version | port | zone | status |
±--------------±--------±-----±------±-------+
| 172.16.11.134 | 4.0.0.0 | 2881 | zone1 | ACTIVE |
| 172.16.11.135 | 4.0.0.0 | 2881 | zone2 | ACTIVE |
| 172.16.11.136 | 4.0.0.0 | 2881 | zone3 | ACTIVE |
±--------------±--------±-----±------±-------+
在以上拓扑中
请问在OB里创建10张表,这10张表是均匀分布在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 为空,表示没有用到表分组。

1 个赞

老师,请问如何打散表的主呢?

ERROR 1146 (42S02): Table ‘oceanbase.__all_virtual_meta_table’ doesn’t exist

root@sys租户上执行

租户上设置primary-zone,多个zone上实现打散;
表建立分区,多分区,分区会自动分布到多个zone上。

[root@172-16-11-134 ~]#  obclient -h172.16.11.134 -P2883 -uroot@sys
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 39
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 [(none)]> 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 
    ->     t.table_type IN (3)
    ->     AND d.database_name = 'test'
    -> 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

老师,请问怎样在 root@sys租户上执行?

比较晚了~可能得等明天的老师了

1 个赞

不好意思,才看到你的是4.0版本,上边SQL是3.1.x版本的。

4.0看看这个吧

单机用户分区总数量及 Leader 分布

MySQL [oceanbase]> select svr_ip,count(1) from __all_virtual_ls_meta_table where tenant_id=1002 group by svr_ip;
+---------------+----------+
| svr_ip        | count(1) |
+---------------+----------+
| 192.168.30.65 |        1 |
| 192.168.30.66 |        1 |
| 192.168.30.67 |        1 |
+---------------+----------+
3 rows in set 

MySQL [oceanbase]> select svr_ip,count(1) from __all_virtual_ls_meta_table where tenant_id=1001 and role=1 group by svr_ip;
+---------------+----------+
| svr_ip        | count(1) |
+---------------+----------+
| 192.168.30.65 |        5 |
+---------------+----------+
1 row in set 

https://www.oceanbase.com/docs/community-observer-cn-10000000000901668

直接看这个表

1 个赞

谢谢之前的解答;还有请问老师,怎么挪动 role : 1 主副本到 172.16.11.135 机器上?

obclient [oceanbase]> select svr_ip,count(1) from __all_virtual_ls_meta_table where tenant_id=1002 and role=1 group by svr_ip;
+---------------+----------+
| svr_ip        | count(1) |
+---------------+----------+
| 172.16.11.134 |        2 |
| 172.16.11.135 |        1 |
| 172.16.11.136 |        1 |
+---------------+----------+

4.0是以日志流进行切换的 找到你table对应的ls
alter system switch replica leader ls=xxxx server=‘xxxx:xxx’ tenant=‘xxxx’

obclient [oceanbase]> select * from __all_virtual_ls_meta_table where tenant_id=1002 and role=1;
+-----------+-------+---------------+----------+----------------------------+----------------------------+----------+------+----------------------------------------------------------------+-------------+--------------+----------------+----------------+------------------+---------+-------+----------------------+-----------+---------------+
| tenant_id | ls_id | svr_ip        | svr_port | gmt_create                 | gmt_modified               | sql_port | role | member_list                                                    | proposal_id | replica_type | replica_status | restore_status | memstore_percent | unit_id | zone  | paxos_replica_number | data_size | required_size |
+-----------+-------+---------------+----------+----------------------------+----------------------------+----------+------+----------------------------------------------------------------+-------------+--------------+----------------+----------------+------------------+---------+-------+----------------------+-----------+---------------+
|      1002 |     1 | 172.16.11.134 |     2882 | 2022-12-08 16:24:25.712694 | 2022-12-11 20:27:04.529314 |     2881 |    1 | 172.16.11.134:2882:1,172.16.11.135:2882:1,172.16.11.136:2882:1 |           2 |            0 | NORMAL         |              0 |              100 |    1001 | zone1 |                    3 |         0 |             0 |
|      1002 |  1001 | 172.16.11.134 |     2882 | 2022-12-08 16:24:36.490841 | 2022-12-11 20:27:42.360693 |     2881 |    1 | 172.16.11.134:2882:1,172.16.11.135:2882:1,172.16.11.136:2882:1 |           3 |            0 | NORMAL         |              0 |              100 |    1001 | zone1 |                    3 |         0 |             0 |
|      1002 |  1002 | 172.16.11.135 |     2882 | 2022-12-08 16:24:36.591647 | 2022-12-11 20:27:41.860016 |     2881 |    1 | 172.16.11.134:2882:1,172.16.11.135:2882:1,172.16.11.136:2882:1 |           2 |            0 | NORMAL         |              0 |              100 |    1002 | zone2 |                    3 |         0 |             0 |
|      1002 |  1003 | 172.16.11.136 |     2882 | 2022-12-08 16:24:36.663628 | 2022-12-11 20:27:52.706417 |     2881 |    1 | 172.16.11.134:2882:1,172.16.11.135:2882:1,172.16.11.136:2882:1 |           3 |            0 | NORMAL         |              0 |              100 |    1003 | zone3 |                    3 |         0 |             0 |
+-----------+-------+---------------+----------+----------------------------+----------------------------+----------+------+----------------------------------------------------------------+-------------+--------------+----------------+----------------+------------------+---------+-------+----------------------+-----------+---------------+
4 rows in set (0.375 sec)




obclient [oceanbase]> alter system switch replica leader ls=1002 server='172.16.11.135:2882' tenant='1002';
ERROR 5157 (HY000): Unknown tenant

老师,table对应的ls 怎么找呢?

tenant要写租户的名字不是id

首先你可以通过CDB_OBJECTS 查询表对应的Tablet ID (DATA_OBJECT_ID)
然后你可以通过CDB_OB_TABLET_TO_LS 查询Tablet对应的LS信息
最后你可以通过CDB_OB_LS_LOCATIONS 查询对应的LS位置信息
上述每一步在对应的租户下都有DBA_xxx的视图

如果你觉得上面比较繁琐可以直接通过 DBA_OB_TABLE_LOCATIONS 或者 CDB_OB_TABLE_LOCATIONS 查询

可以了谢谢老师
alter system switch replica leader ls=1003 server=‘172.16.11.135:2882’ tenant=‘mysqltenant1’;