【 使用环境 】测试环境
【 OB or 其他组件 】OceanBase
【 使用版本 】OceanBase-Ce-4.2.1-bp11
【问题描述】OCP 告警提示服务器数据盘使用率超限,但并没有大量导入数据,通过OCP上租户管理-数据库管理查看到,业务租户下OceanBase库磁盘使用量达540GB(三副本总量),SYS租户下OceanBase库磁盘使用量才不到1GB
通过SQL语句查询,发现业务租户下,OceanBase库下__all_histogram_stat_history磁盘占用量很大
MySQL [oceanbase]> select /*+ READ_CONSISTENCY(WEAK) */ t1.table_name, concat(round(sum(t2.data_size/1024/1024/1024), 2), 'G') data_size_gb , concat(round(sum(t2.required_size)/1024/1024/1024, 2), 'G') required_size_gb from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2 where t.tenant_id=t1.tenant_id and t1.svr_ip=t2.svr_ip and t1.tenant_id=t2.tenant_id and t1.ls_id=t2.ls_id and t1.tablet_id=t2.tablet_id and t1.role='leader' and t.tenant_id = 1002 and t1.database_name='oceanbase' group by t1.table_name order by 3 desc limit 10;
+------------------------------------------+--------------+------------------+
| table_name | data_size_gb | required_size_gb |
+------------------------------------------+--------------+------------------+
| __all_column_stat_history | 4.45G | 6.25G |
| __all_histogram_stat_history | 131.03G | 171.50G |
| __idx_334_idx_histogram_stat_his_savtime | 10.59G | 15.33G |
| __idx_333_idx_column_stat_his_savtime | 0.42G | 0.67G |
| __all_ddl_operation | 0.05G | 0.09G |
| __all_tablet_checksum | 0.02G | 0.04G |
| __all_table_history | 0.02G | 0.03G |
| __all_histogram_stat | 0.02G | 0.03G |
| __all_column_history | 0.01G | 0.02G |
| __all_column_stat | 0.02G | 0.02G |
+------------------------------------------+--------------+------------------+
10 rows in set (1 min 46.66 sec)
根据文档库中的信息,关于 __all_histogram_stat_history
表数据的产生机制
__all_histogram_stat_history
表存储的是 OceanBase 数据库中历史统计信息的一部分。这些统计信息通常由 DBMS_STATS
自动或手动收集生成,用于优化器选择更优的执行计划。每当统计信息被重新收集时,旧的历史数据会被保存到此类表中
__all_histogram_stat_history直方图的历史信息 在默认情况下,历史统计信息保留31天,并且存在一个每日的定时任务用于清理过期的历史统计信息。
按照官方文档的指南,执行了 purge_stats
,并且通过 alter_stats_history_retention
仅保留一天得统计信息。
但从__all_histogram_stat_history的查询结果看,收集的信息最早可以追溯到20250429
MySQL [oceanbase]> select gmt_create, gmt_modified, tenant_id, savtime, endpoint_value from __all_histogram_stat_history limit 10;
+----------------------------+----------------------------+-----------+----------------------------+----------------+
| gmt_create | gmt_modified | tenant_id | savtime | endpoint_value |
+----------------------------+----------------------------+-----------+----------------------------+----------------+
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 0 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 201001 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 201002 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 201003 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 500002 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 500004 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 538259 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 543799 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 544250 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 544251 |
+----------------------------+----------------------------+-----------+----------------------------+----------------+
10 rows in set (0.00 sec)
检查可用的统计信息窗口
MySQL [oceanbase]> SELECT dbms_stats.get_stats_history_availability() FROM DUAL;
+---------------------------------------------+
| dbms_stats.get_stats_history_availability() |
+---------------------------------------------+
| 2025-06-01 00:00:02.967966 |
+---------------------------------------------+
1 row in set (0.26 sec)
请淇铭老师指导一下,如何迅速清理掉__all_histogram_stat_history表中的数据,降低磁盘的占用率,从历史现象看,这个表的磁盘占用量一致在增加。
6 个赞
之前有个帖子跟淇铭老师一直互动,过去一个月 这个问题还没解决,淇铭老师让开个新帖子跟进一下
3 个赞
淇铭
2025 年6 月 6 日 11:27
#6
你查一下 这个表__all_histogram_stat_history有多少数据
2 个赞
count了一下数量
MySQL [oceanbase]> select count(*) from __all_histogram_stat_history;
+----------+
| count(*) |
+----------+
| 12664 |
+----------+
1 row in set (0.02 sec)
从磁盘占用的角度看,一直在上涨;4月份的时候,占用117GB磁盘,6月份上涨到了171GB
+------------------------------------------+--------------+------------------+
| table_name | data_size_gb | required_size_gb |
+------------------------------------------+--------------+------------------+
| __all_column_stat_history | 3.80G | 5.55G |
| __all_histogram_stat_history | 87.79G | 117.05G |
| __idx_334_idx_histogram_stat_his_savtime | 7.70G | 11.19G |
| __idx_333_idx_column_stat_his_savtime | 0.37G | 0.60G |
| __all_table_stat_history | 0.15G | 0.17G |
| __idx_332_idx_table_stat_his_savtime | 0.07G | 0.13G |
| __all_ddl_operation | 0.05G | 0.09G |
| __all_histogram_stat | 0.02G | 0.03G |
| __all_tablet_checksum | 0.02G | 0.03G |
| __all_column_stat | 0.02G | 0.02G |
+------------------------------------------+--------------+------------------+
+------------------------------------------+--------------+------------------+
| table_name | data_size_gb | required_size_gb |
+------------------------------------------+--------------+------------------+
| __all_column_stat_history | 4.45G | 6.25G |
| __all_histogram_stat_history | 131.03G | 171.50G |
| __idx_334_idx_histogram_stat_his_savtime | 10.59G | 15.33G |
| __idx_333_idx_column_stat_his_savtime | 0.42G | 0.67G |
| __all_ddl_operation | 0.05G | 0.09G |
| __all_tablet_checksum | 0.02G | 0.04G |
| __all_table_history | 0.02G | 0.03G |
| __all_histogram_stat | 0.02G | 0.03G |
| __all_column_history | 0.01G | 0.02G |
| __all_column_stat | 0.02G | 0.02G |
+------------------------------------------+--------------+------------------+
2 个赞
淇铭
2025 年6 月 6 日 15:22
#10
SELECT tenant_id,
job_name,
what,
start_date,
this_date,
last_date,
next_date,
enabled
FROM oceanbase.__all_virtual_tenant_scheduler_job
WHERE tenant_id = {tenant_id}
AND job_name IN (‘OPT_STATS_HISTORY_MANAGER’)
AND job != 0; 查一下这个信息
3 个赞
淇铭:
SELECT tenant_id,
job_name,
what,
start_date,
this_date,
last_date,
next_date,
enabled
FROM oceanbase.__all_virtual_tenant_scheduler_job
WHERE tenant_id = {tenant_id}
AND job_name IN (‘OPT_STATS_HISTORY_MANAGER’)
AND job != 0;
MySQL [oceanbase]> SELECT tenant_id, job_name, what, start_date, this_date, last_date, next_date, enabled FROM oceanbase.__all_virtual_tenant_scheduler_job WHERE tenant_id = 1002 AND job_name IN ('OPT_STATS_HISTORY_MANAGER') AND job != 0;
+-----------+---------------------------+------------------------------+----------------------------+-----------+----------------------------+----------------------------+---------+
| tenant_id | job_name | what | start_date | this_date | last_date | next_date | enabled |
+-----------+---------------------------+------------------------------+----------------------------+-----------+----------------------------+----------------------------+---------+
| 1002 | OPT_STATS_HISTORY_MANAGER | DBMS_STATS.PURGE_STATS(NULL) | 2024-03-01 14:53:56.700318 | NULL | 2025-02-21 13:35:32.916801 | 2025-02-21 14:53:56.700318 | 1 |
+-----------+---------------------------+------------------------------+----------------------------+-----------+----------------------------+----------------------------+---------+
1 row in set (0.05 sec)
MySQL [oceanbase]> SELECT tenant_id, job_name, what, start_date, this_date, last_date, next_date, enabled FROM oceanbase.__all_virtual_tenant_scheduler_job WHERE tenant_id = 1001 AND job_name IN ('OPT_STATS_HISTORY_MANAGER') AND job != 0;
+-----------+---------------------------+------------------------------+----------------------------+-----------+----------------------------+----------------------------+---------+
| tenant_id | job_name | what | start_date | this_date | last_date | next_date | enabled |
+-----------+---------------------------+------------------------------+----------------------------+-----------+----------------------------+----------------------------+---------+
| 1001 | OPT_STATS_HISTORY_MANAGER | DBMS_STATS.PURGE_STATS(NULL) | 2024-03-01 14:53:41.746863 | NULL | 2025-06-06 14:53:41.752677 | 2025-06-07 14:53:41.746863 | 1 |
+-----------+---------------------------+------------------------------+----------------------------+-----------+----------------------------+----------------------------+---------+
1 row in set (0.07 sec)
1 个赞
1001租户是SYS租户,1002是业务租户,我在业务租户的root用户下手动执行过DBMS_STATS.PURGE_STATS,但最终超时了
MySQL [oceanbase]> CALL DBMS_STATS.PURGE_STATS('2025-06-01 00:00:00');
ERROR 4012 (HY000): Timeout
1 个赞
淇铭
2025 年6 月 6 日 15:40
#16
按照这个步骤执行一下
1、root@sys 登录,
select * from _all_tenant;
2、然后切到问题租户的meta租户里面1
alter system change tenant meta ter;
3、执行如下sql
update _all_tenant_scheduler_job set failures=0,state=‘SCHEDULED’,flag=0;
4、然后将出现问题的几个自动收集统计信息任分别执行
然后再执行每个有问题任务的,距离周一将时间写成下周一,具体时间看您需求
update all_tenant_scheduler_job set next_date=‘2025-06-02 03:00:00’ where job_name=‘MONDAY_WINDOW’;
然后以此类推按照第4步骤,把其他的有问题的周三周五周六周日,每日定时任务修改
应该是调度的已知问题
4 个赞
mysql> update __all_tenant_scheduler_job set failures=0,state='SCHEDULED',flag=0;
Query OK, 9 rows affected (0.08 sec)
Rows matched: 17 Changed: 9 Warnings: 0
mysql> update __all_tenant_scheduler_job set next_date='2025-06-02 03:00:00' where job_name='MONDAY_WINDOW';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
依次类推把周二到周日的都依据日期更新了
这个只能等待调度生效是吗?有手动处理的方式进行清理吗?
1 个赞
淇铭
2025 年6 月 6 日 15:52
#18
不用手动处理了 等待生效吧 你现在空间不是的够的么
1 个赞
好的,那明后天我们再观察一下再来本贴反馈,谢谢淇铭老师
1 个赞
mysql> select * from __all_tenant_scheduler_job where job_name = 'SUNDAY_WINDOW' \G
*************************** 1. row ***************************
gmt_create: 2024-02-29 14:53:41.760017
gmt_modified: 2025-06-06 15:48:29.335080
tenant_id: 0
job_name: SUNDAY_WINDOW
job: 0
lowner: root
powner: root
cowner: oceanbase
last_date: NULL
this_date: NULL
next_date: 2025-06-08 03:00:00.000000
total: 0
interval#: FREQ=WEEKLY; INTERVAL=1
failures: 0
flag: 0
what: NULL
nlsenv:
charenv: NULL
field1:
scheduler_flags: 0
exec_env: 281018368,45,45,45,
job_style: REGULER
program_name:
job_type: STORED_PROCEDURE
job_action: NULL
number_of_argument: 0
start_date: 2024-03-03 06:00:00.000000
repeat_interval: FREQ=WEEKLY; INTERVAL=1
end_date: 4000-01-01 00:00:00.000000
job_class: DEFAULT_JOB_CLASS
enabled: 1
auto_drop: 0
state: SCHEDULED
run_count: NULL
retry_count: NULL
last_run_duration: NULL
max_run_duration: 72000
comments: used to auto gather table stats
credential_name:
destination_name:
interval_ts: 604800000000
user_id: -1
database_id: -1
max_failures: 0
*************************** 2. row ***************************
gmt_create: 2024-02-29 14:53:41.760017
gmt_modified: 2025-06-08 06:00:01.323295
tenant_id: 0
job_name: SUNDAY_WINDOW
job: 7
lowner: root
powner: root
cowner: oceanbase
last_date: 2025-06-08 06:00:00.019235
this_date: NULL
next_date: 2025-06-15 06:00:00.000000
total: 365528084
interval#: FREQ=WEEKLY; INTERVAL=1
failures: 0
flag: 0
what: DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(72000000000)
nlsenv:
charenv: NULL
field1:
scheduler_flags: 0
exec_env: 281018368,45,45,45,
job_style: REGULER
program_name:
job_type: STORED_PROCEDURE
job_action: DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(72000000000)
number_of_argument: 0
start_date: 2024-03-03 06:00:00.000000
repeat_interval: FREQ=WEEKLY; INTERVAL=1
end_date: 4000-01-01 00:00:00.000000
job_class: DEFAULT_JOB_CLASS
enabled: 1
auto_drop: 0
state: SCHEDULED
run_count: NULL
retry_count: NULL
last_run_duration: NULL
max_run_duration: 72000
comments: used to auto gather table stats
credential_name:
destination_name:
interval_ts: 604800000000
user_id: -1
database_id: -1
max_failures: 0
2 rows in set (0.01 sec)
上周五按照淇铭老师说的,
update _all_tenant_scheduler_job set failures=0,state=‘SCHEDULED’,flag=0;
省略周一至周六的更新语句
update all_tenant_scheduler_job set next_date=‘2025-06-08 03:00:00’ where job_name=‘SUNDAY_WINDOW’;
周一来看,发现清理操作并没有生效,问题租户下的直方图统计信息历史,还是起始于2025年4月29日,请老师再帮忙看看
MySQL [oceanbase]> select /*+ READ_CONSISTENCY(WEAK) */ t1.table_name, concat(round(sum(t2.data_size/1024/1024/1024), 2), 'G') data_size_gb , concat(round(sum(t2.required_size)/1024/1024/1024, 2), 'G') required_size_gb from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2 where t.tenant_id=t1.tenant_id and t1.svr_ip=t2.svr_ip and t1.tenant_id=t2.tenant_id and t1.ls_id=t2.ls_id and t1.tablet_id=t2.tablet_id and t1.role='leader' and t.tenant_id = 1002 and t1.database_name='oceanbase' group by t1.table_name order by 3 desc limit 10;
+------------------------------------------+--------------+------------------+
| table_name | data_size_gb | required_size_gb |
+------------------------------------------+--------------+------------------+
| __all_column_stat_history | 4.30G | 6.25G |
| __all_histogram_stat_history | 130.91G | 171.50G |
| __idx_334_idx_histogram_stat_his_savtime | 10.57G | 15.43G |
| __idx_333_idx_column_stat_his_savtime | 0.40G | 0.65G |
| __all_ddl_operation | 0.05G | 0.09G |
| __all_tablet_checksum | 0.03G | 0.03G |
| __all_histogram_stat | 0.02G | 0.03G |
| __all_column_history | 0.01G | 0.02G |
| __all_table_history | 0.02G | 0.02G |
| __all_column_stat | 0.02G | 0.02G |
+------------------------------------------+--------------+------------------+
10 rows in set (6 min 22.07 sec)
MySQL [oceanbase]> select gmt_create, gmt_modified, tenant_id, savtime, endpoint_value from __all_histogram_stat_history limit 10;
+----------------------------+----------------------------+-----------+----------------------------+----------------+
| gmt_create | gmt_modified | tenant_id | savtime | endpoint_value |
+----------------------------+----------------------------+-----------+----------------------------+----------------+
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 0 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 201001 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 201002 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 201003 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 500002 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 500004 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 538259 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 543799 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 544250 |
| 2025-04-29 10:11:06.105851 | 2025-04-29 10:11:06.105851 | 0 | 2025-04-29 10:11:06.085361 | 544251 |
+----------------------------+----------------------------+-----------+----------------------------+----------------+
1 个赞
淇铭
2025 年6 月 9 日 10:21
#22
SELECT tenant_id,
job_name,
what,
start_date,
this_date,
last_date,
next_date,
enabled
FROM oceanbase.__all_virtual_tenant_scheduler_job
WHERE tenant_id = {tenant_id}
AND job_name IN (‘OPT_STATS_HISTORY_MANAGER’)
AND job != 0;
这个信息 在查看一下
1 个赞