ob_tenant_mview_refresh_delay Materialized View Automatic Refresh Delay

Alert description

This alert monitors whether there is a delay in materialized view refreshes in the databases of a tenant. If a delay is detected, this alert is triggered.

Alert principle

1. The OCP scheduled task executes the following SQL in the sys tenant of all clusters to automatically refresh untimely data from the corresponding materialized views and store the data in the sql_aggregate_data table of OCP MonitorDB. The scheduled task runs every 60 seconds.

    SELECT E.tenant_id, E.tenant_name, A.database_name, B.table_name AS mview_name, C.last_refresh_date, D.next_date
    FROM oceanbase.__all_virtual_database A, oceanbase.__all_virtual_table B, oceanbase.__all_virtual_mview C, oceanbase.__all_virtual_tenant_scheduler_job D, oceanbase.DBA_OB_TENANTS E
    WHERE A.tenant_id = B.tenant_id
      AND B.tenant_id = C.tenant_id
      AND C.tenant_id = D.tenant_id
      AND D.tenant_id = E.tenant_id
      AND A.database_id = B.database_id
      AND B.table_id = C.mview_id
      AND C.refresh_job = D.job_name
      AND C.refresh_job IS NOT NULL
      AND D.job != 0
      AND D.next_date < Now()
      AND E.tenant_type <> 'META'
      AND E.tenant_name IS NOT NULL
      AND E.tenant_role = 'PRIMARY';
   

2. Use the OCP alert detection task to identify whether the alert item is of the SQL_AUDIT type. If so, run the following command to scan for abnormal data in sql_aggregate_data and generate the corresponding alert based on the configured alert detection rules.

   select * from sql_aggregate_data where tags like '%mview_stat%' order by timestamp desc
   

Rule information

Alert information

Alert template

• Alert overview

  • Template: ${alarm_target} ${alarm_name}
  • Example: alarm_template_id=0:ob_cluster=test471-11:tenant_name=mysqlTes:database=test:mview_name=test_mv_refresh_retry_mv2 Materialized View Refresh Delay

• Alert details

  • Template: The materialized view ${mview_name} in the database ${database} of the tenant ${tenant_name} in the OceanBase cluster ${ob_cluster_name} does not refresh automatically. The expected refresh time is ${next_date}.
  • Example: In the OceanBase cluster test471, the materialized view test_mv_refresh_retry_mv2 in the database test of the tenant mysqlTes does not refresh automatically in a timely manner. The expected refresh time is 2025-11-14T16:13:55.026383+08:00.

Impact on the system

The timeliness of materialized views cannot be guaranteed.

Possible causes

Procedure

1. Run the following command in the sys tenant to query sessions related to materialized views:

   select id, svr_ip, svr_port,tenant_id, time, info
   from oceanbase.__all_virtual_processlist
   where info like '%mview_name%'\G
   

   Replace mview_name with the name of the materialized view that causes the issue.

2. For sessions with excessively long execution times, further check the current execution plans:

   select dbms_xplan.display_active_session_plan(id, 'all', svr_ip, svr_port)\G
   

   Replace id, svr_ip, and svr_port with the values obtained from the previous step.

3. Analyze the plan information to identify the cause of the prolonged refresh. If necessary, contact OCP Technical Support for assistance.