Configured NetXMS as much as I can - what about Postgres settings?

[paul]

The Admin guide is helpful for NetXMS settings, but I think my constraint is likely Postgres given I have a stock standard install - default settings.

We have 2000 nodes, 50k Objects, 70k DCI's and 150 EPP's. Once we get above 300 events per minute we start backing up.
Maximum backlog = 113K events - taking 4.5 hours from Event creation to Alarm creation.

I tried https://pgtune.leopard.in.ua/#/ and got the following:

# DB Version: 9.6
# OS Type: windows
# DB Type: oltp
# Total Memory (RAM): 12 GB
# CPUs num: 6
# Data Storage: ssd

max_connections = 300                      (currently 100)
shared_buffers = 512MB                    (currently 128MB)
effective_cache_size = 9GB                (default - unknown)
maintenance_work_mem = 768MB      (default - 712MB)
checkpoint_completion_target = 0.9
wal_buffers = 16MB                          (default 16MB)
default_statistics_target = 100
random_page_cost = 1.1
work_mem = 4466kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 6
max_parallel_workers_per_gather = 3

Other than going with the above, anybody have any other suggestions as to which Postgres settings impact or assist NetXMS the most?

[Tursiops]

You might want to add some memory to your system.
We have ~ 5000 nodes, 35k objects and 320k DCIs and our NetXMS server alone happily grabs ~ 50-60GB of memory.
At present we're running this on a single server with 160 GB of RAM. Note that this was added about a year ago when we hit some odd memory issues which turned out to be caused by a bug which gobbled up all available memory mappings over time, so it always looked like our system ran out of memory... we added a crazy amount to confirm it wasn't that. Having said that, our Postgres also happily eats up 64GB of memory. Could I cut our server down to ~ 128GB? Probably. Would it run on 12GB? I wouldn't think so.

I am pretty sure the recommendation by the Postgres guys is to set shared buffers to ~25% of your RAM (and that there's generally no point going over 40%).
There are several other configuration items we have in our system (e.g. synchronous_commit=off, wal_level=minimal, significantly higher effective_io_concurrency than default, but this does depend on your disk subsystem). However, as I am not a Postgres guy and of course didn't add notes to the config as to why I changed values :-[ and we added things at times when NetXMS was running significantly less stable for an install size such as ours, I am not sure which of these would actually still be relevant or are in fact counterproductive.

[paul]

Thanks for the feedback - appreciated.

Memory I can add - will go to 24GB and see if noticeable difference. Being windows, shared buffers tops out at 512MB I believe(Windows limit), and to use effective_cache_size instead.

Found that exclusions for ativir were not working - getting them fixed to leave postgres folders alone!!

As we are SAN based flash, it is fast, but not local, so will leave the commit and WAL settings where they are for the moment.

Plugged in 24GB and 2 more cores and got the following. Confirms effective_cache vs shared buffers for windows. Will see how these go.

# DB Version: 9.6
# OS Type: windows
# DB Type: oltp
# Total Memory (RAM): 24 GB
# CPUs num: 8
# Data Storage: san

max_connections = 300
shared_buffers = 512MB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
work_mem = 6844kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4