Tuning Database Parameters¶
To ensure high performance of the database, you are advised to configure GUC parameters based on available resources and the actual workloads. This section describes some of the common parameters and the recommended configurations for them. For more details, see Configuring GUC Parameters.
Database Communication Parameters¶
By default, nodes in a database cluster communicate using the TCP proxy communication library.
GUC Parameter | Description | Configuration Suggestion |
---|---|---|
comm_quota_size | comm_quota_size controls the size of data transmitted every time in each flow channel. Its default value is 1M. | In a high concurrency scenario, you can increase its value to improve communication performance, but doing so consumes more memory. Optimize this parameter as needed. If you query the pg_total_memory_detail view of a DN and find that the memory used by the communication layer has reached the threshold of comm_usable_memory, set comm_quota_size to a small value, such as 512K. |
comm_usable_memory | comm_usable_memory controls the memory on a DN that can be used for database communication. | The value of this parameter is only used for memory flow control. The default flow control value is 1 MB. If the memory usage exceeds half of the parameter value, the flow control value will be automatically changed to 0.5 MB. If only 20% of the memory specified by the parameter is available, the flow control value will be changed to the allowed minimum, 8 KB. |
Database Connection Parameters¶
GUC Parameter | Description | Configuration Suggestion |
---|---|---|
max_connections | Specifies the maximum number of concurrent connections to the database. This parameter affects the concurrent processing capability of the cluster. | Retain the default value of this parameter on CNs. Set this parameter on DNs to a value calculated using this formula: Number of CNs x Value of this parameter on a CN. If the value of this parameter is increased, GaussDB(DWS) may require more System V shared memory or semaphore, which may exceed the default maximum value of the OS. In this case, modify the value as needed. |
max_prepared_transactions | Specifies the maximum number of transactions that can stay in the prepared state simultaneously. If the value of this parameter is increased, GaussDB(DWS) requires more System V shared memory than the default system setting. | The value of max_connections is related to max_prepared_transactions. Before configuring max_connections, ensure that the value of max_prepared_transactions is greater than or equal to that of max_connections. In this way, each session has a prepared transaction in the waiting state. |
session_timeout | Specifies the maximum duration a database connection can stay idle before it is automatically disconnected. | The value can be an integer in the range 0 to 86400. The minimum unit is second (s). The value 0 disables this timeout mechanism. Generally, you are advised not to set this parameter to 0. |