Excessive number of replication slots for 12->14 logical replication - Mailing list pgsql-bugs
| From | hubert depesz lubaczewski |
|---|---|
| Subject | Excessive number of replication slots for 12->14 logical replication |
| Date | |
| Msg-id | 20220714115155.GA5439@depesz.com Whole thread Raw |
| Responses |
Re: Excessive number of replication slots for 12->14 logical replication
Re: Excessive number of replication slots for 12->14 logical replication |
| List | pgsql-bugs |
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-1411: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
pgsql-bugs by date: