PostgreSQL_Tuning

PostgreSQL Tuning

The CDP4-COMET server relies on PostgreSQL to store and retrieve the data. The standard configuration of PostgreSQL may not suffice for a production environment and is of course also dependent on the specs of the server CDP4-COMET is hosted on. In order to determine the configuration parameters for PostgreSQL we recommend PGTune. Make sure to use "Mixed type of application" for DB Type.

In case you are using a Docker deployment using docker-compose, make use to use these settings as well, see example below:

version: '3.8'
services:
  cdp4_db:
    image: stariongroup/cdp4-database-community-edition:3.4.0
    shm_size: 2gb
    hostname: cdp4-postgresql
    command: postgres -c max_locks_per_transaction=2048 -c max_connections=100 -c shared_buffers=8GB -c effective_cache_size=24GB -c maintenance_work_mem=2GB -c checkpoint_completion_target=0.9 -c wal_buffers=16MB -c default_statistics_target=100 -c random_page_cost=1.1 -c effective_io_concurrency=200 -c work_mem=10485kB -c min_wal_size=1GB -c max_wal_size=4GB -c max_worker_processes=8 -c max_parallel_workers_per_gather=4 -c max_parallel_workers=8 -c max_parallel_maintenance_workers=4

Also note the use of the shm_size parameter. This increases the docker container shared memory from the default 64MB to the value that fits your environment. In the example this value has been set to 2 GB.