Using dynamic resource tuning v16
EDB Postgres Advanced Server supports dynamic tuning of the database server to make the optimal use of the system resources available on the host machine where it's installed. The two parameters that control this functionality are located in the postgresql.conf
file. These parameters are:
edb_dynatune
edb_dynatune_profile
Implementing dynamic tuning
edb_dynatune
determines how much of the host system's resources for the database server to use. It bases the determination on the host machine's total available resources and the intended use of the host machine.
When EDB Postgres Advanced Server is first installed, you set the edb_dynatune
parameter according to the host machine's use as a development machine, mixed-use machine, or dedicated server. For most purposes, the database administrator doesn't need to adjust the configuration parameters in the postgresql.conf
file to improve performance.
Here are some things to keep in mind when setting up dynamic tuning:
You can change the value of the
edb_dynatune
parameter after the initial installation of EDB Postgres Advanced Server by editing thepostgresql.conf
file. You must restart the postmaster for the new configuration to take effect.You can set the
edb_dynatune
parameter to any integer value from 0 to 100. A value of 0 turns off the dynamic tuning feature, leaving the database server resource use under the control of the other configuration parameters in thepostgresql.conf
file.A low, non-zero value, that is, 1–33, dedicates the least amount of the host machine's resources to the database server. Use this setting for a development machine where many other applications are being used.
A value in the range of 34–66 dedicates a moderate amount of resources to the database server. You might use this setting for a dedicated application server that has a fixed number of other applications running on the same machine as EDB Postgres Advanced Server.
The highest values, that is, 67–100, dedicate most of the server's resources to the database server. Use this setting for a host machine that's totally dedicated to running EDB Postgres Advanced Server.
Changing parameter values
After you select a value for edb_dynatune
, you can further fine-tune database server performance by adjusting the other configuration parameters in the postgresql.conf
file. Any adjusted setting overrides the corresponding value chosen by edb_dynatune
.
To change the value of a parameter:
- Uncomment the configuration parameter.
- Specify the desired value.
- Restart the database server.
Controlling tuning behavior
Use the edb_dynatune_profile
parameter to control tuning aspects based on the expected workload profile on the database server. This parameter takes effect when you start the database server.
The table shows the possible values for edb_dynatune_profile
.
Value | Usage |
---|---|
oltp | Recommended when the database server is processing heavy online transaction processing workloads. |
reporting | Recommended for database servers used for heavy data reporting. |
mixed | Recommended for servers that provide a mix of transaction processing and data reporting. |