E-MapReduce:Business Insight

Query latency

Query latency data is statistically analyzed based on daily audit data.

Query Latency P99 is an important performance monitoring metric that measures the distribution of system response times. Specifically, P99 indicates the time within which 99% of requests receive a response. This metric is crucial for evaluating service quality and user experience.

Monitoring P99 latency provides insight into the service response speed that most users experience. If P99 latency is too high, you may need to add computing resources or optimize query logic to improve processing efficiency.

Displaying query latency data for the last seven days helps you identify potential performance risks in an instance early. This helps prevent major impacts on your services.

DB Lock

The DB Lock feature monitors and analyzes lock contention that occurs during database access. This feature is especially useful when multiple transactions attempt to access or modify the same data resource simultaneously. To ensure data consistency and integrity, the database uses a lock mechanism to control concurrent access to specific data rows or tables. If a transaction waits a long time to acquire a lock, it can affect the overall system response speed and service quality. DB Lock data helps you analyze these performance issues.

SQL query analysis

This section ranks SQL statements executed by StarRocks based on dimensions such as query time, CPU consumption, and memory consumption. This helps you identify the top SQL statements and view their execution metrics. You can use these metrics to optimize potential performance issues. For example, in the Slow SQL Top10 section, you can view the 10 SQL statements with the longest execution time on a specified date. Then, you can use the details provided in the Profile Query Analysis section to optimize these slow SQL statements. For more information, see Introduction to Query Profile.

The following table describes the main fields in the report.

Parameterized SQL analysis

A parameterized SQL statement is created by replacing constants in an SQL statement with a ? parameter. This process retains the original syntax structure, removes comments, and adjusts spaces to generate a new SQL statement. Parameterization maps the syntax of multiple original SQL statements to a single, identical parameterized SQL statement. This helps you perform a comprehensive analysis of similar SQL types.

For example, the following two SQL statements belong to the same SQL class after they are parameterized.

• Original SQL

  SELECT * FROM orders WHERE customer_id=10 AND quantity>20
  
  SELECT * FROM orders WHERE customer_id=20 AND quantity>100

• Parameterized SQL

  SELECT * FROM orders WHERE customer_id=? AND quantity>?

Parameterized SQL analysis ranks SQL statements based on dimensions such as execution frequency, total execution time, execution time variance, total CPU resource consumption, total memory resource consumption, and execution failure frequency. This helps you identify the top SQL statements and view their related metrics.

Parameterized SQL analysis lets you:

• View the overall SQL execution status of the StarRocks database.

• Optimize SQL statements that are executed frequently, have long execution times, or consume large amounts of CPU and memory. This provides greater optimization benefits.

• Use the Coefficient of Variation of Query Time to measure the stability of SQL execution time and identify potential performance issues. For example, a longer execution time for similar SQL statements may indicate data skew or pending tasks caused by insufficient resources.

  The following describes the relevant fields.

  Field	Description
  Parameterized SQL ID	The hash value of the parameterized SQL statement, used to mark the statement.
  Coefficient of Variation of Query Time	The ratio of the standard deviation of the SQL query execution time to its average value. A larger coefficient of variation usually means a greater difference in the execution time of similar SQL statements.
  Executions	The total number of executions for the parameterized SQL statement.
  Parameterized SQL Text	The text of the parameterized SQL statement.

• Find the causes of SQL failures using the Execution Failure Count to identify potential issues.

  The following table describes the fields.

  Field	Description
  Parameterized SQL ID	The hash value of the parameterized SQL statement, used to mark the statement.
  Execution Failure Count	The number of times the parameterized SQL statement failed to execute.
  Executions	The total number of executions for the parameterized SQL statement.
  Parameterized SQL Text	The text of the parameterized SQL statement.

Too many versions scenario

Data with a high import frequency is identified based on a statistical analysis of "too many versions" logs. When the Compaction Score exceeds 1000, the system reports an error, and StarRocks returns a "Too many versions" error. To resolve this issue, you can reduce the concurrency and frequency of imports.

Top analysis

Usage notes

A higher cache hit ratio improves the query performance of an instance. This page analyzes the cache status of the instance based on Query Profile data. A Query Profile records the execution information of all operators in a query. You can use the Query Profile to retrieve cache-related metrics for the query, which allows for an in-depth analysis of cache usage.

In a Query Profile, each scan of a table, view, or materialized view corresponds to a CONNECTOR_SCAN operator. The Query Profile provides various metrics for this operator. The following table lists some cache-related metrics.

Based on the preceding metrics, you can calculate the following three cache-related metrics:

• Cache hit ratio = Cache hit volume / (Cache hit volume + Cache miss volume)

• Local IO time percentage = Local IO time / (Local IO time + Remote IO time)

• Local IO count percentage = Local IO count / (Local IO count + Remote IO count)

The analysis process extracts these metrics for all CONNECTOR_SCAN operators from a day's worth of Query Profile records. It then groups the data by three dimensions and calculates metrics for each group, such as average cache hit volume and cache hit ratio. This lets you analyze the cache status from different dimensions. The following table provides example scenarios for each dimension.

Table dimension

This section displays data tables sorted by metrics such as access count, cache hit ratio, cache hit volume, and cache miss volume. Analyzing the data from the table dimension provides the following cache-related information:

• You can view the cache hit status of the top-ranked tables by access count. For example, in the figure below, the table with the highest access count (6,411) has a cache hit ratio of only 24.2%. Increasing the cache hit ratio for this table can improve query efficiency. To optimize cache usage, you can prefetch the cache for tables that have a high access frequency and a low cache hit ratio.

• You can view tables with a low cache hit ratio or a high cache miss volume. You can use cache prefetching to improve their cache status, which in turn improves the performance of the corresponding queries.

In practice, you usually need to consider multiple metrics when deciding whether to prefetch the cache for a table. For example, if a table appears in the "Top 20 by Access Frequency", "Top 20 by Low Cache Hit Ratio", and "Top 20 by Average Cache Miss Volume" lists, this indicates that the table has a high access frequency, a large query data volume, and a low cache hit ratio. Therefore, you should prioritize prefetching the cache for this table.

The following table describes the main fields.

Parameterized SQL dimension

A parameterized SQL statement is created by replacing constants in an SQL statement with the ? parameter. This process preserves the original syntax structure while removing comments and adjusting spaces. Parameterization maps original SQL statements that have the same syntax structure to a single parameterized SQL statement, which allows for a comprehensive analysis of similar types of SQL statements.

Parameterized SQL analysis sorts by metrics such as scan count, cache hit ratio, cache hit volume, and cache miss volume to retrieve the top parameterized SQL statements and display their related metrics.

Parameterized SQL analysis provides the following information:

• You can view the cache hit status of the top-ranked parameterized SQL statements by scan count. For example, in the figure below, the seventh-ranked parameterized SQL statement has a scan count of 1,086 and a long average query time of 42.5 seconds, but its cache hit ratio is only 6.5%. By optimizing these SQL statements or prefetching the cache for the tables they scan, you can use the cache more effectively and improve query performance.

  

• You can view the cache hit status of the top-ranked parameterized SQL statements by average query time. For example, as shown in the figure below, the first-ranked parameterized SQL statement has an average query time of 201 seconds, but its scan count is only 4. This indicates that the parameterized SQL statement corresponds to a cold query. You can determine whether it requires optimization based on your business needs. In contrast, the seventh-ranked parameterized SQL statement has an average query time of 42.5 seconds and a scan count of 1,086. This parameterized SQL statement deserves more attention.

  

• You can view parameterized SQL statements with a low cache hit ratio and a high cache miss volume. You can decide whether to optimize them based on their scan count, average query time, and other metrics.

  

The following table describes the main fields.

SQL dimension

This section sorts SQL statements executed in StarRocks by metrics such as query time, cache hit ratio, cache hit volume, and cache miss volume. This lets you retrieve the top SQL statements and view their corresponding metrics.

You can use these metrics for in-depth cache analysis. For example, you can view SQL statements with long execution times, low cache hit ratios, and high cache miss counts. You can improve the cache hit status by optimizing these SQL statements or prefetching the cache for the tables they access.

The following table describes the main fields.