Thread: vacuum confusion
I've got a 7.4.6 db running on linux where we've been vacuuming "full" the database each night, and have recently switched to "vacuum analyse" instead. We keep seeing this message at the end of the vacuum run: WARNING: some databases have not been vacuumed in 2013308218 transactions HINT: Better vacuum them within 134175429 transactions, or you may have a wraparound failure. VACUUM Why are we seeing this message when the only databases in this cluster is the one we are vacuuming (each night), and template0 and template1? Is there something that we're not doing right? John Sidney-Woollett
John Sidney-Woollett wrote: > I've got a 7.4.6 db running on linux where we've been vacuuming "full" > the database each night, and have recently switched to "vacuum analyse" > instead. > > We keep seeing this message at the end of the vacuum run: > > WARNING: some databases have not been vacuumed in 2013308218 transactions > HINT: Better vacuum them within 134175429 transactions, or you may have > a wraparound failure. > VACUUM > > Why are we seeing this message when the only databases in this cluster > is the one we are vacuuming (each night), and template0 and template1? > > Is there something that we're not doing right? Are you doing template0/1 too? The transaction IDs are shared between all databases AFAIK. Before the numbers wrap-around any "low" numbers need to be replaced by a "frozen" marker (I think it's 0 or 1). -- Richard Huxton Archonet Ltd
Ah.., no we're not vacuuming template0 or 1. I didn't realise that the transaction ID would be stored here - I assumed that they'd be in our database. Do I need to need to (plain) vacuum, or vacuum full these template0 and template1? And is this something that can be done once a week rather than every night (like our main database)? Thanks for your help. John Sidney-Woollett Richard Huxton wrote: > John Sidney-Woollett wrote: > >> I've got a 7.4.6 db running on linux where we've been vacuuming "full" >> the database each night, and have recently switched to "vacuum >> analyse" instead. >> >> We keep seeing this message at the end of the vacuum run: >> >> WARNING: some databases have not been vacuumed in 2013308218 >> transactions >> HINT: Better vacuum them within 134175429 transactions, or you may >> have a wraparound failure. >> VACUUM >> >> Why are we seeing this message when the only databases in this cluster >> is the one we are vacuuming (each night), and template0 and template1? >> >> Is there something that we're not doing right? > > > Are you doing template0/1 too? The transaction IDs are shared between > all databases AFAIK. Before the numbers wrap-around any "low" numbers > need to be replaced by a "frozen" marker (I think it's 0 or 1). > > -- > Richard Huxton > Archonet Ltd
John Sidney-Woollett wrote: > Ah.., no we're not vacuuming template0 or 1. > > I didn't realise that the transaction ID would be stored here - I > assumed that they'd be in our database. > > Do I need to need to (plain) vacuum, or vacuum full these template0 and > template1? And is this something that can be done once a week rather > than every night (like our main database)? Ah! Found the section of the manuals - see "Routine Database Maintenance Tasks" for details. AFAIK it's a simple vacuum and once a week is more than enough. The manual recommends once every 500million transactions, though you can leave it longer. -- Richard Huxton Archonet Ltd
Thanks Richard, I found the page too... However the implication (and my confusion) is that you need to vacuum your own databases only. It's not clear (to me) that you have to do the same for template0 and template1 as well. Perhaps when someone is updating the docs, something more explicit than this <OLD> Recommended practice for most sites is to schedule a database-wide VACUUM once a day at a low-usage time of day, supplemented by more frequent vacuuming of heavily-updated tables if necessary. (If you have multiple databases in a cluster, don't forget to vacuum each one; the program vacuumdb may be helpful.) Use plain VACUUM, not VACUUM FULL, for routine vacuuming for space recovery.</OLD> could be replaced by this <NEW> Recommended practice for most sites is to schedule a database-wide VACUUM once a day at a low-usage time of day, supplemented by more frequent vacuuming of heavily-updated tables if necessary. (If you have multiple databases in a cluster, don't forget to vacuum each one (INCLUDING template0 and template1); the program vacuumdb may be helpful.) Use plain VACUUM, not VACUUM FULL, for routine vacuuming for space recovery.</NEW> And perhaps an explicit reference to vacuuming template0/1 in the section on "Preventing transaction ID wraparound failures" would be helpful. I'll add a weekly cron job to vacuum these two template databases. Thanks for your help again. John Sidney-Woollett Richard Huxton wrote: > John Sidney-Woollett wrote: > >> Ah.., no we're not vacuuming template0 or 1. >> >> I didn't realise that the transaction ID would be stored here - I >> assumed that they'd be in our database. >> >> Do I need to need to (plain) vacuum, or vacuum full these template0 >> and template1? And is this something that can be done once a week >> rather than every night (like our main database)? > > > Ah! Found the section of the manuals - see "Routine Database Maintenance > Tasks" for details. > > AFAIK it's a simple vacuum and once a week is more than enough. The > manual recommends once every 500million transactions, though you can > leave it longer. > > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
I've just noticed that the v8 docs are MUCH better in explaining this than the 7.4.6 docs that I'm using (since I'm using 7.4.6 in production). Perhaps if the same texts <QUOTE> play=# VACUUM; WARNING: some databases have not been vacuumed in 1613770184 transactions HINT: Better vacuum them within 533713463 transactions, or you may have a wraparound failure. VACUUM </QUOTE> and warning <QUOTE> To be sure of safety against transaction wraparound, it is necessary to vacuum every table, including system catalogs, in every database at least once every billion transactions. We have seen data loss situations caused by people deciding that they only needed to vacuum their active user tables, rather than issuing database-wide vacuum commands. That will appear to work fine ... for a while. </QUOTE> were added to the 7.4.x docs that would help others too. Thanks John Sidney-Woollett Richard Huxton wrote: > John Sidney-Woollett wrote: > >> Ah.., no we're not vacuuming template0 or 1. >> >> I didn't realise that the transaction ID would be stored here - I >> assumed that they'd be in our database. >> >> Do I need to need to (plain) vacuum, or vacuum full these template0 >> and template1? And is this something that can be done once a week >> rather than every night (like our main database)? > > > Ah! Found the section of the manuals - see "Routine Database Maintenance > Tasks" for details. > > AFAIK it's a simple vacuum and once a week is more than enough. The > manual recommends once every 500million transactions, though you can > leave it longer. > > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
(Apologies if this message comes twice - my imap server and mail client had a little problem)... I've just noticed that the v8 docs are MUCH better in explaining this than the 7.4.6 docs that I'm using (since I'm using 7.4.6 in production). Perhaps if the same texts <QUOTE> play=# VACUUM; WARNING: some databases have not been vacuumed in 1613770184 transactions HINT: Better vacuum them within 533713463 transactions, or you may have a wraparound failure. VACUUM </QUOTE> and warning <QUOTE> To be sure of safety against transaction wraparound, it is necessary to vacuum every table, including system catalogs, in every database at least once every billion transactions. We have seen data loss situations caused by people deciding that they only needed to vacuum their active user tables, rather than issuing database-wide vacuum commands. That will appear to work fine ... for a while. </QUOTE> were added to the 7.4.x docs that would help others too. Thanks John Sidney-Woollett John Sidney-Woollett wrote: > Thanks Richard, I found the page too... > > However the implication (and my confusion) is that you need to vacuum > your own databases only. It's not clear (to me) that you have to do the > same for template0 and template1 as well. > > Perhaps when someone is updating the docs, something more explicit than > this > > <OLD> Recommended practice for most sites is to schedule a database-wide > VACUUM once a day at a low-usage time of day, supplemented by more > frequent vacuuming of heavily-updated tables if necessary. (If you have > multiple databases in a cluster, don't forget to vacuum each one; the > program vacuumdb may be helpful.) Use plain VACUUM, not VACUUM FULL, for > routine vacuuming for space recovery.</OLD> > > could be replaced by this > > <NEW> Recommended practice for most sites is to schedule a database-wide > VACUUM once a day at a low-usage time of day, supplemented by more > frequent vacuuming of heavily-updated tables if necessary. (If you have > multiple databases in a cluster, don't forget to vacuum each one > (INCLUDING template0 and template1); the program vacuumdb may be > helpful.) Use plain VACUUM, not VACUUM FULL, for routine vacuuming for > space recovery.</NEW> > > And perhaps an explicit reference to vacuuming template0/1 in the > section on "Preventing transaction ID wraparound failures" would be > helpful. > > I'll add a weekly cron job to vacuum these two template databases. > > Thanks for your help again. > > John Sidney-Woollett > > > Richard Huxton wrote: > >> John Sidney-Woollett wrote: >> >>> Ah.., no we're not vacuuming template0 or 1. >>> >>> I didn't realise that the transaction ID would be stored here - I >>> assumed that they'd be in our database. >>> >>> Do I need to need to (plain) vacuum, or vacuum full these template0 >>> and template1? And is this something that can be done once a week >>> rather than every night (like our main database)? >> >> >> >> Ah! Found the section of the manuals - see "Routine Database >> Maintenance Tasks" for details. >> >> AFAIK it's a simple vacuum and once a week is more than enough. The >> manual recommends once every 500million transactions, though you can >> leave it longer. >> >> -- >> Richard Huxton >> Archonet Ltd >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: Have you searched our list archives? >> >> http://archives.postgresql.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
John Sidney-Woollett <johnsw@wardbrook.com> writes:
> However the implication (and my confusion) is that you need to vacuum
> your own databases only. It's not clear (to me) that you have to do the
> same for template0 and template1 as well.
You have to vacuum template1, but not template0 because the latter is
marked not datallowconn. Not sure if this is adequately explained
anywhere. The next-to-last para in section 21.1.3 does mention the
datallowconn exception, but perhaps doesn't spell it out well enough.
regards, tom lane
Thanks Tom, I figured it out after I saw the error message when trying to vacuum template0. I think the 7.4.x docs could do with a more explanation. The 8.0 docs are better although a more explicit explanation stating that you MUST vacuum analyze template1 (periodically) would be useful. Thanks John Sidney-Woollett Tom Lane wrote: > John Sidney-Woollett <johnsw@wardbrook.com> writes: > >>However the implication (and my confusion) is that you need to vacuum >>your own databases only. It's not clear (to me) that you have to do the >>same for template0 and template1 as well. > > > You have to vacuum template1, but not template0 because the latter is > marked not datallowconn. Not sure if this is adequately explained > anywhere. The next-to-last para in section 21.1.3 does mention the > datallowconn exception, but perhaps doesn't spell it out well enough. > > regards, tom lane