Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cYtPv-0003xh-32 for pgsql-admin@arkaria.postgresql.org; Wed, 01 Feb 2017 11:49:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cYtPu-0000c5-JB for pgsql-admin@arkaria.postgresql.org; Wed, 01 Feb 2017 11:49:38 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cYtPt-0000bI-8X for pgsql-admin@postgresql.org; Wed, 01 Feb 2017 11:49:37 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cYtPl-0000zJ-OH for pgsql-admin@postgresql.org; Wed, 01 Feb 2017 11:49:35 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id v11BnRD8091445 for ; Wed, 1 Feb 2017 13:49:27 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) From: Achilleas Mantzios Subject: Re: Big sized materialized views break replication References: <5891C050.3060100@matrix.gatewaynet.com> To: pgsql-admin@postgresql.org Message-ID: <5891CB47.2090104@matrix.gatewaynet.com> Date: Wed, 1 Feb 2017 13:49:27 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------080602040503040901060201" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------080602040503040901060201 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 >: > > Για σου Δημήτρη, > > 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 --------------080602040503040901060201 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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@matrix.gatewaynet.com>:
Για σου Δημήτρη,

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
--------------080602040503040901060201--