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: 2020-06-01

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\data96 folder. If you still use PostgreSQL 9.1, the default location of postgresql.conf is the C:\Program Files (x86)\EventSentry\data 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, but the maximum usable value is 4GB. 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: 3GB.

checkpoint_segments - In PostgreSQL 9.6 this setting does not exist and does not need to be adjusted. If you still use PostgreSQL 9.1 it is highly recommended to increase this value. Setting this value to at least 10 can significantly improve performance for PostgresSQL 9.1 data. For a machine that is dedicated to EventSentry and its PostgreSQL 9.1 database, we would recommend setting this to 32.

checkpoint_completion_target - We recommend setting this to 0.9 for all versions. If you -do not- have PostgreSQL 9.6 please remember that you need to also increase the checkpoint_segments value in the paragraph above.

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 v9.6" service ("EventSentry Database" service if you still have PostgreSQL 9.1) 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.