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

On Fri, Aug 11, 2023 at 7:15 PM Melih Mutlu <m(dot)melihmutlu(at)gmail(dot)com> wrote:
>
> 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 am not surprised to see that you don't see regression because as per
Vignesh's analysis, this is purely a timing issue where sometimes
after the patch the slot creation can take more time because there is
a constant inflow of transactions on the publisher. I think we are
seeing it because this workload is predominantly just creating and
destroying slots. We can probably improve it later as discussed
earlier by using a single for multiple copies (especially for small
tables) or something like that.

--
With Regards,
Amit Kapila.