1.1. Purpose and target audience #

This guide is intended for ByteHouse CDW (Cloud Data Warehouse) users, including data analysts, data engineers, and database administrators (DBAs), providing a systematic SQL performance optimization framework and practical methodology. Whether you are new to ByteHouse or wish to further improve the performance of existing queries, this guide will offer clear and actionable guidance.
Our goal is:

• Establish performance awareness: Help you understand the core elements of SQL query performance and the working principles of ByteHouse.
• Provide a systematic approach: Offer a comprehensive optimization methodology covering performance diagnosis, SQL writing, data model design, and monitoring management.
• Empower practice: Through rich code samples, scenario-based analysis, and real cases, enable you to solve actual performance problems encountered in your work.

1.2. ByteHouse CDW query optimization core concepts #

• Reduce data scan (Reduce Data Scan): Read as little data as possible. This is the first golden rule of performance optimization. By effectively leveraging partition keys, sorting keys, and indexes, avoid full table scans from the outset.
• Increase computation efficiency (Increase Computation Efficiency): In a distributed computing framework, optimize the degree of parallelism and computation efficiency. For example, select the optimal JOIN algorithm (such as Broadcast Join), avoid data skew, and use aggregate functions efficiently.
• Utilize resources wisely (Utilize Resources Wisely): By configuring appropriate virtual warehouse (VW) specifications, managing concurrent queries, and optimizing resource queues, ensure that key tasks receive sufficient computing resources.
• Embrace automation (Embrace Automation): Fully utilize the automation tools provided by ByteHouse, such as automatic statistics collection, to simplify optimization work and ensure the accuracy of the query plan.

1.3. How to use this guide #

This guide is organized in a logical sequence from fundamentals to advanced, and then to practical application.

• 2. ByteHouse CDW query optimization basicsand3. Core SQL optimization RD: Explains the basic knowledge and core technologies of optimization, suitable for all users to read, to establish a solid theoretical foundation.
• 4. Scenario-based tuning practices: Focuses on specific business scenarios, providing targeted optimization strategies and examples to help you address particular issues.
• 5. Performance Monitoring and manage: Introduces performance monitoring and management, suitable for users who require system-level performance governance.

It is recommended to read from start to finish to build a complete knowledge system. Experienced users can jump directly to the chapters of interest.

2.1 Quick start with query optimization #

Understanding the query lifecycle #

The journey of a SQL query in ByteHouse typically includes the following stages:

1. Parsing (Parsing): The client submits the SQL text, and ByteHouse's parser performs lexical and syntactic analysis to generate an abstract syntax tree (AST).
2. Optimization (Optimization): The query optimizer is the core of performance optimization. It generates multiple candidate execution plans based on statistics, system parameters, and preset rules, and selects the plan with the lowest cost.
3. Execution (Execution): The execution engine carries out specific computation tasks (such as data scanning, filtering, aggregation, JOIN, and more) on each compute node according to the selected execution plan.
4. Return (Return): The final computation result is returned to the client.

Performance bottlenecks can occur at any stage, but the vast majority of issues are concentrated in the Execution phase, and the key to optimization lies in influencing optimizer decisions.

Identifying performance bottlenecks: key metrics and tools #

Before starting optimization, first locate the problem. The following are key performance metrics:

• Query latency (Query Latency): The total time from query submission to result return.
• CPU time (CPU Time): The total CPU time consumed by all compute nodes for the query.
• Data scanned (Data Scanned): The size of the raw data read by the query.
• Peak memory usage (Peak Memory Usage): The maximum memory consumed during query execution.

Core tools:

• EXPLAIN: View the logical and physical execution plans of the query; this is the primary tool for SQL optimization.
• bh_system.query_log: Records a detailed log of all executed searches, including all the performance metrics mentioned above. It is an essential tool for post-analysis of performance problems. For details on how to activate, please refer to View detailed log (query_log).

Optimization example: A simple journey to optimize slow searches #

Suppose we have a sales record table sales, which contains the partition key dt (date).
Before optimization: A common slow search occurs when the partition is not specified, resulting in a full table scan.

• Performance: Data scanned: 10TB, time: 5 minutes.
  • After optimization: By adding a partition key filter, the amount of data scanned can be greatly reduced.
• Performance: Data scanned: 10GB, time: 3 seconds. This simple example illustrates the core idea of optimization: Read only the data you need whenever possible.

SQL before optimization:

-- Intention: Query yesterday's total sales amount
-- Problem: WHERE dt = ... was not used to limit the partition, resulting in a full table scan
SELECT sum(amount)
FROM sales;

SQL after optimization:

-- Optimization: Accurately specify the partition
SELECT sum(amount)
FROM sales
WHERE dt = '2025-08-24';

2.2 Understanding the EXPLAIN execution plan #

EXPLAIN is a window for understanding how a search is executed. Through it, we can see the path the optimizer selects for our SQL. For details on the execution plan, see Execution plan EXPLAIN.

2.2.1. How to view and analyze EXPLAIN ANALYZE #

In ByteHouse, you can obtain the execution plan by directly adding the EXPLAIN ANALYZE keyword (optionally followed by Distributed and Pipeline) before the SQL.

EXPLAIN ANALYZE SELECT ...;

The output is a tree structure, read from the bottom up. Each line represents an Operator (Operator), which indicates a specific calculation step. Text indentation represents the parent-child relationship between operators.
The key to analyzing EXPLAIN ANALYZE is:

1. Focus on the underlying operators: Especially TableScan, check the number of partitions (partitions) and rows (rows) scanned, and confirm whether unnecessary data is being scanned.
2. Check the Filter operator: Confirm whether the WHERE condition is effectively pushed down to the storage layer, so that data is filtered during the scan.
3. Analyze the Join type: Confirm whether the most efficient Join algorithm is used, for example, whether the smaller table is used as the right table.
4. Look for high-cost operations: Pay attention to operators that typically consume more resources, such as Shuffle, Sorting, and others.

2.2.2. Interpretation of key operators (Operator) #

• TableScan, Filter, Project
  • TableScan: Indicates reading data from a table. The key information is the partitions and number of rows it reads. If the Data volume here far exceeds expectations, it is usually due to Partition pruning or index not being activated.
  • Filter: Runs the Filters in the WHERE clause. Ideally, Filter should be as close as possible to TableScan.
  • Project: Corresponds to the SELECT List, indicating which columns to output.
• Join Type (Broadcast, Shuffle)
  • Broadcast Join: When one table (usually the right table) is small, ByteHouse will broadcast its Complete Data to all nodes participating in the Join. This avoids redistribution (Shuffle) of the left table Data, resulting in extremely high performance.
  • Shuffle Join: When both tables are large, Data from both tables must be rehashed and distributed (Shuffle) based on the Join Key to ensure the same Join Key is distributed to the same node. Shuffle results in a large amount of Network IO and is one of the main performance overheads.
  • Colocation Join: When the JOIN key and the bucket key of both tables are consistent, you can use Colocation Join.
• Aggregation
  • Aggregation: Runs the GROUP BY Aggregate operation. ByteHouse performs two-stage aggregation: first, Partial Aggregation is carried out on each node, then the partial results are Shuffled to a few nodes for Final Aggregation to lift the Degree of parallelism.
• Runtime Filter
  • The runtime filter (Runtime Filter) is a key RD in ByteHouse's Join optimization. During the build phase of Join (Build side, usually the small table), it will generate a filter based on the Join Key, then push this filter down to the scan phase of the fact table (Probe side, usually the large table). This allows a large amount of Data that cannot Match the Join Condition to be filtered out early during the scan of the large table, greatly reducing Data transfer and Calculate volume during the Join process. Seeing Runtime Filter in EXPLAIN usually means Join performance will be Significantly lifted. It relies on statistics, and you can appropriately increase the wait_runtime_filter_timeout Parameter value.

2.2.3. Pass EXPLAIN to identify FAQs #

• Full table scan: TableScan reads all Partitions.
• Predicate pushdown failed: The Filter Operator is far from TableScan, separated by Join or other complex Actions.
• Unreasonable Join Type: The order of Join between large and small tables is incorrect. If there is Insufficient information, you can adjust it using SQL hint.
• Data skew: An instance of an Aggregation or Join Operator takes much longer than other instances (requires query_log Analysis).

3.1 Data model and DDL optimization #

Data models are the cornerstone of data warehouses, and good design is the starting point for high performance. In ByteHouse, the design of the table structure directly determines the physical storage of data and the efficiency of data scanning during queries.

Best practices for table structure design #

• Select partition keys (Partition Key) appropriately
  Partition keys are the most important and effective pruning tools in ByteHouse. By specifying filter conditions for partition keys in the WHERE clause, ByteHouse can scan only the relevant partitions and skip a large amount of unrelated data.
  Selection principles:

  1. Must be fields commonly used for query filtering: Select columns that are used to filter data in the vast majority of queries, with the most typical being date or time fields (such as dt, event_date).
  2. Moderate cardinality (Cardinality): The number of partitions should not be too many or too few. Too many partitions (such as partitioning by seconds) will increase metadata management overhead; too few partitions (such as partitioning by year) will lose the benefit of partition pruning. It is generally recommended to partition by month, week, or day, with the total number of partitions kept below 100,000.
  3. Uniform partition granularity: Ensure that the amount of data in each partition is roughly balanced to avoid data skew.
     Example:

  -- Create an event table partitioned by day
  CREATE TABLE events (
      event_id String,
      user_id UInt64,
      event_time DateTime,
      -- ... Other columns
  ) ENGINE = CNCHMergeTree
  PARTITION BY toYYYYMMDD(event_time) -- Use date as the partition key
  ORDER BY (user_id, event_time);
  

• Order key (Order Key) and data distribution
  The order key determines how data is physically sorted and stored within each partition. This ordering brings the following benefits:

  1. Accelerated filtering: For range queries on sorted columns, ByteHouse can leverage their ordering to quickly locate data blocks, similar to the effect of an index.
  2. Improved compression ratio: Identical values are clustered together, allowing for a higher compression ratio.
     Selection principles:
  3. High-frequency filtering columns first: Place the columns most commonly used in WHERE conditions at the beginning of the order key.
  4. Low cardinality columns first: Place columns with lower cardinality (such as country, gender) before columns with higher cardinality (such as user_id) to improve compression effectiveness.

• Data type selection
  Select the most compact and appropriate data type, which not only saves storage space but also improves computational efficiency.

  • Type: Use the UInt series instead of Int (if there are no negative numbers), and select the smallest type that can accommodate the data range, such as using UInt8 or UInt16 instead of UInt64.
  • String: For strings with a limited cardinality, use the LowCardinality(String) or Enum type, which can be converted to dictionary encoding for storage, greatly reducing storage and accelerating filtering and aggregation.
  • Avoid ****NULL: Define the NOT NULL property for columns whenever possible; Nullable columns incur additional storage and computational overhead.

3.2 Data skipping index #

Data skipping index is a query acceleration function supported by ByteHouse. When processing large volumes of data, query performance may decline due to the need for full column scans to apply the WHERE clause. Data skipping index addresses this problem by allowing queries to skip data blocks that are confirmed not to contain matching values.

Create a data skipping index #

Four main parameters are involved when creating an index:

1. Index Name (Index Name): Serves as the identifier for creating, deleting, or materializing index files.
2. Index Expression (Index Expression): The formula used to calculate and determine the range of values stored in the index, which can include columns, basic operators, and functions.
3. Type (Type): Defines the calculation method used to skip reading and evaluating each index block.
4. Granularity (Granularity): Defines the number of granules contained in each index block. For example, if the primary table index granularity is 8,192 rows and the data skipping index granularity is 4, then each index block will contain 32,768 rows (8,192 x 4).

Syntax example

/*Index name: key_i_idxIndex expression: key_iType: minmaxGranularity: 1*/
INDEX key_i_idx key_i TYPE minmax GRANULARITY 1

Supported data types #

1. Minmax (minmax)

• Description: This is an efficient indexing method that retains the minimum and maximum values of the index expression for each data block. It is most effective for loosely sorted columns and is typically the most cost-effective index type during query execution.
• Limit: Only applicable to scalar or tuple expressions; cannot be used with expressions that generate array or map data types.

2. Set (Set)

• Description: This lightweight index includes a parameter max_size for setting the size of the value set for each block. When max_size is 0, the value set size is unlimited. It is suitable for columns where the median value of each group of granules is clustered.
• Cost: The cost and performance of the index depend on the cardinality within the block. If there are too many unique values, the evaluation cost will be higher; if the value set is empty because it exceeds max_size, the index will not be applied.

3. Bloom filter types (Bloom filter types)

A Bloom filter is a compact data structure used to determine whether a member exists in a set; there may be a very small false positive rate. In the skip index, false positives only lead to reading some unnecessary blocks.

• tokenbf_v1: Customized for enhanced Bloom filter functionality, suitable for String and FixedString types. It splits the input expression into a sequence of characters by non-alphanumeric characters. This index can be used for LIKE, EQUALS, in operations, and is suitable for searching a small number of specific values in unstructured logs. Its parameters include:
  • Filter size (bytes)
  • Number of hash functions
  • Hash function seed
• ngrambf_v1: The function is similar to tokenbf_v1, but adds an extra parameter, which is the size of the ngram to be indexed. It is useful for text search in languages without word breaks, such as Chinese.
• bloom_filter([false_positive]): General Bloom filter, the false positive probability can be specified by the false_positive parameter (range 0-1, default 0.025). Supports multiple data types, including Int*, UInt*, Float*, String, Array, and others.

Analyze index effectiveness

EXPLAIN ANALYZE indexes = 1 SQL

Examples are as follows:

3.3 Usage examples #

1. Load the dataset into ByteHouse #

First, create a database and a table to load sample data.

DROP DATABASE IF EXISTS test_si;
CREATE DATABASE test_si;

-- Create a table to load the numbers_source.csv dataset
CREATE TABLE test_si.numbers
(
    `number` UInt64
)
ENGINE = CnchMergeTree
ORDER BY number;

Then, use the data loading function to load the numbers_source.csv dataset into the test_si.numbers table.

2. Create a table without an index #

Create a table without an index and insert data.

CREATE TABLE test_skipindex
(
    `id` UInt64,
    `key_i` UInt64,
    `p_date` Date
)
ENGINE = CnchMergeTree
PARTITION BY p_date
ORDER BY id;

INSERT INTO test_skipindex SELECT
    number,
    number,
    today()
FROM test_si.numbers
LIMIT 10000000;

Running the following query will process all 10 million rows of data.

SELECT * FROM test_skipindex WHERE key_i in (200, 700);

3. Add an index #

Add an index to the new table

Define the index directly when creating the table.

CREATE TABLE test_skipindex
(
    `id` UInt64,
    `key_i` UInt64,
    `p_date` Date,
    -- Add index when creating the table
    INDEX key_i_idx key_i TYPE minmax GRANULARITY 1
)
ENGINE = CnchMergeTree
PARTITION BY p_date
ORDER BY id
SETTINGS index_granularity = 8192;

INSERT INTO test_skipindex SELECT
    number,
    number,
    today()
FROM test_si.numbers
LIMIT 1000000;

Running the following query again, only 8,192 rows of data are processed, and the execution speed is significantly improved.

SELECt * FROM test_skipindex WHERE key_i in (200, 700);

Add an index to historical data

If the index is added after data insertion, by default, the index only applies to newly written data. For existing historical data, the materialize operation must be performed.

1. Add index:

   ALTER TABLE test_si.test_skipindex ADD index key_i_idx key_i TYPE minmax GRANULARITY 1;
   

2. Add materialized index:

   -- Build indexes for all historical data
   ALTER TABLE test_si.test_skipindex MATERIALIZE INDEX key_i_idx;
   

After execution, queries will be able to utilize the index, greatly reducing the number of data rows processed.

Add index to historical data partition

To build an index for only specific partitions, use the IN PARTITION clause. Only the data in the specified partitions will generate indexes, while historical data in other partitions will not be affected.

-- Assume the previous day's date is '20230927'
ALTER TABLE test_si.test_skipindex MATERIALIZE INDEX key_i_idx IN PARTITION '20230927';

4. Delete index #

Use the DROP INDEX command to delete an existing index.

ALTER TABLE test_si.test_skipindex DROP INDEX key_i_idx;

Usage suggestions #

When the data volume is small, the cost of building an index is not high. But if you need to build an index for all historical data, directly using MATERIALIZE INDEX may place a heavy load on the cluster. In this case, it is recommended to use IN PARTITION to build indexes partition by partition, reducing the pressure on the cluster.
When the data satisfying the query conditions has obvious "locality", you can use a tuned index to accelerate the query.

3.4 Efficient SQL writing techniques #

Good data models require efficient SQL to match, and following best practices can avoid many common performance pitfalls.

SELECT query optimization #

Reduce scanned data volume: WHERE clause best practices

• Partition key filtering: Always include partition key filters in WHERE.
• Predicate pushdown: Ensure filters can be pushed down to the storage layer. Avoid using non-monotonic functions on columns in the WHERE clause, as this may cause index and partition pruning failure.

WHERE toYYYYMM(event_time) > 202508;

WHERE event_time >= '2025-08-01 00:00:00' AND event_time < '2025-09-01 00:00:00';

This example uses value filters. Based on partition filtering, the engine can further prune unnecessary Parts using minmax information on the Part partition key.

Use PREWHERE

For filtering on non-sorting keys, PREWHERE is more efficient than WHERE. ByteHouse will first read columns related to PREWHERE, filter the data, and then read other columns required by SELECT.
The normal engine will automatically push suitable conditions from the WHERE clause down to PREWHERE. If the business logic can determine that a certain condition has a high filtering rate, you can manually write the WHERE condition into PREWHERE, especially for MAP-type subcolumns (the engine does not automatically push these down).

SELECT 
    order_id, 
    user_id, 
    total_amount, 
    payment_method,
    province,
    items  -- Read large fields last
FROM order_data
PREWHERE 
    order_date BETWEEN '2024-04-01' AND '2024-06-30'  -- First filter the date range
    AND status = 'paid'  -- Filter for paid status
WHERE 
    payment_method = 'credit_card'  -- Further filter by payment method
    AND total_amount > 1000  -- Filter by amount condition

Optimize JOIN operations

• JOIN order: Place the large table on the left side of the JOIN clause and the small table on the right side. The engine usually automatically reorders them; if not, you can first collect statistics. For details, please refer to 4.1 Statistics collection.
• Pre-filtering before JOIN: Use CTE or subqueries to filter and aggregate the tables involved in the JOIN, reducing the data volume for JOIN.

Theoretical knowledge needs to be combined with actual scenarios to maximize its value. This section provides specific optimization strategies and case analysis for three typical search scenarios—complex search, batch processing, and real-time search.

4.1 Statistics collection #

Automatic collection #

For ByteHouse CDW, you need to specify the virtual warehouse used to run statistics collection.

-- Specify virtual warehouse for collecting statistics
CREATE AUTO STATS *.* WITH statistics_virtual_warehouse='vw-stats-collector';

Manual trigger collection #

After significant changes in data (such as importing large volumes of data), you can manually trigger statistics collection once to ensure the optimizer can immediately use the latest information.

-- Manually collect statistics for a single table
CREATE STATS table_name;

-- Collect statistics for the entire database
CREATE STATS DATABASE db_name;

How to determine if statistics are outdated or inaccurate #

• View EXPLAIN plan: If the estimated rows value shown in EXPLAIN differs greatly from the actual value (by more than an order of magnitude), it usually means the statistics are inaccurate.
• Search performance fluctuation: If a stable search suddenly performs worse, it may be because data changes have caused the original statistics to become invalid and new statistics have not yet been collected.

4.2 Complex search optimization #

Complex searches typically involve JOIN operations across multiple large tables, nested subqueries, CTEs, and window functions, making them hotspots for performance problems.

Multi-table JOIN search #

Core strategy:

1. Reduce the data volume for JOIN: Before JOIN, use WHERE or CTE to filter out invalid data as much as possible.
2. Join order: Ensure the small table is on the right side.
3. Consistent JOIN key types and use of Runtime Filter: This is ByteHouse's flagship feature. Ensure that the JOIN key types of the fact table (large table) and the dimension table (small table) are completely consistent, so that the Runtime Filter is effective and to avoid the performance overhead caused by implicit type conversion.

Queries containing subqueries or CTEs #

Core strategy:

1. Use CTEs to improve readability: Prefer using the WITH clause (CTE) instead of nested subqueries. The code is clearer and also easier for the optimizer to analyze.
2. Materialize CTE (MATERIALIZE): If a CTE is referenced multiple times, consider using a materialized view (trading space for time).

Case study: Complex report query optimization #

A typical scenario is to calculate the sales amount and number of users for each city and each category.

• Performance problem: Both the sales table and the users table are very large. Direct JOIN leads to massive data Shuffle. count(DISTINCT) operation performs poorly on high cardinality columns.
• Performance before optimization: Query time 95 seconds.
• Optimization approach: First, pre-aggregate the largest table sales by week, which greatly reduces the amount of data involved in the JOIN. At the same time, use the uniqCombined approximate deduplication function to avoid the high cost of exact deduplication.
• Performance after optimization: Query time 8 seconds.

SQL before optimization:

-- Directly JOIN three large tables without pre-filtering
SELECT
    ci.city_name,
    p.category_name,
    sum(s.sale_amount) AS total_sales,
    count(DISTINCT s.user_id) AS total_users
FROM sales s
JOIN users u ON s.user_id = u.user_id
JOIN cities ci ON u.city_id = ci.city_id
JOIN products p ON s.product_id = p.product_id
WHERE s.dt BETWEEN '2025-08-01' AND '2025-08-07'
GROUP BY 1, 2;

SQL after optimization:

-- Use CTE for pre-aggregation and replace exact deduplication with approximate calculation functions
WITH weekly_sales AS (
    -- Step 1: Pre-aggregate and filter the largest fact table
    SELECT
        user_id,
        product_id,
        sum(sale_amount) AS sale_amount
    FROM sales
    WHERE dt BETWEEN '2025-08-01' AND '2025-08-07'
    GROUP BY 1, 2
)
SELECT
    ci.city_name,
    p.category_name,
    sum(s.sale_amount) AS total_sales,
    uniqCombined(s.user_id) AS approx_total_users -- Step 2: Use uniqCombined for high-performance approximate deduplication
FROM weekly_sales s
-- Step 3: JOIN the small aggregated result set with the dimension table
JOIN users u ON s.user_id = u.user_id
JOIN cities ci ON u.city_id = ci.city_id
JOIN products p ON s.product_id = p.product_id
GROUP BY 1, 2;

4.3 Batch data processing optimization #

Batch processing tasks (such as ETL/ELT) are characterized by large data volumes and scheduled execution. The focus of optimization is on throughput and resource efficiency.

Large-scale data import (INSERT INTO ... SELECT) #

Core strategy:

1. Adjust block size: Appropriately increase the size of the write block (max_insert_block_size) to generate more regular data partitions and reduce the pressure of background merge.
2. SELECT side optimization: INSERT INTO ... The bottleneck of SELECT is often on the SELECT side. Apply all SELECT optimization techniques from previous chapters.

Periodic ETL/ELT task tuning #

Core strategy:

1. Task splitting: Split a large, complex ETL task into multiple independent and parallel subtasks.
2. Resource isolation: Configure a dedicated compute group (VW) for ETL tasks to avoid competing for resources with online query services.
3. Enable BSP mode: For operation details, please refer to ELT development.

Case study: Night batch processing task performance improvement #

This task requires cleaning and transforming the day's log data every day at midnight, then writing it into a user behavior summary daily table.

• Performance problem: A single SQL is overly complex, making it difficult for the optimizer to generate the optimal plan. All calculations are concentrated in one task, making parallelism impossible.
• Performance before optimization: Task duration is 2.5 hours, with frequent latency, affecting downstream reports.
• Optimization approach: Apply the divide-and-conquer strategy to the monolithic task. Each step performs only one task, resulting in clear logic. Steps can be executed in parallel (if there are no dependencies), and the intermediate result of each step can be reused and checked. This reduces the complexity of optimization and also improves overall robustness.
• Performance after optimization: Task duration is 40 minutes.

SQL before optimization:

-- A massive INSERT ... SELECT statement containing complex JOIN and transformation logic
INSERT INTO user_behavior_daily
SELECT
    user_id,
    toDate(event_time) AS dt,
    -- ... Numerous complex UDFs and multi-table JOIN logic ...
FROM raw_logs
WHERE toDate(event_time) = yesterday();

Optimized solution:

-- Step 1: Clean and initially process logs, write to temporary table 1
CREATE TABLE cleaned_logs_tmp ...;
INSERT INTO cleaned_logs_tmp
SELECT ... FROM raw_logs WHERE toDate(event_time) = yesterday();

-- Step 2: Relate dimension information, write to temporary table 2
CREATE TABLE enriched_logs_tmp ...;
INSERT INTO enriched_logs_tmp
SELECT ... FROM cleaned_logs_tmp JOIN users ON ...;

-- Step 3: Final aggregation, write to target table
INSERT INTO user_behavior_daily
SELECT ... FROM enriched_logs_tmp GROUP BY ...;

-- Clean up temporary tables
DROP TABLE cleaned_logs_tmp;
DROP TABLE enriched_logs_tmp;

4.4 Real-time query optimization #

Real-time query scenarios (such as online Dashboard) require extremely low query latency and high concurrency capability.

Key points for designing low-latency queries #

Core strategy:

1. Data pre-aggregation: Do not perform complex calculations during real-time queries. Create pre-aggregated summary tables (Flink wide table flattening and materialized views are excellent tools for this), so that queries return results directly.
2. Ultimate SELECT optimization: Apply all SELECT optimization techniques to ensure the query makes maximum use of partitions and indexes, scanning the least amount of data.
3. Avoid joining large tables: Real-time queries should avoid JOIN operations as much as possible, especially Shuffle Join. If a JOIN is necessary, use the wide table mode for large tables to flatten dimension information into the fact table in advance.

Concurrent query performance tuning #

Core strategy:

1. Expand compute group: During peak periods, handle high-concurrency requests by increasing the number of nodes in the compute group (Scale Out) or upgrading node specifications (Scale Up).
2. Query queue and priority: Set different resource queues and priorities for queries of varying importance to ensure that core business queries are not blocked by low-priority tasks.
3. Leverage intermediate results cache: For highly repetitive queries, ByteHouse's intermediate results cache can return results within seconds, greatly reducing backend load. Activate the parameter enable_intermediate_result_cache.

Case study: High-concurrency dashboard query response time optimization #

A monitoring Global needs to refresh the latest 1 minute Core Metric every 5 seconds.

• Performance Problem: The api_calls table writes tens of thousands of rows per second. Even when searching only 1 minute Data, the Aggregate calculation overhead is unacceptable under high concurrency.
• Performance before optimization: AVG response Time 500 ms, Latency spikes to 2-3 seconds as concurrency rises.
• Optimization approach: Materialized views continuously and incrementally pre-Aggregate in the background. The frontend Search no longer needs to Calculate Origin Event, but instead searches a materialized view with very few rows (only several Status types * 6 Time window items). countState and sumState and others functions enable efficient Merge of Aggregate As a result.
• Performance after optimization: AVG response Time < 50ms, remains stable under high concurrency.

SQL before optimization:

-- Aggregate directly on the origin event table
SELECT
    status,
    count(*),
    avg(response_time)
FROM api_calls
WHERE event_time >= now() - INTERVAL '1 MINUTE'
GROUP BY status;

Optimized solution:

-- Step 1: Create a materialized view that refreshes every 10 seconds
CREATE MATERIALIZED VIEW api_calls_10s_mv
ENGINE = CnchMergeTree
AS SELECT
    toStartOfInterval(event_time, INTERVAL 10 SECOND) AS t_10s,
    status,
    countState() AS calls,
    sumState(response_time) AS total_response_time
FROM api_calls
GROUP BY t_10s, status;

-- Step 2: Dashboard queries materialized view
SELECT
    status,
    sum(calls),
    sum(total_response_time) / sum(calls)
FROM api_calls_10s_mv
WHERE t_10s >= toStartOfInterval(now() - INTERVAL '1 MINUTE', INTERVAL 10 SECOND)
GROUP BY status;

SQL optimization is not a one-time Mission, but a continuous process. Establishing effective Monitor and manage mechanisms is key to ensuring long-term health and high efficiency of the Data warehouse.

5.1 Search Monitor and Analysis #

use System table (bh_system.query_log) for performance Analysis #

bh_system.query_log is ByteHouse's "black box". It Record detailed information for every Search Run. This is the most important Datasource for post-performance Diagnosis and audit.
Recommend key Field:

• query: Run SQL statement.
• type: Search Event type (such as QueryStart, QueryFinish).
• query_duration_ms: Total Search Time (ms).
• read_rows, read_bytes: Number of rows and bytes read.
• written_rows, written_bytes: Number of rows and bytes written.
• memory_usage: Peak memory usage.
• exception_code: Abnormal code, used to locate Failed Search.

Analysis Example:

-- Find the top 10 most time-consuming queries in the past 24 hours
SELECT
    query_duration_ms,
    read_rows,
    memory_usage,
    query
FROM bh_system.query_log
WHERE type = 'QueryFinish'
  AND query_start_time >= now() - INTERVAL '1 DAY'
ORDER BY query_duration_ms DESC
LIMIT 10;

Locate slow Search and Search with Large Resources Consumption #

Pass Aggregate Analysis of bh_system.query_log, you can easily locate various Problematic Search.

• Find CPU Consumption heavy Search: Click sort by ascend ProfileEvents['UserTimeMicroseconds'] + ProfileEvents['SystemTimeMicroseconds'].
• Find memory Consumption heavy Search: Click sort by ascend memory_usage.
• Find Search with high Failed rate: Count Search where exception_code != 0.

Visualization Performance Monitoring Tools #

Analysis can be performed according to Metrics exposed in the Bytehouse Console. For details, please refer to Alarm.

5.2 Resource management and concurrency control #

Configuration and scaling of virtual warehouses #

The virtual warehouse (Virtual Warehouse, VW) is the unit for managing computing resources in ByteHouse. By properly configuring virtual warehouses, you can achieve resource isolation and elastic scaling.

• Create different virtual warehouses for different business scenarios: For example, create separate virtual warehouses for ETL tasks, BI reports, and ad-hoc queries to prevent interference between them.
• Elastic scaling:
  • Scale Up (vertical scaling): Increase the node specifications of the virtual warehouse (such as L -> XL -> 2XL), suitable for complex queries that require larger memory or greater single-node computing power.
  • Scale Out (horizontal scaling): Increase the number of nodes in the virtual warehouse, suitable for handling high-concurrency query scenarios.

Concurrent query tuning and queuing strategy #

• Max concurrency settings: You can set max_concurrent_queries for each virtual warehouse to prevent too many queries from entering simultaneously, which would slow down all queries.
• Limit large queries: Limit the CPU usage cap for a single query by setting the parameter max_query_cpu_seconds.
• Query queue: When the concurrency cap is reached, subsequent queries will enter the queue and wait. Proper queue settings can ensure system stability.
• Priority: You can set query priorities for different users or roles to ensure that core business requests are processed first.