How can I optimize the performance of the built-in EventSentry

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.

Note: Since version 5.1.1 EventSentry includes a tool to set this parameters in the main GUI. From top menu Tools -> Utilities -> Built-In Database Optimization

We recommend adjusting the following settings when tuning the database:

Memory Tweaking

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 - Postgress expect this to be set in MB. 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.

CPU Tweaking

max_worker_processes This sets the maximum number of background processes that PostgreSQL can use for parallel queries. On a dedicated server is the number of available Logical Processors. If the server runs other services, lower the number leaving 2 logical cores for other services.

max_parallel_workers_per_gatherThis controls the number of parallel workers that can be started by a single Gather node in a query plan. Set this to a value that allows efficient use of multiple cores. Normally set by total of cores / 3 (not Logical Processors)

max_parallel_workers This parameter sets the maximum number of parallel workers that the system can support for parallel queries. This should be set to a value that makes sense given your server's resources. On a dedicated server is normally set to the number of available Cores (not Logical Processors)

parallel_leader_participation This should be set to ON. When set to on, the leader process will participate in parallel query execution. This can improve performance by utilizing the leader process along with the worker processes.

Notes

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.