Help Center/ GaussDB/ Developer Guide(Centralized_2.x)/ Performance Tuning/ Performance Tuning Process
Updated on 2023-10-23 GMT+08:00
View PDF
Share

Performance Tuning Process

Multiple factors need to be considered during GaussDB performance optimization. Therefore, optimization personnel must have a wide and deep understanding of the system software architecture, software and hardware configurations, database configuration parameters, concurrency control (The current feature is a lab feature. Contact Huawei technical support before using it.), query processing, and database applications.

During performance optimization, the database needs to be restarted sometimes, which may interrupt current services. Therefore, after services are brought online, if the database needs to be restarted for performance optimization, submit an application to the management department for the operation window time. The operation can be performed only after the application is approved.

Tuning Process

Figure 1 shows the procedure of performance tuning.

Figure 1 GaussDB performance optimization process

Table 1 lists the details about each phase.

Table 1 GaussDB performance optimization process

Phase

Description

Determining the Scope of Performance Tuning

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

SQL Optimization

The SQL statements 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 performs 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.
Parent topic: Performance Tuning