Re: Transaction commits VS Transaction commits (with parallel) VS query mean time

On Sat, Feb 9, 2019 at 4:07 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Fri, Feb 8, 2019 at 6:55 AM Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
> wrote:
> >
> > On Thu, Feb 7, 2019 at 9:31 PM Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
> wrote:
> >>
> >>
> >> This is because of larger xact_commit value than default configuration.
> With the changed server configuration, that leads to generate more parallel
> workers and every parallel worker operation is treated as an extra commit,
> because of this reason, the total number of commits increased, but the
> overall query performance is decreased.
> >>
> >> Is there any relation of transaction commits to performance?
> >>
> >> Is there any specific reason to consider the parallel worker activity
> also as a transaction commit? Especially in my observation, if we didn't
> consider the parallel worker activity as separate commits, the test doesn't
> show an increase in transaction commits.
> >
> >
> > The following statements shows the increase in the xact_commit value with
> > parallel workers. I can understand that workers updating the seq_scan
> stats
> > as they performed the seq scan.
> >
>

Thanks for your opinion.

> Yeah, that seems okay, however, one can say that for the scan they
> want to consider it as a single scan even if part of the scan is
> accomplished by workers or may be a separate counter for parallel
> workers scan.
>

OK.

> > Is the same applied to parallel worker transaction
> > commits also?
> >
>
> I don't think so. It seems to me that we should consider it as a
> single transaction. Do you want to do the leg work for this and try
> to come up with a patch? On a quick look, I think we might want to
> change AtEOXact_PgStat so that the commits for parallel workers are
> not considered. I think the caller has that information.
>

I try to fix it by adding a check for parallel worker or not and based on it
count them into stats. Patch attached.

With this patch, currently it doesn't count parallel worker transactions,
and
rest of the stats like seqscan and etc are still get counted. IMO they
still
may need to be counted as those stats represent the number of tuples
returned and etc.

Comments?

Regards,
Haribabu Kommi
Fujitsu Australia