How can I optimize the performance of the built-in EventSentry (PostgreSQL) database?

Article ID: 232
Category: Database
Applies to: 2.93 and newer
Updated: 2019-09-10

The built-in EventSentry database ships with a basic configuration which is tuned for wide compatibility instead of speed. If you are collecting a lot of data, it is likely that the default parameters are undersized for your system, resulting in poor collector performance (high outbound queue size) or resulting in poor performance when searching in the web reports.

If you are experiencing a slow response in the web reports or encountering errors about the collector queues, then we suggest adjusting some of the parameters in the postgresql.conf file to more effectively optimize for your particular hardware and to improve performance. By default, the postgresql.conf file is located in the C:\Program Files (x86)\EventSentry\data folder. If you have PostgreSQL 9.6, the default location of postgresql.conf is the C:\Program Files (x86)\EventSentry\data96 folder.

We recommend adjusting the following settings when tuning the database:

shared_buffers - This determines how much memory is dedicated to the database for caching data. On Windows, the useful range is 64MB to 512MB. If you have more than 4 GB of RAM, 512 MB is a good choice.

effective_cache_size - If the machine is dedicated to EventSentry and its database, the recommended setting is 1/2 - 3/4 of the total system RAM. If the machine is used for other tasks, it is recommended to use a value that is close to the "System Cache" size which you can find in Task Manager on the performance tab. Example: 4GB.

checkpoint_segments - (PostgreSQL 9.1 only) It is highly recommended to increase this value. Setting this value to at least 10 can significantly improve performance. For a machine that is dedicated to EventSentry and its database, we would recommend setting this to 32. In PostgreSQL 9.6 this setting does not exist and does not need to be adjusted.

checkpoint_completion_target - We recommend setting this to 0.9, and if you -do not- have PostgreSQL 9.6 you need to also increase the checkpoint_segments value.

work_mem - We recommend setting this to 24MB.

maintenance_work_mem - We recommend setting this to 256MB.

You will need to restart the "EventSentry Database" service ("EventSentry Database v9.6" service if you have PostgreSQL 9.6) for the changes to be applied.

For more information on tuning the database, take a look at the official PostgreSQL Wiki at the link below.