自学内容网 自学内容网
自学内容网 > 正文

OceanBase 中常用的查询语句

🕗 发布于 2024-11-22 15:50 oceanbase  分布式数据库  SQL  查询语句  

本文汇总整理了一些 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;

原文地址：https://blog.csdn.net/OceanBaseGFBK/article/details/141103953

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024