Documentation
 
 
 

Chapter 13. Performance Tips

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.

13.1. Setting the Database Server for Optimal 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.

 
 ©2004-2007 EnterpriseDB All Rights Reserved