Thread: 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
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 areallowed 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