Message-ID: <8c4bb46ad4541b730dfeca62815cdf9c36314b42.camel@ucll.be>
Subject: Re: viewing connectioninfo used by subscriber on the publication
 server when inactive
From: Wim Bertels <wim.bertels@ucll.be>
Reply-To: wim.bertels@ucll.be
To: Keith Fiske <keith.fiske@crunchydata.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Date: Tue, 26 May 2020 15:29:09 +0200
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>
Organization: UC Leuven-Limburg
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.30.5-1.1 
Content-Transfer-Encoding: 7bit
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
Received: from ptr-7u36h5k51lesqlynxiz.18120a2.ip6.access.telenet.be
 (2a02:1811:526:1700:60e:3cff:feb0:7f7b) by
 AM0PR02CA0100.eurprd02.prod.outlook.com (2603:10a6:208:154::41) with
 Microsoft SMTP Server (version=TLS1_2,
 cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3021.23 via Frontend
 Transport; Tue, 26 May 2020 13:29:10 +0000
X-Originating-IP: [2a02:1811:526:1700:60e:3cff:feb0:7f7b]
X-MS-PublicTrafficType: Email
X-MS-Office365-Filtering-Correlation-Id: c18708ef-0ec2-4b9c-1e3f-08d80178c630
X-MS-TrafficTypeDiagnostic: AM0P195MB0708:
X-Microsoft-Antispam-PRVS: 
	<AM0P195MB0708C30DCB6EAB5E1A2D7BF1E3B00@AM0P195MB0708.EURP195.PROD.OUTLOOK.COM>
X-MS-Oob-TLC-OOBClassifiers: OLM:7691;
X-Forefront-PRVS: 041517DFAB
X-MS-Exchange-SenderADCheck: 1
X-Microsoft-Antispam: BCL:0;
X-Microsoft-Antispam-Message-Info: 
	CCdcOeExCXSTfQlfGNeXueocfpBxfahTynEswxHPmZHsRDUCkDwmSvnMg9XI+34YGgHMgRNJksvQ3iiKiTL3ZBFmEVBHyOd9hZpfQJM8Lo0OWjVXRVPceg8PkBtrVs+nnkZhjPbKcMW7iOq8TGL62XX2qEIob6vr8qyd7umg6t3+s0o5ZZuyufUVWav/xhiE+NCbD9Ik/kHJELpmJ0TMhlVMT2Xr79/puGnIzQftGueMhQDngiwdym6ztPO/71NpFWD6tHj53/NZ1NHEhVHbY0jPvVknn3WoTzrJxSGtYp3ac2+4jqWy50euPTbW2GAF3134Npn/yrbSPE4sji3a1Q==
X-Forefront-Antispam-Report: 
	CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AM0P195MB0324.EURP195.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFTY:;SFS:(4636009)(366004)(2906002)(66556008)(66476007)(498600001)(66946007)(6512007)(2616005)(4326008)(44832011)(86362001)(3450700001)(6916009)(6486002)(36916002)(16526019)(6506007)(5660300002)(8676002)(186003)(8936002)(52116002)(36756003);DIR:OUT;SFP:1101;
X-MS-Exchange-AntiSpam-MessageData: 
	ZqVJfbxiF0Pr7G2Ut+cbWYTdrbqBNNe7HoSXNblxwIlcS69xgxHdRcwwtKRmjIX7RLf6YHph4/iZ+ANPMsnAh9f3SAuJbZ7N4p0j3U/sQuclwztBaKTiBCdsHGeBtnYXmuajbNhE05mBOnmoRdkHyI2LZyESIxSlMqqpJNAp9OodZ+Rn41sQKczwh4LE2ZCDVPrsFpqUrHfA6sSLdv8J9sW1hZgfj2/CwQuu94xFOAMm97DSnKfM1PPtHOnptGbnU/0swx246SsxZpEP3O1fVnFtVBcg7Inp3bfRsiykdujsFTlf/7DyfmNnPUXiR4iKcpgURNCmc1IhE/BwKvayiawI5dM+8N3+urKw/GRCzCP+Qg+mjlaIpcF8gXNt+NCoPwDPG1GBA6eWFWCmmpGN+z1NETTIkY6f0kZFeA/AFt4mEdl8Rrg8DVAMsNkrkfy+N1wTHEL519VVoz9bpvwCwhHjntc9oFLfBJmeYN+yWhDLZNIGugvF97vG9HZduEt0DVQID0YYmJhEBbAkn6q9CzRWUJt3SPliu+IywfHurWE=
X-OriginatorOrg: ucll.be
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 c18708ef-0ec2-4b9c-1e3f-08d80178c630
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 26 May 2020 13:29:10.8039
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: e638861b-15d9-4de6-a65d-b48789ae1f08
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 
 XqM73jvZrR6mERG4uubT+WSJZS3IuqorXd0jJ1w8fZCbsKxbdreEt4FA9thxgiiklJQWzahJjyHMgUrYfrLDoQ==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: AM0P195MB0708
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Precedence: bulk

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)

>