Master Hologres: End-to-End SQL Diagnosis & Optimization for Stable Cloud Data

Abstract: In this talk we demonstrate how to use a series of diagnostic and tuning tools to achieve comprehensive diagnosis of SQL and database anomalies, thereby improving instance stability.

Outline

Hologres Diagnosis and Optimization Practice

Pre‑stage: Real‑time monitoring of instance anomalies

In‑stage: Using active SQL logs to quickly locate long queries

Post‑stage: Diagnosing slow queries with slow‑query logs + Query Insight

Post‑stage: Governing erroneous queries

Stability governance: Long‑term SQL diagnosis

Stability governance: Meta diagnosis for table issues

Cost governance

Pre‑stage: Instance Real‑time Monitoring

Teams should review business in advance to detect potential issues. Use monitoring metrics and set alerts. Hologres provides five categories of metrics covering resources, query, I/O, traffic, and framework. Detailed usage is in the documentation.

Resource and Query Monitoring

CPU monitoring: high CPU usage indicates resource shortage; uneven CPU across workers may signal data skew.

Memory monitoring: shows overall and module‑specific memory usage.

QPS and RPS: reflect read/write load, dependent on instance specs and query complexity.

Query latency: includes stage‑wise latency, overall latency, P99; watch longest running query.

Failed queries: spikes may indicate instance anomalies.

I/O and Storage Monitoring

IO: reflects interaction between queries and underlying storage.

Storage: shows actual data storage.

Traffic and Framework Monitoring

Endpoint traffic: monitors inbound/outbound traffic across networks.

Framework: monitors shard replica latency; high FE replay latency signals worker stalls.

Gateway: monitors CPU, memory, and network usage for SQL routing.

Serverless and Elastic Resources

Serverless Computing: monitor query latency and queue status.

Computing Resource: monitor elastic usage.

Binlog and Analyze

Auto Analyze: shows missing statistics for tables.

Binlog: monitors throughput and sender connections.

Monitoring Alert Best Practices

Set alert thresholds in the alarm settings. Recommended alerts include CPU usage, query latency, failed queries, and longest running query duration.

In‑stage: Active SQL Log for Long Queries

Use Holoweb → Diagnosis & Optimization → Active Query to view running SQLs with duration and engine info. Cancel long‑running queries to free resources.

Post‑stage: Slow Query Diagnosis

Use Slow Query Log combined with Query Insight. Steps: 1) Retrieve slow SQL list, focus on duration, engine type, CPU time. 2) Open Query Insight for detailed analysis (engine type, read rows/bytes). 3) Examine execution plan, focusing on operators such as partitionselected, filter, time, rows.

Post‑stage: Governing Erroneous Queries

When Failed Query count rises, use Slow Query Log + Query Insight. Steps: monitor FailedQueryQPS, locate error SQLs in the log, then use Query Insight to view error details and AI‑generated remediation suggestions.

Stability Governance: Long‑term SQL Diagnosis

Hologres provides a SQL Diagnosis Report showing daily and historical SQL performance, error rates, and application sources. Governance items include failed query analysis, long‑running query trends, application source breakdown, and execution engine distribution (HQE, PQE, SQE).

Stability Governance: Meta Diagnosis

Meta diagnosis checks consistency between the storage master and FE node metadata. Inconsistencies cause DDL errors. The tool updates weekly and offers one‑click repair during low‑traffic periods.

Cost Governance: Table & Index Diagnosis

As table count grows, governance focuses on storage, partition sub‑tables, zero‑storage tables, missing primary keys, mismatched distribution/cluster keys, excessive column counts (>300), and index configurations with more than three columns. Binlog settings are also reviewed.

By analyzing table attributes, teams can optimize storage, index usage, and overall cost.