Re: Big sized materialized views break replication

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Of course your problems is with the high latency cloud servers.
You are using 9.4 so this is the time to use the feature introduced in 9.4 called : replication slots.
Read the docs, replication slots address the problems you are now facing.

On 01/02/2017 13:13, Κοκμάδης Δημήτριος wrote:
Hi,

- max_replication_slots is 0 the default value
- it is internal network 1GB 
- How can I check this? I think all my wal files are 16MB

Samed:
My Postgres version is 9.4 and I use the refresh command.

I have also to notice that I have 4 replication servers and the problem happens most of the times in cloud servers. In bare metal server, most times refresh works without problem. 

Regards,

Dimitris



2017-02-01 13:02 GMT+02:00 Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx>:
Για σου Δημήτρη,

you will have to give more info on :
- setup of replication slots
- speed/latency of connection between primary and standby
- size of wals produced during the recreation of materialization


On 01/02/2017 12:39, Κοκμάδης Δημήτριος wrote:
Hello,

I use hot standby replication mode. 
When I try to recreate big sized materialised views, about 3G size, the replication breaks and I have to resync it. 

Is there any way to avoid it?

My master server settings

shared_buffers = 30GB
work_mem = 3146kB
maintenance_work_mem = 2GB
dynamic_shared_memory_type = posix # the default is the first option
wal_level = hot_standby
wal_buffers = 16MB
checkpoint_segments = 128
checkpoint_completion_target = 0.9
max_wal_senders = 128
wal_keep_segments = 8
effective_cache_size = 90GB
default_statistics_target = 100
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = '/var/log/postgresql' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d.log' # log file name pattern,
log_rotation_age = 1d # Automatic rotation of logfiles will
log_min_duration_statement = 3000 # -1 is disabled, 0 logs all statements
log_line_prefix = '%t  ' # special values:
log_timezone = 'localtime'
stats_temp_directory = '/var/run/postgresql/9.4-main.pg_stat_tmp'
autovacuum = on # Enable autovacuum subprocess?  'on'
log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
autovacuum_max_workers = 3 # max number of autovacuum subprocesses
autovacuum_naptime = 15min # time between autovacuum runs
autovacuum_vacuum_threshold = 50 # min number of row updates before
autovacuum_analyze_threshold = 50 # min number of row updates before


Regards,
Dimitris


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux