public inbox for [email protected]
help / color / mirror / Atom feedDoes cancelling autovacuum make you lose all the work it did?
6+ messages / 4 participants
[nested] [flat]
* Does cancelling autovacuum make you lose all the work it did?
@ 2020-06-12 10:18 Greg Rychlewski (LCL) <[email protected]>
2020-06-12 17:40 ` RE: Does cancelling autovacuum make you lose all the work it did? Stephen Froehlich <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Greg Rychlewski (LCL) @ 2020-06-12 10:18 UTC (permalink / raw)
To: pgsql-novice
I have a long running autovacuum (to prevent wraparound) that has been doing on for 6 days. I’m considering changing the autovacuum settings to be more aggressive, cancelling the current one, and then let it restart. Would the tuples its cleaned be lost if I did this?
This email message is confidential, may be legally privileged and is intended for the exclusive use of the addressee. If you received this message in error or are not the intended recipient, you should destroy the email message and any attachments or copies, and you are prohibited from retaining, distributing, disclosing or using any information contained. Please inform us of the delivery error by return email. Thank you for your cooperation.
Le présent message électronique est confidentiel et peut être couvert par le secret professionnel. Il est à l’usage exclusif du destinataire. Si vous recevez ce message par erreur ou si vous n’en êtes pas le destinataire prévu, vous devez détruire le message et toute pièce jointe ou copie et vous êtes tenu de ne pas conserver, distribuer, divulguer ni utiliser tout renseignement qu’il contient. Veuillez nous informer de toute erreur d’envoi en répondant à ce message. Merci de votre collaboration.
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Does cancelling autovacuum make you lose all the work it did?
2020-06-12 10:18 Does cancelling autovacuum make you lose all the work it did? Greg Rychlewski (LCL) <[email protected]>
@ 2020-06-12 17:40 ` Stephen Froehlich <[email protected]>
2020-06-12 17:56 ` Re: Does cancelling autovacuum make you lose all the work it did? David G. Johnston <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Stephen Froehlich @ 2020-06-12 17:40 UTC (permalink / raw)
To: Greg Rychlewski (LCL) <[email protected]>; pgsql-novice
Hi Greg,
No autovacuum or vacuum causes no user-visible or breaking changes of any kind … it does change the performance of the database backend, but that’s all.
--Stephen
From: Greg Rychlewski (LCL) <[email protected]>
Sent: Friday, June 12, 2020 04:19
To: [email protected]
Subject: Does cancelling autovacuum make you lose all the work it did?
I have a long running autovacuum (to prevent wraparound) that has been doing on for 6 days. I’m considering changing the autovacuum settings to be more aggressive, cancelling the current one, and then let it restart. Would the tuples its cleaned be lost if I did this?
This email message is confidential, may be legally privileged and is intended for the exclusive use of the addressee. If you received this message in error or are not the intended recipient, you should destroy the email message and any attachments or copies, and you are prohibited from retaining, distributing, disclosing or using any information contained. Please inform us of the delivery error by return email. Thank you for your cooperation.
Le présent message électronique est confidentiel et peut être couvert par le secret professionnel. Il est à l’usage exclusif du destinataire. Si vous recevez ce message par erreur ou si vous n’en êtes pas le destinataire prévu, vous devez détruire le message et toute pièce jointe ou copie et vous êtes tenu de ne pas conserver, distribuer, divulguer ni utiliser tout renseignement qu’il contient. Veuillez nous informer de toute erreur d’envoi en répondant à ce message. Merci de votre collaboration.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Does cancelling autovacuum make you lose all the work it did?
2020-06-12 10:18 Does cancelling autovacuum make you lose all the work it did? Greg Rychlewski (LCL) <[email protected]>
2020-06-12 17:40 ` RE: Does cancelling autovacuum make you lose all the work it did? Stephen Froehlich <[email protected]>
@ 2020-06-12 17:56 ` David G. Johnston <[email protected]>
2020-06-12 20:33 ` Re: Does cancelling autovacuum make you lose all the work it did? Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2020-06-12 17:56 UTC (permalink / raw)
To: Stephen Froehlich <[email protected]>; +Cc: Greg Rychlewski (LCL) <[email protected]>; pgsql-novice
The custom here is to inline or bottom-post.
On Fri, Jun 12, 2020 at 10:41 AM Stephen Froehlich <
[email protected]> wrote:
> No autovacuum or vacuum causes no user-visible or breaking changes of any
> kind … it does change the performance of the database backend, but that’s
> all.
>
Um...doesn't really answer the question and besides hitting the wrap-around
limit causes the server to not start in multi-user mode which is surely a
user-visible effect. Performance is a user-visible effect too - the only
thing it better not impact is the correctness of the results.
To answer the original question: vacuum's effects are basically immediate
so work is not lost if it gets cancelled.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Does cancelling autovacuum make you lose all the work it did?
2020-06-12 10:18 Does cancelling autovacuum make you lose all the work it did? Greg Rychlewski (LCL) <[email protected]>
2020-06-12 17:40 ` RE: Does cancelling autovacuum make you lose all the work it did? Stephen Froehlich <[email protected]>
2020-06-12 17:56 ` Re: Does cancelling autovacuum make you lose all the work it did? David G. Johnston <[email protected]>
@ 2020-06-12 20:33 ` Tom Lane <[email protected]>
2020-06-13 00:32 ` Re: [EXT] Re: Does cancelling autovacuum make you lose all the work it did? Greg Rychlewski (LCL) <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2020-06-12 20:33 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Stephen Froehlich <[email protected]>; Greg Rychlewski (LCL) <[email protected]>; pgsql-novice
"David G. Johnston" <[email protected]> writes:
> To answer the original question: vacuum's effects are basically immediate
> so work is not lost if it gets cancelled.
Mmm ... not entirely true. vacuum does a cycle like this:
1. scan table looking for removable rows; remember their TIDs
2. when memory for said TIDs is full, make a pass over the table's
indexes to find and delete index entries pointing at those TIDs
3. then, go back to the heap pages and actually remove the rows
4. if we didn't reach the end of the table yet, return to step 1.
If we get cancelled at any point, the data structure is still
consistent; there may be heap rows that don't have a full set
of index entries, but that doesn't matter because nobody cares
about finding those entries from the indexes. However, when
you redo the vacuum, it'll have to redo some of the work from
the current cycle, depending on exactly how far along it was
when you cancelled it. At the very least it's going to be
repeating some heap-scanning work, though that's the cheapest
part of this because it's basically read-only. The most
expensive parts are the actual index and heap tuple removals,
and any one of those won't need to be done over.
The size of the cycles depends on maintenance_work_mem; so if
you have that set really large, you can lose more time than
if it's not so large.
TBH, though, are you sure the vacuum is doing work and not
just blocked waiting for somebody else? An autovac can be
blocked indefinitely by some other query holding a table-level
or page-level lock. Check its state in pg_stat_activity.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [EXT] Re: Does cancelling autovacuum make you lose all the work it did?
2020-06-12 10:18 Does cancelling autovacuum make you lose all the work it did? Greg Rychlewski (LCL) <[email protected]>
2020-06-12 17:40 ` RE: Does cancelling autovacuum make you lose all the work it did? Stephen Froehlich <[email protected]>
2020-06-12 17:56 ` Re: Does cancelling autovacuum make you lose all the work it did? David G. Johnston <[email protected]>
2020-06-12 20:33 ` Re: Does cancelling autovacuum make you lose all the work it did? Tom Lane <[email protected]>
@ 2020-06-13 00:32 ` Greg Rychlewski (LCL) <[email protected]>
2020-06-13 03:18 ` Re: [EXT] Re: Does cancelling autovacuum make you lose all the work it did? Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Greg Rychlewski (LCL) @ 2020-06-13 00:32 UTC (permalink / raw)
To: Tom Lane <[email protected]>; David G. Johnston <[email protected]>; +Cc: Stephen Froehlich <[email protected]>; pgsql-novice
Hi Tom,
Thanks for your reply. Regarding it being blocked, I don't think it is but I could be wrong. Both wait_event and wait_event_type are null. One thing I noticed though is that n_dead_tup in pg_stat_all_tables either stays the same or goes up. Should this be going down during the autovacuum?
On 2020-06-12, 4:33 PM, "Tom Lane" <[email protected]> wrote:
CAUTION: External email. Do not click links or open attachments unless you recognize the sender and know the content is safe.
"David G. Johnston" <[email protected]> writes:
> To answer the original question: vacuum's effects are basically immediate
> so work is not lost if it gets cancelled.
Mmm ... not entirely true. vacuum does a cycle like this:
1. scan table looking for removable rows; remember their TIDs
2. when memory for said TIDs is full, make a pass over the table's
indexes to find and delete index entries pointing at those TIDs
3. then, go back to the heap pages and actually remove the rows
4. if we didn't reach the end of the table yet, return to step 1.
If we get cancelled at any point, the data structure is still
consistent; there may be heap rows that don't have a full set
of index entries, but that doesn't matter because nobody cares
about finding those entries from the indexes. However, when
you redo the vacuum, it'll have to redo some of the work from
the current cycle, depending on exactly how far along it was
when you cancelled it. At the very least it's going to be
repeating some heap-scanning work, though that's the cheapest
part of this because it's basically read-only. The most
expensive parts are the actual index and heap tuple removals,
and any one of those won't need to be done over.
The size of the cycles depends on maintenance_work_mem; so if
you have that set really large, you can lose more time than
if it's not so large.
TBH, though, are you sure the vacuum is doing work and not
just blocked waiting for somebody else? An autovac can be
blocked indefinitely by some other query holding a table-level
or page-level lock. Check its state in pg_stat_activity.
regards, tom lane
This email message is confidential, may be legally privileged and is intended for the exclusive use of the addressee. If you received this message in error or are not the intended recipient, you should destroy the email message and any attachments or copies, and you are prohibited from retaining, distributing, disclosing or using any information contained. Please inform us of the delivery error by return email. Thank you for your cooperation.
Le présent message électronique est confidentiel et peut être couvert par le secret professionnel. Il est à l’usage exclusif du destinataire. Si vous recevez ce message par erreur ou si vous n’en êtes pas le destinataire prévu, vous devez détruire le message et toute pièce jointe ou copie et vous êtes tenu de ne pas conserver, distribuer, divulguer ni utiliser tout renseignement qu’il contient. Veuillez nous informer de toute erreur d’envoi en répondant à ce message. Merci de votre collaboration.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [EXT] Re: Does cancelling autovacuum make you lose all the work it did?
2020-06-12 10:18 Does cancelling autovacuum make you lose all the work it did? Greg Rychlewski (LCL) <[email protected]>
2020-06-12 17:40 ` RE: Does cancelling autovacuum make you lose all the work it did? Stephen Froehlich <[email protected]>
2020-06-12 17:56 ` Re: Does cancelling autovacuum make you lose all the work it did? David G. Johnston <[email protected]>
2020-06-12 20:33 ` Re: Does cancelling autovacuum make you lose all the work it did? Tom Lane <[email protected]>
2020-06-13 00:32 ` Re: [EXT] Re: Does cancelling autovacuum make you lose all the work it did? Greg Rychlewski (LCL) <[email protected]>
@ 2020-06-13 03:18 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2020-06-13 03:18 UTC (permalink / raw)
To: Greg Rychlewski (LCL) <[email protected]>; +Cc: David G. Johnston <[email protected]>; Stephen Froehlich <[email protected]>; pgsql-novice
"Greg Rychlewski (LCL)" <[email protected]> writes:
> Thanks for your reply. Regarding it being blocked, I don't think it is but I could be wrong. Both wait_event and wait_event_type are null. One thing I noticed though is that n_dead_tup in pg_stat_all_tables either stays the same or goes up. Should this be going down during the autovacuum?
No ... ongoing transactions could be creating new dead rows. IIRC,
a vacuum won't report its removal of dead rows to the stats collector
till the very end, so what I'd expect is a big drop when it finishes.
(I am not sure offhand what happens to those stats if you cancel the
vacuum partway through --- it might report nothing, causing the
stats to be way off until the next successful vacuum.)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-06-13 03:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-12 10:18 Does cancelling autovacuum make you lose all the work it did? Greg Rychlewski (LCL) <[email protected]>
2020-06-12 17:40 ` Stephen Froehlich <[email protected]>
2020-06-12 17:56 ` David G. Johnston <[email protected]>
2020-06-12 20:33 ` Tom Lane <[email protected]>
2020-06-13 00:32 ` Greg Rychlewski (LCL) <[email protected]>
2020-06-13 03:18 ` Tom Lane <[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