Tuning Recommendations for the Server :: SAS® 9.4 Web Applications: Tuning for Performance and Scalability, Third Edition

Database Sizes

The suggested configurations in this chapter are based on the following database size definitions:

A small database receives more than one concurrent query or update, and creates hundreds of records daily.
A medium database receives several concurrent queries or updates (most of which use indexes), and creates thousands of records per hour.
A large database receives many concurrent queries or updates, creates thousands of records per minute, receives many queries to scan tables, and processes complex queries and regular bulk loads.

Tuning a Database

To tune a database:

Tune the system and operating system that supports the database.

The PostgreSQL data server benefits from the same system tuning as SAS. A fast disk resource, sufficient memory for caching, and a sufficient number of cores with good single thread performance are crucial. Operating system tuning should favor input/output. In addition, set values for semaphores. For more information, see Recommendations for Semaphores.

For detailed operating system configuration information, see the appropriate operating system documentation.
Tune the database configuration.

The data server automatically uses available hardware efficiently. In many cases, there is little to be gained by trying to tune the server configuration parameters. The default tuning handles a wide variety of use cases. In very few cases, configuration tuning makes a significant difference, but in most cases, configuration tuning does not change performance.

For configuration changes such as shared buffers that can help improve performance, see Initial Configuration Recommendations.
Tune the database schema design.

This includes the following tasks: database normalizing or de-normalizing; creating artificial keys, where appropriate; creating indexes on frequent access paths, including foreign key; defining index types (expression index and multiple column versus multiple single column); and partitioning tables.
Tune the queries or access.

This includes the following tasks: configuring connection pooling, using indexed paths and prepared statements, setting fetch size, and running batch updates and analysis to update statistics.

To check and monitor performance, see Monitoring Performance.

Tuning Multiple Data Server Instances

If you have multiple data server instances, add the individual requirements for shared buffers to determine the total amount of memory for your shared buffer configuration.

Recommendations for Semaphores

Recent versions of Linux are shipped with appropriate semaphore values. Review the recommended values for semaphores settings that are provided in the following table and ensure that your settings match the recommended values:

Recommended Semaphore Values
Setting	Recommended Value
max number of arrays	256
max semaphores per array	250
max semaphores system wide	32000
max ops per semop call	32
semaphore max value	32767

For more information about semaphore settings on different operating systems, see http://www.postgresql.org/docs/9.1/static/kernel-resources.html.

Additional Documentation for PostgreSQL Tuning

For detailed information about tuning PostgreSQL, see the following documentation:

Performance Optimization: https://wiki.postgresql.org/wiki/Performance_Optimization
Tuning Your PostgreSQL Server: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Managing Kernel Resources: http://www.postgresql.org/docs/9.1/static/kernel-resources.html