pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBig sized materialized views break replication
6+ messages / 4 participants
[nested] [flat]
* Big sized materialized views break replication
@ 2017-02-01 10:39 Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
2017-02-01 11:02 ` Re: Big sized materialized views break replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-02-01 11:08 ` Re: Big sized materialized views break replication Samed YILDIRIM <samed@reddoc.net>
0 siblings, 2 replies; 6+ messages in thread
From: Κοκμάδης Δημήτριος @ 2017-02-01 10:39 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Big sized materialized views break replication
2017-02-01 10:39 Big sized materialized views break replication Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
@ 2017-02-01 11:02 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-02-01 11:49 ` Re: Big sized materialized views break replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 6+ messages in thread
From: Achilleas Mantzios @ 2017-02-01 11:02 UTC (permalink / raw)
To: pgsql-admin
Για σου Δημήτρη,
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Big sized materialized views break replication
2017-02-01 10:39 Big sized materialized views break replication Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
2017-02-01 11:02 ` Re: Big sized materialized views break replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2017-02-01 11:49 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-02-21 07:22 ` Re: Big sized materialized views break replication Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Achilleas Mantzios @ 2017-02-01 11:49 UTC (permalink / raw)
To: pgsql-admin
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 <mailto: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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Big sized materialized views break replication
2017-02-01 10:39 Big sized materialized views break replication Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
2017-02-01 11:02 ` Re: Big sized materialized views break replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-02-01 11:49 ` Re: Big sized materialized views break replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2017-02-21 07:22 ` Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
2017-02-21 09:06 ` Re: Big sized materialized views break replication Lazaro Garcia <lazaro3487@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Κοκμάδης Δημήτριος @ 2017-02-21 07:22 UTC (permalink / raw)
To: pgsql-admin
Hello,
Is it possible to use replication with slots and without them in the same
setup or it could cause a problem?
Regards,
Dimitris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Big sized materialized views break replication
2017-02-01 10:39 Big sized materialized views break replication Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
2017-02-01 11:02 ` Re: Big sized materialized views break replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-02-01 11:49 ` Re: Big sized materialized views break replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-02-21 07:22 ` Re: Big sized materialized views break replication Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
@ 2017-02-21 09:06 ` Lazaro Garcia <lazaro3487@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Lazaro Garcia @ 2017-02-21 09:06 UTC (permalink / raw)
To: 'Κοκμάδης Δημήτριος' <dkokmadis@gmail.com>; pgsql-admin
If recovery.conf has this configuration parameter:
primary_slot_name = repmgr_slot_1
The replication use replication slots otherwise when wal_keep_segments is reached, the redo logs are rotated.
With replication slot you only have the required wal files but with wal_keep_segments you only has the amount especified in it.
If you do not use replication slots there is no need of creating slots, see SELECT * from pg_replication_slots; on the master.
When a slot is used, wal_keep_segments has no effects and could be 0.
Regards.
De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de ???µ?d?? ??µ?t????
Enviado el: martes, 21 de febrero de 2017 08:23 a. m.
Para: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] Big sized materialized views break replication
Hello,
Is it possible to use replication with slots and without them in the same setup or it could cause a problem?
Regards,
Dimitris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Big sized materialized views break replication
2017-02-01 10:39 Big sized materialized views break replication Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
@ 2017-02-01 11:08 ` Samed YILDIRIM <samed@reddoc.net>
1 sibling, 0 replies; 6+ messages in thread
From: Samed YILDIRIM @ 2017-02-01 11:08 UTC (permalink / raw)
To: Κοκμάδης Δημήτριος <dkokmadis@gmail.com>; pgsql-admin
<div>Hi Dimitri,</div><div> </div><div>Could you please share Postgres installation version?</div><div>Also what do you mean by saying recreate materialized view, refresh or drop/create?</div><div> </div><div>Best regards.</div><div> </div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>01.02.2017, 12:41, "Κοκμάδης Δημήτριος" <dkokmadis@gmail.com>:</div><blockquote type="cite"><div>Hello,<div> </div><div>I use hot standby replication mode. </div><div>When I try to recreate big sized materialised views, about 3G size, the replication breaks and I have to resync it. </div><div> </div><div>Is there any way to avoid it?</div><div> </div><div>My master server settings</div><div> </div><div><div>shared_buffers = 30GB</div><div>work_mem = 3146kB</div><div>maintenance_work_mem = 2GB</div><div>dynamic_shared_memory_type = posix<span style="white-space:pre;"> </span># the default is the first option</div><div>wal_level = hot_standby</div><div>wal_buffers = 16MB</div><div>checkpoint_segments = 128</div><div>checkpoint_completion_target = 0.9</div><div>max_wal_senders = 128</div><div>wal_keep_segments = 8</div><div>effective_cache_size = 90GB</div><div>default_statistics_target = 100</div><div>logging_collector = on<span style="white-space:pre;"> </span># Enable capturing of stderr and csvlog</div><div>log_directory = '/var/log/postgresql'<span style="white-space:pre;"> </span># directory where log files are written,</div><div>log_filename = 'postgresql-%Y-%m-%d.log'<span style="white-space:pre;"> </span># log file name pattern,</div><div>log_rotation_age = 1d<span style="white-space:pre;"> </span># Automatic rotation of logfiles will</div><div>log_min_duration_statement = 3000<span style="white-space:pre;"> </span># -1 is disabled, 0 logs all statements</div><div>log_line_prefix = '%t '<span style="white-space:pre;"> </span># special values:</div><div>log_timezone = 'localtime'</div><div>stats_temp_directory = '/var/run/postgresql/9.4-main.pg_stat_tmp'</div><div>autovacuum = on<span style="white-space:pre;"> </span># Enable autovacuum subprocess? 'on'</div><div>log_autovacuum_min_duration = -1<span style="white-space:pre;"> </span># -1 disables, 0 logs all actions and</div><div>autovacuum_max_workers = 3<span style="white-space:pre;"> </span># max number of autovacuum subprocesses</div><div>autovacuum_naptime = 15min<span style="white-space:pre;"> </span># time between autovacuum runs</div><div>autovacuum_vacuum_threshold = 50<span style="white-space:pre;"> </span># min number of row updates before</div><div>autovacuum_analyze_threshold = 50<span style="white-space:pre;"> </span># min number of row updates before</div><div> </div></div><div> </div><div>Regards,</div><div>Dimitris</div></div></blockquote>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2017-02-21 09:06 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-02-01 10:39 Big sized materialized views break replication Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
2017-02-01 11:02 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-02-01 11:49 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-02-21 07:22 ` Κοκμάδης Δημήτριος <dkokmadis@gmail.com>
2017-02-21 09:06 ` Lazaro Garcia <lazaro3487@gmail.com>
2017-02-01 11:08 ` Samed YILDIRIM <samed@reddoc.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox