Re: Performance degradation on concurrent COPY into a single relation in PG16. - Mailing list pgsql-hackers

From Alvaro Herrera
Subject Re: Performance degradation on concurrent COPY into a single relation in PG16.
Date
Msg-id 20230710132541.3e76bokr4loq4b4c@alvherre.pgsql
Whole thread Raw
In response to Performance degradation on concurrent COPY into a single relation in PG16.  (Masahiko Sawada <sawada.mshk@gmail.com>)
Responses Re: Performance degradation on concurrent COPY into a single relation in PG16.
List pgsql-hackers
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)



pgsql-hackers by date:

Previous
From: Pavel Luzanov
Date:
Subject: Re: Things I don't like about \du's "Attributes" column
Next
From: Sergei Kornilov
Date:
Subject: Re: doc: clarify the limitation for logical replication when REPILICA IDENTITY is FULL