@Rocky - are there any caveats in starrocks 3.5.16 related to creating an async materialized view on top of an external jdbc catalog with an hourly refresh?

For StarRocks v3.5.16, creating an asynchronous Materialized View (MV) on an external JDBC Catalog with an hourly refresh is fully supported, but there are several critical caveats regarding performance, refresh behavior, and resource management you should consider: 1. Full vs. Incremental Refresh The most significant caveat is how StarRocks detects changes in a JDBC source: * MySQL Only (Range Partitioned): Partition-level (incremental) refresh is currently only supported for MySQL base tables that use RangeColumn partitioning (starting from v3.1.4). * Other JDBC Sources: For PostgreSQL, SQL Server, Oracle, or non-partitioned MySQL tables, StarRocks cannot detect partition-level changes. This means an hourly refresh will trigger a full refresh (re-reading and overwriting the entire MV) every hour. This can be extremely resource-intensive for large datasets. 2. Query Rewrite Limitations * No Automatic Rewrite: By default, StarRocks does not support automatic query rewrite for MVs built on JDBC catalogs. Queries against the base JDBC table will not be transparently redirected to the MV unless you query the MV directly. * Forcing Rewrite: You can attempt to force rewrite by setting properties like

force_external_table_query_rewrite

=

TRUE

and

query_rewrite_consistency

=

LOOSE

, but this is generally not recommended for JDBC due to the risk of returning stale data, as StarRocks cannot perceive external metadata changes in real-time. 3. Resource Management & Task Queuing * Task Merging: If your hourly refresh takes longer than 60 minutes to complete, StarRocks will not start a second concurrent refresh for the same MV. The newly triggered task will either be "Merged" or wait until the current one finishes. * Default Resource Group: Refresh tasks run in the

default_mv_wg

resource group by default (limited to 1 CPU core and 80% memory). For an hourly full refresh of a large JDBC table, you should create a dedicated resource group to prevent the refresh from impacting the performance of interactive queries. * JNI Memory: JDBC scans use JNI to communicate with the Java drivers. In v3.5.16, several fixes were included to address JNI local-reference leaks and metadata overhead (e.g., #72913, #73488), making this version more stable for high-frequency JDBC refreshes than earlier 3.x releases. 4. Configuration Requirements * Driver & Environment: Ensure the correct JDBC driver is in the

fe/lib

and

be/lib

(or

runtime_migration

paths) and that

JAVA_HOME

is correctly set to a JDK (not JRE) in your BE

start_be.sh

. * Connection Pooling: If you have many MVs refreshing hourly, monitor your JDBC connection pool settings (e.g.,

connection_pool_size

) to ensure you don't exhaust connections on the source database. 5. Specific Fixes in v3.5.16 Version 3.5.16 includes targeted improvements for these scenarios: * JDBC Scanner Stability: Fixed potential crashes and memory leaks during external JDBC scans. * SQL Server Specifics: Fixed refresh failures specifically occurring with SQL Server JDBC tables (#72962). Summary Recommendation If your JDBC table is large and not a MySQL range-partitioned table, an hourly refresh may cause significant load on both StarRocks and your source database. It is recommended to: 1. Test the refresh duration to ensure it comfortably fits within the 1-hour window. 2. Define a Partition TTL (

partition_retention_condition

) if the data is time-series, to avoid keeping infinite history in the MV. 3. Monitor the Task Status using

SELECT * FROM information_schema.task_runs

to watch for failures or overlaps. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md