Tune parameters
Change PostgreSQL settings such as work_mem or statement_timeout, mostly without a restart.
The Parameters tab lists every PostgreSQL setting you can change, with its default, your value and what the server runs now. Replace ewdb-0123456789ab with your database's id.
Prerequisites
- Permission for
rds:ModifyDBParameters(andrds:DescribeDBParametersto read them). - In the console, also
rds:DescribeDBInstancesto open the database's page (without it the page is not found).
Set or reset parameters
- Open the database, then choose Parameters.
- In the Your value column, enter the new values. To return one to its default, clear it or choose the reset icon (Reset to default).
- Choose Apply n changes.
The page answers Applied. and names the parameters that take effect after a reboot.
dcloud rds instances modify-parameters ewdb-0123456789ab \
--parameters work_mem=32MB --parameters statement_timeout=30sPATCH /rds/api/instances/ewdb-0123456789ab/parameters
{ "parameters": { "work_mem": "32MB", "statement_timeout": "30s", "jit": null } }A value sets; null resets.
resource "dcloud_db_parameters" "orders" {
db_instance_id = dcloud_db_instance.orders.id
parameters = {
work_mem = "32MB"
statement_timeout = "30s"
}
}Result
Most changes take effect at once, without a restart. shared_buffers and max_connections take
effect at the next restart: the answer lists them under pendingRestart, and Parameters marks
them until you reboot (see Stop, start or reboot a database). Each change
is on Activity (phase parameters).
On an older cluster whose machines were built before parameter settings existed, shared_buffers
is pinned in each machine's local configuration and a change does not take effect. The answer
reports it under notApplied, and the console says Not in effect on this database.
On a cluster, every node applies a change within about ten seconds.
Defaults
Memory defaults follow the machine size: shared_buffers a quarter of memory,
effective_cache_size three quarters, plus maintenance_work_mem and work_mem sized to match.
Changing the machine type moves them, and your own values stay as you set them.
Settable parameters
| Group | Parameters (* = takes effect at the next restart) |
|---|---|
| Memory | shared_buffers*, effective_cache_size, work_mem, maintenance_work_mem, temp_file_limit |
| Connections | max_connections* |
| Query tuning | random_page_cost, effective_io_concurrency, default_statistics_target, max_parallel_workers_per_gather, jit |
| Timeouts | statement_timeout, lock_timeout, idle_in_transaction_session_timeout |
| Logging | log_min_duration_statement, log_statement, log_lock_waits, log_temp_files, track_io_timing |
| Autovacuum | autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor, autovacuum_vacuum_cost_limit |
| WAL | checkpoint_timeout, max_wal_size |
Values are written as PostgreSQL writes them (64MB, 30s, on), and each has bounds.
GET /rds/api/instances/{id}/parameters returns them with every parameter's description.
Replication, archiving, TLS, authentication and preload libraries belong to the platform and are
not on the list.
Related
- Find slow queries:
log_min_duration_statementand the slow query log. PATCH /rds/api/instances/{id}/parameters·dcloud rds instances modify-parameters·dcloud_db_parameters