If you want to improve the performance of an ApsaraDB RDS for PostgreSQL instance or meet specific business requirements, you can modify the parameters of the RDS instance in the ApsaraDB RDS console or by calling the ApsaraDB RDS API. You can also view the parameter modification history in the ApsaraDB RDS console.
Usage notes
The modification of some parameters triggers a restart of your RDS instance. After you modify the parameters and click Apply Changes, your RDS instance immediately restarts. To check whether the modification of a parameter triggers a restart, log on to the ApsaraDB RDS console, go to the Modifiable Parameters tab, and then view the value in the Force Restart column for the parameter. If the value is Yes, the modification of the parameter triggers a restart. If the value is No, the modification of the parameter does not trigger a restart. If the RDS instance restarts, your application is disconnected from the RDS instance. Make sure that you arrange your business before the restart of the RDS instance. Proceed with caution.
When you modify the parameters of your RDS instance, you can view the valid values of a parameter in the Value Range column of the parameter on the Modifiable Parameters tab of the Parameters page in the ApsaraDB RDS console.
When you modify the following parameters on the primary instance of a cluster, the modification is synchronized to all read-only nodes of the cluster: wal_level
, max_replication_slots
, max_wal_senders
, and max_locks_per_transaction
.
After you modify a parameter, the system tries to apply the modified parameter. If the modified parameter value is invalid or blocks the start of the instance, the modification is rolled back. In this case, the value in the Status column corresponding to the parameter on the Edit History tab is Failed.
Modify parameters
Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the instance ID.
In the left-side navigation pane, click Parameters.
On the Modifiable Parameters tab, modify the parameters of your RDS instance based on your business requirements.
Modify a single parameter of the RDS instance.
Find the parameter and click the
icon in the Running Parameter Value column.
Enter a new value and click OK.
Click Apply Changes.
In the dialog box that appears, select the time range during which you want the new parameter settings to take effect. You can select Take Effect Immediately, Take Effect Within Maintenance Window, or Take Effect at Specified Time. Then, click OK.
If you want to reconfigure multiple parameters at a time, perform the following steps:
Click Export Parameters to download the parameter settings of your RDS instance as a file to your computer.
Open the file and modify the parameters.
Click Import Parameters.
In the Import Parameters dialog box, paste the parameter settings that you have copied from the file. Then, click OK.
Confirm the values of the modified parameters and click Apply Changes.
In the dialog box that appears, select the time range during which you want the new parameter settings to take effect. You can select Take Effect Immediately, Take Effect Within Maintenance Window, or Take Effect at Specified Time. Then, click OK.
Note
On the Scheduled Tasks tab, you can view the modification tasks for which Take Effect Within Maintenance Window or Take Effect at Specified Time is selected.
View the parameter configuration history
Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the instance ID.
In the left-side navigation pane, click Parameters.
Click the Edit History tab.
Select a time range and click OK.
Configure parameters by using expressions
You can set a parameter to an expression for your RDS instance. If you set an instance type-related parameter to an expression, the value of the parameter dynamically changes when the instance type changes.
The following table describes the supported expression syntax.
Category | Description | Example |
Category | Description | Example |
Parameters |
Parameters that support expressions
work_mem: The amount of memory that is used by internal sorting operations and hash tables before data is written to temporary files. maintenance_work_mem: The maximum amount of memory that can be used for maintenance operations, such as VACUUM and CREATE INDEX. autovacuum_work_mem: The maximum amount of memory that each autovacuum worker process can use. max_parallel_workers: The maximum number of workers that the system can support for parallel queries. max_parallel_workers_per_gather: The maximum number of workers that can be started by a single Gather or Gather Merge node. effective_cache_size: The planner assumption about the effective size of the disk cache that is available to a single query. autovacuum_max_workers: The maximum number of autovacuum processes that can run at the same time, except the autovacuum launcher. max_wal_size: The size of the write-ahead logging (WAL) files that trigger a checkpoint. min_wal_size: The minimum size of WAL files to recycle for reuse at a future checkpoint. As long as WAL disk usage remains below this setting, old WAL files are always recycled for future use at a checkpoint, rather than deleted. temp_file_limit: The maximum disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor. wal_buffers: The size of disk-page buffers in shared memory for WAL files, which indicates the amount of shared memory used for WAL data that is not written to a disk. max_parallel_maintenance_workers: The maximum number of parallel workers that can be started by a single CREATE INDEX operation.
Note The max_parallel_maintenance_workers parameter is supported for PostgreSQL 11.0 to PostgreSQL 14.0. Other parameters are supported for PostgreSQL 10.0 to PostgreSQL 14.0. | effective_cache_size |
Variables | AllocatedStorage: the storage capacity that is supported by the instance type in MB. The value of this variable is an integer. DBInstanceClassMemory: the memory capacity that is supported by the instance type in bytes. The value of this variable is an integer. DBInstanceClassCPU: the number of cores that are supported by the instance type. The value of this variable is an integer. DBInstanceClassConnections: the maximum number of connections that are supported by the instance type. The value of this variable is an integer.
| effective_cache_size={DBInstanceClassMemory/16384} |
Operators | Expression syntax: An expression is enclosed by a pair of braces {} . Division operator (/): This operator is used to divide a number by another number. If the quotient is a decimal, the quotient is not rounded, and only the integer part is counted. The dividend and divisor can be decimals. Multiplication operator (*): This operator is used to multiply a number by another number. If the product is a decimal, the product is not rounded, and only the integer part is counted. The multipliers can be decimals.
|
Functions | The GREATEST() function returns the largest value among an array of integers or the largest value that is calculated by an array of expressions. The LEAST() function returns the smallest value among an array of integers or the smallest value that is calculated by an array of expressions. The SUM() function adds integers or the values that are calculated by specified expressions.
| max_parallel_workers={GREATEST(DBInstanceClassCPU*3/4, 8)} |
References
For more information about PostgreSQL parameters, see Server Configuration.
For more information about how to modify parameters by using API operations, see the following table.