本文汇总整理了一些 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_gbFROM __all_virtual_server_stat aJOIN __all_server b ON (a.svr_ip=b.svr_ipAND 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_GBFROM 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
// 创建UnitMySQL [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 enoughSELECT 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_weightFROM __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_nameFROM __all_resource_pool t1JOIN __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_nameFROM __all_resource_pool t1JOIN __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_gbFROM __all_virtual_server_stat aJOIN __all_server b ON (a.svr_ip=b.svr_ipAND 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_ipFROM __all_resource_pool poolINNER JOIN __all_tenant tenant ON pool.tenant_id=tenant.tenant_idINNER JOIN __all_unit unit ON pool.resource_pool_id=unit.resource_pool_idWHERE pool.tenant_id>=1ORDER 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_GBfrom gv$memstorewhere tenant_id >1000 or TENANT_ID=1order 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 ,CASEWHEN ROLE=1 THEN 'leader'WHEN ROLE=2 THEN 'follower'ELSE NULLEND AS ROLE,tab.primary_zoneFROM __all_virtual_meta_table metaINNER JOIN __all_tenant tenant ON meta.tenant_id=tenant.tenant_idINNER JOIN __all_virtual_table tab ON meta.tenant_id=tab.tenant_idAND meta.table_id=tab.table_idWHERE 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 ,CASEWHEN ROLE=1 THEN 'leader'WHEN ROLE=2 THEN 'follower'ELSE NULLEND AS ROLE,tab.primary_zoneFROM __all_virtual_meta_table metaINNER JOIN __all_tenant tenant ON meta.tenant_id=tenant.tenant_idINNER JOIN __all_virtual_table tab ON meta.tenant_id=tab.tenant_idINNER JOIN __all_tablegroup tg on (tab.tenant_id = tg.tenant_id and tab.tablegroup_id = tg.tablegroup_id)AND meta.table_id=tab.table_idWHERE 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_mbFROM gv$sql_auditWHERE tenant_id=1AND user_name IN ('u_sysbench')ORDER BY request_time DESC LIMIT 100;
