Re: [PATCH] Reuse Workers and Replication Slots during Logical Replication

Hi Peter,

Peter Smith <smithpb2250(at)gmail(dot)com>, 11 Ağu 2023 Cum, 01:26 tarihinde şunu
yazdı:

> No, I meant what I wrote there. When I ran the tests the HEAD included
> the v25-0001 refactoring patch, but v26 did not yet exist.
>
> For now, we are only performance testing the first
> "Reuse-Tablesyc-Workers" patch, but not yet including the second patch
> ("Reuse connection when...").
>
> Note that those "Reuse-Tablesyc-Workers" patches v24-0002 and v26-0001
> are equivalent because there are only cosmetic log message differences
> between them.
>

Ok, that's fair.

> So, my testing was with HEAD+v24-0002 (but not including v24-0003).
> Your same testing should be with HEAD+v26-0001 (but not including
> v26-0002).
>

That's actually what I did. I should have been more clear about what I
included in my previous email.With v26-0002, results are noticeably better
anyway.
I just rerun the test again against HEAD, HEAD+v26-0001 and additionally
HEAD+v26-0001+v26-0002 this time, for better comparison.

Here are my results with the same scripts you shared earlier (I obviously
only changed the number of inserts before each commit. ).
Note that this is when synchronous_commit = off.

100 inserts/tx
+-------------+-------+------+------+------+
| | 2w | 4w | 8w | 16w |
+-------------+-------+------+------+------+
| v26-0002 | 10421 | 6472 | 6656 | 6566 |
+-------------+-------+------+------+------+
| improvement | 31% | 12% | 0% | 5% |
+-------------+-------+------+------+------+
| v26-0001 | 14585 | 7386 | 7129 | 7274 |
+-------------+-------+------+------+------+
| improvement | 9% | 5% | 12% | 7% |
+-------------+-------+------+------+------+
| HEAD | 16130 | 7785 | 8147 | 7827 |
+-------------+-------+------+------+------+

1000 inserts/tx
+-------------+-------+------+------+------+
| | 2w | 4w | 8w | 16w |
+-------------+-------+------+------+------+
| v26-0002 | 13796 | 6848 | 5942 | 6315 |
+-------------+-------+------+------+------+
| improvement | 9% | 7% | 10% | 8% |
+-------------+-------+------+------+------+
| v26-0001 | 14685 | 7325 | 6675 | 6719 |
+-------------+-------+------+------+------+
| improvement | 3% | 0% | 0% | 2% |
+-------------+-------+------+------+------+
| HEAD | 15118 | 7354 | 6644 | 6890 |
+-------------+-------+------+------+------+

2000 inserts/tx
+-------------+-------+-------+------+------+
| | 2w | 4w | 8w | 16w |
+-------------+-------+-------+------+------+
| v26-0002 | 22442 | 9944 | 6034 | 5829 |
+-------------+-------+-------+------+------+
| improvement | 5% | 2% | 4% | 10% |
+-------------+-------+-------+------+------+
| v26-0001 | 23632 | 10164 | 6311 | 6480 |
+-------------+-------+-------+------+------+
| improvement | 0% | 0% | 0% | 0% |
+-------------+-------+-------+------+------+
| HEAD | 23667 | 10157 | 6285 | 6470 |
+-------------+-------+-------+------+------+

5000 inserts/tx
+-------------+-------+-------+-------+------+
| | 2w | 4w | 8w | 16w |
+-------------+-------+-------+-------+------+
| v26-0002 | 41443 | 21385 | 10832 | 6146 |
+-------------+-------+-------+-------+------+
| improvement | 0% | 0% | 1% | 16% |
+-------------+-------+-------+-------+------+
| v26-0001 | 41293 | 21226 | 10814 | 6158 |
+-------------+-------+-------+-------+------+
| improvement | 0% | 1% | 1% | 15% |
+-------------+-------+-------+-------+------+
| HEAD | 41503 | 21466 | 10943 | 7292 |
+-------------+-------+-------+-------+------+

Again, I couldn't reproduce the cases where you saw significantly degraded
performance. I wonder if I'm missing something. Did you do anything not
included in the test scripts you shared? Do you think v26-0001 will
perform 84% worse than HEAD, if you try again? I just want to be sure that
it was not a random thing.
Interestingly, I also don't see an improvement in above results as big as
in your results when inserts/tx ratio is smaller. Even though it certainly
is improved in such cases.

Thanks,
--
Melih Mutlu
Microsoft