本文汇总整理了一些 OceanBase 中的常用查询语句，包括租户创建、转储与合并、表相关等场景，希望帮大家解决日常运维操作中的常见的问题。
租户类
- OceanBase支持多租户架构，其中默认存在一个名为sys的租户。为了满足业务使用需求，我们通常需自行创建一个新的租户。创建完整租户的过程遵循一定的顺序：首先创建unit，接着配置resource pool，最后创建tenant。然而，在初始创建租户阶段，可能会遇到的一个常见问题就是资源不足的错误。因此，在着手创建之前，需要先确认资源的可用性情况。
SELECT a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, cpu_assigned, (cpu_total-cpu_assigned) cpu_free,
mem_total/1024/1024/1024 mem_total_gb,
mem_assigned/1024/1024/1024 mem_assign_gb,
(mem_total-mem_assigned)/1024/1024/1024 mem_free_gb
FROM __all_virtual_server_stat a
JOIN __all_server b ON (a.svr_ip=b.svr_ip
AND a.svr_port=b.svr_port)
ORDER BY a.zone,
a.svr_ip ;
// 4.0之后:
SELECT SVR_IP ,
SVR_PORT ,
ZONE ,
SQL_PORT ,
CPU_CAPACITY ,
CPU_CAPACITY_MAX ,
CPU_ASSIGNED ,
CPU_ASSIGNED_MAX ,
MEM_CAPACITY/1024/1024/1024 as MEM_CAPACITY_GB ,
MEM_ASSIGNED/1024/1024/1024 as MEM_ASSIGNED_GB,
LOG_DISK_CAPACITY/1024/1024/1024 as LOG_DISK_CAPACITY_GB ,
LOG_DISK_ASSIGNED/1024/1024/1024 as LOG_DISK_ASSIGNED_GB ,
LOG_DISK_IN_USE/1024/1024/1024 as LOG_DISK_IN_USE_GB ,
DATA_DISK_CAPACITY/1024/1024/1024 as DATA_DISK_CAPACITY_GB ,
DATA_DISK_IN_USE/1024/1024/1024 as DATA_DISK_IN_USE_GB,
DATA_DISK_HEALTH_STATUS ,
MEMORY_LIMIT/1024/1024/1024 as MEMORY_LIMIT_GB
FROM GV$OB_SERVERS;
//结果如下面看到 cpu、mem、disk 可使用最大资源和已使用情况，后面创建租户时就知道最大能使用的资源了。:
+---------------+----------+-------+----------+--------------+------------------+--------------+------------------+-----------------+-----------------+----------------------+----------------------+--------------------+-----------------------+---------------------+-------------------------+-----------------+
| SVR_IP | SVR_PORT | ZONE | SQL_PORT | CPU_CAPACITY | CPU_CAPACITY_MAX | CPU_ASSIGNED | CPU_ASSIGNED_MAX | MEM_CAPACITY_GB | MEM_ASSIGNED_GB | LOG_DISK_CAPACITY_GB | LOG_DISK_ASSIGNED_GB | LOG_DISK_IN_USE_GB | DATA_DISK_CAPACITY_GB | DATA_DISK_IN_USE_GB | DATA_DISK_HEALTH_STATUS | MEMORY_LIMIT_GB |
+---------------+----------+-------+----------+--------------+------------------+--------------+------------------+-----------------+-----------------+----------------------+----------------------+--------------------+-----------------------+---------------------+-------------------------+-----------------+
| 10.140.118.7 | 2882 | zone3 | 2881 | 16 | 16 | 1 | 1 | 8.000000000000 | 2.000000000000 | 30.000000000000 | 2.000000000000 | 1.625000000000 | 60.000000000000 | 1.257812500000 | NORMAL | 10.000000000000 |
| 10.140.114.12 | 2882 | zone1 | 2881 | 16 | 16 | 1 | 1 | 8.000000000000 | 2.000000000000 | 30.000000000000 | 2.000000000000 | 1.625000000000 | 19.990234375000 | 1.271484375000 | NORMAL | 10.000000000000 |
| 10.140.60.14 | 2882 | zone2 | 2881 | 16 | 16 | 1 | 1 | 8.000000000000 | 2.000000000000 | 30.000000000000 | 2.000000000000 | 1.625000000000 | 60.000000000000 | 1.257812500000 | NORMAL | 10.000000000000 |
+---------------+----------+-------+----------+--------------+------------------+--------------+------------------+-----------------+-----------------+----------------------+----------------------+--------------------+-----------------------+---------------------+-------------------------+-----------------+
3 rows in set (0.005 sec)
```
- 创建Unit
// 创建Unit
MySQL [oceanbase]> create resource unit S2 max_cpu=2, min_cpu=2, max_memory='4G', min_memory='2G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='50G';
Query OK, 0 rows affected (0.009 sec)
//4.0之后:
obclient [oceanbase]> create resource unit S2 max_cpu=7, min_cpu=2, MEMORY_SIZE='8G', max_iops=10000, min_iops=10000;
Query OK, 0 rows affected (0.011 sec)
// 目前对IOPS的限制是不生效的- 创建资源池
//创建资源池，unit_num根据自身实际情况修改，unit_num不超过zone内机器数量即可
MySQL [oceanbase]> create resource pool pool_2 unit='S2', unit_num=1;
Query OK, 0 rows affected (0.021 sec)在这里会经常遇到报错，说资源不足，也可以单独查看unit资源配置情况：
obclient [oceanbase]> create resource unit S2 max_cpu=7, min_cpu=2, MEMORY_SIZE='10G', max_iops=10000, min_iops=10000;
Query OK, 0 rows affected (0.010 sec)
obclient [oceanbase]>
obclient [oceanbase]> create resource pool pool_2 unit='S2', unit_num=1;
ERROR 4733 (HY000): zone 'zone1' resource not enough to hold 1 unit. You can check resource info by views: DBA_OB_UNITS, GV$OB_UNITS, GV$OB_SERVERS.
server '"10.140.114.12:2882"' MEMORY resource not enough
SELECT gmt_create,
gmt_modified,
unit_config_id,
name,
max_cpu,
min_cpu,
memory_size/1024/1024/1024 AS memory_size_gb,
log_disk_size/1024/1024/1024 AS log_disk_size_gb ,
max_iops,
min_iops,
iops_weight
FROM __all_unit_config ;
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-----------------+------------------+----------+----------+-------------+
| gmt_create | gmt_modified | unit_config_id | name | max_cpu | min_cpu | memory_size_gb | log_disk_size_gb | max_iops | min_iops | iops_weight |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-----------------+------------------+----------+----------+-------------+
| 2023-02-19 10:23:07.392620 | 2023-02-19 10:23:07.392620 | 1 | sys_unit_config | 1 | 1 | 2.000000000000 | 2.000000000000 | 10000 | 10000 | 1 |
| 2023-02-23 11:09:04.767938 | 2023-02-23 11:09:04.767938 | 1002 | S2 | 7 | 2 | 10.000000000000 | 30.000000000000 | 10000 | 10000 | 0 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-----------------+------------------+----------+----------+-------------+
向上面这种情况，看到Unit的memory_size超过了MEM_CAPACITY-MEM_ASSIGNED可用的内存大小，这块有两种方法调整内存大小:
1. 删除Unit重新创建，降低内存大小
2. 使用命令调整Unit内存大小，但需要注意log_disk_size默认是Unit创建时内存大小的三倍，调整内存后log_disk_size并不会自动变化，可根据情况同样用命令调整
修改unit资源限制:
ALTER RESOURCE unit S2 MEMORY_SIZE='6G';
ALTER RESOURCE unit S2 log_disk_size='18G';- 创建租户
MySQL [oceanbase]> CREATE TENANT IF NOT EXISTS tenant_2
-> charset='utf8mb4',
-> replica_num=3,
-> zone_list=('zone1','zone2','zone3'),
-> primary_zone='RANDOM',
-> resource_pool_list=('pool_2');
Query OK, 0 rows affected (1.477 sec)- 以Pool维度查看资源使用情况:
SELECT t1.name resource_pool_name,
t2.`name` unit_config_name,
t2.max_cpu,
t2.min_cpu,
t2.max_memory/1024/1024/1024 max_mem_gb,
t2.min_memory/1024/1024/1024 min_mem_gb,
t3.unit_id,
t3.zone,
concat(t3.svr_ip,':',t3.`svr_port`) observer,
t4.tenant_id,
t4.tenant_name
FROM __all_resource_pool t1
JOIN __all_unit_config t2 ON (t1.unit_config_id=t2.unit_config_id)
JOIN __all_unit t3 ON (t1.`resource_pool_id` = t3.`resource_pool_id`)
LEFT JOIN __all_tenant t4 ON (t1.tenant_id=t4.tenant_id)
ORDER BY t1.`resource_pool_id`,
t2.`unit_config_id`,
t3.unit_id ;
// 4.0之后
SELECT t1.name resource_pool_name,
t2.`name` unit_config_name,
t2.max_cpu,
t2.min_cpu,
t2.memory_size/1024/1024/1024 memory_size,
t3.unit_id,
t3.zone,
concat(t3.svr_ip,':',t3.`svr_port`) observer,
t4.tenant_id,
t4.tenant_name
FROM __all_resource_pool t1
JOIN __all_unit_config t2 ON (t1.unit_config_id=t2.unit_config_id)
JOIN __all_unit t3 ON (t1.`resource_pool_id` = t3.`resource_pool_id`)
LEFT JOIN __all_tenant t4 ON (t1.tenant_id=t4.tenant_id)
ORDER BY t1.`resource_pool_id`,
t2.`unit_config_id`,
t3.unit_id ;- 以zone维度查看资源使用情况(适用于3.x版本):
SELECT a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, cpu_assigned, (cpu_total-cpu_assigned) cpu_free,
mem_total/1024/1024/1024 mem_total_gb,
mem_assigned/1024/1024/1024 mem_assign_gb,
(mem_total-mem_assigned)/1024/1024/1024 mem_free_gb
FROM __all_virtual_server_stat a
JOIN __all_server b ON (a.svr_ip=b.svr_ip
AND a.svr_port=b.svr_port)
ORDER BY a.zone,
a.svr_ip ;- 查看租户对应的Unit数量，Zone信息
SELECT pool.tenant_id,
tenant.tenant_name,
name AS pool_name,
unit_config_id,
unit_count,
unit.unit_id,
pool.zone_list,
unit.svr_ip
FROM __all_resource_pool pool
INNER JOIN __all_tenant tenant ON pool.tenant_id=tenant.tenant_id
INNER JOIN __all_unit unit ON pool.resource_pool_id=unit.resource_pool_id
WHERE pool.tenant_id>=1
ORDER BY tenant.tenant_name,
zone_list;转储和合并
- 查看租户内存使用情况,当MEMSTORE_USED>FREEZE_TRIGGER时就会触发转储
select /*+ READ_CONSISTENCY(WEAK),query_timeout(100000000) */ TENANT_ID,IP,
round(ACTIVE/1024/1024/1024,2)ACTIVE_GB,
round(TOTAL/1024/1024/1024,2) TOTAL_GB,
round(FREEZE_TRIGGER/1024/1024/1024,2) FREEZE_TRIGGER_GB,
round(TOTAL/FREEZE_TRIGGER*100,2) percent_trigger,
round(MEM_LIMIT/1024/1024/1024,2) MEM_LIMIT_GB
from gv$memstore
where tenant_id >1000 or TENANT_ID=1
order by tenant_id,TOTAL_GB desc;
//4.0之后
select round(ACTIVE_SPAN/1024/1024/1024,2) as ACTIVE_SPAN_GB , round(FREEZE_TRIGGER/1024/1024/1024,2) as FREEZE_TRIGGER_GB, round(MEMSTORE_USED/1024/1024/1024,2) as MEMSTORE_USED_GB , round(MEMSTORE_LIMIT/1024/1024/1024, 2) as MEMSTORE_LIMIT_GB from GV$OB_MEMSTORE where tenant_id = 1002;
+----------------+-------------------+------------------+-------------------+
| ACTIVE_SPAN_GB | FREEZE_TRIGGER_GB | MEMSTORE_USED_GB | MEMSTORE_LIMIT_GB |
+----------------+-------------------+------------------+-------------------+
| 0.04 | 0.30 | 0.04 | 3.00 |
| 0.03 | 0.30 | 0.03 | 3.00 |
| 0.03 | 0.30 | 0.03 | 3.00 |
+----------------+-------------------+------------------+-------------------+
3 rows in set (0.003 sec)- 查看MemStore使用率达到freeze_trigger_percentage而触发的租户级转储
select * from __all_server_event_history where event like '%merge%' or event like '%minor%' order by gmt_create desc limit 10;- 可以单独针对一张表，查看SSTable的情况，先通过oceanbase.CDB_OB_TABLE_LOCATIONS查看表的TABLET_ID：
obclient [oceanbase]> select * from oceanbase.CDB_OB_TABLE_LOCATIONS where tenant_id = 1002 and database_name = 'sysbenchdb';
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+----------+--------------+
| TENANT_ID | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP | SVR_PORT | ROLE | REPLICA_TYPE |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+----------+--------------+
| 1002 | sysbenchdb | sbtest1 | 500015 | USER TABLE | NULL | NULL | NULL | NULL | 200008 | 1002 | zone1 | 10.140.114.12 | 2882 | FOLLOWER | FULL |
| 1002 | sysbenchdb | sbtest1 | 500015 | USER TABLE | NULL | NULL | NULL | NULL | 200008 | 1002 | zone2 | 10.140.60.14 | 2882 | LEADER | FULL |
| 1002 | sysbenchdb | sbtest1 | 500015 | USER TABLE | NULL | NULL | NULL | NULL | 200008 | 1002 | zone3 | 10.140.118.7 | 2882 | FOLLOWER | FULL |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+----------+--------------+
3 rows in set (0.023 sec)当达到minor_compact_trigger设置个数后，会触发MINOR_MERGE或MINOR_MERGE类型的转储
obclient [oceanbase]> SELECT count(*) , type FROM oceanbase.GV$OB_TABLET_COMPACTION_HISTORY where tenant_id = 1002 AND TABLET_ID = 2000068 and svr_ip = '10.140.114.12' group by type;
+----------+------------+
| count(*) | type |
+----------+------------+
| 6 | MINI_MERGE |
+----------+------------+
1 row in set (0.005 sec)
obclient [oceanbase]> SELECT count(*) , type FROM oceanbase.GV$OB_TABLET_COMPACTION_HISTORY where tenant_id = 1002 AND TABLET_ID = 200008 and svr_ip = '10.140.114.12' group by type;
+----------+-------------+
| count(*) | type |
+----------+-------------+
| 7 | MINI_MERGE |
| 1 | MINOR_MERGE |
+----------+-------------+
2 rows in set (0.004 sec)手工执行合并:
alter system major freeze;查看合并进程情况:
select * from oceanbase.__all_zone where name = 'merge_status';
4.0+
SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION\G表相关
OB中有分区表，每个分区表都是三副本放到不同的节点上，有Leader和Follower之分，可以用过下面语句查看分区的分部情况:
SELECT tenant.tenant_name,
meta.table_id,
tab.table_name,
partition_id,
ZONE,
concat(svr_ip, ':', svr_port) observer ,
CASE
WHEN ROLE=1 THEN 'leader'
WHEN ROLE=2 THEN 'follower'
ELSE NULL
END AS ROLE,
tab.primary_zone
FROM __all_virtual_meta_table meta
INNER JOIN __all_tenant tenant ON meta.tenant_id=tenant.tenant_id
INNER JOIN __all_virtual_table tab ON meta.tenant_id=tab.tenant_id
AND meta.table_id=tab.table_id
WHERE tenant.tenant_id='1001'
ORDER BY tenant.tenant_name,
TABLE_NAME,
partition_id,
ZONE ;
4.0之后 ， sys租户中查询
select * from oceanbase.CDB_OB_TABLE_LOCATIONS where tenant_id = 1002 and database_name = 'sysbenchdb';
用户租户内查询
obclient [oceanbase]> select * from DBA_OB_TABLE_LOCATIONS where database_name = 'sysbenchdb';
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+----------+--------------+
| DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP | SVR_PORT | ROLE | REPLICA_TYPE |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+----------+--------------+
| sysbenchdb | cust_info | 500020 | USER TABLE | p0 | NULL | NULL | NULL | 200017 | 1001 | zone1 | 10.140.114.12 | 2882 | LEADER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p0 | NULL | NULL | NULL | 200017 | 1001 | zone2 | 10.140.60.14 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p0 | NULL | NULL | NULL | 200017 | 1001 | zone3 | 10.140.118.7 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p1 | NULL | NULL | NULL | 200018 | 1001 | zone1 | 10.140.114.12 | 2882 | LEADER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p1 | NULL | NULL | NULL | 200018 | 1001 | zone2 | 10.140.60.14 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p1 | NULL | NULL | NULL | 200018 | 1001 | zone3 | 10.140.118.7 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p2 | NULL | NULL | NULL | 200019 | 1002 | zone1 | 10.140.114.12 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p2 | NULL | NULL | NULL | 200019 | 1002 | zone2 | 10.140.60.14 | 2882 | LEADER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p2 | NULL | NULL | NULL | 200019 | 1002 | zone3 | 10.140.118.7 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p3 | NULL | NULL | NULL | 200020 | 1002 | zone1 | 10.140.114.12 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p3 | NULL | NULL | NULL | 200020 | 1002 | zone2 | 10.140.60.14 | 2882 | LEADER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p3 | NULL | NULL | NULL | 200020 | 1002 | zone3 | 10.140.118.7 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p4 | NULL | NULL | NULL | 200021 | 1003 | zone1 | 10.140.114.12 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p4 | NULL | NULL | NULL | 200021 | 1003 | zone2 | 10.140.60.14 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p4 | NULL | NULL | NULL | 200021 | 1003 | zone3 | 10.140.118.7 | 2882 | LEADER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p5 | NULL | NULL | NULL | 200022 | 1003 | zone1 | 10.140.114.12 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p5 | NULL | NULL | NULL | 200022 | 1003 | zone2 | 10.140.60.14 | 2882 | FOLLOWER | FULL |
| sysbenchdb | cust_info | 500020 | USER TABLE | p5 | NULL | NULL | NULL | 200022 | 1003 | zone3 | 10.140.118.7 | 2882 | LEADER | FULL |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+----------+--------------+
18 rows in set (0.008 sec)- 查看表分布情况(tablegroup，适用于3.x版本):
SELECT tenant.tenant_name,
meta.table_id,
tab.table_name,
tg.tablegroup_name,
partition_id,
ZONE,
concat(svr_ip, ':', svr_port) observer ,
CASE
WHEN ROLE=1 THEN 'leader'
WHEN ROLE=2 THEN 'follower'
ELSE NULL
END AS ROLE,
tab.primary_zone
FROM __all_virtual_meta_table meta
INNER JOIN __all_tenant tenant ON meta.tenant_id=tenant.tenant_id
INNER JOIN __all_virtual_table tab ON meta.tenant_id=tab.tenant_id
INNER JOIN __all_tablegroup tg on (tab.tenant_id = tg.tenant_id and tab.tablegroup_id = tg.tablegroup_id)
AND meta.table_id=tab.table_id
WHERE tenant.tenant_id='1'
ORDER BY tenant.tenant_name,
TABLE_NAME,
partition_id,
ZONE ;- 查看sql执行情况(适用于3.x版本):
SELECT /*+ read_consistency(weak) query_timeout(1000000000) */ usec_to_time(request_time) req_time,
svr_Ip,
trace_id,
sid,
client_ip,
tenant_id,
tenant_name,
user_name,
db_name,
query_sql,
affected_rows,
ret_code,
event,
STATE,
elapsed_time,
execute_time,
queue_time,
decode_time,
get_plan_time,
block_cache_hit,
bloom_filter_cache_Hit,
block_index_cache_hit,
disk_reads,
retry_cnt,
table_scan,
memstore_read_row_count,
ssstore_read_row_count,
round(request_memory_used/1024/1024) req_mem_mb
FROM gv$sql_audit
WHERE tenant_id=1
AND user_name IN ('u_sysbench')
ORDER BY request_time DESC LIMIT 100;