From: Wim Bertels <wim.bertels@ucll.be>
To: Keith Fiske <keith.fiske@crunchydata.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: viewing connectioninfo used by subscriber on the publication server when inactive
Date: Tue, 26 May 2020 15:29:09 +0200
Message-ID: <8c4bb46ad4541b730dfeca62815cdf9c36314b42.camel@ucll.be> (raw)
In-Reply-To: <95bb0c6c21a2a96a526a5ce01f16e62ea4f073f5.camel@ucll.be>
References: <25037dc66dadcdd61148f6d59208217441abfd80.camel@ucll.be>
<CAODZiv5QMzAd5_ckxMMfxv84Y3YeASX9ePF3k1u1ERnRKgmrEw@mail.gmail.com>
<5f77a6c973aaf8f9086cf42933f476e5bd9a948d.camel@ucll.be>
<CAODZiv4LpJ+RfkfesjC91w446hjA3Hhsk_9-TG=cVO8h-0ihLQ@mail.gmail.com>
<562890eff48a048d4b09d23f76506fceb867a31f.camel@ucll.be>
<CAODZiv5J2op7t5_P-6gQwKXVefmFXV9WhLPtN159U_PbyyM_jQ@mail.gmail.com>
<95bb0c6c21a2a96a526a5ce01f16e62ea4f073f5.camel@ucll.be>
Wim Bertels schreef op do 14-05-2020 om 16:22 [+0200]:
> Keith Fiske schreef op do 14-05-2020 om 10:08 [-0400]:
> > It doesn't matter how small the dataset change is. The same WAL
> > stream is used for both logical and physical replication so it has
> > to
> > keep all WAL files until all subscribers for that publication have
> > confirmed they have received them. If even a single subscriber goes
> > offline, all WAL will be kept until that subscriber reconnects.
>
> That is interesting, i assume this the WAL for the whole cluster, as
> logical decoding is then used on this WAL for the logical
> replication,
> do you have an estimate of order of magnitude for the all WAL files?
>
> So far this seems ok over here (with one subscriber inactive for 2
> days):
> # du -ch pg_logical/ pg_wal/ pg_replslot/
> 912K pg_logical/snapshots
> 4,0K pg_logical/mappings
> 924K pg_logical/
> 4,0K pg_wal/archive_status
> 81M pg_wal/
> 8,0K pg_replslot/db2_sub
> 8,0K pg_replslot/db2_sub1
> 8,0K pg_replslot/db2_sub2
> 28K pg_replslot/
> 81M totaal
> this after two days of replication setup.
>
> assuming that students will be offline for at most 2 or 3 days,
> this seems ok?
Follow-up info after 14 days: (this is a typical usecase)
the main stress seems to be the cpu, the load is 3-6 as high as normal,
storage and memory are ok
pg_logical/snapshots takes the most storage in this use case (little
writes, many async log replication users), not pg_wal
8,4G pg_logical/snapshots
4,0K pg_logical/mappings
8,4G pg_logical/
4,0K pg_wal/archive_status
161M pg_wal/
8,0K pg_replslot/db2_sub
8,0K pg_replslot/db2_sub_ma
8,0K pg_replslot/db2_sub_r074
8,0K pg_replslot/db2_sub_si
8,0K pg_replslot/db2_sub_r0604
8,0K pg_replslot/db2_sub_r0761
8,0K pg_replslot/db2_r0653
8,0K pg_replslot/nook_subscription
8,0K pg_replslot/r0745
8,0K pg_replslot/db2_sub_ma
8,0K pg_replslot/db2_sub_an
8,0K pg_replslot/db2_sub_th
8,0K pg_replslot/r07456
8,0K pg_replslot/db2_sub5
8,0K pg_replslot/mysub
12K pg_replslot/db2_sub_mart
8,0K pg_replslot/sub_db2
8,0K pg_replslot/db2_r653
8,0K pg_replslot/sub_rube
8,0K pg_replslot/db2_sub_
8,0K pg_replslot/db2_sub_b
8,0K pg_replslot/db2_sub2
8,0K pg_replslot/db2_sub_b
8,0K pg_replslot/db2_sub73
8,0K pg_replslot/db2_sub_r74
8,0K pg_replslot/db2_sub_andr
8,0K pg_replslot/db2_0653
8,0K pg_replslot/db2_sub_i
8,0K pg_replslot/db2_pub
240K pg_replslot/
8,6G totaal
simple user logging:
a psql outfile is 17MB after 14 days (where the replication connections
are appended each minute)
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: wim.bertels@ucll.be, keith.fiske@crunchydata.com
Subject: Re: viewing connectioninfo used by subscriber on the publication server when inactive
In-Reply-To: <8c4bb46ad4541b730dfeca62815cdf9c36314b42.camel@ucll.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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