Background

A customer reported longer than expected fetch times and frequent Postgres timeouts. With a server configuration of 256 GB RAM and 32 CPU cores, the daily fetch rate was expected to be well above 100,000, but was less than 70.000. This led us to change the Postgres memory usage configuration as described below.

Config Changes

Four parameters were changed in the configuration:

• effective_cache_size estimates how much memory is available for disk caching.

  • The value was changed to 12GB (2GB is the default).

• maintenance_work_mem specifies maximum amount of memory to be used by maintenance operations.*

  • The value was changed to 1GB (256MB is the default and 2GB seems to be the limit on Windows).

• shared_buffers sets the amount of memory dedicated by the server for caching data.

  • The value was changed to 4GB (512MB is the default).

• work_mem sets the amount of memory available for every sort, group or hash operations.*

  • The value was changed to 100MB (50MB is the default and 2GB seems to be the limit on Windows).

These changes are done in the file D:\Program Files\ayfie\saga\data\postgresql\data\custom.conf (assuming recommended install directory)

The changes above were done on a beefy host. Be careful to not configure beyond the available resources.

More in depth explanation of these parameters can be found in this Postgres documentation.

Settings can be viewed running these database queries:

• show all provides a simple view with human readable values

• select * from pg_settings displays a more complex view with more information

Results

The configuration changes produced a positive change in server performance. The daily document fetch rate went above 100,000 and there were no more Postgres timeouts being reported. The values used are the recommended values while at the same time also taking into consideration that Postgres was not running on a dedicated server.