Advanced Database Memory Tuning
To apply database tuning settings like those below, amend the postgresql.conf file, save, and restart the database.
If you are unsure where the conf file is located, you can use the following command:
psql -U postgres -c 'SHOW config_file'
Small Profile
Database memory tuning example with values calculated based on the small profile defined below:
< 20 repositories
< 20GB total blob store size
single repository format type
max_connections = 200 shared_buffers = 4GB effective_cache_size = 12GB maintenance_work_mem = 1GB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 10485kB min_wal_size = 1GB max_wal_size = 4GB max_worker_processes = 4 max_parallel_workers_per_gather = 2 max_parallel_workers = 4 max_parallel_maintenance_workers = 2
Medium
Database memory tuning example with values calculated based on the medium profile defined below:
< 50 repositories
< 200GB total blob store size
a few repository formats
max_connections = 300 shared_buffers = 8GB effective_cache_size = 24GB maintenance_work_mem = 2GB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 10485kB min_wal_size = 1GB max_wal_size = 4GB max_worker_processes = 8 max_parallel_workers_per_gather = 4 max_parallel_workers = 8 max_parallel_maintenance_workers = 4
Large
Database memory tuning example with values calculated based on the large profile defined below:
< 200 repositories
> 200GB total blob store size
diverse repository formats
max_connections = 400 shared_buffers = 8GB effective_cache_size = 24GB maintenance_work_mem = 2GB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 10485kB min_wal_size = 1GB max_wal_size = 4GB max_worker_processes = 16 max_parallel_workers_per_gather = 4 max_parallel_workers = 16 max_parallel_maintenance_workers = 4
For legacy OrientDB, see additional memory tuning procedures.