DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

GBase 8c Performance Tuning: From Statistics to Execution Plan Optimization

#gbase #database #数据库

As data volumes and concurrent access grow, database performance becomes critical to system stability. Drawing from official technical manuals, this article distills core performance optimization techniques for GBASE's GBase 8c, the China‑domestically developed distributed database, covering statistics updates, execution plan intervention, SQL rewriting, and key parameter configuration.

The Foundation: Understanding Execution Plan Generation

GBase 8c is a cost‑based distributed database. Its optimizer acts like a precise navigation system, choosing what it considers the optimal execution path (execution plan) for every SQL statement. This decision relies heavily on "map data"—statistics.

The optimizer estimates row counts and costs based on statistics gathered by the ANALYZE command (stored in pg_class and pg_statistic system tables). Outdated statistics are like using an old map—they can easily mislead the optimizer into generating poor execution plans.

Core principle: Before attempting any complex tuning, first ensure your statistics are accurate and up to date.

Essential Optimization: Updating Statistics

Statistics are the source data the planner uses to generate plans:

-- Update statistics for a single table
ANALYZE tablename;

-- Update statistics for all tables in the current database
ANALYZE;

-- Collect multi‑column statistics for correlated columns
ANALYZE tablename((column_1, column_2));
Enter fullscreen mode Exit fullscreen mode

Automation: GBase 8c provides the autovacuum daemon to automatically reclaim space and update statistics. Use the autovacuum and autovacuum_mode parameters to control its behavior (e.g., set to mix mode for both cleanup and analysis).

Finding Bottlenecks: How to Locate Slow Queries

Business signals: Interface timeouts, slow application responses, or errors are the most direct indicators.

Proactive detection: Conduct database inspections or query slow logs.

Enable slow logging by configuring the track_stmt_stat_level parameter (e.g., 'OFF,L0') and ensure enable_stmt_track is on.

Query slow logs:

SELECT * FROM dbe_perf.get_global_slow_sql_by_timestamp('2024-05-07 04:00:00', '2024-05-07 04:10:00');
Enter fullscreen mode Exit fullscreen mode

Deep Intervention: Using Plan Hints to Change Execution Plans

When the optimizer chooses an undesirable plan, GBase 8c provides powerful Plan Hint functionality to guide the optimizer directly.

How to Use

Add Hints using /*+ ... */ comment syntax before the SQL statement. Separate multiple Hints with spaces:

SELECT /*+ ... */ * FROM table_name;
Enter fullscreen mode Exit fullscreen mode

Supported Hints

  • Join order: Leading((t1 t2 t3))
  • Join method: NestLoop(t1 t2), HashJoin(t1 t2), MergeJoin(t1 t2)
  • Scan method: IndexScan(t1 index_name), SeqScan(t1), IndexOnlyScan(t1 index_name)
  • Estimated row count: Rows(t1 #10) (suggests table t1 has about 10 rows)

Tuning Example

By default, a two‑table join may use HashJoin. If you determine NestLoop is better, force it:

-- Default may be HashJoin
EXPLAIN SELECT * FROM stu s JOIN stu_info i ON s.id = i.stu_id;

-- Force NestLoop with a Hint
SELECT /*+ NestLoop(s i) */ * FROM stu s JOIN stu_info i ON s.id = i.stu_id;
Enter fullscreen mode Exit fullscreen mode

Caution: Hints are a double‑edged sword. They bypass the optimizer's cost calculations. Use them as a last resort and continually verify their effectiveness.

System‑Level Tuning: Key GUC Parameters

Adjusting database parameters can nudge execution plans toward better strategies:

  • work_mem: Increasing this value helps operations like sorts and hash joins complete in memory, reducing disk I/O.
  • shared_buffers: Used for caching data. For read‑heavy applications, increasing this value can significantly improve performance.
  • Optimizer switches: Parameters like enable_hashjoin and enable_indexscan can disable specific query strategies, forcing the optimizer to choose alternatives (similar to Hints, but applied at the session or global level).

Example configuration:

gs_guc set -Z coordinator -D /path/to/data_dir -c "work_mem = 16MB"
Enter fullscreen mode Exit fullscreen mode

SQL Rewriting Tips

  • Use UNION ALL instead of UNION: If business logic guarantees no duplicates, UNION ALL avoids expensive deduplication.
  • Add non‑null filters for JOIN columns: If NULL values are common on JOIN columns, adding IS NOT NULL filters data early and improves JOIN efficiency.
  • Use TRUNCATE instead of DELETE to clear tables: TRUNCATE is faster and releases disk space immediately.
  • Explicitly specify column names in INSERT statements: Improves readability and stability.
  • Choose the right storage model: Select row‑store or column‑store tables based on the workload (OLTP vs OLAP).

Performance tuning for GBase 8c is a systematic process, with statistics, execution plan intervention, and SQL rewriting all interlinked. Integrating these methods into daily operations will help you effectively tackle the growing challenge of slow queries in your gbase database environment.

Top comments (0)