Re: Improve eviction algorithm in ReorderBuffer - Mailing list pgsql-hackers

On Mon, 26 Feb 2024 at 12:33, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Fri, Feb 23, 2024 at 6:24 PM vignesh C <vignesh21@gmail.com> wrote:
> >
> > On Fri, 9 Feb 2024 at 20:51, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > >
> > >
> > > I think this performance regression is not acceptable. In this
> > > workload, one transaction has 10k subtransactions and the logical
> > > decoding becomes quite slow if logical_decoding_work_mem is not big
> > > enough. Therefore, it's a legitimate and common approach to increase
> > > logical_decoding_work_mem to speedup the decoding. However, with thie
> > > patch, the decoding becomes slower than today. It's a bad idea in
> > > general to optimize an extreme case while sacrificing the normal (or
> > > more common) cases.
> > >
> >
> > Since this same function is used by pg_dump sorting TopoSort functions
> > also, we can just verify once if there is no performance impact with
> > large number of objects during dump sorting:
>
> Okay. I've run the pg_dump regression tests with --timer flag (note
> that pg_dump doesn't use indexed binary heap):
>
> master:
> [16:00:25] t/001_basic.pl ................ ok      151 ms ( 0.00 usr
> 0.00 sys +  0.09 cusr  0.06 csys =  0.15 CPU)
> [16:00:25] t/002_pg_dump.pl .............. ok    10157 ms ( 0.23 usr
> 0.01 sys +  1.48 cusr  0.37 csys =  2.09 CPU)
> [16:00:36] t/003_pg_dump_with_server.pl .. ok      504 ms ( 0.00 usr
> 0.01 sys +  0.10 cusr  0.07 csys =  0.18 CPU)
> [16:00:36] t/004_pg_dump_parallel.pl ..... ok     1044 ms ( 0.00 usr
> 0.00 sys +  0.12 cusr  0.08 csys =  0.20 CPU)
> [16:00:37] t/005_pg_dump_filterfile.pl ... ok     2390 ms ( 0.00 usr
> 0.00 sys +  0.34 cusr  0.19 csys =  0.53 CPU)
> [16:00:40] t/010_dump_connstr.pl ......... ok     4813 ms ( 0.01 usr
> 0.00 sys +  2.13 cusr  0.45 csys =  2.59 CPU)
>
> patched:
> [15:59:47] t/001_basic.pl ................ ok      150 ms ( 0.00 usr
> 0.00 sys +  0.08 cusr  0.07 csys =  0.15 CPU)
> [15:59:47] t/002_pg_dump.pl .............. ok    10057 ms ( 0.23 usr
> 0.02 sys +  1.49 cusr  0.36 csys =  2.10 CPU)
> [15:59:57] t/003_pg_dump_with_server.pl .. ok      509 ms ( 0.00 usr
> 0.00 sys +  0.09 cusr  0.08 csys =  0.17 CPU)
> [15:59:58] t/004_pg_dump_parallel.pl ..... ok     1048 ms ( 0.01 usr
> 0.00 sys +  0.11 cusr  0.11 csys =  0.23 CPU)
> [15:59:59] t/005_pg_dump_filterfile.pl ... ok     2398 ms ( 0.00 usr
> 0.00 sys +  0.34 cusr  0.20 csys =  0.54 CPU)
> [16:00:01] t/010_dump_connstr.pl ......... ok     4762 ms ( 0.01 usr
> 0.00 sys +  2.15 cusr  0.42 csys =  2.58 CPU)
>
> There is no noticeable difference between the two results.

Thanks for verifying it, I have also run in my environment and found
no noticeable difference between them:
Head:
[07:29:41] t/001_basic.pl ................ ok      332 ms
[07:29:41] t/002_pg_dump.pl .............. ok    11029 ms
[07:29:52] t/003_pg_dump_with_server.pl .. ok      705 ms
[07:29:53] t/004_pg_dump_parallel.pl ..... ok     1198 ms
[07:29:54] t/005_pg_dump_filterfile.pl ... ok     2822 ms
[07:29:57] t/010_dump_connstr.pl ......... ok     5582 ms

With Patch:
[07:42:16] t/001_basic.pl ................ ok      328 ms
[07:42:17] t/002_pg_dump.pl .............. ok    11044 ms
[07:42:28] t/003_pg_dump_with_server.pl .. ok      719 ms
[07:42:29] t/004_pg_dump_parallel.pl ..... ok     1188 ms
[07:42:30] t/005_pg_dump_filterfile.pl ... ok     2816 ms
[07:42:33] t/010_dump_connstr.pl ......... ok     5609 ms

Regards,
Vignesh