Thread: Configuration changes with multiple standby nodes
Hi,
I'm using postgresql10.5 with 1 master node and 2 slaves.
Each of node is having following specs
# DB Version: 10.5
# OS Type: CentOS 7
# Total Memory (RAM): 32 GB
# CPUs num: 24
# Data Storage: ssd
https://pgtune.leopard.in.ua suggested me tuning config as below
max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 699kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 24
max_parallel_workers_per_gather = 12
max_parallel_workers = 24
I have two questions regarding this
1. What changes do I have to do if I add more standbys or it will be same over all servers?
2. How to select a value for max_connections based on the fact I have 3 servers with same specs and client will using it for read operations only and data is loaded once a month.
Happy to help :)
Prince Pathria Systems Architect Intern Evive +91 9478670472 goevive.com
On Fri, Sep 21, 2018 at 1:11 PM Prince Pathria <prince.pathria@goevive.com> wrote:
Hi,I'm using postgresql10.5 with 1 master node and 2 slaves.Each of node is having following specs# DB Version: 10.5# OS Type: CentOS 7# Total Memory (RAM): 32 GB# CPUs num: 24# Data Storage: ssdhttps://pgtune.leopard.in.ua suggested me tuning config as belowmax_connections = 200shared_buffers = 8GBeffective_cache_size = 24GBmaintenance_work_mem = 2GBcheckpoint_completion_target = 0.7wal_buffers = 16MBdefault_statistics_target = 100random_page_cost = 1.1effective_io_concurrency = 200work_mem = 699kBmin_wal_size = 1GBmax_wal_size = 2GBmax_worker_processes = 24max_parallel_workers_per_gather = 12max_parallel_workers = 24I have two questions regarding this1. What changes do I have to do if I add more standbys or it will be same over all servers?
There are very minimal changes like IP addresses and ports because your standbys are in the different domain and standbys are build from the primary basebackup.2. How to select a value for max_connections based on the fact I have 3 servers with same specs and client will using it for read operations only and data is loaded once a month.
The max_connection is totally based on your application priority because your master will observed read and write operations and your slave will go into the read operation only.
It depends on how the application is written divert to balance the load of your read and write queries between the primary and standbys.Happy to help :)Prince Pathria Systems Architect Intern Evive +91 9478670472 goevive.com