Re: Performance degradation on concurrent COPY into a single relation in PG16.

Hello,

On 2023-Jul-03, Masahiko Sawada wrote:

> While testing PG16, I observed that in PG16 there is a big performance
> degradation in concurrent COPY into a single relation with 2 - 16
> clients in my environment. I've attached a test script that measures
> the execution time of COPYing 5GB data in total to the single relation
> while changing the number of concurrent insertions, in PG16 and PG15.

This item came up in the RMT meeting. Andres, I think this item belongs
to you, because of commit 00d1e02be2.

The regression seems serious enough at low client counts:

> * PG15 (4b15868b69)
> PG15: nclients = 1, execution time = 14.181
> PG15: nclients = 2, execution time = 9.319
> PG15: nclients = 4, execution time = 5.872
> PG15: nclients = 8, execution time = 3.773
> PG15: nclients = 16, execution time = 3.202

> * PG16 (c24e9ef330)
> PG16: nclients = 1, execution time = 17.112
> PG16: nclients = 2, execution time = 14.084
> PG16: nclients = 4, execution time = 27.997
> PG16: nclients = 8, execution time = 10.554
> PG16: nclients = 16, execution time = 7.074

So the fact that the speed has clearly gone up at larger client counts
is not an excuse for not getting it fixed, XFS-specificity
notwithstanding.

> The relevant commit is 00d1e02be2 "hio: Use ExtendBufferedRelBy() to
> extend tables more efficiently". With commit 1cbbee0338 (the previous
> commit of 00d1e02be2), I got a better numbers, it didn't have a better
> scalability, though:
>
> PG16: nclients = 1, execution time = 17.444
> PG16: nclients = 2, execution time = 10.690
> PG16: nclients = 4, execution time = 7.010
> PG16: nclients = 8, execution time = 4.282
> PG16: nclients = 16, execution time = 3.373

Well, these numbers are better, but they still look worse than PG15.
I suppose there are other commits that share blame.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"La virtud es el justo medio entre dos defectos" (Aristóteles)