Re: Suggest the best values for the postgres configuration parameters

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