Query performance can be affected by many things. Some of these can
be manipulated by the user, while others are fundamental to the underlying
design of the system. This chapter provides some hints about understanding
and tuning EnterpriseDB performance.
EnterpriseDB supports dynamic tuning of the database server
to make the optimal usage of the system resources available on the
host machine on which it is installed.
The configuration parameter, edb_dynatune, determines how much of the host system's
resources are to be used by the EnterpriseDB database server based upon the host
machine's total available resources and the intended usage of the host machine.
When EnterpriseDB is initially installed, the edb_dynatune
parameter is set in accordance with the selected usage of the host machine on which it was installed
- i.e., development machine, mixed use machine, or dedicated server. For most purposes,
there is no need for the database administrator to adjust the various configuration parameters in the
postgresql.conf file in order to improve performance.
The value of the edb_dynatune parameter may be changed after the initial installation of
EnterpriseDB by changing its value in the postgresql.conf file.
The edb-postmaster on the affected database cluster must be restarted in order for the new configuration to take effect.
The edb_dynatune parameter can be set to any integer value between 0 and 100, inclusive.
A value of 0, turns off the dynamic tuning feature thereby leaving the database server resource usage
totally under the control of the other configuration parameters in the postgresql.conf file.
A low non-zero, value (e.g., 1 - 33) would dedicate the least amount of the host machine's resources to
EnterpriseDB. This setting would be used for a development machine where many other applications are being used.
A value in the range of 34 - 66 would dedicate a moderate amount of resources to the EnterpriseDB
database server. This setting might be used for a dedicated application server that may have a fixed number of other applications running
on the same machine as EnterpriseDB.
The highest values (e.g., 67 - 100) would dedicate most of the server's resources to the EnterpriseDB
database server. This setting would be used for a host machine that is totally dedicated to running
EnterpriseDB.
Once a value of edb_dynatune is selected, database server performance can be further
fine-tuned if necessary, by adjusting the other configuration parameters in the postgresql.conf
file. Any adjusted setting (done by uncommenting the configuration parameter, specifying the desired value,
and restarting the database server) overrides any value set by edb_dynatune.
The current value of any configuration parameter can be displayed using the
SHOW command.