Re: Failed transaction statistics to measure the logical replication progress - Mailing list pgsql-hackers

On Mon, Aug 2, 2021 at 2:52 PM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote:
>
> On Thursday, July 29, 2021 10:50 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > On Thu, Jul 8, 2021 at 3:55 PM osumi.takamichi@fujitsu.com
> > <osumi.takamichi@fujitsu.com> wrote:
> > > When the current HEAD fails during logical decoding, the failure
> > > increments txns count in pg_stat_replication_slots - [1] and adds
> > > the transaction size to the sum of bytes in the same repeatedly on
> > > the publisher, until the problem is solved.
> > > One of the good examples is duplication error on the subscriber side
> > > and this applies to both streaming and spill cases as well.
> > >
> > > This update prevents users from grasping the exact number and size
> > > of successful and unsuccessful transactions. Accordingly, we need to
> > > have new columns of failed transactions that will work to
> > > differentiate both of them for all types, which means spill,
> > > streaming and normal transactions. This will help users to measure
> > > the exact status of logical replication.
> >
> > Could you please elaborate on use cases of the proposed statistics?
> > For example, the current statistics on pg_replication_slots can be
> > used for tuning logical_decoding_work_mem as well as inferring the
> > total amount of bytes passed to the output plugin. How will the user use those statistics?
> >
> > Also, if we want the stats of successful transactions why don't we
> > show the stats of successful transactions in the view instead of ones
> > of failed transactions?
> It works to show the ratio of successful and unsuccessful transactions,
> which should be helpful in terms of administrator perspective.
> FYI, the POC patch added the columns where I prefixed 'failed' to those names.
> But, substantially, it meant the ratio when user compared normal columns and
> newly introduced columns by this POC in the pg_stat_replication_slots.

What can the administrator use the ratio of successful and
unsuccessful logical replication transactions for? For example, IIUC
if a conflict happens on the subscriber as you mentioned, the
successful transaction ratio calculated by those statistics is getting
low, perhaps meaning the logical replication stopped. But it can be
checked also by checking pg_stat_replication view or
pg_replication_slots view (or error counts of
pg_stat_subscription_errors view I’m proposing[1]). Do you have other
use cases?

> > Moreover, if we want to add more statistics on the view in the future,
> > it further increases the usage of shared memory. If we want to track
> > the stats of successful transactions, I think it's easier to track
> > them on the subscriber side rather than the publisher side. We can
> > increase counters when applying [stream]commit/abort logical changes on the subscriber.
> It's true that to track the stats of successful and unsuccessful transactions on the *sub*
> is easier than on the pub. After some survey, it turned out that I cannot distinguish
> the protocol messages between the cases of any failure (e.g. duplication error on the sub)
> from user intentional and successful operations(e.g. DROP SUBSCRIPTION and ALTER SUBSCRIPTION DISABLE) on the pub.
>
> If we truly want to achieve this change on the publisher side,
> protocol change requires in order to make above cases distinguishable,
> now I feel that it is better to do this in the subscriber side.
>
> Accordingly, I'm thinking to have unsuccessful and successful stats on the sub side.
> Sawada-san is now implementing a new view in [1].
> Do you think that I should write a patch to introduce a new separate view
> or write a patch to add more columns to the new view "pg_stat_subscription_errors" that is added at [1] ?

pg_stat_subscriptions_errors view I'm proposing is a view showing the
details of error happening during logical replication. So I think a
separate view or pg_stat_subscription view would be a more appropriate
place.

Regards,

[1] https://www.postgresql.org/message-id/flat/CAD21AoDeScrsHhLyEPYqN3sydg6PxAPVBboK=30xJfUVihNZDA@mail.gmail.com

--
Masahiko Sawada
EDB:  https://www.enterprisedb.com/