__all_histogram_stat_history表异常占用大量磁盘空间

达瓦里希1001 · 2025 年6 月 5 日 18:24

【使用环境】测试环境
【 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表中的数据，降低磁盘的占用率，从历史现象看，这个表的磁盘占用量一致在增加。

AntTech_R8AHNC · 2025 年6 月 5 日 20:58

都指定老师回答了

达瓦里希1001 · 2025 年6 月 6 日 09:18

之前有个帖子跟淇铭老师一直互动，过去一个月这个问题还没解决，淇铭老师让开个新帖子跟进一下

独善其身 · 2025 年6 月 6 日 10:15

有表分析功能,重新生成一下直方图看看

淇铭 · 2025 年6 月 6 日 11:27

你查一下这个表__all_histogram_stat_history有多少数据

达瓦里希1001 · 2025 年6 月 6 日 13:45

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            |
+------------------------------------------+--------------+------------------+

淇铭 · 2025 年6 月 6 日 14:56

好的我问一下相关的同学稍后在同步给你

淇铭 · 2025 年6 月 6 日 15: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; 查一下这个信息

达瓦里希1001 · 2025 年6 月 6 日 15:25

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)

达瓦里希1001 · 2025 年6 月 6 日 15:28

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

淇铭 · 2025 年6 月 6 日 15:28

先别急我们先排查一下看看

达瓦里希1001 · 2025 年6 月 6 日 15:29

好的，谢谢淇铭老师

淇铭 · 2025 年6 月 6 日 15:38

看起来是调度的问题调度没有执行

淇铭 · 2025 年6 月 6 日 15:40

按照这个步骤执行一下

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步骤，把其他的有问题的周三周五周六周日，每日定时任务修改
应该是调度的已知问题

达瓦里希1001 · 2025 年6 月 6 日 15:49

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

依次类推把周二到周日的都依据日期更新了

这个只能等待调度生效是吗？有手动处理的方式进行清理吗？

淇铭 · 2025 年6 月 6 日 15:52

不用手动处理了等待生效吧你现在空间不是的够的么

达瓦里希1001 · 2025 年6 月 6 日 15:52

好的，那明后天我们再观察一下再来本贴反馈，谢谢淇铭老师

淇铭 · 2025 年6 月 6 日 15:56

好的观察一下应该是已知问题导致的调度没有执行