Feedback-ID: i0fe9450f:Fastmail
Date: Mon, 23 Oct 2023 17:18:32 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: "Callahan, Drew" <callaan@amazon.com>,
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18155: Logical Apply Worker Timeout During TableSync Causes
 Either Stuckness or Data Loss
Message-ID: <ZTYsWHEfF39gDoJB@paquier.xyz>
References: <18155-33f555bcc7e1105e@postgresql.org>
 <CAA4eK1JRhXZCg46OL--V+4ZqDGtBySqiraWGrJZXkyh2H-M1eg@mail.gmail.com>
 <3AF2B908-D293-43F9-AE06-73C7797C96B0@amazon.com>
 <CAA4eK1+ytq53_GmDLCmxkDqvwgbgxBNg-694grxxaVSQadZA5Q@mail.gmail.com>
 <ZS8zRgKfB7AcxJWv@paquier.xyz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="jzrtOIBOT8cGGaMe"
Content-Disposition: inline
In-Reply-To: <ZS8zRgKfB7AcxJWv@paquier.xyz>
Archived-At: 
 <https://www.postgresql.org/message-id/ZTYsWHEfF39gDoJB%40paquier.xyz>
Precedence: bulk


--jzrtOIBOT8cGGaMe
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Oct 18, 2023 at 10:22:14AM +0900, Michael Paquier wrote:
> On Tue, Oct 17, 2023 at 09:50:52AM +0530, Amit Kapila wrote:
>> On Tue, Oct 17, 2023 at 4:46=E2=80=AFAM Callahan, Drew <callaan@amazon.c=
om> wrote:
>>> On the server side, we did not see evidence of WALSenders being launche=
d. As a result, the gap kept increasing further
>>> and further since they workers would not transition to the catchup stat=
e after several hours due to this.
>>=20
>> One possibility is that the system has reached
>> 'max_logical_replication_workers' limit due to which it is not
>> allowing to launch the apply worker. If so, then consider increasing
>> the value of 'max_logical_replication_workers'. You can query
>> 'pg_stat_subscription' to know more information about workers. See the
>> description of subscriber-side parameters [1].
>=20
> Hmm.  So you basically mean that not being able to launch new workers
> prevents the existing workers to move on with their individual sync,
> freeing slots once their sync is done for other tables.  Then, this
> causes all all of the existing workers to remain in a syncwait state,
> further increasing the gap in WAL replay.  Am I getting that right?

I was looking more at what we could do here, played with the apply and
sync workers and I really doubt that just ERROR-ing in a sync worker
is a good idea on v14~ just for the sake of freeing bgworker slots,
which would have the effect of spawning more apply workers.  I mean,
if your system is under pressure because of a lack of bgworker slots,
assuming that a set of N apply workers are gone, leaving M sync
workers (M >=3D N) lying around without being able to get updates from
an apply worker, ERRORs in sync workers would give more room to spawn
apply workers, but then the launcher worker may fight for the free'd
slots to attempt to spawn sync workers for the fresh apply workers,
no?  It also seems to make the reasoning around the table states
harder to think about, as any future apply workers need to handle more
error cases related to sync workers.

I got a few extra questions about all that:
- How did the apply worker stop?  That can be said to be the origin of
the issue.
- Did logicalrep_worker_launch() provide some WARNINGs because the
cluster is running out of slots, preventing the spawn of a new apply
worker to replace the one that's down, so as the table sync workers
are able to move on with their sync phases?  I guess it did..  The
frequency would offer hints about the pressure put on the system.
--
Michael

--jzrtOIBOT8cGGaMe
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmU2LFgACgkQnvQgOdby
QH2zlA//Z7dRwsz4JiPuDoty7nlf+C1PPdRqZlCJEomQv471Qw/8wyqv1D891RLd
QaVW/dA1S4NUvACWD7R6uBQzdQ6xF8tcG7ZdkwnZo9vq8YA+uqDrovC3KSq5ycNi
yFpz459LSlj4PocKCLwhNv/YXP0hveVOSfkEW4aglHGp3Asp9OT/mUEJjiIGMqcF
TRZxkLuawQN0JiX/RmtcMvMg8QYEZr1cBqiv6DhViQLTZns2GBq1miZky+rYo2b7
6BAzngen30U9O59Cmm9SyYQhhWDhPQa9YDCaPJNaczr0knyYbIr7bOUobtThUCGM
CC1lafI0yFYanGooRsT2QlE/TcAyIBtEBbYX615FpTLrHnbqmFiTIoxEIiZqdCzo
RKVlgBEkfWGkWU2bXIjtxsCwkT/s0lBr96gzofDytZOCLATLkZePWF57w5b/R7jL
NNSmZgHq/pgQe9WAfNDwHt+Lc8iFuXH4rkA+Z9W4euuo2kN6dTmgIIsSgNEn/dbt
IAS+kiX5dqNJJ9waIuJavLNW/Gzjm5klfLWxwqOp1u+sSHS+9anvRiVb7enYMzwI
pePD/P2WwrxvAT354UGFgqW0WBBZYSczvRCy4vL/R1bDQFDqa/ZXTtUDsAAjqUBZ
cuvtL+YEn8BaIbOzlYjdMwNDVVJxKW2OQ+IKTPVKcNGHzQJO89A=
=/ExH
-----END PGP SIGNATURE-----

--jzrtOIBOT8cGGaMe--