ob4.2.2_CE运行一段时间后，现在操作数据库表非常缓慢

kneenan · 2024 年4 月 16 日 14:31

【使用环境】生产环境
【 OB or 其他组件】OB4.2.2_CE
【使用版本】OB4.2.2_CE社区版
【问题描述】运行了半年左右，现在新增数据库，操作数据库表，反应都比较慢，表现为读取表慢，写入表也慢。表记录数月70万条。对比刚开始运行的4.2.2_CE版本，能明显感觉操作慢。不存在索引，分区的区分。用navicat打开一个数据库，里面含1162张表，打开耗时2-3秒，而同样navicat打开mysql8的1201张表的库，秒开。
【复现路径】系统是在内网环境。
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

obpilot · 2024 年4 月 16 日 15:12

可以先看看当前的集群资源分布情况。

select zone,concat(SVR_IP,':',SVR_PORT) observer,
	cpu_capacity_max cpu_total,cpu_assigned_max cpu_assigned,
	cpu_capacity-cpu_assigned_max as cpu_free,
	round(memory_limit/1024/1024/1024,2) as memory_total,
	round((memory_limit-mem_capacity)/1024/1024/1024,2) as system_memory,
	round(mem_assigned/1024/1024/1024,2) as mem_assigned,
	round((mem_capacity-mem_assigned)/1024/1024/1024,2) as memory_free,
	round(log_disk_capacity/1024/1024/1024,2) as log_disk_capacity,
	round(log_disk_assigned/1024/1024/1024,2) as log_disk_assigned,
	round((log_disk_capacity-log_disk_assigned)/1024/1024/1024,2) as log_disk_free,
	round((data_disk_capacity/1024/1024/1024),2) as data_disk,
	round((data_disk_in_use/1024/1024/1024),2) as data_disk_used,
	round((data_disk_capacity-data_disk_in_use)/1024/1024/1024,2) as data_disk_free
from gv$ob_servers;

select t1.name resource_pool_name, t2.`name` unit_config_name, 
	t2.max_cpu, t2.min_cpu, 
	round(t2.memory_size/1024/1024/1024,2) mem_size_gb,
	round(t2.log_disk_size/1024/1024/1024,2) log_disk_size_gb, t2.max_iops, 
	t2.min_iops, 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;