Tuning Wizard v10

The Tuning Wizard reviews your PostgreSQL or EDB Postgres Advanced Server installation, recommending a set of configuration options tuned to the installation's anticipated workload. Benchmarking systems or systems with a high work load might require also require manual tuning to reach optimum performance.

Before using the Tuning Wizard, you must specify the name of the service in the server's Properties dialog box. In the Advanced tab, enter the service name in the Server ID field.

The Tuning Wizard can make recommendations only for those servers that reside on the same server as their bound PEM agent. If you specify a value of Yes in the Remote monitoring field when defining your server, the server doesn't display in the Tuning Wizard tree.

1. To open the Tuning Wizard, in the PEM client select Management > Tuning Wizard.

2. Select Next.

3. When you expand the Servers node of the tree, a list of servers appears. All of these servers are currently monitored by PEM and available for tuning. Select a server to tune it.

   Note

   If you don't provide the server's service name, then the Tuning Wizard displays a warning next to the server name on the tree.

4. Select Next.

5. Select an option in the Machine utilization field to specify the type of work performed by each server. The type of work performed by the server determines how the Tuning Wizard allocates system resources:

   • Select Dedicated to dedicate the majority of the system resources to the database server.
   • Select Mixed use to dedicate a moderate amount of system resources to the database server.
   • Select Developer workstation to dedicate a relatively small amount of system resources to the database server.
   • Select an option in the Workload Selection field to specify the type of workload typically performed on the selected server:
     • Select OLTP if the selected server is used primarily to process online transaction workloads.
     • Select Mixed if the selected server provides a mix of transaction processing and data reporting.
     • Select Data warehouse if the server is used for heavy data reporting.

6. Select Next.

7. The tree on the Tuning Changes Summary dialog box displays the parameter setting modifications recommended for each server analyzed by the Tuning Wizard. Select the recommendations that you want the Tuning Wizard to apply or to include in a preview report:

   • Select a parameter name, and the Tuning Wizard includes the parameter setting.
   • Select the server name, and the Tuning Wizard includes all parameter setting recommendations for the specified server.

8. Select Next.

9. In the Schedule or Run? dialog box, either select a time for PEM to apply the recommended changes or generate a report that details the recommended changes.

   PEM makes the recommended changes that you selected in the Tuning Changes Summary dialog box. If you choose to generate a report, then PEM creates a report. It contains a list of the current values and recommended changes to the configuration parameters as selected in the Tuning Changes Summary dialog box. To implement changes, you must open the Tuning Wizard a second time, selecting the parameters you want to modify in the Tuning Changes Summary dialog box.

   Select Schedule changes to view and specify your scheduling options.

   You can set the Configuration now? slider to:

   • Yes — Apply the Tuning Wizard's recommendations and restart the server.

   • No — Enable the Time? field where you can specify a date and time with the calendar selector. PEM applies the recommended changes and restarts the server at this time.

     Select Generate report to view your report options.

     You can set the View report now? slider to:

   • Yes — Display the Tuning Wizard report onscreen.

   • No — Enable the Save the report to file field where you can specify a file name and location.

10. Select Finish.

    To confirm that the Tuning Wizard implemented the recommended changes, review the postgresql.conf file for the modified server. When the change is applied, the Tuning Wizard adds a comment above each modified parameter in the postgresql.conf file.

    

    You can also confirm a parameter value by querying the server. For example, to confirm the value of the shared_buffers parameter, open a SQL command line using either the Query tool (accessed through the Tools menu) or the psql client, and issue the command:

    Toggle WrapCopy

    SHOW shared_buffers;

The value returned by the server confirms whether the parameter was modified.