Tune PostgreSQL by changing config parameters

Question

I have postgresql 9.6 which is runnig on ubuntu server 16.04 with 122gb RAM, 320gb SSD, 18 vCPU on AWS. I want to know what parameters do I need to change in postgresql.config file according to these resource in order to get high performance? How to force postgresql to load the data in memory as much as possible?

Valery Viktorovsky · Accepted Answer · 2017-01-23 22:41:09Z

1

For quick start you can use pgtune util or web alternative http://pgtune.leopard.in.ua. Let's assume you write web app:

# WARNING
# this tool not being optimal 
# for very high memory systems

max_connections = 200
shared_buffers = 31232MB
effective_cache_size = 93696MB
work_mem = 159907kB
maintenance_work_mem = 2GB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100

answered Jan 23, 2017 at 22:41

Valery Viktorovsky

6,7863 gold badges47 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arezoo Over a year ago

Thanks, I know this tool but it is informing that is not optimal for high memory systems! are there some references that explain what is the best way of changing config parameters?

Valery Viktorovsky Over a year ago

wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and revsys.com/writings/postgresql-performance.html explain the most important params and initial steps to tune postgresql.

Collectives™ on Stack Overflow

Tune PostgreSQL by changing config parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related