Re: Configuration changes with multiple standby nodes

[Shreeyansh Dba <shreeyansh2014@xxxxxxxxx>]

Hi Prince,

Please find the answers inline.

On Fri, Sep 21, 2018 at 1:11 PM Prince Pathria <prince.pathria@xxxxxxxxxxx> 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: 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?

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