PostgreSQL®- postgres.conf Parameter
For the read performance of the NGA queries (dpGetPeriod, dpGetPeriodSplit...),
parameters must be changed in the file <PG installation path>/<PG
version>/postgres.conf.
Note:
Note that these parameters are set automatically for a
PostgreSQL® database backend managed by WinCC OA.
However, set the following parameters for a DBAdmin-managed PostgreSQL®
database backend:
| Parameter | Description |
|---|---|
| shared_buffers | This parameter determines how much memory is dedicated to the
server for caching data. It is recommended to set this to 25% of
your total RAM.
As WinCC OA is deployed in range of very small to very big servers, at the first start of a managed NGA project, the available physical RAM size is detected and the shared buffer size is defined by main memory RAM * 0,25. The |
| work_mem | This setting controls the amount of memory allocated for internal
sort operations and hash tables before writing to temporary disk
files. Adjust this based on your workload and the number of
concurrent connections. Set the parameter
work_mem:
|
| max_parallel_workers_per_gather | Parallelizing query execution, which can significantly speed up
large, complex queries by distributing the workload across multiple
CPU cores. Set the parameter
|
| random_page_cost | This parameter helps the query planner estimate the cost of
random disk page accesses, which are typically more expensive than
sequential accesses due to the additional seek time involved. Set
the parameter random_page_cost:
|
| maintenance_work_mem | This parameter allocates memory for maintenance tasks, which can
help speed up operations like vacuuming and indexing by allowing
them to process more data in memory rather than relying on slower
disk I/O. Detect also the available physical RAM size and set the
parameter to
main memory-RAM * 0,03125 e.g.
|
