Failed transaction statistics to measure the logical replication progress
Hello, hackers
When the current HEAD fails during logical decoding, the failure
increments txns count in pg_stat_replication_slots - [1]https://www.postgresql.org/docs/devel/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW 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.
Attached file is the POC patch for this.
Current design is to save failed stats data in the ReplicationSlot struct.
This is because after the error, I'm not able to access the ReorderBuffer object.
Thus, I chose the object where I can interact with at the ReplicationSlotRelease timing.
Below is one example that I can get on the publisher,
after the duplication error on the subscriber caused by insert is solved.
postgres=# select * from pg_stat_replication_slots;
-[ RECORD 1 ]-------+------
slot_name | mysub
spill_txns | 0
spill_count | 0
spill_bytes | 0
failed_spill_txns | 0
failed_spill_bytes | 0
stream_txns | 0
stream_count | 0
stream_bytes | 0
failed_stream_txns | 0
failed_stream_bytes | 0
total_txns | 4
total_bytes | 528
failed_total_txns | 3
failed_total_bytes | 396
stats_reset |
Any ideas and comments are welcome.
Best Regards,
Takamichi Osumi
Attachments:
failed_transaction_stats_POC_v01.patchapplication/octet-stream; name=failed_transaction_stats_POC_v01.patchDownload+163-19
On Thu, Jul 8, 2021 at 12:25 PM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote:
Hello, hackers
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.Attached file is the POC patch for this.
Current design is to save failed stats data in the ReplicationSlot struct.
This is because after the error, I'm not able to access the ReorderBuffer object.
Thus, I chose the object where I can interact with at the ReplicationSlotRelease timing.Below is one example that I can get on the publisher,
after the duplication error on the subscriber caused by insert is solved.postgres=# select * from pg_stat_replication_slots;
-[ RECORD 1 ]-------+------
slot_name | mysub
spill_txns | 0
spill_count | 0
spill_bytes | 0
failed_spill_txns | 0
failed_spill_bytes | 0
stream_txns | 0
stream_count | 0
stream_bytes | 0
failed_stream_txns | 0
failed_stream_bytes | 0
total_txns | 4
total_bytes | 528
failed_total_txns | 3
failed_total_bytes | 396
stats_reset |Any ideas and comments are welcome.
+1 for having logical replication failed statistics. Currently if
there is any transaction failure in the subscriber after sending the
decoded data to the subscriber like constraint violation, object not
exist, the statistics will include the failed decoded transaction info
and there is no way to identify the actual successful transaction
data. This patch will help in measuring the actual decoded transaction
data.
Regards,
Vignesh
On Tuesday, July 13, 2021 2:50 PM vignesh C <vignesh21@gmail.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.Attached file is the POC patch for this.
Current design is to save failed stats data in the ReplicationSlot struct.
This is because after the error, I'm not able to access the ReorderBufferobject.
Thus, I chose the object where I can interact with at the
ReplicationSlotRelease timing.
Any ideas and comments are welcome.
...
+1 for having logical replication failed statistics. Currently if
there is any transaction failure in the subscriber after sending the decoded
data to the subscriber like constraint violation, object not exist, the statistics
will include the failed decoded transaction info and there is no way to identify
the actual successful transaction data. This patch will help in measuring the
actual decoded transaction data.
Yeah, we can apply this improvement to other error cases.
Thank you for sharing ideas to make this enhancement more persuasive.
Best Regards,
Takamichi Osumi
On Thu, Jul 8, 2021 at 4:55 PM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote:
Attached file is the POC patch for this.
Current design is to save failed stats data in the ReplicationSlot struct.
This is because after the error, I'm not able to access the ReorderBuffer object.
Thus, I chose the object where I can interact with at the ReplicationSlotRelease timing.
I think this is a good idea to capture the failed replication stats.
But I'm wondering how you are deciding if
the replication failed or not? Not all cases of ReplicationSLotRelease
are due to a failure. It could also be due to a planned dropping
of subscription or disable of subscription. I have not tested this but
won't the failed stats be updated in this case as well? Is that
correct?
regards,
Ajin Cherian
Fujitsu Australia
On Tuesday, July 27, 2021 3:59 PM Ajin Cherian <itsajin@gmail.com> wrote:
On Thu, Jul 8, 2021 at 4:55 PM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote:Attached file is the POC patch for this.
Current design is to save failed stats data in the ReplicationSlot struct.
This is because after the error, I'm not able to access the ReorderBufferobject.
Thus, I chose the object where I can interact with at the
ReplicationSlotRelease timing.
I think this is a good idea to capture the failed replication stats.
But I'm wondering how you are deciding if the replication failed or not? Not all
cases of ReplicationSLotRelease are due to a failure. It could also be due to a
planned dropping of subscription or disable of subscription. I have not tested
this but won't the failed stats be updated in this case as well? Is that correct?
Yes, what you said is true. Currently, when I run DROP SUBSCRIPTION or
ALTER SUBSCRIPTION DISABLE, failed stats values are added
to pg_stat_replication_slots unintentionally, if they have some left values.
This is because all those commands, like the subscriber apply failure
by duplication error, have the publisher get 'X' message at ProcessRepliesIfAny()
and go into the path to call ReplicationSlotRelease().
Also, other opportunities like server stop call the same in the end,
which leads to a situation that after the server restart,
the value of failed stats catch up with the (successful) existing stats values.
Accordingly, I need to change the patch to adjust those situations.
Thank you.
Best Regards,
Takamichi Osumi
On Thu, Jul 8, 2021 at 3:55 PM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote:
Hello, hackers
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?
Attached file is the POC patch for this.
Current design is to save failed stats data in the ReplicationSlot struct.
This is because after the error, I'm not able to access the ReorderBuffer object.
Thus, I chose the object where I can interact with at the ReplicationSlotRelease timing.
When discussing the pg_stat_replication_slots view, there was an idea
to store the slot statistics on ReplicationSlot struct. But the idea
was rejected mainly because the struct is on the shared buffer[1]/messages/by-id/CAA4eK1Kuj+3G59hh3wu86f4mmpQLpah_mGv2-wfAPyn+zT=P4A@mail.gmail.com. If
we store those counts on ReplicationSlot struct it increases the usage
of shared memory. And those statistics are used only by logical slots
and don’t necessarily need to be shared among the server processes.
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.
Regards,
[1]: /messages/by-id/CAA4eK1Kuj+3G59hh3wu86f4mmpQLpah_mGv2-wfAPyn+zT=P4A@mail.gmail.com
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
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.
Attached file is the POC patch for this.
Current design is to save failed stats data in the ReplicationSlot struct.
This is because after the error, I'm not able to access the
ReorderBufferobject.
Thus, I chose the object where I can interact with at the
ReplicationSlotRelease timing.
When discussing the pg_stat_replication_slots view, there was an idea
to store the slot statistics on ReplicationSlot struct. But the idea
was rejected mainly because the struct is on the shared buffer[1]. If
we store those counts on ReplicationSlot struct it increases the usage
of shared memory. And those statistics are used only by logical slots
and don’t necessarily need to be shared among the server processes.
Yes, I was aware of this.
I was not sure if this design will be expected or not for the enhancement,
I thought of changing the design accordingly once the idea gets accepted by the community.
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]/messages/by-id/CAD21AoDeScrsHhLyEPYqN3sydg6PxAPVBboK=30xJfUVihNZDA@mail.gmail.com.
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]/messages/by-id/CAD21AoDeScrsHhLyEPYqN3sydg6PxAPVBboK=30xJfUVihNZDA@mail.gmail.com ?
[1]: /messages/by-id/CAD21AoDeScrsHhLyEPYqN3sydg6PxAPVBboK=30xJfUVihNZDA@mail.gmail.com
Best Regards,
Takamichi Osumi
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]/messages/by-id/CAD21AoDeScrsHhLyEPYqN3sydg6PxAPVBboK=30xJfUVihNZDA@mail.gmail.com). 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]: /messages/by-id/CAD21AoDeScrsHhLyEPYqN3sydg6PxAPVBboK=30xJfUVihNZDA@mail.gmail.com
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
On Mon, Aug 2, 2021 at 1:13 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Mon, Aug 2, 2021 at 2:52 PM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote: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.
+1 for having these stats in pg_stat_subscription. Do we want to add
two columns (xact_commit: number of transactions successfully applied
in this subscription, xact_rollback: number of transactions that have
been rolled back in this subscription) or do you guys have something
else in mind?
--
With Regards,
Amit Kapila.
On Tue, Aug 3, 2021 at 11:47 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Aug 2, 2021 at 1:13 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Mon, Aug 2, 2021 at 2:52 PM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote: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.+1 for having these stats in pg_stat_subscription. Do we want to add
two columns (xact_commit: number of transactions successfully applied
in this subscription, xact_rollback: number of transactions that have
been rolled back in this subscription)
Sounds good. We might want to have separate counters for the number of
transactions failed due to an error and transactions rolled back by
stream_abort.
pg_stat_subscription currently shows logical replication worker stats
on the shared memory. I think xact_commit and xact_rollback should
survive beyond the server restarts, so it would be better to be
collected by the stats collector. My skipping transaction patch adds a
hash table of which entry represents a subscription stats. I guess we
can use the hash table so that one subscription stats entry has both
transaction stats and errors.
or do you guys have something else in mind?
Osumi-san was originally concerned that there is no way to grasp the
exact number and size of successful and unsuccessful transactions. The
above idea covers only the number of successful and unsuccessful
transactions but not the size. What do you think, Osumi-san?
Regards,
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
On Mon, Aug 2, 2021 at 1:13 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
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?
We could also include failed_data_size, this will help us to identify
the actual bandwidth consumed by the failed transaction. It will help
the DBA's to understand the network consumption in a better way.
Currently only total transaction and total data will be available but
when there is a failure, the failed transaction data will be sent
repeatedly, if the DBA does not solve the actual cause of failure,
there can be significant consumption of the network due to failure
transaction being sent repeatedly. DBA will not be able to understand
why there is so much network bandwidth consumption. If we give the
failed transaction information, the DBA might not get alarmed in this
case and understand that the network consumption is genuine. Also it
will help monitoring tools to project this value.
Regards,
Vignesh
On Tuesday, August 3, 2021 2:29 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Tue, Aug 3, 2021 at 11:47 AM Amit Kapila <amit.kapila16@gmail.com>
wrote:On Mon, Aug 2, 2021 at 1:13 PM Masahiko Sawada
<sawada.mshk@gmail.com> wrote:
On Mon, Aug 2, 2021 at 2:52 PM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote: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.+1 for having these stats in pg_stat_subscription. Do we want to add
two columns (xact_commit: number of transactions successfully applied
in this subscription, xact_rollback: number of transactions that have
been rolled back in this subscription)Sounds good. We might want to have separate counters for the number of
transactions failed due to an error and transactions rolled back by stream_abort.
Okay. I wanna make those separate as well for this feature.
pg_stat_subscription currently shows logical replication worker stats on the
shared memory. I think xact_commit and xact_rollback should survive beyond
the server restarts, so it would be better to be collected by the stats collector.
My skipping transaction patch adds a hash table of which entry represents a
subscription stats. I guess we can use the hash table so that one subscription
stats entry has both transaction stats and errors.or do you guys have something else in mind?
Osumi-san was originally concerned that there is no way to grasp the exact
number and size of successful and unsuccessful transactions. The above idea
covers only the number of successful and unsuccessful transactions but not the
size. What do you think, Osumi-san?
Yeah, I think tracking sizes of failed transactions and roll-backed transactions
is helpful to identify the genuine network consumption,
as mentioned by Vignesh in another mail.
I'd like to include those also and post a patch for this.
Best Regards,
Takamichi Osumi
On Tue, Aug 3, 2021 at 10:59 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Tue, Aug 3, 2021 at 11:47 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Aug 2, 2021 at 1:13 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Mon, Aug 2, 2021 at 2:52 PM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote: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.+1 for having these stats in pg_stat_subscription. Do we want to add
two columns (xact_commit: number of transactions successfully applied
in this subscription, xact_rollback: number of transactions that have
been rolled back in this subscription)Sounds good. We might want to have separate counters for the number of
transactions failed due to an error and transactions rolled back by
stream_abort.
I was trying to think based on similar counters in pg_stat_database
but if you think there is a value in showing errored and actual
rollbacked transactions separately then we can do that but how do you
think one can make use of it?
--
With Regards,
Amit Kapila.
On Tue, Aug 3, 2021 at 6:11 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Tue, Aug 3, 2021 at 10:59 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Tue, Aug 3, 2021 at 11:47 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Aug 2, 2021 at 1:13 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Mon, Aug 2, 2021 at 2:52 PM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote: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.+1 for having these stats in pg_stat_subscription. Do we want to add
two columns (xact_commit: number of transactions successfully applied
in this subscription, xact_rollback: number of transactions that have
been rolled back in this subscription)Sounds good. We might want to have separate counters for the number of
transactions failed due to an error and transactions rolled back by
stream_abort.I was trying to think based on similar counters in pg_stat_database
but if you think there is a value in showing errored and actual
rollbacked transactions separately then we can do that but how do you
think one can make use of it?
I'm concerned that the value that includes both errored and actual
rollbacked transactions doesn't make sense in practice since the
number of errored transactions can easily get increased once a
conflict happens. IMO the errored transaction doesn’t not necessarily
necessary since the number of (successive) errors that happened on the
subscription is tracked by pg_stat_subscription_errors view. So it
might be enough to have actual rollbacked transactions. If this value
is high, it's likely that many rollbacked transactions are streamed,
unnecessarily consuming network bandwidth. So the user might want to
increase logical_decoding_work_mem to suppress transactions getting
streamed.
Regards,
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
On Wed, Aug 4, 2021 at 6:19 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Tue, Aug 3, 2021 at 6:11 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
I was trying to think based on similar counters in pg_stat_database
but if you think there is a value in showing errored and actual
rollbacked transactions separately then we can do that but how do you
think one can make use of it?I'm concerned that the value that includes both errored and actual
rollbacked transactions doesn't make sense in practice since the
number of errored transactions can easily get increased once a
conflict happens. IMO the errored transaction doesn’t not necessarily
necessary since the number of (successive) errors that happened on the
subscription is tracked by pg_stat_subscription_errors view.
It sounds awkward to display two of the xact (xact_commit,
xact_rollback) counters in one view and then the other similar counter
(xact_error or something like that) in another view. Isn't it better
to display all of them together possibly in pg_stat_subscription? I
guess it might be a bit tricky to track counters for tablesync workers
separately but we can track them in the corresponding subscription.
So it
might be enough to have actual rollbacked transactions. If this value
is high, it's likely that many rollbacked transactions are streamed,
unnecessarily consuming network bandwidth. So the user might want to
increase logical_decoding_work_mem to suppress transactions getting
streamed.
Okay, we might want to probably document such a use case.
--
With Regards,
Amit Kapila.
On Wed, Aug 4, 2021 at 12:17 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Wed, Aug 4, 2021 at 6:19 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Tue, Aug 3, 2021 at 6:11 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
I was trying to think based on similar counters in pg_stat_database
but if you think there is a value in showing errored and actual
rollbacked transactions separately then we can do that but how do you
think one can make use of it?I'm concerned that the value that includes both errored and actual
rollbacked transactions doesn't make sense in practice since the
number of errored transactions can easily get increased once a
conflict happens. IMO the errored transaction doesn’t not necessarily
necessary since the number of (successive) errors that happened on the
subscription is tracked by pg_stat_subscription_errors view.It sounds awkward to display two of the xact (xact_commit,
xact_rollback) counters in one view and then the other similar counter
(xact_error or something like that) in another view. Isn't it better
to display all of them together possibly in pg_stat_subscription? I
guess it might be a bit tricky to track counters for tablesync workers
separately but we can track them in the corresponding subscription.
I meant that the number of rolled back transactions due to an error
seems not to be necessary since pg_stat_subscription_errors has a
similar value. So what I imagined is that we have xact_commit and
xact_rollback (counting only actual rollbacked transaction) counters
in pg_stat_subscription. What do you think of this idea? Or did you
mean the number of errored transactions also has value so should be
included in pg_stat_subscription along with xact_commit and
xact_rollback?
Originally I thought your proposal of having the number of rollback
transactions includes both errored transactions and actual rolled back
transactions so my point was it's better to separate them and include
only the actual rolled-back transaction.
Regards,
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
On Wed, Aug 4, 2021 at 12:35 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Wed, Aug 4, 2021 at 12:17 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Wed, Aug 4, 2021 at 6:19 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Tue, Aug 3, 2021 at 6:11 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
I was trying to think based on similar counters in pg_stat_database
but if you think there is a value in showing errored and actual
rollbacked transactions separately then we can do that but how do you
think one can make use of it?I'm concerned that the value that includes both errored and actual
rollbacked transactions doesn't make sense in practice since the
number of errored transactions can easily get increased once a
conflict happens. IMO the errored transaction doesn’t not necessarily
necessary since the number of (successive) errors that happened on the
subscription is tracked by pg_stat_subscription_errors view.It sounds awkward to display two of the xact (xact_commit,
xact_rollback) counters in one view and then the other similar counter
(xact_error or something like that) in another view. Isn't it better
to display all of them together possibly in pg_stat_subscription? I
guess it might be a bit tricky to track counters for tablesync workers
separately but we can track them in the corresponding subscription.I meant that the number of rolled back transactions due to an error
seems not to be necessary since pg_stat_subscription_errors has a
similar value.
I got that point.
So what I imagined is that we have xact_commit and
xact_rollback (counting only actual rollbacked transaction) counters
in pg_stat_subscription. What do you think of this idea? Or did you
mean the number of errored transactions also has value so should be
included in pg_stat_subscription along with xact_commit and
xact_rollback?
I meant the later one. I think it might be better to display all three
(xact_commit, xact_abort, xact_error) in one place. Earlier it made
sense to me to display it in pg_stat_subscription_errors but not sure
after this proposal. Won't it be better for users to see all the
counters in one view?
Originally I thought your proposal of having the number of rollback
transactions includes both errored transactions and actual rolled back
transactions so my point was it's better to separate them and include
only the actual rolled-back transaction.
I am fine with your proposal to separate the actual rollback and error
transactions counter but I thought it would be better to display them
in one view.
--
With Regards,
Amit Kapila.
Hi
I've made a new patch to extend pg_stat_subscription
as suggested in [1]/messages/by-id/CAA4eK1+tOV-+ssGjj1zq+nAL8a9LfPsxbtyupZGvZ0U7nV0A7g@mail.gmail.com to have columns
xact_commit, xact_error and independent xact_abort mentioned in [2]/messages/by-id/CAA4eK1KMT8biciVqTBoZ9gYV-Gf297JFeNhJaxZNmFrZL8m2jA@mail.gmail.com.
Also, during discussion, we touched a topic if we should
include data sizes for each column above and concluded that it's
better to have ones. Accordingly, I've implemented corresponding
columns to show the data sizes as well.
Note that this patch depends on v12 patchset of apply error callback
provided in [3]/messages/by-id/CAD21AoA5HrhXqwbYLpSobGzV6rWoJmH3-NB9J3YarKDwARBj4w@mail.gmail.com. Therfore, applying the patchset first is required,
if you would like to test my patch.
[1]: /messages/by-id/CAA4eK1+tOV-+ssGjj1zq+nAL8a9LfPsxbtyupZGvZ0U7nV0A7g@mail.gmail.com
[2]: /messages/by-id/CAA4eK1KMT8biciVqTBoZ9gYV-Gf297JFeNhJaxZNmFrZL8m2jA@mail.gmail.com
[3]: /messages/by-id/CAD21AoA5HrhXqwbYLpSobGzV6rWoJmH3-NB9J3YarKDwARBj4w@mail.gmail.com
Best Regards,
Takamichi Osumi
Attachments:
extend_subscription_stats_of_transaction_v02.patchapplication/octet-stream; name=extend_subscription_stats_of_transaction_v02.patchDownload+435-6
Hi
On Thursday, September 2, 2021 11:23 AM I wrote:
I've made a new patch to extend pg_stat_subscription as suggested in [1] to
have columns xact_commit, xact_error and independent xact_abort mentioned
in [2].
Also, during discussion, we touched a topic if we should include data sizes for
each column above and concluded that it's better to have ones. Accordingly,
I've implemented corresponding columns to show the data sizes as well.
I've updated my previous patch of subscriber's stats.
The main change of this version is
the bytes calculation that are exported by pg_stat_subscription.
I prepared a new function which is equivalent to ReorderBufferChangeSize
on the subscriber side to calculate the resource consumption.
It's because this is in line with actual process of the subscriber.
Other changes are minor and cosmetic.
Also, this patch is based on the v12 patch-set of skip xid,
as described in my previous email.
Note that you need to use past commit-id if you want to apply v12 set.
I'll update mine as well when the latest patch-set v13 is shared on hackers.
Best Regards,
Takamichi Osumi
Attachments:
extend_subscription_stats_of_transaction_v03.patchapplication/octet-stream; name=extend_subscription_stats_of_transaction_v03.patchDownload+611-5
Hi
On Thursday, September 9, 2021 5:04 PM I wrote:
Also, this patch is based on the v12 patch-set of skip xid, as described in my
previous email.
Note that you need to use past commit-id if you want to apply v12 set.
I'll update mine as well when the latest patch-set v13 is shared on hackers.
Rebased, using v13 patch-set in [1].
[1 ] - /messages/by-id/CAD21AoBUXM4ODfPFa=h7M6vSKwOKysapUce3tS4rs9mfVMm+cQ@mail.gmail.com
Best Regards,
Takamichi Osumi
