Performance Tuning Overview
Query performance tuning is a systematic effort that requires evaluating and optimizing the database system across multiple layers and dimensions. From a practitioner's perspective, this article introduces the overall workflow, methodology, and tooling system for VeloDB Cloud performance tuning.
Prepare to tune queries
Before getting into specific tuning work, complete the following preparation:
- You know the cluster size and the workload sharing that cluster.
- You can review CPU, memory, cache, and network usage in Metrics.
- You have confirmed the warehouse Core version and the features it supports.
- You can distinguish between "slow business" and "slow SQL," and pinpoint the specific problematic SQL.
- You are familiar with the available diagnostic and analysis tools.
Performance tuning methodology
Performance tuning in one sentence: First understand the system, then locate slow SQL, then use tools to find bottlenecks, and finally apply optimizations.
Four-step tuning method
The following table summarizes the core workflow of performance tuning:
| Step | Goal | Key Actions |
|---|---|---|
| 1 | Understand the system | Know the cluster size, warehouse Core version, workload, and available features |
| 2 | Locate the problematic SQL | Use diagnostic tools to efficiently and quickly identify slow SQL |
| 3 | Analyze performance bottlenecks | Use EXPLAIN, Query Profile, and cluster metrics to collect runtime information |
| 4 | Apply tuning | Based on the bottleneck analysis, adjust parameters, rewrite SQL, and apply optimizations such as indexes and materialized views |
Apply each step
- Step 1: Understand the system. Business users and DBAs need to build a comprehensive understanding of the database system in use to avoid investigating problems in the wrong direction.
- Step 2: Locate slow SQL. A useful performance diagnostic tool is a prerequisite for locating performance issues. Subsequent work is meaningful only after the problematic SQL is quickly identified.
- Step 3: Analyze bottlenecks. Compare
EXPLAINand Query Profile with Metrics for the same time range to determine where the bottleneck lies. - Step 4: Apply optimizations. Based on detailed runtime information and supplementary analysis, adjust configuration or rewrite SQL in a targeted way.
Tuning tools
VeloDB Cloud provides corresponding tools at every layer of the tuning workflow. The following table maps tools to scenarios:
| Tool Category | Problem It Solves | Documentation Entry |
|---|---|---|
| Diagnostic Tools | Quickly locate slow SQL and problematic SQL | Diagnostic Tools |
| Analysis Tools | Analyze SQL execution details and bottlenecks | Analysis Tools |
| Tuning Process | Complete end-to-end tuning practice guide | Tuning Process |
FAQ and common misconceptions
Q1: Does performance tuning have to start with rewriting SQL?
Not necessarily. Tuning should start with "understanding the system + locating slow SQL." Skipping these two steps and jumping straight to rewriting SQL often misses the real bottleneck.
Q2: Are EXPLAIN and Query Profile alone enough?
Compare them with cluster metrics. CPU, memory, cache, remote-storage, and network metrics help distinguish operator inefficiency from resource pressure. For persistent infrastructure issues, collect the query ID and incident time range for VeloDB Cloud support.
Q3: Is tuning the DBA's job alone?
No. Performance tuning requires collaboration among business users, DBAs, and even developers: business users understand the SQL semantics, while DBAs understand the cluster and database characteristics.
Next steps
- Locate slow SQL: see Diagnostic Tools
- Analyze execution details: see Analysis Tools
- End-to-end practice: see Tuning Process