Postgres 12 Tuning Configuration for heavily transaction server, have I set values too low?

Question

We have a postgres 12 environment. Which has 130GB of RAM and 32 CPUs, have been monitoring the memory usage of the server in recent months and it uses around half of the memory on the server, I understand that making shared_buffers changes involves a restart but if effective_cache_size is changed do I need to do shared_buffers at same time (know dont need to restart for effective_cache_size just reload).

The server is a heavily transactional system.

Some of the important postgres configuration:

listen_addresses = '*'
max_connections = 1000
effective_cache_size = 60GB
shared_buffers = 25GB
temp_buffers = 32MB
max_prepared_transactions = 1000
work_mem = 256MB
maintenance_work_mem = 1GB

effective_io_concurrency = 200
random_page_cost = 1.1

max_worker_processes = 14
max_parallel_workers_per_gather = 7
max_parallel_workers = 14
max_parallel_maintenance_workers = 7

wal_level = logical
wal_buffers = 1MB
checkpoint_timeout = 5min
checkpoint_completion_target = 0.9
max_wal_senders = 6
max_wal_size = 4GB
min_wal_size = 256MB
wal_keep_segments = 400
wal_sender_timeout = 5min
wal_receiver_timeout = 5min
max_replication_slots = 6 

I also have a replica slave server which has slightly less resources would this make any difference?

I have seen that the recommendation for shared_buffers is half or more of the server.

Apart from shared_buffers being altered to a higher value, the server doesnt use more memory (60GB out of 130GB and Swap at 5GB/14GB)

Any help is much appreciated.

Answer 1

I'll go through your settings and point out where I think there is room for improvement. A assume you have local SSD storage or similar.

• max_connections = 1000

  Not a good idea with 32 cores; the default 100 is ample. Use a connection pool and limit its size to something slightly above 32 - read this for more details.

• max_prepared_transactions = 1000

  Only if you know you need prepared transactions, and certainly not 1000. Prepared transactions are a health risk for your database.

• work_mem = 256MB

  Very high for transactional workload, but that won't do any harm.

• effective_io_concurrency = 200
random_page_cost = 1.1

  Local SSD, right?

• max_worker_processes = 14
max_parallel_workers_per_gather = 7
max_parallel_workers = 14
max_parallel_maintenance_workers = 7

  With a transactional workload you normally want to optimize throughput, and parallel query is bad for that. It makes queries faster at the expense of extra resources. Do you want one accidental big query to hog 8 of your 32 cores, when you need them for the transactional workload? No.

  Set max_parallel_workers_per_gather to 0 and leave max_parallel_maintenance_workers to something more moderate like the default 2.

• wal_buffers = 1MB

  It might not matter with your workload, but there is no benefit in setting it so small. Leave it at its default value -1.

• checkpoint_timeout = 5min
max_wal_size = 4GB

  Now here is the biggest potential for tuning a write-heavy workload. Fewer checkpoints mean fewer writes. Set max_wal_size large (dozens of GB is just fine) and crank up checkpoint_timeout to half an hour or more.

  The only price you are paying is a little more disk space and long recovery time in the unlikely event of a crash.

• wal_keep_segments = 400

  Replication slots are more effective.

One additional parameter you might contemplate is setting synchronous_commit = off. If you can afford losing half a second of committed transactions in the unlikely event of a crash, that is an easy way to boost performance.