Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to stop autovacuum for daily partition old tables

How to stop autovacuum for daily partition old tables

From
AI Rumman
Date:
Hi,

I have a table with daily partition schema on Postgresql 9.1 where we are keeping 2 years of data.
Often I experience that autovacuum process is busy with old tables where there is no change. How can I stop it?
Please advice.

Thanks.

Re: How to stop autovacuum for daily partition old tables

From
Melvin Davidson
Date:

ALTER TABLE your_schema.your_table SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

On Wed, Jan 20, 2016 at 6:22 PM, AI Rumman <rummandba@gmail.com> wrote:
Hi,

I have a table with daily partition schema on Postgresql 9.1 where we are keeping 2 years of data.
Often I experience that autovacuum process is busy with old tables where there is no change. How can I stop it?
Please advice.

Thanks.




--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: How to stop autovacuum for daily partition old tables

From
AI Rumman
Date:
But, will it not create transaction wraparound for those table?

Thanks.

On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

ALTER TABLE your_schema.your_table SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

On Wed, Jan 20, 2016 at 6:22 PM, AI Rumman <rummandba@gmail.com> wrote:
Hi,

I have a table with daily partition schema on Postgresql 9.1 where we are keeping 2 years of data.
Often I experience that autovacuum process is busy with old tables where there is no change. How can I stop it?
Please advice.

Thanks.




--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: How to stop autovacuum for daily partition old tables

From
Melvin Davidson
Date:
You can only get a transaction wraparound if you actually do transactions (insert/update/delete) on a table.
Since YOU claim you are not doing that, then you should not get a transaction wraparound for that table.


On Wed, Jan 20, 2016 at 7:54 PM, AI Rumman <rummandba@gmail.com> wrote:
But, will it not create transaction wraparound for those table?

Thanks.

On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

ALTER TABLE your_schema.your_table SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

On Wed, Jan 20, 2016 at 6:22 PM, AI Rumman <rummandba@gmail.com> wrote:
Hi,

I have a table with daily partition schema on Postgresql 9.1 where we are keeping 2 years of data.
Often I experience that autovacuum process is busy with old tables where there is no change. How can I stop it?
Please advice.

Thanks.




--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.




--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: How to stop autovacuum for daily partition old tables

From
Adrian Klaver
Date:
On 01/20/2016 04:54 PM, AI Rumman wrote:
> But, will it not create transaction wraparound for those table?

See below for complete details:

http://www.postgresql.org/docs/9.1/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

but short version from above:


23.1.4. Preventing Transaction ID Wraparound Failures

1)

"PostgreSQL's MVCC transaction semantics depend on being able to compare
transaction ID (XID) numbers: a row version with an insertion XID
greater than the current transaction's XID is "in the future" and should
not be visible to the current transaction. But since transaction IDs
have limited size (32 bits) a cluster that runs for a long time (more
than 4 billion transactions) would suffer transaction ID wraparound: the
XID counter wraps around to zero, and all of a sudden transactions that
were in the past appear to be in the future — which means their output
become invisible. In short, catastrophic data loss. (Actually the data
is still there, but that's cold comfort if you cannot get at it.) To
avoid this, it is necessary to vacuum every table in every database at
least once every two billion transactions."



2)

"The maximum time that a table can go unvacuumed is two billion
transactions minus the vacuum_freeze_min_age value at the time VACUUM
last scanned the whole table. If it were to go unvacuumed for longer
than that, data loss could result. To ensure that this does not happen,
autovacuum is invoked on any table that might contain XIDs older than
the age specified by the configuration parameter
autovacuum_freeze_max_age. (This will happen even if autovacuum is
disabled.)"

>
> Thanks.
>
> On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson <melvin6925@gmail.com
> <mailto:melvin6925@gmail.com>> wrote:
>
>
>     ALTER TABLE your_schema.your_table SET (autovacuum_enabled = false,
>     toast.autovacuum_enabled = false);
>
>     On Wed, Jan 20, 2016 at 6:22 PM, AI Rumman <rummandba@gmail.com
>     <mailto:rummandba@gmail.com>> wrote:
>
>         Hi,
>
>         I have a table with daily partition schema on Postgresql 9.1
>         where we are keeping 2 years of data.
>         Often I experience that autovacuum process is busy with old
>         tables where there is no change. How can I stop it?
>         Please advice.
>
>         Thanks.
>
>
>
>
>     --
>     *Melvin Davidson*
>     I reserve the right to fantasize.  Whether or not you
>     wish to share my fantasy is entirely up to you.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: How to stop autovacuum for daily partition old tables

From
Scott Mead
Date:


On Jan 20, 2016, at 19:54, AI Rumman <rummandba@gmail.com> wrote:

But, will it not create transaction wraparound for those table?

Thanks.

On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

ALTER TABLE your_schema.your_table SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

On Wed, Jan 20, 2016 at 6:22 PM, AI Rumman <rummandba@gmail.com> wrote:
Hi,

I have a table with daily partition schema on Postgresql 9.1 where we are keeping 2 years of data.
Often I experience that autovacuum process is busy with old tables where there is no change. How can I stop it?
Please advice.

I typically run a vacuum freeze in old partitions that don't get any changes as part of a maintenance script.  If the tables actually get no changes, autovac should ignore them unless wrap becomes an issue at max_freeze_age... Which, it shouldn't of you vacuum freeze and there are no changes. 


Thanks.




--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: How to stop autovacuum for daily partition old tables

From
Jeff Janes
Date:
On Wed, Jan 20, 2016 at 6:00 PM, Scott Mead <scottm@openscg.com> wrote:


On Jan 20, 2016, at 19:54, AI Rumman <rummandba@gmail.com> wrote:

But, will it not create transaction wraparound for those table?

Thanks.

On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

ALTER TABLE your_schema.your_table SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

On Wed, Jan 20, 2016 at 6:22 PM, AI Rumman <rummandba@gmail.com> wrote:
Hi,

I have a table with daily partition schema on Postgresql 9.1 where we are keeping 2 years of data.
Often I experience that autovacuum process is busy with old tables where there is no change. How can I stop it?
Please advice.

I typically run a vacuum freeze in old partitions that don't get any changes as part of a maintenance script.  If the tables actually get no changes, autovac should ignore them unless wrap becomes an issue at max_freeze_age... Which, it shouldn't of you vacuum freeze and there are no changes. 

Unfortunately that is not true (although it may become true in 9.6).

Currently the only way PostgreSQL has of knowing that a table is all-frozen is to read the whole table and observe each tuple to be frozen.

What I do is always set vacuum_cost_page_hit and vacuum_cost_page_miss to zero.

Vacuuming an all-frozen table for wrap around then goes very quickly, as there is nothing to write and the reading goes very fast.

The only problem I've had with that is for very large gin or gist indexes, which are not vacuumed in physical order but rather logical order.  Even then the problem is mostly how long it takes to do the vacuum (i.e. those settings fail to fix the problem, but don't cause more problems), not the impact it has on the IO of other processes.   Your mileage may vary, of course.

Cheers,

Jeff