Background

A customer reported longer the 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 to us to change the Postgres memory usage configuration.

Config Changes

Four parameters were changed in the configuration:

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

  • Value changed to 12GB (2GB is the default).

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

  • Value changed to 1GB (256MB is the default).

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

  • Value changed to 4GB (512MB is the default).

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

  • Value changed to 100MB (50MB is the default).

* Please note that maintenance_work_mem and work_mem appear to be limited to 2GB on Windows.

These or any other changes should be applied in custom.conf file which is located in \data\postgresql\data in Saga installation folder.

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

Settings can be viewed using database queries :

• show all to display simple view with human readable value

• select * from pg_settings to display more complex view with additional columns and information

Results

As a result of the changes that were made, we noticed 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 set above were based on recommended values while at the same time also taking into consideration that Postgres was not running on a dedicated server.