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

[淇铭]

好的 我问一下相关的同学 看看这个问题

[淇铭]

call dbms_scheduler.set_attribute(‘OPT_STATS_HISTORY_MANAGER’, ‘NEXT_DATE’, ‘$your_time’);
这样执行一下

[达瓦里希1001]

@淇铭

call dbms_scheduler.set_attribute(‘OPT_STATS_HISTORY_MANAGER’, ‘NEXT_DATE’, ‘$your_time’);

好的，已在sys租户下执行以下语句

MySQL [oceanbase]> call dbms_scheduler.set_attribute('OPT_STATS_HISTORY_MANAGER', 'NEXT_DATE', '2025-06-11 15:40:00.000000');
Query OK, 0 rows affected (0.26 sec)

淇铭老师再指导下后续如何观测是否生效嘛？

[淇铭]

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;

[达瓦里希1001]

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 | 2025-06-11 14:53:56.702623 | 2025-06-11 00:00:01.105231 | 2025-06-12 14:53:56.700318 |       1 |
+-----------+---------------------------+------------------------------+----------------------------+----------------------------+----------------------------+----------------------------+---------+
1 row in set (0.01 sec)

和早上查的好像没变化，刚刚那个set_attribute是不是应该在问题业务租户下执行？我刚刚是在sys租户下执行的

我在业务租户下，又执行了一次，并且在sys租户下检查了您提供的这个SQL，结果如下

业务租户下
MySQL [oceanbase]> call dbms_scheduler.set_attribute('OPT_STATS_HISTORY_MANAGER', 'NEXT_DATE', '2025-06-11 15:55:00.000000');
Query OK, 0 rows affected (0.32 sec)

sys租户下
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) | 2025-06-11 15:55:00.000000 | 2025-06-11 14:53:56.702623 | 2025-06-11 00:00:01.105231 | 2025-06-11 15:55:00.000000 |       1 |
+-----------+---------------------------+------------------------------+----------------------------+----------------------------+----------------------------+----------------------------+---------+
1 row in set (0.03 sec)

[淇铭]

按照这个步骤执行

1、root@sys 登录，

select * from _all_tenant；

2、然后切到问题租户的租户里面

alter system change tenant tenant_name;

3、执行如下sql

update _all_tenant_scheduler_job set failures=0,state=‘SCHEDULED’,flag=0;

4、然后将出现问题的每天自动收集统计信息任执行
call dbms_scheduler.set_attribute(‘OPT_STATS_HISTORY_MANAGER’, ‘NEXT_DATE’, ‘$your_time’);

[达瓦里希1001]

我在执行前先查了一下__all_tenant_scheduler_job 这张表，发现OPT_STATS_HISTORY_MANAGER任务的failures的值是2，这里是不是意味着清理任务失败了两次？可否帮忙指导下如何排查清理失败的原因？

MySQL [oceanbase]> select * from __all_tenant_scheduler_job where failures != 0 or state != 'SCHEDULED' or flag !=0 \G
*************************** 1. row ***************************
        gmt_create: 2024-02-29 14:53:56.707542
      gmt_modified: 2025-06-11 15:51:25.193334
         tenant_id: 0
          job_name: OPT_STATS_HISTORY_MANAGER
               job: 8
            lowner: root
            powner: root
            cowner: oceanbase
         last_date: 2025-06-11 00:00:01.105231
         this_date: 2025-06-11 14:53:56.702623
         next_date: 2025-06-11 15:55:00.000000
             total: 1936993935597
         interval#: FREQ=DAYLY; INTERVAL=1
          failures: 2
              flag: 0
              what: DBMS_STATS.PURGE_STATS(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: DBMS_STATS.PURGE_STATS(NULL)
number_of_argument: 0
        start_date: 2025-06-11 15:55:00.000000
   repeat_interval: FREQ=DAYLY; 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: 43200
          comments: used to stats history manager
   credential_name: 
  destination_name: 
       interval_ts: 86400000000
           user_id: -1
       database_id: -1
      max_failures: 0
1 row in set (0.00 sec)

按照老师您提供的指引，通过sys租户跳转问题业务租户，又重新设置了，但存在报错返回

call dbms_scheduler.set_attribute('OPT_STATS_HISTORY_MANAGER', 'NEXT_DATE', '2025-06-11 16:10:00.000000');
ERROR 1235 (0A000): tenant changed, statement not supported

[淇铭]

直接在业务租户下 执行吧 执行完再查询一下

[达瓦里希1001]

在业务租户下执行了
MySQL [(none)]> call dbms_scheduler.set_attribute('OPT_STATS_HISTORY_MANAGER', 'NEXT_DATE', '2025-06-11 16:20:00.000000'); Query OK, 0 rows affected (0.13 sec)

业务租户下查询__all_tenant_scheduler_job 表， failures 都是0

mysql> select * from __all_tenant_scheduler_job where job_name = 'OPT_STATS_HISTORY_MANAGER' \G
*************************** 1. row ***************************
        gmt_create: 2024-02-29 14:53:56.707542
      gmt_modified: 2025-06-11 16:19:07.051558
         tenant_id: 0
          job_name: OPT_STATS_HISTORY_MANAGER
               job: 0
            lowner: root
            powner: root
            cowner: oceanbase
         last_date: NULL
         this_date: NULL
         next_date: 2025-06-11 16:20:00.000000
             total: 0
         interval#: FREQ=DAYLY; INTERVAL=1
          failures: 0
              flag: 0
              what: DBMS_STATS.PURGE_STATS(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: DBMS_STATS.PURGE_STATS(NULL)
number_of_argument: 0
        start_date: 2025-06-11 16:20:00.000000
   repeat_interval: FREQ=DAYLY; 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: 43200
          comments: used to stats history manager
   credential_name: 
  destination_name: 
       interval_ts: 86400000000
           user_id: -1
       database_id: -1
      max_failures: 0
*************************** 2. row ***************************
        gmt_create: 2024-02-29 14:53:56.707542
      gmt_modified: 2025-06-11 16:19:07.051558
         tenant_id: 0
          job_name: OPT_STATS_HISTORY_MANAGER
               job: 8
            lowner: root
            powner: root
            cowner: oceanbase
         last_date: 2025-06-11 00:00:01.105231
         this_date: 2025-06-11 14:53:56.702623
         next_date: 2025-06-11 16:20:00.000000
             total: 1936993935597
         interval#: FREQ=DAYLY; INTERVAL=1
          failures: 0
              flag: 0
              what: DBMS_STATS.PURGE_STATS(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: DBMS_STATS.PURGE_STATS(NULL)
number_of_argument: 0
        start_date: 2025-06-11 16:20:00.000000
   repeat_interval: FREQ=DAYLY; 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: 43200
          comments: used to stats history manager
   credential_name: 
  destination_name: 
       interval_ts: 86400000000
           user_id: -1
       database_id: -1
      max_failures: 0
2 rows in set (0.01 sec)

sys租户下查询，看起来NEXT_DATE修改成功了

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) | 2025-06-11 16:20:00.000000 | 2025-06-11 14:53:56.702623 | 2025-06-11 00:00:01.105231 | 2025-06-11 16:20:00.000000 |       1 |
+-----------+---------------------------+------------------------------+----------------------------+----------------------------+----------------------------+----------------------------+---------+
1 row in set (0.01 sec)

目前还不清楚如何检查清理是否生效，是不是要等合并一次才能看到磁盘使用率的下降？

[淇铭]

我们在排查一下 看看什么问题导致的 业务租户下 select * from __all_scheduler_job_run_detail_v2 where job_name = ‘OPT_STATS_HISTORY_MANAGER’ order by time desc limit 10;

[达瓦里希1001]

@淇铭

select * from __all_scheduler_job_run_detail_v2 where job_name = ‘OPT_STATS_HISTORY_MANAGER’ order by time desc limit 10;

MySQL [oceanbase]> select * from __all_scheduler_job_run_detail_v2 where job_name = 'OPT_STATS_HISTORY_MANAGER' order by time desc limit 10;
+----------------------------+----------------------------+---------------------------+----------------------------+------+--------+----------+-------+-------------+-------------------+-----------+--------+-------+----------------+-------------------+--------------+-------------+------------+-----------+----------+-----------+-----------+------------+------------------+-----------------+-------------------+-------------+-------------------+
| gmt_create                 | gmt_modified               | job_name                  | time                       | job  | log_id | log_date | owner | job_subname | job_class         | operation | status | code  | req_start_date | actual_start_date | run_duration | instance_id | session_id | slave_pid | cpu_used | user_name | client_id | global_uid | credential_owner | credential_name | destination_owner | destination | message           |
+----------------------------+----------------------------+---------------------------+----------------------------+------+--------+----------+-------+-------------+-------------------+-----------+--------+-------+----------------+-------------------+--------------+-------------+------------+-----------+----------+-----------+-----------+------------+------------------+-----------------+-------------------+-------------+-------------------+
| 2025-06-12 02:53:57.394537 | 2025-06-12 02:53:57.394537 | OPT_STATS_HISTORY_MANAGER | 2025-06-12 02:53:57.394537 |    8 |      0 | NULL     | NULL  | NULL        | DEFAULT_JOB_CLASS | NULL      | NULL   | -4012 | NULL           | NULL              |         NULL | NULL        |       NULL | NULL      |     NULL | NULL      | NULL      | NULL       | NULL             | NULL            | NULL              | NULL        | Timeout           |
| 2025-06-12 02:53:57.259044 | 2025-06-12 02:53:57.259044 | OPT_STATS_HISTORY_MANAGER | 2025-06-12 02:53:57.259044 |    8 |      0 | NULL     | NULL  | NULL        | DEFAULT_JOB_CLASS | NULL      | NULL   | -4012 | NULL           | NULL              |         NULL | NULL        |       NULL | NULL      |     NULL | NULL      | NULL      | NULL       | NULL             | NULL            | NULL              | NULL        | check job timeout |
| 2025-06-11 12:00:01.255235 | 2025-06-11 12:00:01.255235 | OPT_STATS_HISTORY_MANAGER | 2025-06-11 12:00:01.255235 |    8 |      0 | NULL     | NULL  | NULL        | DEFAULT_JOB_CLASS | NULL      | NULL   | -4012 | NULL           | NULL              |         NULL | NULL        |       NULL | NULL      |     NULL | NULL      | NULL      | NULL       | NULL             | NULL            | NULL              | NULL        | Timeout           |
| 2025-06-11 00:00:00.129824 | 2025-06-11 00:00:00.129824 | OPT_STATS_HISTORY_MANAGER | 2025-06-11 00:00:00.129824 |    8 |      0 | NULL     | NULL  | NULL        | DEFAULT_JOB_CLASS | NULL      | NULL   | -4012 | NULL           | NULL              |         NULL | NULL        |       NULL | NULL      |     NULL | NULL      | NULL      | NULL       | NULL             | NULL            | NULL              | NULL        | Timeout           |
| 2025-06-11 00:00:00.103030 | 2025-06-11 00:00:00.103030 | OPT_STATS_HISTORY_MANAGER | 2025-06-11 00:00:00.103030 |    8 |      0 | NULL     | NULL  | NULL        | DEFAULT_JOB_CLASS | NULL      | NULL   | -4012 | NULL           | NULL              |         NULL | NULL        |       NULL | NULL      |     NULL | NULL      | NULL      | NULL       | NULL             | NULL            | NULL              | NULL        | check job timeout |
| 2025-06-09 14:58:36.148928 | 2025-06-09 14:58:36.148928 | OPT_STATS_HISTORY_MANAGER | 2025-06-09 14:58:36.148928 |    8 |      0 | NULL     | NULL  | NULL        | DEFAULT_JOB_CLASS | NULL      | NULL   |     0 | NULL           | NULL              |         NULL | NULL        |       NULL | NULL      |     NULL | NULL      | NULL      | NULL       | NULL             | NULL            | NULL              | NULL        | check job missed  |
+----------------------------+----------------------------+---------------------------+----------------------------+------+--------+----------+-------+-------------+-------------------+-----------+--------+-------+----------------+-------------------+--------------+-------------+------------+-----------+----------+-----------+-----------+------------+------------------+-----------------+-------------------+-------------+-------------------+
6 rows in set (0.03 sec)

[淇铭]

看着是调度成功了 目前看着是执行超时了

[达瓦里希1001]

对的，我前面也提及过，我在问题租户下执行手动清理，很长时间无响应，直至超时报错

MySQL [oceanbase]> CALL DBMS_STATS.PURGE_STATS('2025-06-01 00:00:00');
ERROR 4012 (HY000): Timeout

其他租户下，执行以上命令是可以正常返回的。请淇铭老师帮忙内部升级下，找找相关同学帮忙看看吧

[淇铭]

参考下面的步骤 抓取一下日志 超时以后 会有trace_id抛出来YB420BA1CC68-000615A0A8EA5E38-0-0
alter system set enable_rich_error_msg=true;
MySQL [oceanbase]> CALL DBMS_STATS.PURGE_STATS(‘2025-06-01 00:00:00’);
ERROR 4012 (HY000): Timeout
[xx.xx.xx.1:2882] [2024-04-13 20:10:20.292087] [YB420BA1CC68-000615A0A8EA5E38-0-0]
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” rootservice.log
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” observer.log
alter system set enable_rich_error_msg=false;

[淇铭]

临时处理可以这样直接删除 按照天删除 如果数据量比较大 可以按照小时删除 可以写个存储过程分批次删除 delete from oceanbase.__all_histogram_stat_history where gmt_modified=str_to_date(‘2025-04-09’, ‘%Y-%m-%d %H’);

[达瓦里希1001]

我在问题租户下，执行delete 报权限错误，之前我一直认为虚拟表是不能删除的，帮忙确认下呢？

ERROR 1142 (42000): DELETE command denied to user 'root'@'%' for table '__all_histogram_stat_history'


MySQL [oceanbase]> show grants;
+----------------------------------------------------------+
| Grants for root@%                                        |
+----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root' WITH GRANT OPTION  |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'root'              |
| GRANT ALL PRIVILEGES ON `oceanbase`.* TO 'root'          |
| GRANT ALL PRIVILEGES ON `test`.* TO 'root'               |
| GRANT ALL PRIVILEGES ON `information_schema`.* TO 'root' |
| GRANT ALL PRIVILEGES ON `__public`.* TO 'root'           |
| GRANT ALL PRIVILEGES ON `__recyclebin`.* TO 'root'       |
+----------------------------------------------------------+
7 rows in set (0.05 sec)

上面那个日志捞取，我收集好发出来

[达瓦里希1001]

我通过sys租户 alter system change tenant 到问题租户下可以执行删除了，我写个脚本定时清理下。谢谢淇铭老师

[淇铭]

可以的 写个定时任务 或者存储过程 都可以解决 目前主要就是数据量太大了 导致调度任务删除超时了