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.