Performance tuning PostgreSQL for Archive and Version services

The Archive and Version Service that runs on Open Enterprise Server 2 for Linux uses the PostgreSQL object-relational database for storing its meta data information.

There would be various deployments possible that are associated with the Archive Version service. For example there may be scenario's where you need to optimize PostgreSQL for Read intensive or Write intensive scenario.

This document is written so that you may optimize performance that is best suitable for your Archive and Version deployment.

Please consider the fact that when tuning is performed, this is usually done with one specific kind of operation in mind, like for example tuning for reads or tuning for writes. When doing so, in general the other operation may take a hit.

By default, Archive and Version services creates the database tables without any specific tuning in mind.

This document provides a background overview on some of the tuning parameters that could be considered for various deployment scenarios.

This document does not consider all aspects of PostgreSQL database tuning. The idea here is to provide some overview on those parameters that could be considered for tuning Archive and Version services.

There are four tables created per job, those are arkdataxxxxx_f, arkdataxxxxx_h, arkdataxxxxx_u and arkdataxxxxx_m tables which are representing files, history, users and metadata. In various scenarios it is found that adding the following indexes to the tables reduces the response time and CPU utilization.

• CREATE UNIQUE INDEX arkdataxxxf_idx ON arkdataxxx_f (parent_key, name, status, file_key);

• CREATE UNIQUE INDEX arkdataxxxh_idx ON arrkdataxxx_h (file_key, op_sequence);

• CREATE UNIQUE INDEX arkdataxxxm_idx ON arkdataxxx_m (op_sequence);
  

The following steps can be used to add and verify the above mentioned indexes:

• From the Linux console enter "psql -d archive_database -U arkuser" and when prompted enter the password for the arkuser which is specified during installation of the Archive and Versioning service.
  

• From the now PostgreSQL command line "/dt" (without quotes) can be used to list all current defined tables.

• Use the "CREATE UNIQUE INDEX" commands mentioned above to add the indexes to the three mentioned existing tables.

• To verify the indexes were created correctly enter "\d <table_name>". This will list information for the specified table, with the current indexes for this table displayed at the bottom of the list.
  

Modifying the following parameters in the "var/opt/novell/arkmanager/data/postgresql.conf" file have been found to also improve performance:

• Increase shared_buffers,work_mem ,effective_cache_size

• set log_statement = 'none'

Note that changing any of the above parameters in the conf file requires a restart of the Archive and Versioning service (rcnovell-ark restart).

The above are considered a general set of guidelines to tune specific usage, users are advised to go through the PostgreSQL performance tuning guide and then do the required changes.

Please consult the http://www.postgresql.org/ website for details