pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: hubert depesz lubaczewski <depesz@depesz.com>
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Excessive number of replication slots for 12->14 logical replication
Date: Thu, 14 Jul 2022 13:51:55 +0200
Message-ID: <20220714115155.GA5439@depesz.com> (raw )
Hi,
We're using logical replication to replicate database from pg 12.9 to
14.4.
Given the number of objects we made decision to use 10 separate
publications, each containing subset of tables.
So, we have:
$ select pubname, count(*) from pg_publication_tables where pubname ~ 'focal' group by pubname;
pubname │ count
════════════╪═══════
focal14_1 │ 412
focal14_10 │ 15580
focal14_2 │ 14316
focal14_3 │ 14352
focal14_4 │ 14367
focal14_5 │ 15187
focal14_6 │ 15171
focal14_7 │ 15187
focal14_8 │ 15169
focal14_9 │ 15578
(10 rows)
On both sides we have max_sync_workers_per_subscription set to 2.
So, I would assume that during initial subscription, we can go up to 30
connections. To make it safe we set limit to 50, but apparently it's not
enough.
I'm getting *LOTS* of errors about number of replication slots:
2022-07-14 11:40:30.612 UTC,"upgrayedd","dbname",11829,"10.1.191.34:37394",62d000ae.2e35,5,"idle in transaction",2022-07-14 11:40:30 UTC,38/350893,0,ERROR,53400,"all replication slots are in use",,"Free one or increase max_replication_slots.",,,,"CREATE_REPLICATION_SLOT ""pg_1337070_sync_189406_7119125406647933415"" LOGICAL pgoutput USE_SNAPSHOT",,,"pg_1337070_sync_189406_7119125406647933415"
We're getting them (in the beginning of sync) to the tune of 2-4 thousand per minute.!
Took a snapshot of pg_replication_slots and it looked like this:
#v+
slot_name │ plugin │ slot_type │ datoid │ database │ temporary │ active │ active_pid │ xmin │ catalog_xmin │ restart_lsn │ confirmed_flush_lsn
════════════════════════════════════════════╪══════════╪═══════════╪═════════╪══════════╪═══════════╪════════╪════════════╪════════╪══════════════╪══════════════╪═════════════════════
focal14_1 │ pgoutput │ logical │ 7436115 │ dbname │ f │ t │ 11306 │ [null] │ 175118841 │ BAD/BAC6D98 │ BAD/BACFFF0
focal14_9 │ pgoutput │ logical │ 7436115 │ dbname │ f │ t │ 11413 │ [null] │ 175118841 │ BAD/BAC6E08 │ BAD/BACFFF0
focal14_5 │ pgoutput │ logical │ 7436115 │ dbname │ f │ t │ 11400 │ [null] │ 175118841 │ BAD/BAC6DD0 │ BAD/BACFFF0
focal14_2 │ pgoutput │ logical │ 7436115 │ dbname │ f │ t │ 11395 │ [null] │ 175118841 │ BAD/BAC6E08 │ BAD/BACFFF0
focal14_3 │ pgoutput │ logical │ 7436115 │ dbname │ f │ t │ 11397 │ [null] │ 175118841 │ BAD/BAC6DD0 │ BAD/BACFFF0
focal14_4 │ pgoutput │ logical │ 7436115 │ dbname │ f │ t │ 11398 │ [null] │ 175118841 │ BAD/BAC6DD0 │ BAD/BACFFF0
pg_1337073_sync_570162_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118837 │ BAD/BAABB10 │ BAD/BAB2EF8
focal14_6 │ pgoutput │ logical │ 7436115 │ dbname │ f │ t │ 11401 │ [null] │ 175118841 │ BAD/BAC6DD0 │ BAD/BACFFF0
focal14_7 │ pgoutput │ logical │ 7436115 │ dbname │ f │ t │ 11403 │ [null] │ 175118841 │ BAD/BAC6D98 │ BAD/BACFFF0
focal14_8 │ pgoutput │ logical │ 7436115 │ dbname │ f │ t │ 11406 │ [null] │ 175118841 │ BAD/BAC6DD0 │ BAD/BACFFF0
pg_1336982_sync_443064_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118837 │ BAD/BAAB8E0 │ BAD/BAB2EF8
pg_1337042_sync_41643_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118837 │ BAD/BAAB918 │ BAD/BAB2EF8
pg_1337072_sync_884811_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118843 │ BAD/BAE5668 │ BAD/BAEC2C0
pg_1336981_sync_392740_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BABFE90 │ BAD/BAC6D98
pg_1337042_sync_139273_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BABFEC8 │ BAD/BAC6D98
pg_1337056_sync_648956_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118837 │ BAD/BAAB9F8 │ BAD/BAB2EF8
pg_1337055_sync_423485_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118837 │ BAD/BAABA30 │ BAD/BAB2EF8
pg_1337072_sync_448264_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118837 │ BAD/BAABA68 │ BAD/BAB2EF8
pg_1336982_sync_271954_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118838 │ BAD/BAB6650 │ BAD/BABD7B8
pg_1337071_sync_82458_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118837 │ BAD/BAABAD8 │ BAD/BAB2EF8
pg_1337072_sync_550946_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118837 │ BAD/BAABB48 │ BAD/BAB2EF8
focal14_10 │ pgoutput │ logical │ 7436115 │ dbname │ f │ t │ 12859 │ [null] │ 175118805 │ BAD/B916658 │ BAD/B916690
pg_1337056_sync_553531_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118837 │ BAD/BAABB80 │ BAD/BAB2EF8
pg_1336982_sync_920960_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BABFC98 │ BAD/BAC6D98
pg_1337072_sync_150987_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BABFF00 │ BAD/BAC6D98
pg_1337071_sync_199748_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118837 │ BAD/BAABC28 │ BAD/BAB2EF8
pg_1337055_sync_790318_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BABFF38 │ BAD/BAC6D98
pg_1337056_sync_79981_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BABFF70 │ BAD/BAC6D98
pg_1337042_sync_919197_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BABFFA8 │ BAD/BAC6D98
pg_1337055_sync_671382_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BABFFE0 │ BAD/BAC6D98
pg_1337072_sync_937506_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BAC0030 │ BAD/BAC6D98
pg_1337070_sync_89436_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BAC0068 │ BAD/BAC6D98
pg_1337071_sync_92364_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BAC00A0 │ BAD/BAC6D98
pg_1336982_sync_237037_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BAC00D8 │ BAD/BAC6D98
pg_1337056_sync_473314_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BAC0110 │ BAD/BAC6D98
pg_1337071_sync_559400_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BAC0148 │ BAD/BAC6D98
pg_1337070_sync_521659_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BAC0180 │ BAD/BAC6D98
pg_1337073_sync_342687_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BAC01B8 │ BAD/BAC6D98
pg_1337073_sync_668407_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118839 │ BAD/BAC01F0 │ BAD/BAC6D98
pg_1337056_sync_737045_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118844 │ BAD/BAEE5D8 │ BAD/BAF4168
pg_1337073_sync_477925_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118841 │ BAD/BAD2E88 │ BAD/BAD87D0
pg_1336981_sync_393379_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118843 │ BAD/BAE5710 │ BAD/BAEC2C0
pg_1337070_sync_22486_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118841 │ BAD/BAD2EF8 │ BAD/BAD87D0
pg_1337071_sync_618386_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118843 │ BAD/BAE57F0 │ BAD/BAEC2C0
pg_1337056_sync_706794_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118843 │ BAD/BAE5780 │ BAD/BAEC2C0
pg_1337070_sync_266887_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118843 │ BAD/BAE55C0 │ BAD/BAEC2C0
pg_1337072_sync_845361_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118841 │ BAD/BAD2FA0 │ BAD/BAD87D0
pg_1337056_sync_75850_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118841 │ BAD/BAD2FD8 │ BAD/BAD87D0
pg_1337055_sync_792553_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118843 │ BAD/BAE55F8 │ BAD/BAEC2C0
pg_1336982_sync_760909_7119125406647933415 │ pgoutput │ logical │ 7436115 │ dbname │ f │ f │ [null] │ [null] │ 175118843 │ BAD/BAE5748 │ BAD/BAEC2C0
#v-
What is happening, and why Pg is trying to get more than 30 concurrent
slots in this case?
Best regards,
depesz
view thread (61+ messages) latest in thread
Message-ID: <20220714115155.GA5439@depesz.com>
Permalink: ../20220714115155.GA5439@depesz.com/
Also on: postgresql.org/message-id/20220714115155.GA5439@depesz.com
copy link · copy postgr.es
reply 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-bugs@postgresql.org
Cc: depesz@depesz.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Excessive number of replication slots for 12->14 logical replication
In-Reply-To: <20220714115155.GA5439@depesz.com>
* 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