E-MapReduce:Business Insights

Query latency

Query latency statistics are derived from daily audit data.

P99 query latency is a key performance metric that measures the distribution of system response times. Specifically, P99 indicates that 99% of requests are completed within this time. This metric is crucial for evaluating service quality and user experience.

By monitoring P99 latency, you can understand the actual response speeds for most users.

The seven-day query latency data helps you proactively identify potential performance risks in an instance before they significantly impact your services.

DB lock

The DB Lock feature helps you monitor and analyze lock contention, which occurs when multiple transactions attempt to access the same data. To ensure data consistency and integrity, databases use a locking mechanism to control concurrent access to specific rows or tables. Long waits for a lock can degrade system performance and service quality. Use this data to troubleshoot these performance issues.

SQL analysis

This section ranks SQL statements executed in StarRocks by metrics such as query time, CPU cost, and memory consumption. This allows you to identify top SQL statements and view their execution metrics. You can use these metrics to tune potential performance bottlenecks. For example, you can view the 10 longest-running SQL statements on a specific day in the Slow Query Top 10 list. Then, use the detailed information provided by the query profile to optimize these slow-running SQL statements. For more information about query profile analysis, see Query Profile Introduction.

The following table describes the main fields in the report.

Parameterized SQL analysis

A parameterized SQL is a template created by replacing literal values in an SQL statement with a ? parameter. This process normalizes queries by removing comments and standardizing spacing, while preserving the core syntax. It allows you to group and analyze structurally similar SQL statements together.

For example, the following two SQL statements are reduced to the same parameterized template:

• 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 queries based on metrics such as execution count, total execution time, execution time variance, total CPU resource consumption, total memory resource consumption, and execution failure count. This helps you identify top parameterized SQL and view related metrics.

With Parameterized SQL Analysis, you can:

• Understand the overall SQL execution status in your StarRocks database.

• Optimize frequently executed, long-running, and resource-intensive (CPU and memory) SQL to achieve significant performance gains.

• Use the query time CV to measure the stability of SQL execution times and identify potential performance issues. For example, increased execution time for similar SQL queries may indicate issues such as data skew or resource contention.

  The following table describes the related fields.

  Field	Description
  Parameterized SQL ID	The hash value of the parameterized SQL, used as its unique identifier.
  query time CV	The ratio of the standard deviation of query execution time to its mean. A higher coefficient of variation (CV) typically indicates greater variability in the execution time of similar SQL queries.
  execution count	The total number of executions for the parameterized SQL.
  Parameterized SQL Text	The template text of the parameterized SQL.

• Use the execution failure count to investigate the causes of SQL failures and identify potential issues.

  The following table describes the related fields.

  Field	Description
  Parameterized SQL ID	The hash value of the parameterized SQL, used as its unique identifier.
  execution failure count	The total number of failed executions for the parameterized SQL.
  execution count	The total number of executions for the parameterized SQL.
  Parameterized SQL Text	The template text of the parameterized SQL.

Too many versions scenario

The analysis of high-frequency imports is based on logs containing the "too many versions" error. When the compaction score exceeds 1,000, StarRocks reports this error. To resolve this issue, you can reduce the concurrency and frequency of your import tasks.

Top analysis

Usage

Increasing an instance's cache hit ratio improves query performance. This page analyzes the cache status of an instance based on its Query Profile data. A Query Profile records the execution information of all operators in a query and provides cache-related metrics for 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 describes some of the key cache-related metrics.

You can use the preceding metrics to calculate the following three cache-related indicators:

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

• local I/O time percentage = local I/O time / (local I/O time + remote I/O time)

• local I/O count percentage = local I/O count / (local I/O count + remote I/O count)

The system extracts the preceding metrics from the CONNECTOR_SCAN operators for all SQL statements in a day's Query Profile records. It then groups them by the following three dimensions and calculates metrics such as average cache hit volume and cache hit ratio for each group. This process helps you analyze cache status from different perspectives. The following table describes the use cases for each dimension.

Table dimension

Analysis by table provides the following cache-related information:

• View the cache hit status of tables with the highest scan counts. For example, in the following figure, the table with the highest scan count (6,411) has a cache hit ratio of only 24.2%. Increasing this table's cache hit ratio improves query efficiency. You can perform cache prefetch for such tables with high scan counts and low cache hit ratios to optimize cache usage.

• Identify tables with low cache hit ratios or high cache miss volumes. You can use cache prefetch to improve their cache status and, in turn, boost the performance of related queries.

In practice, you often need to consider multiple metrics to decide whether to perform cache prefetch for a table. For example, if a table appears in the "Top 20 by Scan Count," "Top 20 by Low Cache Hit Ratio," and "Top 20 by Average Cache Miss Volume" lists, it indicates that the table has a high scan count, involves large data reads, and has a low cache hit ratio. Therefore, you should prioritize it for cache prefetch.

The following table describes the key fields involved.

Parameterized SQL dimension

Parameterized SQL replaces constants in an SQL statement with ? parameters, while preserving the original syntax, removing comments, and adjusting spaces to create a new statement. This process maps SQL statements with similar structures to a single parameterized format, which helps in the comprehensive analysis of similar types of SQL.

This parameterized SQL analysis ranks queries based on metrics such as scan count, cache hit ratio, cache hit volume, and cache miss volume to identify the top parameterized SQL statements and display their related indicators.

This analysis provides the following insights:

• View the cache hit status of parameterized SQL statements with high scan counts. For example, a parameterized SQL ranked seventh 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%. Optimizing these SQL statements or prefetching the tables they scan can help you use the cache more effectively and improve query performance.

• View the cache hit status of parameterized SQL statements with long average query times. For instance, the top-ranked parameterized SQL has an average query time of 201 seconds but only 4 scans, indicating it is a cold query. Whether it needs optimization depends on your business requirements. In contrast, the seventh-ranked parameterized SQL has an average query time of 42.5 seconds and a scan count of 1,086, making it a higher priority for optimization.

• Identify parameterized SQL statements with low cache hit ratios and high cache miss volumes, and decide whether to optimize them based on their scan counts and average query times.

The following table describes the key fields involved.

SQL dimension

This section ranks SQL statements executed in StarRocks by metrics such as query time, cache hit ratio, cache hit volume, and cache miss volume, and displays the top SQL statements and their corresponding indicators.

Optimizing these SQL statements or performing cache prefetch on the tables they access can improve cache performance.

This page includes ranking dimensions such as Top 20 Slow SQL and Top 20 High Cache Hit Ratio, which respectively show the SQL statements with the longest query times and the highest cache hit ratios, along with their various metrics.

The following table describes the key fields involved.