Обсуждение: PANIC killing vacuum process
Alls,
we have develop a script to execute the vacuum full on all tables of our
very big database , since it is a 24 x 7 available system we have not a
timeframe to exec the vacuum full.
so we try with this script running the vauum full table by table and if
the vacuum generate the waiting status for other connections we kill the
vacuum .
But we encounter following problem:
with kill command:
2010-11-03 14:25:27 CET [19324]: [4-1] FATAL: terminating connection
due to administrator command
2010-11-03 14:25:27 CET [19324]: [5-1] STATEMENT: vacuum full analyze
verbose tracking.as_history_status ;
2010-11-03 14:25:27 CET [19324]: [6-1] PANIC: cannot abort transaction
75073917, it was already committed
with pg_cancel_backend(pid) command:
CPU 0.18s/0.26u sec elapsed 3.79 sec.
ERROR: canceling statement due to user request
PANIC: cannot abort transaction 75081452, it was already committed
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> quit
-> \q
the server crash and we have a service unavailiability on our production
system.
Is it possible to softly kill a vacuum process without risk a panic ?????
thanks a lot
Silvio Brandani
---
Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche
quandoli comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi
diritti,sono riportate alla pagina http://www.savinodelbene.com/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli
eventualiallegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei
all'attivitàlavorativa o contrari a norme.
--
Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: > we have develop a script to execute the vacuum full on all tables > of our very big database , since it is a 24 x 7 available system > we have not a timeframe to exec the vacuum full. > so we try with this script running the vauum full table by table > and if the vacuum generate the waiting status for other > connections we kill the vacuum . > But we encounter following problem: > with kill command: > > 2010-11-03 14:25:27 CET [19324]: [4-1] FATAL: terminating > connection due to administrator command > 2010-11-03 14:25:27 CET [19324]: [5-1] STATEMENT: vacuum full > analyze verbose tracking.as_history_status ; > 2010-11-03 14:25:27 CET [19324]: [6-1] PANIC: cannot abort > transaction 75073917, it was already committed > > with pg_cancel_backend(pid) command: > > CPU 0.18s/0.26u sec elapsed 3.79 sec. > ERROR: canceling statement due to user request > PANIC: cannot abort transaction 75081452, it was already > committed server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > !> quit > -> \q > > the server crash and we have a service unavailiability on our > production system. What version of PostgreSQL is this? > Is it possible to softly kill a vacuum process without risk a > panic ????? Normally, yes. VACUUM FULL is more prone to problems than a normal vacuum, especially if you are using an old version. There are very few circumstances where VACUUM FULL is the right thing to use. Have you recovered your database yet? If so how? (Restart, PITR backup, pg_dump output, etc.) -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Silvio Brandani <silvio.brandani@tech.sdb.it> wrote:
>> we have develop a script to execute the vacuum full on all tables
>> of our very big database , since it is a 24 x 7 available system
>> we have not a timeframe to exec the vacuum full.
>> so we try with this script running the vauum full table by table
>> and if the vacuum generate the waiting status for other
>> connections we kill the vacuum .
>> [ and get ]
>> 2010-11-03 14:25:27 CET [19324]: [6-1] PANIC: cannot abort
>> transaction 75073917, it was already committed
> What version of PostgreSQL is this?
Anything pre-9.0 might do that; it's one of the known problems with the
old VACUUM FULL implementation that made us decide to get rid of it.
However, versions that are less than about a year old do have a hack
that should avoid the PANIC for a query-cancel interrupt ... at the
cost of ignoring the cancel interrupt, so that's not all that helpful
a solution here.
>> Is it possible to softly kill a vacuum process without risk a
>> panic ?????
> Normally, yes. VACUUM FULL is more prone to problems than a normal
> vacuum, especially if you are using an old version. There are very
> few circumstances where VACUUM FULL is the right thing to use.
Indeed. If you think you need to use VACUUM FULL on a routine basis,
rethink that.
regards, tom lane
Kevin Grittner ha scritto: > Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: > > >> we have develop a script to execute the vacuum full on all tables >> of our very big database , since it is a 24 x 7 available system >> we have not a timeframe to exec the vacuum full. >> so we try with this script running the vauum full table by table >> and if the vacuum generate the waiting status for other >> connections we kill the vacuum . >> But we encounter following problem: >> with kill command: >> >> 2010-11-03 14:25:27 CET [19324]: [4-1] FATAL: terminating >> connection due to administrator command >> 2010-11-03 14:25:27 CET [19324]: [5-1] STATEMENT: vacuum full >> analyze verbose tracking.as_history_status ; >> 2010-11-03 14:25:27 CET [19324]: [6-1] PANIC: cannot abort >> transaction 75073917, it was already committed >> >> with pg_cancel_backend(pid) command: >> >> CPU 0.18s/0.26u sec elapsed 3.79 sec. >> ERROR: canceling statement due to user request >> PANIC: cannot abort transaction 75081452, it was already >> committed server closed the connection unexpectedly >> This probably means the server terminated abnormally >> before or while processing the request. >> The connection to the server was lost. Attempting reset: Failed. >> !> quit >> -> \q >> >> the server crash and we have a service unavailiability on our >> production system. >> > > What version of PostgreSQL is this? > > >> Is it possible to softly kill a vacuum process without risk a >> panic ????? >> > > Normally, yes. VACUUM FULL is more prone to problems than a normal > vacuum, especially if you are using an old version. There are very > few circumstances where VACUUM FULL is the right thing to use. > > Have you recovered your database yet? If so how? (Restart, PITR > backup, pg_dump output, etc.) > > -Kevin > > Postgres version : PostgreSQL 8.3.1 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14) -- Silvio Brandani --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti,sono riportate alla pagina http://www.savinodelbene.com/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventualiallegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitàlavorativa o contrari a norme. --
Kevin Grittner ha scritto: > Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: > > >> we have develop a script to execute the vacuum full on all tables >> of our very big database , since it is a 24 x 7 available system >> we have not a timeframe to exec the vacuum full. >> so we try with this script running the vauum full table by table >> and if the vacuum generate the waiting status for other >> connections we kill the vacuum . >> But we encounter following problem: >> with kill command: >> >> 2010-11-03 14:25:27 CET [19324]: [4-1] FATAL: terminating >> connection due to administrator command >> 2010-11-03 14:25:27 CET [19324]: [5-1] STATEMENT: vacuum full >> analyze verbose tracking.as_history_status ; >> 2010-11-03 14:25:27 CET [19324]: [6-1] PANIC: cannot abort >> transaction 75073917, it was already committed >> >> with pg_cancel_backend(pid) command: >> >> CPU 0.18s/0.26u sec elapsed 3.79 sec. >> ERROR: canceling statement due to user request >> PANIC: cannot abort transaction 75081452, it was already >> committed server closed the connection unexpectedly >> This probably means the server terminated abnormally >> before or while processing the request. >> The connection to the server was lost. Attempting reset: Failed. >> !> quit >> -> \q >> >> the server crash and we have a service unavailiability on our >> production system. >> > > What version of PostgreSQL is this? > > >> Is it possible to softly kill a vacuum process without risk a >> panic ????? >> > > Normally, yes. VACUUM FULL is more prone to problems than a normal > vacuum, especially if you are using an old version. There are very > few circumstances where VACUUM FULL is the right thing to use. > > Have you recovered your database yet? If so how? (Restart, PITR > backup, pg_dump output, etc.) > > -Kevin > > We had to kill the postmaster and restart the database recovering it. thanks -- Silvio Brandani --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti,sono riportate alla pagina http://www.savinodelbene.com/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventualiallegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitàlavorativa o contrari a norme. --
Tom Lane ha scritto: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > >> Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: >> >>> we have develop a script to execute the vacuum full on all tables >>> of our very big database , since it is a 24 x 7 available system >>> we have not a timeframe to exec the vacuum full. >>> so we try with this script running the vauum full table by table >>> and if the vacuum generate the waiting status for other >>> connections we kill the vacuum . >>> [ and get ] >>> 2010-11-03 14:25:27 CET [19324]: [6-1] PANIC: cannot abort >>> transaction 75073917, it was already committed >>> > > >> What version of PostgreSQL is this? >> > > Anything pre-9.0 might do that; it's one of the known problems with the > old VACUUM FULL implementation that made us decide to get rid of it. > > However, versions that are less than about a year old do have a hack > that should avoid the PANIC for a query-cancel interrupt ... at the > cost of ignoring the cancel interrupt, so that's not all that helpful > a solution here. > > >>> Is it possible to softly kill a vacuum process without risk a >>> panic ????? >>> > > >> Normally, yes. VACUUM FULL is more prone to problems than a normal >> vacuum, especially if you are using an old version. There are very >> few circumstances where VACUUM FULL is the right thing to use. >> > > Indeed. If you think you need to use VACUUM FULL on a routine basis, > rethink that. > > regards, tom lane > > We were running vacuum full because of a lot of IO problems , so we try this way to reorganize tables.... should be better thane normal vacuum or you think the benefits of running vacuum full are not so good to run it and a normal vacuum analyze is enough ??? thanks, -- Silvio Brandani --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti,sono riportate alla pagina http://www.savinodelbene.com/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventualiallegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitàlavorativa o contrari a norme. --
On Wed, Nov 3, 2010 at 10:06 AM, Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: > > Alls, > > we have develop a script to execute the vacuum full on all tables of our Vacuum full is more of a recovery / offline command and is to be used sparingly, especially before 9.0. > very big database , since it is a 24 x 7 available system we have not a > timeframe to exec the vacuum full. Is there a reason you're avoiding autovacuum and tuning it to keep up? It's usually the better option. > PostgreSQL 8.3.1 on x86_64-redhat-linux-gnu Is there a good reason for avoiding about two years of updates (8.3.latest has a lot of bug fixes.)
Scott Marlowe <scott.marlowe@gmail.com> wrote: > Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: >> we have develop a script to execute the vacuum full on all tables > Vacuum full is more of a recovery / offline command and is to be > used sparingly, especially before 9.0. And before 9.0, most of the situations where you might reasonably consider VACUUM FULL, you were better off with CLUSTER. >> very big database , since it is a 24 x 7 available system we have >> not a timeframe to exec the vacuum full. > > Is there a reason you're avoiding autovacuum and tuning it to keep > up? It's usually the better option. Even if you have a case for doing database vacuums during off-peak hours, you should almost certainly use autovacuum with settings at least as aggressive as the default. At our shop we configure autovacuum more aggressively than the default, to keep our small, volatile tables tidy, and run a vacuum of the entire database each night (which is, by the way, a very different thing than a VACUUM FULL). >> PostgreSQL 8.3.1 on x86_64-redhat-linux-gnu > > Is there a good reason for avoiding about two years of updates > (8.3.latest has a lot of bug fixes.) Yeah, this is important. See this page: http://www.postgresql.org/support/versioning Many of those fixes to 8.3 after 8.3.1 were to vacuum or autovacuum. You can poke around the release notes here: http://www.postgresql.org/docs/8.3/static/release.html If problems with autovacuum were what drove you toward VACUUM FULL, you should update and try autovacuum again. Going from 8.3.1 to 8.3.12 is pretty painless and very safe -- just read the release notes for details on what types of indexes need to be rebuilt after the update. (That probably won't affect you, but you should check.) -Kevin
Kevin Grittner ha scritto: > Scott Marlowe <scott.marlowe@gmail.com> wrote: > >> Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: >> > > >>> we have develop a script to execute the vacuum full on all tables >>> > > >> Vacuum full is more of a recovery / offline command and is to be >> used sparingly, especially before 9.0. >> > > And before 9.0, most of the situations where you might reasonably > consider VACUUM FULL, you were better off with CLUSTER. > > >>> very big database , since it is a 24 x 7 available system we have >>> not a timeframe to exec the vacuum full. >>> >> Is there a reason you're avoiding autovacuum and tuning it to keep >> up? It's usually the better option. >> > > Even if you have a case for doing database vacuums during off-peak > hours, you should almost certainly use autovacuum with settings at > least as aggressive as the default. At our shop we configure > autovacuum more aggressively than the default, to keep our small, > volatile tables tidy, and run a vacuum of the entire database each > night (which is, by the way, a very different thing than a VACUUM > FULL). > > >>> PostgreSQL 8.3.1 on x86_64-redhat-linux-gnu >>> >> Is there a good reason for avoiding about two years of updates >> (8.3.latest has a lot of bug fixes.) >> > > Yeah, this is important. See this page: > > http://www.postgresql.org/support/versioning > > Many of those fixes to 8.3 after 8.3.1 were to vacuum or autovacuum. > You can poke around the release notes here: > > http://www.postgresql.org/docs/8.3/static/release.html > > If problems with autovacuum were what drove you toward VACUUM FULL, > you should update and try autovacuum again. Going from 8.3.1 to > 8.3.12 is pretty painless and very safe -- just read the release > notes for details on what types of indexes need to be rebuilt after > the update. (That probably won't affect you, but you should check.) > > -Kevin > > Thanks a lot. We will migrate to 8.3.12 and keep autovacuum running instead of vauum full. -- Silvio Brandani --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti,sono riportate alla pagina http://www.savinodelbene.com/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventualiallegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitàlavorativa o contrari a norme. --
Silvio Brandani ha scritto: > Kevin Grittner ha scritto: >> Scott Marlowe <scott.marlowe@gmail.com> wrote: >> >>> Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: >>> >> >> >>>> we have develop a script to execute the vacuum full on all tables >>>> >> >> >>> Vacuum full is more of a recovery / offline command and is to be >>> used sparingly, especially before 9.0. >>> >> >> And before 9.0, most of the situations where you might reasonably >> consider VACUUM FULL, you were better off with CLUSTER. >> >> >>>> very big database , since it is a 24 x 7 available system we have >>>> not a timeframe to exec the vacuum full. >>>> >>> Is there a reason you're avoiding autovacuum and tuning it to keep >>> up? It's usually the better option. >>> >> >> Even if you have a case for doing database vacuums during off-peak >> hours, you should almost certainly use autovacuum with settings at >> least as aggressive as the default. At our shop we configure >> autovacuum more aggressively than the default, to keep our small, >> volatile tables tidy, and run a vacuum of the entire database each >> night (which is, by the way, a very different thing than a VACUUM >> FULL). >> >> >>>> PostgreSQL 8.3.1 on x86_64-redhat-linux-gnu >>>> >>> Is there a good reason for avoiding about two years of updates >>> (8.3.latest has a lot of bug fixes.) >>> >> >> Yeah, this is important. See this page: >> >> http://www.postgresql.org/support/versioning >> >> Many of those fixes to 8.3 after 8.3.1 were to vacuum or autovacuum. >> You can poke around the release notes here: >> >> http://www.postgresql.org/docs/8.3/static/release.html >> >> If problems with autovacuum were what drove you toward VACUUM FULL, >> you should update and try autovacuum again. Going from 8.3.1 to >> 8.3.12 is pretty painless and very safe -- just read the release >> notes for details on what types of indexes need to be rebuilt after >> the update. (That probably won't affect you, but you should check.) >> >> -Kevin >> >> > Thanks a lot. > We will migrate to 8.3.12 and keep autovacuum running instead of vauum > full. > > > > the postgres 9.0.x could be consider a stable version ?? or is better to wait 9.1 , in this case when will be released ?? thanks -- Silvio Brandani Infrastructure Administrator SDB Information Technology Phone: +39.055.3811222 Fax: +39.055.5201119 --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti,sono riportate alla pagina http://www.savinodelbene.com/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventualiallegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitàlavorativa o contrari a norme. --
Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: > the postgres 9.0.x could be consider a stable version ?? or is > better to wait 9.1 , in this case when will be released ?? You would gain nothing by waiting for 9.1. 9.0.x is no more or less a stable version than 8.3.x. It's the "x" which means something regarding release maturity. Since you had enough tolerance of "early adopter" issues to have been using 8.3.1 until very recently, you're probably OK with 9.0.1; but this time I would recommend applying minor releases as they are published. Over time people find odd little corner cases where there are problems, and the release becomes more stable as the patches to fix them are applied. -Kevin
we are using reindex on single defragmented indexes on a nigtly schedule script. last night we encounter a problem of lock , a idle in transaction connection keep a lock on the table and the reindex was in a wating status and other connections were in a BIND waiting. Is it possible to use a reindex in a share lock mode ?? we have a 8.3 version. In higher postgres version the reindex mode change????? thanks a lot Silvio Brandani --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti,sono riportate alla pagina http://www.savinodelbene.com/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventualiallegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitàlavorativa o contrari a norme. --