public inbox for [email protected]
help / color / mirror / Atom feedPostgreSQL and a Catch-22 Issue related to dead rows
17+ messages / 7 participants
[nested] [flat]
* PostgreSQL and a Catch-22 Issue related to dead rows
@ 2024-12-09 11:02 Lars Aksel Opsahl <[email protected]>
2024-12-09 13:18 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows [email protected] <[email protected]>
2024-12-09 13:35 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
2024-12-09 14:25 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Rick Otten <[email protected]>
2024-12-09 14:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus <[email protected]>
0 siblings, 4 replies; 17+ messages in thread
From: Lars Aksel Opsahl @ 2024-12-09 11:02 UTC (permalink / raw)
To: [email protected] <[email protected]>
Hi
When processing multiple simple feature layers through PostGIS Topology to perform overlays and eliminate small areas/slivers, we face a complex workflow. To manage the workload, we split the input into smaller jobs using the Content Balanced Grid<https://gitlab.com/nibioopensource/content_balanced_grid;. These jobs are then executed in parallel using Postgres Execute Parallel<https://gitlab.com/nibioopensource/postgres_execute_parallel;.
In one case, we processed a total of 750 cells, with an overall runtime of 40 hours. However, one specific cell took over 12 hours to complete, most of which was spent on removing small areas by deleting edges in PostGIS Topology. The root cause of this delay is related to removal of dead rows.
By introducing periodic COMMIT statements and VACUUM (FULL) operations, we managed to reduce the processing time for that single cell to approximately 3 hours. However, when we scaled this updated code to use 100 parallel threads, we encountered the expected “LWLock | SubtransControlLock” bottleneck, leading to an overall increase in runtime for all the cells.
One issue is the inability to accurately estimate the size of a job before execution, making it difficult to optimize job splitting in advance. Currently, the only viable solution seems to be implementing a timer for each job. The job would terminate after a predefined time, committing its progress and returning control to the caller. The caller must then trigger new jobs to complete the remaining tasks until done.
If there were a way to remove dead rows without requiring a commit from totally unrelated jobs, it would be much easier. The tables in use are unlogged and I already control the vacuum process from the caller. I can't use temp tables. Such a mechanism would also prevent unrelated long running jobs to cause problems for my jobs. To avoid that problem now, I have to move my jobs to a server not used by others also.
Yes there are very good reason for the way removal for dead rows work now, but is there any chance of adding an option when creating table to disable this behavior for instance for unlogged tables ?
Thanks
Lars
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
@ 2024-12-09 13:18 ` [email protected] <[email protected]>
2024-12-09 13:37 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
3 siblings, 1 reply; 17+ messages in thread
From: [email protected] @ 2024-12-09 13:18 UTC (permalink / raw)
To: Lars Aksel Opsahl <[email protected]>; +Cc: [email protected]
--Apple-Mail-FC87A795-A703-4D06-BB70-C770A47AF233
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">You could always turn off vacuuming at the t=
able level and then resume later<br id=3D"lineBreakAtBeginningOfSignature"><=
div dir=3D"ltr">Sent from my iPhone</div><div dir=3D"ltr"><br><blockquote ty=
pe=3D"cite">On Dec 9, 2024, at 6:03=E2=80=AFAM, Lars Aksel Opsahl <Lars.O=
[email protected]> wrote:<br><br></blockquote></div><blockquote type=3D"cite=
"><div dir=3D"ltr">=EF=BB=BF
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DWindows-12=
52">
<div class=3D"elementToProof" style=3D"margin-top: 1em; margin-bottom: 1em; f=
ont-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helveti=
ca, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hi</div>
<div class=3D"elementToProof" style=3D"margin-top: 1em; margin-bottom: 1em; f=
ont-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helveti=
ca, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
When processing multiple simple feature layers through PostGIS Topology to p=
erform overlays and eliminate small areas/slivers, we face a complex workflo=
w. To manage the workload, we split the input into smaller jobs using the
<a href=3D"https://gitlab.com/nibioopensource/content_balanced_grid"; target=3D=
"_new" id=3D"OWA9a6de625-6b13-f87c-e4db-bab95502c438" class=3D"OWAAutoLink" r=
el=3D"noopener">
Content Balanced Grid</a>. These jobs are then executed in paralle=
l using <a href=3D"https://gitlab.com/nibioopensource/postgres_execute_paral=
lel" target=3D"_new" id=3D"OWA7523cac3-886e-47d5-e75b-a907b57af367" class=3D=
"OWAAutoLink" rel=3D"noopener">
Postgres Execute Parallel</a>.</div>
<div class=3D"elementToProof" style=3D"margin-top: 1em; margin-bottom: 1em; f=
ont-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helveti=
ca, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
In one case, we processed a total of 750 cells, with an overall runtime of 4=
0 hours. However, one specific cell took over 12 hours to complete, most of w=
hich was spent on removing small areas by deleting edges in PostGIS Topology=
. The root cause of this delay
is related to removal of dead rows.</div>
<div class=3D"elementToProof" style=3D"margin-top: 1em; margin-bottom: 1em; f=
ont-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helveti=
ca, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
By introducing periodic <code>COMMIT</code> statements and <code>VACUUM=
(FULL)</code> operations, we managed to reduce the processing time for=
that single cell to approximately 3 hours. However, when we scaled this upd=
ated code to use 100 parallel threads,
we encountered the expected =E2=80=9CLWLock | SubtransControlLock=E2=80=9D b=
ottleneck, leading to an overall increase in runtime for all the cells.</div=
>
<div class=3D"elementToProof" style=3D"margin-top: 1em; margin-bottom: 1em; f=
ont-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helveti=
ca, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
One issue is the inability to accurately estimate the size of a job before e=
xecution, making it difficult to optimize job splitting in advance. Currentl=
y, the only viable solution seems to be implementing a timer for each job. T=
he job would terminate after
a predefined time, committing its progress and returning control to the cal=
ler. The caller must then trigger new jobs to complete the remaining tasks u=
ntil done.</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFon=
t, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; col=
or: rgb(0, 0, 0);">
If there were a way to remove dead rows without requiring a commit from tota=
lly unrelated jobs, it would be much easier. The tables in use are unlogged a=
nd I already control the vacuum process from the caller. I can't use temp ta=
bles. Such a mechanism would
also prevent unrelated long running jobs to cause problems for my jobs. To a=
void that problem now, I have to move my jobs to a server not used by o=
thers also.</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFon=
t, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; col=
or: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFon=
t, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; col=
or: rgb(0, 0, 0);">
Yes there are very good reason for the way removal for dead rows work now, b=
ut is there any chance of adding an option when creating table to disable th=
is behavior for instance for unlogged tables ?</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFon=
t, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; col=
or: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFon=
t, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; col=
or: rgb(0, 0, 0);">
Thanks</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFon=
t, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; col=
or: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFon=
t, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; col=
or: rgb(0, 0, 0);">
Lars</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFon=
t, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; col=
or: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFon=
t, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; col=
or: rgb(0, 0, 0);">
<br>
</div>
</div></blockquote></body></html>=
--Apple-Mail-FC87A795-A703-4D06-BB70-C770A47AF233--
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 13:18 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows [email protected] <[email protected]>
@ 2024-12-09 13:37 ` Lars Aksel Opsahl <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Lars Aksel Opsahl @ 2024-12-09 13:37 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: [email protected] <[email protected]>
--_000_AM7P189MB1028BA1F3FFE08B1517F85CD9D3C2AM7P189MB1028EURP_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
________________________________
From: [email protected] <[email protected]>
Sent: Monday, December 9, 2024 2:18 PM
To: Lars Aksel Opsahl <[email protected]>
Cc: [email protected] <[email protected]=
sql.org>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
You could always turn off vacuuming at the table level and then resume late=
r
Sent from my iPhone
Hi
I am running without auto vacuuming for those tables already.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
@ 2024-12-09 13:35 ` Greg Sabino Mullane <[email protected]>
2024-12-09 13:56 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
3 siblings, 1 reply; 17+ messages in thread
From: Greg Sabino Mullane @ 2024-12-09 13:35 UTC (permalink / raw)
To: Lars Aksel Opsahl <[email protected]>; +Cc: [email protected] <[email protected]>
On Mon, Dec 9, 2024 at 6:03 AM Lars Aksel Opsahl <[email protected]>
wrote:
> In one case, we processed a total of 750 cells, with an overall runtime of
> 40 hours. However, one specific cell took over 12 hours to complete, most
> of which was spent on removing small areas by deleting edges in PostGIS
> Topology. The root cause of this delay is related to removal of dead rows.
>
Can you please expand exactly what you mean by "removal of dead rows" here,
and what the exact issue you are facing is?
> By introducing periodic COMMIT statements and VACUUM (FULL) operations
>
Vacfull is a pretty rough solution, and almost always not the correct tool
for the job, IMHO.
Yes there are very good reason for the way removal for dead rows work now,
> but is there any chance of adding an option when creating table to disable
> this behavior for instance for unlogged tables ?
>
It's still not clear exactly what the ask is here, but there is little
chance we would design an alternative MVCC system just to accommodate this
use case.
Cheers,
Greg
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 13:35 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
@ 2024-12-09 13:56 ` Lars Aksel Opsahl <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Lars Aksel Opsahl @ 2024-12-09 13:56 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]>
________________________________
From: Greg Sabino Mullane <[email protected]>
Sent: Monday, December 9, 2024 2:35 PM
To: Lars Aksel Opsahl <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
On Mon, Dec 9, 2024 at 6:03 AM Lars Aksel Opsahl <[email protected]<mailto:[email protected]>> wrote:
In one case, we processed a total of 750 cells, with an overall runtime of 40 hours. However, one specific cell took over 12 hours to complete, most of which was spent on removing small areas by deleting edges in PostGIS Topology. The root cause of this delay is related to removal of dead rows.
Can you please expand exactly what you mean by "removal of dead rows" here, and what the exact issue you are facing is?
Hi
In the logs I have this 'tuples: 207 removed, 7983843 remain, 7954046 are dead but not yet removable removable cutoff: 1189559933, which was 2662 XIDs old when operation ended '
There are some more info here https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/issues/97.
When the oldest job commits we are able to remove this dead rows.
By introducing periodic COMMIT statements and VACUUM (FULL) operations
Vacfull is a pretty rough solution, and almost always not the correct tool for the job, IMHO.
The problems is that I have to remove those dead rows to get an OK performance.
It's still not clear exactly what the ask is here, but there is little chance we would design an alternative MVCC system just to accommodate this use case.
The problem related dead remove is a quite common issue/problem for Postgresql which is also very nicely described here https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/
by Laurenz also.
Thanks
Lars
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
@ 2024-12-09 14:25 ` Rick Otten <[email protected]>
2024-12-10 05:19 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
3 siblings, 1 reply; 17+ messages in thread
From: Rick Otten @ 2024-12-09 14:25 UTC (permalink / raw)
To: Lars Aksel Opsahl <[email protected]>; +Cc: [email protected] <[email protected]>
>
>
> Yes there are very good reason for the way removal for dead rows work now,
> but is there any chance of adding an option when creating table to disable
> this behavior for instance for unlogged tables ?
>
>
>
Are you saying your job is I/O bound (not memory or cpu). And that you can
only improve I/O performance by committing more frequently because the
commit removes dead tuples which you have no other means to clear? Is
your WAL already on your fastest disk?
All of your parallel jobs are operating on the same set of rows? So
partitioning the table wouldn't help?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 14:25 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Rick Otten <[email protected]>
@ 2024-12-10 05:19 ` Lars Aksel Opsahl <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Lars Aksel Opsahl @ 2024-12-10 05:19 UTC (permalink / raw)
To: Rick Otten <[email protected]>; +Cc: [email protected] <[email protected]>
From: Rick Otten <[email protected]>
Sent: Monday, December 9, 2024 3:25 PM
To: Lars Aksel Opsahl <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
Yes there are very good reason for the way removal for dead rows work now, but is there any chance of adding an option when creating table to disable this behavior for instance for unlogged tables ?
Are you saying your job is I/O bound (not memory or cpu). And that you can only improve I/O performance by committing more frequently because the commit removes dead tuples which you have no other means to clear? Is your WAL already on your fastest disk?
All of your parallel jobs are operating on the same set of rows? So partitioning the table wouldn't help?
The problem is not IO or CPU bound, or related to WAL files, but that "dead rows" are impacting the sql queries. About partitioning at this stage, the data are split in about 750 different topology structures. We have many workers working in parallel on these different structures but only one worker at the same on the same structure.
Thanks
Lars
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
@ 2024-12-09 14:32 ` Christophe Pettus <[email protected]>
2024-12-09 16:07 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Tom Lane <[email protected]>
3 siblings, 1 reply; 17+ messages in thread
From: Christophe Pettus @ 2024-12-09 14:32 UTC (permalink / raw)
To: Lars Aksel Opsahl <[email protected]>; +Cc: [email protected] <[email protected]>
> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl <[email protected]> wrote:
> If there were a way to remove dead rows without requiring a commit from totally unrelated jobs, it would be much easier.
Without seeing into the future, PostgreSQL doesn't know if a particular open transaction is "totally unrelated" to any other open transaction. Any open transaction can potentially see rows that have been deleted or updated since it began, and without knowing what operations are coming, it doesn't know if it is safe to remove them.
(Strictly speaking, the rows you are describing are not "dead," in that they are still visible to some transaction.)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 14:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus <[email protected]>
@ 2024-12-09 16:07 ` Tom Lane <[email protected]>
2024-12-10 05:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-10 08:55 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Tom Lane @ 2024-12-09 16:07 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: Lars Aksel Opsahl <[email protected]>; [email protected] <[email protected]>
Christophe Pettus <[email protected]> writes:
>> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl <[email protected]> wrote:
>> If there were a way to remove dead rows without requiring a commit from totally unrelated jobs, it would be much easier.
> (Strictly speaking, the rows you are describing are not "dead," in that they are still visible to some transaction.)
We do only very coarse-grained analysis of whether a row is "dead".
In principle, if vacuum had access to all the live snapshots of
all sessions, it could realize that a row really is dead even though
it's later than the current global xmin horizon. But discovering that
would be quite difficult and therefore expensive. Notably, sessions
would have to expose far more of their snapshot state than they do
today, and there would have to be interlocks to allow other sessions
to inspect that state safely, and that'd probably put us into much the
same sort of too-many-lock-conflicts problem that the OP has already.
I don't think there's any free lunch here. Maybe there's some
other compromise between amount-of-state-exposed versus
dead-row-discoverability, but finding a better way would take
a great deal of creative effort and testing.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 14:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus <[email protected]>
2024-12-09 16:07 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Tom Lane <[email protected]>
@ 2024-12-10 05:32 ` Lars Aksel Opsahl <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Lars Aksel Opsahl @ 2024-12-10 05:32 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; +Cc: [email protected] <[email protected]>
________________________________
From: Tom Lane <[email protected]>
Sent: Monday, December 9, 2024 5:07 PM
To: Christophe Pettus <[email protected]>
Cc: Lars Aksel Opsahl <[email protected]>; [email protected] <[email protected]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
Christophe Pettus <[email protected]> writes:
>> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl <[email protected]> wrote:
>> If there were a way to remove dead rows without requiring a commit from totally unrelated jobs, it would be much easier.
> (Strictly speaking, the rows you are describing are not "dead," in that they are still visible to some transaction.)
We do only very coarse-grained analysis of whether a row is "dead".
In principle, if vacuum had access to all the live snapshots of
all sessions, it could realize that a row really is dead even though
it's later than the current global xmin horizon. But discovering that
would be quite difficult and therefore expensive. Notably, sessions
would have to expose far more of their snapshot state than they do
today, and there would have to be interlocks to allow other sessions
to inspect that state safely, and that'd probably put us into much the
same sort of too-many-lock-conflicts problem that the OP has already.
I don't think there's any free lunch here. Maybe there's some
other compromise between amount-of-state-exposed versus
dead-row-discoverability, but finding a better way would take
a great deal of creative effort and testing.
regards, tom lane
Hi
Thanks for the clarifications , back to more divide and conquer work then.
Lars
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 14:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus <[email protected]>
2024-12-09 16:07 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Tom Lane <[email protected]>
@ 2024-12-10 08:55 ` Lars Aksel Opsahl <[email protected]>
2024-12-10 13:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Lars Aksel Opsahl @ 2024-12-10 08:55 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; +Cc: [email protected] <[email protected]>
________________________________
From: Tom Lane <[email protected]>
Sent: Monday, December 9, 2024 5:07 PM
We do only very coarse-grained analysis of whether a row is "dead".
In principle, if vacuum had access to all the live snapshots of
all sessions, it could realize that a row really is dead even though
it's later than the current global xmin horizon. But discovering that
would be quite difficult and therefore expensive. Notably, sessions
would have to expose far more of their snapshot state than they do
today, and there would have to be interlocks to allow other sessions
to inspect that state safely, and that'd probably put us into much the
same sort of too-many-lock-conflicts problem that the OP has already.
I don't think there's any free lunch here. Maybe there's some
other compromise between amount-of-state-exposed versus
dead-row-discoverability, but finding a better way would take
a great deal of creative effort and testing.
regards, tom lane
Hi again.
On my bike to work this morning I was thinking more about this.
When the application run vacuum I find this "7954046 are dead but not yet removable removable" so the job knows what rows that are involved.
Is it difficult to add parameter like force-dead-rows-removal that we send to the vacuum job that will remove this rows like this ?
Just to test what happens and also how it affects performance.
The tables I work on are unlogged , vacuum command is controlled by the application. If this option is only applicable on unlogged tables which are unsafe anyway and from command line , maybe that can help to reduce complexity and side effects.
I am now testing on PostgreSQL "16.4 (Ubuntu 16.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit"
Thanks.
Lars
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 14:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus <[email protected]>
2024-12-09 16:07 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Tom Lane <[email protected]>
2024-12-10 08:55 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
@ 2024-12-10 13:03 ` Greg Sabino Mullane <[email protected]>
2024-12-10 14:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Greg Sabino Mullane @ 2024-12-10 13:03 UTC (permalink / raw)
To: Lars Aksel Opsahl <[email protected]>; +Cc: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; [email protected] <[email protected]>
On Tue, Dec 10, 2024 at 3:55 AM Lars Aksel Opsahl <[email protected]>
wrote:
> Is it difficult to add parameter like force-dead-rows-removal that we send
> to the vacuum job that will remove this rows like this ?
>
> I'm still not sure what the ask here is - complete literal removal of the
dead rows? That's not how PG works. I'm wondering if we are not in an XY
problem. Your queries are slow, and you think it's because of
autovacuum's output re dead rows. But let's take a step back and look at
the actual queries being run that are slowing down. Perhaps there are other
solutions: less indexing, more freezing, smarter updates, different
partitioning, tweaking fillfactor, etc. etc. There are lots of things we
can try that will be orders of magnitude simpler than trying to redesign
MVCC/vacuuming. :)
Cheers,
Greg
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 14:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus <[email protected]>
2024-12-09 16:07 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Tom Lane <[email protected]>
2024-12-10 08:55 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-10 13:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
@ 2024-12-10 14:03 ` Lars Aksel Opsahl <[email protected]>
2024-12-10 15:31 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Lars Aksel Opsahl @ 2024-12-10 14:03 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; [email protected] <[email protected]>
________________________________
Sent: Tuesday, December 10, 2024 2:03 PM
To: Lars Aksel Opsahl <[email protected]>
Cc: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; [email protected] <[email protected]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
On Tue, Dec 10, 2024 at 3:55 AM Lars Aksel Opsahl <[email protected]<mailto:[email protected]>> wrote:
Is it difficult to add parameter like force-dead-rows-removal that we send to the vacuum job that will remove this rows like this ?
I'm still not sure what the ask here is - complete literal removal of the dead rows? That's not how PG works. I'm wondering if we are not in an XY problem. Your queries are slow, and you think it's because of autovacuum's output re dead rows. But let's take a step back and look at the actual queries being run that are slowing down. Perhaps there are other solutions: less indexing, more freezing, smarter updates, different partitioning, tweaking fillfactor, etc. etc. There are lots of things we can try that will be orders of magnitude simpler than trying to redesign MVCC/vacuuming. :)
Hi
Yes we can solve this with more divide and conquer working but it's complicating thing a lot. I basically do partitioning now by splitting Postgis Topology up many hundreds and sometime many thousands off different topologies, but there is a limit to how much I can split up and later merge because this also has a cost.
So the main issue seems to be related to dead rows. We have samples of queries like this 'SELECT node_id,geom FROM node WHERE containing_face = 0;' going from 2008.947 ms to 0.072 ms, when we did a commit on an unrelated job so xmin did not block removal off dead rows. Here is also some more info https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/issues/67 .Doing anlayze just before the query does not help, the only thing that seems help is get read off rows marked as "DETAIL: 195929 dead row versions cannot be removed yet, oldest xmin: 3475136501 "
Seen from the outside I am not asking for redesign 🙂just a parameter to test forced removal of dead rows when working on unnlogged tables and running vacuum from command line, but may seem to be more complicated than I was hopeing.
Thanks.
Lars
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 14:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus <[email protected]>
2024-12-09 16:07 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Tom Lane <[email protected]>
2024-12-10 08:55 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-10 13:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
2024-12-10 14:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
@ 2024-12-10 15:31 ` Greg Sabino Mullane <[email protected]>
2024-12-10 20:26 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Hannu Krosing <[email protected]>
2024-12-11 13:29 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Greg Sabino Mullane @ 2024-12-10 15:31 UTC (permalink / raw)
To: Lars Aksel Opsahl <[email protected]>; +Cc: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; [email protected] <[email protected]>
Thanks for that link; seeing actual queries is a big help. One thing to try
is to get some index-only scans to run. Regular indexes need to consult the
heap (main table) for visibility information, and a bloated table can make
that consultation expensive.
For example, an index like this should work to trigger an index-only scan:
create index gregtest on node(geom) include(node_id) where
containing_face=0;
For those not following that link, the query is:
SELECT node_id, geom FROM node WHERE containing_face IN (0)
AND geom &&
'0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40'::geometry
Or if containing_face is not always 0, a more generic variant:
create index gregtest on node(geom, containing_face) include (node_id);
What is the nature of the updates that are causing that many dead rows in
the first place?
Cheers,
Greg
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 14:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus <[email protected]>
2024-12-09 16:07 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Tom Lane <[email protected]>
2024-12-10 08:55 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-10 13:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
2024-12-10 14:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-10 15:31 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
@ 2024-12-10 20:26 ` Hannu Krosing <[email protected]>
2024-12-11 07:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Hannu Krosing @ 2024-12-10 20:26 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: Lars Aksel Opsahl <[email protected]>; Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; [email protected] <[email protected]>
If there are unremovable rows it usually also means that index-only scan
degrades to index-scan-with-visibility-checks-in-tables.
I think the ask is to be able to remove the recently dead rows that are not
visible in any current snapshot and can never become visible to any future
snapshot,
Something that Tom described as currently not easily doable above.
Maybe we could figure out a way for long-running transactions to upload
their snapshot set to some meta-vacuum process which can use it to
determine which rows fall into that category. In this way the change would
affect only the long-running transactions and if we do it onl maybe once a
minute it should not be too heavy overhead even for these transactions.
On Tue, Dec 10, 2024 at 4:32 PM Greg Sabino Mullane <[email protected]>
wrote:
> Thanks for that link; seeing actual queries is a big help. One thing to
> try is to get some index-only scans to run. Regular indexes need to consult
> the heap (main table) for visibility information, and a bloated table can
> make that consultation expensive.
>
> For example, an index like this should work to trigger an index-only scan:
>
> create index gregtest on node(geom) include(node_id) where
> containing_face=0;
>
> For those not following that link, the query is:
>
> SELECT node_id, geom FROM node WHERE containing_face IN (0)
> AND geom &&
> '0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40'::geometry
>
> Or if containing_face is not always 0, a more generic variant:
>
> create index gregtest on node(geom, containing_face) include (node_id);
>
> What is the nature of the updates that are causing that many dead rows in
> the first place?
>
> Cheers,
> Greg
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 14:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus <[email protected]>
2024-12-09 16:07 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Tom Lane <[email protected]>
2024-12-10 08:55 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-10 13:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
2024-12-10 14:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-10 15:31 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
2024-12-10 20:26 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Hannu Krosing <[email protected]>
@ 2024-12-11 07:32 ` Lars Aksel Opsahl <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Lars Aksel Opsahl @ 2024-12-11 07:32 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; [email protected] <[email protected]>
________________________________
From: Hannu Krosing <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: Lars Aksel Opsahl <[email protected]>; Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; [email protected] <[email protected]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
If there are unremovable rows it usually also means that index-only scan degrades to index-scan-with-visibility-checks-in-tables.
I think the ask is to be able to remove the recently dead rows that are not visible in any current snapshot and can never become visible to any future snapshot,
Something that Tom described as currently not easily doable above.
Maybe we could figure out a way for long-running transactions to upload their snapshot set to some meta-vacuum process which can use it to determine which rows fall into that category. In this way the change would affect only the long-running transactions and if we do it onl maybe once a minute it should not be too heavy overhead even for these transactions.
Hi
This is tested but only works when the load is very low.
To fix the problem with xmin we use this https://gitlab.com/nibioopensource/postgres_execute_parallel to start the jobs, but the control is returned to the caller at given intervals, and the caller also does a commit and gets a new snapshot and xmin is updated. If all jobs done are done the caller may start a new batch of jobs.
One of the 750 jobs takes for hours to remove (https://postgis.net/docs/ST_RemEdgeNewFace.html) almost 30.000 edges. I do this in a loop takes around 500 edges in every loop. If the time used is to long I forced a commit in this loop in addition to the analyze I do. Adding the commit really helps on execution time but only when running a very few jobs in parallel. With this new commit I could see that dead rows were removed.
The issue with the new commit in the loop where I remove edges is that it becomes problem when many treads run parallel related to subtransactions per transaction
like discussed here https://postgrespro.com/list/thread-id/2478202 and many other threads. Almost like a catch 22, fixing the dead rows problem is causing a new bigger problem.
So to solve problems with subtransactions per transaction I can make new joblist for removing edges also, but that makes things more complicated.
Thanks
Lars.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and a Catch-22 Issue related to dead rows
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 14:32 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus <[email protected]>
2024-12-09 16:07 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Tom Lane <[email protected]>
2024-12-10 08:55 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-10 13:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
2024-12-10 14:03 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-10 15:31 ` Re: PostgreSQL and a Catch-22 Issue related to dead rows Greg Sabino Mullane <[email protected]>
@ 2024-12-11 13:29 ` Lars Aksel Opsahl <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Lars Aksel Opsahl @ 2024-12-11 13:29 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; [email protected] <[email protected]>
________________________________
Sent: Tuesday, December 10, 2024 4:31 PM
To: Lars Aksel Opsahl <[email protected]>
Cc: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; [email protected] <[email protected]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
Thanks for that link; seeing actual queries is a big help. One thing to try is to get some index-only scans to run. Regular indexes need to consult the heap (main table) for visibility information, and a bloated table can make that consultation expensive.
For example, an index like this should work to trigger an index-only scan:
create index gregtest on node(geom) include(node_id) where containing_face=0;
For those not following that link, the query is:
SELECT node_id, geom FROM node WHERE containing_face IN (0)
AND geom && '0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40'::geometry
Or if containing_face is not always 0, a more generic variant:
create index gregtest on node(geom, containing_face) include (node_id);
What is the nature of the updates that are causing that many dead rows in the first place?
Cheers,
Greg
Hi
Thank's I did not know about the include option, I will have this mind when working on this.
I have done some simple testing with it now, but I have not seen any big changes yet, but issue in case 97 is not the same as in issue 67. What problems cases we end up with, will vary depending on the simple feature layers we run overlay between and for instance how many surfaces we need remove from the topology layer before we produce the final result.
In this case based on pg_stat_statements in seems like a lot off the time is used on updates when removing edges.
Why we get all this dead rows are related Postgis Topology database structure and input data. In some cases we get input data with verry many almost parallel lines which are close and that will cause a lot off edges be removed.
Lars
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2024-12-11 13:29 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-09 11:02 PostgreSQL and a Catch-22 Issue related to dead rows Lars Aksel Opsahl <[email protected]>
2024-12-09 13:18 ` [email protected] <[email protected]>
2024-12-09 13:37 ` Lars Aksel Opsahl <[email protected]>
2024-12-09 13:35 ` Greg Sabino Mullane <[email protected]>
2024-12-09 13:56 ` Lars Aksel Opsahl <[email protected]>
2024-12-09 14:25 ` Rick Otten <[email protected]>
2024-12-10 05:19 ` Lars Aksel Opsahl <[email protected]>
2024-12-09 14:32 ` Christophe Pettus <[email protected]>
2024-12-09 16:07 ` Tom Lane <[email protected]>
2024-12-10 05:32 ` Lars Aksel Opsahl <[email protected]>
2024-12-10 08:55 ` Lars Aksel Opsahl <[email protected]>
2024-12-10 13:03 ` Greg Sabino Mullane <[email protected]>
2024-12-10 14:03 ` Lars Aksel Opsahl <[email protected]>
2024-12-10 15:31 ` Greg Sabino Mullane <[email protected]>
2024-12-10 20:26 ` Hannu Krosing <[email protected]>
2024-12-11 07:32 ` Lars Aksel Opsahl <[email protected]>
2024-12-11 13:29 ` Lars Aksel Opsahl <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox