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

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 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

都指定老师回答了

3 个赞
#4

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

3 个赞
#5

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

2 个赞
#6

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

2 个赞
#7

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 个赞
#8

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

2 个赞
#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 个赞
#11 
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 个赞
#12

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 个赞
#13

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

1 个赞
#14

好的，谢谢淇铭老师

1 个赞
#15

image
image3780×296 31.3 KB

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

1 个赞
#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 个赞
#17 
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 个赞
#18

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

1 个赞
#19

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

1 个赞
#20

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

1 个赞
#21 
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 个赞
#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 个赞