Обсуждение: Suggest the best values for the postgres configuration parameters

Поиск
Список
Период
Сортировка

Suggest the best values for the postgres configuration parameters

От
Raghavendra Rao J S V
Дата:
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
​​


Re: Suggest the best values for the postgres configurationparameters

От
Laurenz Albe
Дата:
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