Optimizing Database Parameters ​

To ensure high performance of the database, it is recommended that you set the database system parameters (GUC parameters) based on the hardware resources and actual services. This section describes GUC parameters that affect the database performance. For details about how to set GUC parameters, see the Administrator Guide.

Optimizing Database Memory Parameters ​

The performance of complex query statements strongly depends on the configuration parameters of the database memory. The database memory parameters include the control parameters for logical memory management and parameters determining whether execution operators are spilled to disks.

Parameter for Logical Memory Management ​

max_process_memory is a parameter used for logical memory management. It specifies the maximum available memory on each database node. Set this parameter by referring to max_process_memory.

Use the following formula to calculate the available memory for job execution:

max_process_memory – Shared memory (including shared_buffers) – cstore_buffers

Therefore, the memory available to job execution depends on shared_buffers and cstore_buffers.

Views for logical memory management are provided to display the used memory and peak information in each database block. You can connect to a database node and run pg_total_memory_detail to query information about the memory usage on this database node.

When the specified physical memory is insufficient, work_mem determines whether to write additional operator calculation data into temporary tables based on query characteristics and concurrency. This reduces performance by five to 10 times and prolongs the query response time from seconds to minutes.

• For complex serial queries, each query requires five to ten associated operations. Set work_mem using the following formula: work_mem = 50% of the memory/10.
• For simple serial queries, each query requires two to five associated operations. Set work_mem using the following formula: work_mem = 50% of the memory/5.
• For concurrent queries, set work_mem using the following formula: work_mem = work_mem for serial queries/Number of concurrent SQL statements.

Parameter Determining Whether to Spill Execution Operators to Disks ​

work_mem sets the used memory threshold. Execution operators that can be spilled to disks will be written when the used memory exceeds the threshold. Such execution operators include Hash(VecHashJoin), Agg(VecAgg), Sort(VecSort), Material(VecMaterial), SetOp(VecSetOp), and WindowAgg(VecWindowAgg). They can be vectorized or non-vectorized. This parameter ensures concurrent throughput and the performance of a single query job. Therefore, you need to optimize the parameter based on the output of Explain Performance.

Optimizing Database I/O Parameter ​

I/O Parameters ​

• pagewriter_sleep: controls the page flushing frequency of the backend write process pagewriter in incremental checkpoint mode. When the ratio of dirty pages to the value of shared_buffers reaches the value of dirty_page_percent_max, the number of dirty pages in each batch is calculated based on the value of max_io_capacity. The pagewriter thread is used to push the recovery point. If the pagewriter thread is set to a large value, the recovery point is pushed slowly, the system breaks down and starts for a long time, and Xlogs are stacked.

  To reduce the RTO and log bloat, you need to decrease the value of pagewriter_sleep to accelerate disk flushing, promote the recovery point, and promote log recycling.

• bgwriter_delay: controls the page flushing frequency of the backend writer process bgwriter in incremental checkpoint mode. When the ratio of the number of idle buffer pages to the value of shared_buffers is less than the value of candidate_buf_percent_target, the number of dirty pages in each batch is calculated based on the value of max_io_capacity. The bgwriter thread flushes obsolete pages to disks to accelerate the slot occupation speed during service execution. If the time is too long, the performance will be affected.

  To improve service performance, set bgwriter_delay to a smaller value.

• max_io_capacity: specifies the I/O upper limit per second for the backend write processes (pagewriter and bgwriter) to flush pages in batches. Set this parameter based on the service scenario and disk I/O capability. If the RTO is short or the data volume is much larger than the shared memory, and the service access data volume is random, the value of this parameter cannot be too small. A small parameter value reduces the number of pages flushed by the backend write process. If a large number of pages are eliminated due to service triggering, the services are affected.

  max_io_capacity must be set based on the optimal random write I/O capability.