Thread: auto vaccum is dying
Hello, I have a table that receives lots of updates and inserts.
Auto vaccum is always being cancelled on that table.
One day the database went on standby and I had to act manually to recover.
What should I do to avoid auto vaccum cancel?
On 10/02/2014 07:43 AM, Rodrigo Barboza wrote: > Hello, I have a table that receives lots of updates and inserts. > Auto vaccum is always being cancelled on that table. > One day the database went on standby and I had to act manually to recover. > > What should I do to avoid auto vaccum cancel? Cancellation happens when you run a command that requires an a stronger on the table, like ALTER or TRUNCATE. Plain UPDATEs or INSERTS will not cause cancellations. There must be something else going on, causing the cancellations. - Heikki
I think I've read that when auto-vacuum takes too long, run it more often.
On Thu, Oct 2, 2014 at 8:53 AM, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
Cancellation happens when you run a command that requires an a stronger on the table, like ALTER or TRUNCATE. Plain UPDATEs or INSERTS will not cause cancellations. There must be something else going on, causing the cancellations.On 10/02/2014 07:43 AM, Rodrigo Barboza wrote:Hello, I have a table that receives lots of updates and inserts.
Auto vaccum is always being cancelled on that table.
One day the database went on standby and I had to act manually to recover.
What should I do to avoid auto vaccum cancel?
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Wed, Oct 1, 2014 at 9:43 PM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:
Hello, I have a table that receives lots of updates and inserts.Auto vaccum is always being cancelled on that table.
Do you have a scheduled task that clusters or reindexes the table?
Newer versions of PostgreSQL will log the conflicting statement that caused the vacuum to cancel.
One day the database went on standby and I had to act manually to recover.
I'm not sure what that means. Do you mean it stopped accepting commands to prevent "wrap around" data loss? Once autovacuum starts running on a table in "prevent wrap around", then it no longer voluntarily yields to other processes trying to take a conflicting lock.
What should I do to avoid auto vaccum cancel?
If you have scheduled jobs that do something on the table that requires a lock which conflicts with autovac, then you might want to include a manual VACUUM in that job.
Also, what full version are you running?
Cheers,
Jeff
On Thu, Oct 2, 2014 at 3:53 AM, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
Cancellation happens when you run a command that requires an a stronger on the table, like ALTER or TRUNCATE. Plain UPDATEs or INSERTS will not cause cancellations. There must be something else going on, causing the cancellations.On 10/02/2014 07:43 AM, Rodrigo Barboza wrote:Hello, I have a table that receives lots of updates and inserts.
Auto vaccum is always being cancelled on that table.
One day the database went on standby and I had to act manually to recover.
What should I do to avoid auto vaccum cancel?
- Heikki
I only do updates, inserts and deletes.
On Thu, Oct 2, 2014 at 12:34 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, Oct 1, 2014 at 9:43 PM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:Hello, I have a table that receives lots of updates and inserts.Auto vaccum is always being cancelled on that table.Do you have a scheduled task that clusters or reindexes the table?Newer versions of PostgreSQL will log the conflicting statement that caused the vacuum to cancel.
I have nothing scheduled, only auto vacuum, but with the default parameters.
One day the database went on standby and I had to act manually to recover.I'm not sure what that means. Do you mean it stopped accepting commands to prevent "wrap around" data loss? Once autovacuum starts running on a table in "prevent wrap around", then it no longer voluntarily yields to other processes trying to take a conflicting lock.
Exactly, stopped to prevent wrap around. I think it was because auto vacuum is being canceled.
What should I do to avoid auto vaccum cancel?If you have scheduled jobs that do something on the table that requires a lock which conflicts with autovac, then you might want to include a manual VACUUM in that job.Also, what full version are you running?
I am running postgres 9.1.4 with default auto vacuum parameters. I have only a scheduled job that runs delete for old tuples. Sometimes it a lot of tuples. Beside that, no other tasks.
Cheers,Jeff
On Sat, Oct 4, 2014 at 10:31 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:
On Thu, Oct 2, 2014 at 12:34 PM, Jeff Janes <jeff.janes@gmail.com> wrote:On Wed, Oct 1, 2014 at 9:43 PM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:Hello, I have a table that receives lots of updates and inserts.Auto vaccum is always being cancelled on that table.Do you have a scheduled task that clusters or reindexes the table?Newer versions of PostgreSQL will log the conflicting statement that caused the vacuum to cancel.I have nothing scheduled, only auto vacuum, but with the default parameters.
So what is in the log files pertaining to this?
Also, what full version are you running?I am running postgres 9.1.4 with default auto vacuum parameters. I have only a scheduled job that runs delete for old tuples. Sometimes it a lot of tuples. Beside that, no other tasks.
You are missing 10 minor releases worth of bug fixes, some of which are related to autovacuuming.
Cheers,
Jeff