Suggest the best values for the postgres configuration parameters
We are using postgres *9.2* version on *Centos *operating system.
Total ram available is *80GB *. At present we don't have any connection
pool mechanisiam. Max number of connections are allowed is 1000.
Could you please suggest the best values for the below configuration
parameters?
- shared_buffers
- effective_cache_size
- work_mem
- maintenance_work_mem
- checkpoint_segments
- wal_keep_segments
- checkpoint_completion_target
- Max_prepared_transactions =0
--
Regards,
Raghavendra Rao J S V
Raghavendra Rao J S V wrote:
We are using postgres 9.2 version on Centos operating system.
That's no good, 9.2 is out of support.
Use v10 or at least 9.6.
Total ram available is 80GB . At present we don't have any connection pool mechanisiam. Max number of connections are allowed is 1000.
That's not good either. Use a connection pool.
Could you please suggest the best values for the below configuration parameters?
shared_buffers
4 or 8 GB
effective_cache_size
80 GB minus what the OS and other applications need.
work_mem
Depends on your queries. With max_connections absurdly high as 1000, no more than 50MB.
maintenance_work_mem
1GB
checkpoint_segments
Depends on your workload.
wal_keep_segments
0 (unless you need replication and have nothing better)
checkpoint_completion_target
0.8
Max_prepared_transactions =0
0 (unless you need distributed transactions)
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com