Overview

To fine-tune openGauss performance, you need to identify performance bottlenecks, adjust key parameters, and optimize SQL statements. During performance tuning, locate and analyze performance issues based on performance elements, such as system resources, throughput, and loads to achieve required system performance.

Various factors must be considered during openGauss performance tuning. Therefore, optimization personnel must know well about knowledge, such as system software architecture, hardware and software configuration, database parameter configuration, concurrency control, query processing, and database applications.

NOTICE:
Performance tuning sometimes require openGauss restart, which may interrupt current services. Therefore, after the service goes live and when openGauss needs to be restarted, you must send the request to related management department about the operation window time for approval.

Tuning Process

Figure 1 shows the procedure of performance tuning.

Figure 1 openGauss performance tuning

Table 1 lists the details about each phase.

Table 1 openGauss performance tuning

Phase	Description
Determining the Scope of Performance Tuning	The phase where the CPU, memory, I/O, and network resource usage of each node in openGauss are obtained to check whether these resources are fully used and whether any bottleneck exists
System Optimization	The phase where OS and database system level optimization are performed to fully use the CPU, memory, I/O, and network resources, prevent resource conflicts, and improve the query throughput in the system
SQL Optimization	The phase where the SQL statements used are analyzed to determine whether any optimization can be performed. Analysis of SQL statements comprises: Generating table statistics using ANALYZE: The ANALYZE statement collects statistics about the database table content. Statistical results are stored in the system catalog PG_STATISTIC. The execution plan generator uses these statistics to determine which one is the most effective execution plan. Analyzing the execution plan: The EXPLAIN statement displays the execution plan of SQL statements, and the EXPLAIN PERFORMANCE statement displays the execution time of each operator in SQL statements. Identifying the root causes of issues: Identifies possible causes by analyzing the execution plan and perform specific optimization by modifying database-level SQL optimization parameters. Compiling better SQL statements: Compiles better SQL statements in the scenarios, such as cache of intermediate and temporary data for complex queries, result set cache, and result set combination.

Phase

Description

Determining the Scope of Performance Tuning

The phase where the CPU, memory, I/O, and network resource usage of each node in openGauss are obtained to check whether these resources are fully used and whether any bottleneck exists

System Optimization

The phase where OS and database system level optimization are performed to fully use the CPU, memory, I/O, and network resources, prevent resource conflicts, and improve the query throughput in the system

SQL Optimization

The phase where the SQL statements used are analyzed to determine whether any optimization can be performed. Analysis of SQL statements comprises:

Generating table statistics using ANALYZE: The ANALYZE statement collects statistics about the database table content. Statistical results are stored in the system catalog PG_STATISTIC. The execution plan generator uses these statistics to determine which one is the most effective execution plan.
Analyzing the execution plan: The EXPLAIN statement displays the execution plan of SQL statements, and the EXPLAIN PERFORMANCE statement displays the execution time of each operator in SQL statements.
Identifying the root causes of issues: Identifies possible causes by analyzing the execution plan and perform specific optimization by modifying database-level SQL optimization parameters.
Compiling better SQL statements: Compiles better SQL statements in the scenarios, such as cache of intermediate and temporary data for complex queries, result set cache, and result set combination.