E-MapReduce:Troubleshooting asynchronous materialized views

Check working status

You can use the SHOW MATERIALIZED VIEWS command to check the working status of asynchronous materialized views.

• Syntax

  SHOW MATERIALIZED VIEWS
  [FROM db_name]
  [
  WHERE NAME { = "mv_name" | LIKE "mv_name_matcher"}
  ]

  Parameter	Required	Description
  db_name	No	The name of the database to which the materialized view belongs. If this parameter is not specified, the current database is used by default.
  mv_name	No	The name of the materialized view for exact match.
  mv_name_matcher	No	The materialized view name matcher for fuzzy match.

  In the returned information, you can focus on the following fields:

  • is_active: Whether the status of the materialized view is Active. Only materialized views in Active status can be used for query acceleration and rewriting.

  • last_refresh_state: The status of the most recent refresh, including PENDING, RUNNING, FAILED, and SUCCESS.

  • last_refresh_error_message: The reason for the last refresh failure. If the materialized view status is not Active, the related error message will be recorded.

  • rows: The number of data rows in the materialized view. Note that this value may differ from the actual number of rows in the materialized view because updates may be delayed.

• Example

  SHOW MATERIALIZED VIEWS FROM test_db WHERE NAME LIKE 'mv_pred_2';

View refresh history

You can view the refresh history of asynchronous materialized views by querying the task_runs table in the information_schema database. In the returned information, you can focus on the following fields:

• CREATE_TIME and FINISH_TIME: The start and end times of the refresh task.

• STATE: The status of the refresh task, including PENDING, RUNNING, FAILED, and SUCCESS.

• ERROR_MESSAGE: The reason for the refresh task failure.

The following sample code shows an example.

USE test_db;
SELECT * FROM information_schema.task_runs WHERE task_name ='mv-112517'

Monitor resource consumption

You can monitor and analyze the resources consumed by asynchronous materialized views during or after the refresh task execution.

Verify whether a query is rewritten

You can check whether a query can be rewritten by an asynchronous materialized view by using EXPLAIN to view the query plan.

If the SCAN metric in the query plan shows the name of the corresponding materialized view, the query has been rewritten by the materialized view.

EXPLAIN [ LOGICAL | VERBOSE | COSTS ] <query>

SET enable_materialized_view_rewrite = false;
EXPLAIN LOGICAL SELECT `customer`.`c_custkey`
  FROM `test_db`.`customer`
  GROUP BY `customer`.`c_custkey`;

Failed to create an asynchronous materialized view

If you cannot create an asynchronous materialized view, meaning you cannot execute the CREATE MATERIALIZED VIEW statement, you can start troubleshooting from the following aspects.

• Check if you mistakenly used the SQL statement for creating a synchronous materialized view.

  StarRocks provides two different types of materialized views: synchronous materialized views and asynchronous materialized views.

  The basic SQL statement used to create a synchronous materialized view is as follows:

  CREATE MATERIALIZED VIEW <mv_name> 
  AS <query>

  In comparison, the SQL statement used to create an asynchronous materialized view contains more parameters:

  CREATE MATERIALIZED VIEW <mv_name> 
  REFRESH ASYNC -- Refresh strategy for asynchronous materialized view.
  DISTRIBUTED BY HASH(<column>) -- Data distribution strategy for asynchronous materialized view.
  AS <query>

  Besides the SQL statement, the main difference between these two types of materialized views is that asynchronous materialized views support all query syntax provided by StarRocks, while synchronous materialized views only support a limited set of aggregate functions.

• Check if you specified the correct PARTITION BY column.

  When creating an asynchronous materialized view, you can specify a partition strategy for it, allowing the materialized view to be refreshed at a more granular level.

  Currently, StarRocks only supports single-column partition keys and only supports Range Partition. You can use the date_trunc() function on the partition column to roll up and change the granularity level of the partition strategy. No other expressions are supported.

• Check if you have the necessary permissions to create a materialized view.

  When creating an asynchronous materialized view, you need SELECT permission on all query objects (tables, views, materialized views). When using UDFs in the query, you also need USAGE permission on the functions.

Materialized view refresh failed

If a materialized view refresh fails, meaning the status of the refresh task is not SUCCESS, you can start troubleshooting from the following aspects:

• Check if an inappropriate refresh strategy was used.

  By default, materialized views are refreshed immediately after creation. However, in v2.5 and earlier versions, materialized views with a MANUAL refresh strategy are not automatically refreshed after creation. You must manually refresh them using the REFRESH MATERIALIZED VIEW command.

• Check if the refresh task exceeded the memory limit.

  Typically, when an asynchronous materialized view involves large-scale aggregation or join calculations, it consumes a large amount of memory resources. To resolve this issue, you can:

  • Specify a partition strategy for the materialized view to achieve fine-grained refresh.

  • Enable the intermediate result spilling feature for refresh tasks. Starting from v3.1, StarRocks supports spilling partial intermediate results of materialized view refresh tasks to disk. Execute the following statement to enable the intermediate result spilling feature.

    -- Define properties when creating a materialized view.
    CREATE MATERIALIZED VIEW mv1 
    REFRESH ASYNC
    PROPERTIES ( 'session.enable_spill'='true' )
    AS <query>;
    
    -- Add properties to an existing materialized view.
    ALTER MATERIALIZED VIEW mv2 SET ('session.enable_spill' = 'true');

Materialized view refresh timeout

Larger materialized views may fail to refresh because the refresh task exceeds the timeout period. There are typically several solutions.

• Specify a partition strategy for the materialized view to achieve fine-grained refresh.

  As described in Creating a partitioned materialized view, partitioning a materialized view can enable incremental building and refreshing, which can avoid consuming too many resources during the initial refresh.

• Set a longer timeout period.

  In versions before v3.2, the default timeout for materialized view refresh tasks is 5 minutes. In v3.2 and later versions, the default is 1 hour. When encountering a timeout exception, you can try to modify the timeout period by executing the following command.

  ALTER MATERIALIZED VIEW mv2 SET ( 'session.query_timeout' = '4000' );

• Analyze the performance bottleneck of the materialized view.

  If the materialized view computation is complex, the computation itself will take a long time. You can analyze the performance bottleneck through Query Profile and optimize it:

  1. Obtain the query_id of the refresh task by querying information_schema.task_runs.

  2. Using the query_id obtained above, retrieve and analyze its Query Profile. For more information, see Introduction to Query Profile.

Materialized view is unavailable

If a materialized view cannot rewrite queries or refresh, and the is_active status of the materialized view is false, it may be due to a Schema Change in the base table. To resolve this issue, you can manually set the materialized view status to Active by executing the following statement:

ALTER MATERIALIZED VIEW mv1 ACTIVE;

If the setting does not take effect, you need to delete the materialized view and recreate it.

Materialized view refresh task consumes too many resources

If you find that refresh tasks are using too many system resources, you can start troubleshooting from the following aspects:

• Check if the created materialized view is too large.

  If you joined multiple tables, resulting in a large amount of computation, the refresh task will consume a lot of resources. To resolve this issue, you need to evaluate the size of the materialized view and replan its creation.

• Check if the refresh interval is too frequent.

  If the materialized view uses a fixed interval refresh strategy, you can lower the refresh frequency to resolve the issue. If the refresh task is triggered by data changes in the base table, frequent data import operations may also cause this issue. To resolve this issue, you need to define an appropriate refresh strategy for the materialized view.

• Check if the materialized view is partitioned.

  Unpartitioned materialized views may consume a large amount of resources during refresh because StarRocks refreshes the entire materialized view each time. To resolve this issue, you need to specify a partition strategy for the materialized view to achieve fine-grained refresh.

To stop a refresh task that is consuming too many resources, you can:

• Set the materialized view status to Inactive to stop all refresh tasks:

  ALTER MATERIALIZED VIEW mv1 INACTIVE;

• Terminate a running refresh task using CANCEL REFRESH MATERIALIZED VIEW.

  CANCEL REFRESH MATERIALIZED VIEW mv1;

Materialized view cannot rewrite queries

If a materialized view cannot rewrite related queries, you can start troubleshooting from the following aspects.

• Diagnose the reason for rewrite failure using TRACE.

  StarRocks provides the TRACE command to diagnose why a materialized view cannot rewrite a query:

  • TRACE LOGS MV <query>: Available in v3.2 and later versions, used to analyze the detailed rewrite process and the reasons for rewrite failure.

  • TRACE REASON MV <query>: Available in v3.2.8 and later versions, provides a simplified reason for rewrite failure.

  The following sample code shows an example.

  TRACE REASON MV SELECT SUM(c1) FROM `glue_ice`.`iceberg_test`.`ice_test3`;

• Check if the materialized view and query match.

  • StarRocks uses structure-based rather than text-based matching techniques to match materialized views and queries. Therefore, it is not necessarily the case that a query can be rewritten when it looks the same as the materialized view.

  • Materialized views only support rewriting SPJG (Select/Projection/Join/Aggregation) type queries, and do not support rewriting queries involving window functions, nested aggregations, or Join plus aggregation.

  • Materialized views cannot rewrite queries with complex Join predicates in Outer Joins. For example, in cases like A LEFT JOIN B ON A.dt > '2023-01-01' AND A.id = B.id, it is recommended that you specify the predicates in the WHERE clause instead of in the JOIN ON clause.

  For information about the limitations of materialized view query rewriting, see Limits.

• Check if the materialized view status is Active.

  StarRocks checks the status of the materialized view before rewriting a query. Only when the materialized view status is Active can the query be rewritten. To resolve this issue, you can manually set the materialized view status to Active by executing the following statement.

  ALTER MATERIALIZED VIEW mv1 ACTIVE;

• Check if the materialized view meets the data consistency requirements.

  StarRocks checks the consistency between the materialized view data and the base table data. By default, queries can only be rewritten when the data in the materialized view is up-to-date. To resolve this issue, you can:

  • Add PROPERTIES('query_rewrite_consistency'='LOOSE') to the materialized view to disable consistency checks.

  • Add PROPERTIES('mv_rewrite_staleness_second'='5') to the materialized view to tolerate a certain degree of data inconsistency. As long as the last refresh is within this time interval, the query can be rewritten regardless of whether the data in the base table has changed.

  • Check if the query statement of the materialized view is missing output columns.

    To rewrite range and point queries, you must specify the predicates used for filtering in the SELECT expression of the materialized view's query statement. You need to check the SELECT statement of the materialized view to ensure it contains the columns referenced in the WHERE and ORDER BY clauses of the query.

  Example 1: Materialized view mv1 uses nested aggregation, so it cannot be used to rewrite queries.

  CREATE MATERIALIZED VIEW mv1 REFRESH ASYNC AS
  SELECT COUNT(DISTINCT cnt) 
  FROM (
      SELECT c_city, COUNT(*) cnt 
      FROM customer 
      GROUP BY c_city
  ) t;

  Example 2: Materialized view mv2 uses Join plus aggregation, so it cannot be used to rewrite queries. To resolve this issue, you can create a materialized view with aggregation, and then create a nested materialized view with Join based on that materialized view.

  CREATE MATERIALIZED VIEW mv2 REFRESH ASYNC AS
  SELECT *
  FROM (
      SELECT lect lo_orderkey, lo_custkey, p_partkey, p_name
      FROM lineorder
      JOIN part ON lo_partkey = p_partkey
  ) lo
  JOIN (
      SELECT c_custkey
      FROM customer
      GROUP BY c_custkey
  ) cust
  ON lo.lo_custkey = cust.c_custkey;

  Example 3: Materialized view mv3 cannot rewrite queries with the pattern SELECT c_city, sum(tax) FROM tbl WHERE dt='2023-01-01' AND c_city = 'xxx' because the columns referenced by the predicates are not in the SELECT expression.

  CREATE MATERIALIZED VIEW mv3 REFRESH ASYNC AS
  SELECT c_city, SUM(tax) FROM tbl GROUP BY c_city;

  To resolve this issue, you can create the materialized view as follows.

  CREATE MATERIALIZED VIEW mv3 REFRESH ASYNC AS
  SELECT dt, c_city, SUM(tax) FROM tbl GROUP BY dt, c_city;