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: 2023-08-28

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 configuration file to more effectively optimize for your particular hardware and to improve performance. Since version 5.0 and up, EventSentry includes a separate config file that is called from the main postgresql.conf file by this line at the end: include = 'postgresql_eventsentry.conf' If you are running EventSentry 5.0 or newer then you should edit the postgresql_eventsentry.conf file. Everything in this file will overwrite what is set in postgresql.conf. By default, the postgresql_eventsentry.conf file is located in the C:\Program Files\EventSentry\data14 folder. If you still use PostgreSQL 9.6 (the "EventSentry Database v9.6" service), you should edit postgresql.conf. The default location is 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. We recommend setting this to between 15% and 20% of total RAM on your server.

work_mem - We recommend using this formula to determine your work_mem:

Total RAM * 0.25 / max_connections.

You can find your max_connections setting in the same postgresql_eventsentry.conf file. So for example, if you have 16GB of RAM and 128 max_connections:

16GB * .25/128=.03125GB or 31MB

maintenance_work_mem - We recommend using this formula to determine your work_mem:

Total RAM * 0.05.

checkpoint_completion_target - We recommend setting this to 0.9 unconditionally.

effective_cache_size - If the machine is dedicated to EventSentry and its database, the recommended setting is 50% of the total system RAM. Note that if you're still using PostgreSQL v9.6 then 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.

Please note that when changing any of these values you must remove the pound symbol (#) from the beginning of the setting name in order for it to actually obey your changes. For example:
#maintenance_work_mem = 256MB

It doesn't care that you've customized the value because the pound symbol is still there, which tells it to ignore any changes and use the default value of 4MB instead. When you remove the pound symbol:
maintenance_work_mem = 256MB

After you save your changes to the postgresql.conf file you will need to restart the "EventSentry Database v14" service ("EventSentry Database v9.6" service if you still have PostgreSQL 9.6) in order for the changes to be applied.

Also you can use our PowerShell to print the recommended settings based on computers ram or manual input. Script can be found in our GitHub respository Here

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