Re: [GENERAL] How to stop autovacuum for daily partition old tables

[Jeff Janes]

On Wed, Jan 20, 2016 at 6:00 PM, Scott Mead  wrote:

>
>
> On Jan 20, 2016, at 19:54, AI Rumman  wrote:
>
> But, will it not create transaction wraparound for those table?
>
> Thanks.
>
> On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson 
> 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  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

Re: [GENERAL] How to stop autovacuum for daily partition old tables

[Adrian Klaver]

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 > 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 > 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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to stop autovacuum for daily partition old tables

[Scott Mead]

> On Jan 20, 2016, at 19:54, AI Rumman  wrote:
> 
> But, will it not create transaction wraparound for those table?
> 
> Thanks.
> 
>> On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson  
>> 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  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: [GENERAL] How to stop autovacuum for daily partition old tables

[AI Rumman]

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

Thanks.

On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson 
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  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: [GENERAL] How to stop autovacuum for daily partition old tables

[Melvin Davidson]

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  wrote:

> But, will it not create transaction wraparound for those table?
>
> Thanks.
>
> On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson 
> 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  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.

[GENERAL] How to stop autovacuum for daily partition old tables

[AI Rumman]

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: [GENERAL] How to stop autovacuum for daily partition old tables

[Melvin Davidson]

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  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.