Re: [PATCH] Reuse Workers and Replication Slots during Logical Replication - Mailing list pgsql-hackers

On Fri, Aug 11, 2023 at 7:15 PM Melih Mutlu <m.melihmutlu@gmail.com> wrote:
>
> Peter Smith <smithpb2250@gmail.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,
resultsare 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
eachcommit. ). 
> 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.