Hi,


We turned on autovacuums on 8.2 and we have a database which is read
only , it is basically a USPS database used only for address lookups
(only SELECTS, no updates/deletes/inserts).

This database has about 10gig data and yesterday autovacuum started
on this database and all of a sudden I see lot of archive logs generated
during this time, I guess it might have generated close to 3-4gig data
during this period.

It was doing only vacuum not vacuum analyze.

My question is why does it have to generate so many archive logs on
static tables ?

I am thinking these archive logs are mostly empty , the reason I am
saying that because I noticed that when I restore the db using PITR
backups for my reporting db these same logs are recovered in seconds
compared to the logs generated while vacuums are not running.

Is this a BUG ? or am I missing something here ?


Vacuum Settings
---------------------
vacuum_cost_delay = 30
vacuum_cost_limit = 150
checkpoint_segments = 64
checkpoint_timeout = 5min
checkpoint_warning = 30s
autovacuum = on
autovacuum_naptime = 120min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.001
autovacuum_analyze_scale_factor = 0.001
autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = -1
autovacuum_vacuum_cost_limit = -1



Thanks!
Pallav.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Pallav Kalva <pkalva@livedatagroup.com> writes:
> We turned on autovacuums on 8.2 and we have a database which is read
> only , it is basically a USPS database used only for address lookups
> (only SELECTS, no updates/deletes/inserts).

> This database has about 10gig data and yesterday autovacuum started
> on this database and all of a sudden I see lot of archive logs generated
> during this time, I guess it might have generated close to 3-4gig data
> during this period.

Probably represents freezing of old tuples, which is a WAL-logged
operation as of 8.2. Is it likely that the data is 200M transactions
old?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane wrote:
> Pallav Kalva <pkalva@livedatagroup.com> writes:
>
>> We turned on autovacuums on 8.2 and we have a database which is read
>> only , it is basically a USPS database used only for address lookups
>> (only SELECTS, no updates/deletes/inserts).
>>
>
>
>> This database has about 10gig data and yesterday autovacuum started
>> on this database and all of a sudden I see lot of archive logs generated
>> during this time, I guess it might have generated close to 3-4gig data
>> during this period.
>>
>
> Probably represents freezing of old tuples, which is a WAL-logged
> operation as of 8.2. Is it likely that the data is 200M transactions
> old?
>
If nothing changed on these tables how can it freeze old tuples ?
Does it mean that once it reaches 200M transactions it will do the same
thing all over again ?
If I am doing just SELECTS on these tables ? how can there be any
transactions ? or SELECTS considered transactions too ?

> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Pallav Kalva wrote:
> Tom Lane wrote:

>> Probably represents freezing of old tuples, which is a WAL-logged
>> operation as of 8.2. Is it likely that the data is 200M transactions
>> old?
>>
> If nothing changed on these tables how can it freeze old tuples ?
> Does it mean that once it reaches 200M transactions it will do the same
> thing all over again ?

No -- once tuples are frozen, they don't need freezing again (unless
they are modified by UPDATE or DELETE).

> If I am doing just SELECTS on these tables ? how can there be any
> transactions ? or SELECTS considered transactions too ?

Selects are transactions too. They just don't modify data.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Alvaro Herrera wrote:
> Pallav Kalva wrote:
>
>> Tom Lane wrote:
>>
>
>
>>> Probably represents freezing of old tuples, which is a WAL-logged
>>> operation as of 8.2. Is it likely that the data is 200M transactions
>>> old?
>>>
>>>
>> If nothing changed on these tables how can it freeze old tuples ?
>> Does it mean that once it reaches 200M transactions it will do the same
>> thing all over again ?
>>
>
> No -- once tuples are frozen, they don't need freezing again (unless
> they are modified by UPDATE or DELETE).
>
>
>> If I am doing just SELECTS on these tables ? how can there be any
>> transactions ? or SELECTS considered transactions too ?
>>
>
> Selects are transactions too. They just don't modify data.
>
>
Can you please correct me if I am wrong, I want to understand how this
works.
Based on what you said, it will run autovacuum again when it passes 200M
transactions, as SELECTS are transactions too and are going on these
tables.
But the next time when it runs autovacuum, it shouldnt freeze the tuples
again as they are already frozen and wont generate lot of archive logs ?
Or is this because of it ran autovacuum for the first time on this db ?
just the first time it does this process ?




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Fri, 2007-08-31 at 12:25 -0400, Pallav Kalva wrote:
> Can you please correct me if I am wrong, I want to understand how this
> works.
> Based on what you said, it will run autovacuum again when it passes 200M
> transactions, as SELECTS are transactions too and are going on these
> tables.
> But the next time when it runs autovacuum, it shouldnt freeze the tuples
> again as they are already frozen and wont generate lot of archive logs ?
> Or is this because of it ran autovacuum for the first time on this db ?
> just the first time it does this process ?

That is correct. The tuples are now frozen, which means that they will
not need to be frozen ever again unless you insert/update any records.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

pkalva@livedatagroup.com (Pallav Kalva) writes:
> Tom Lane wrote:
>> Pallav Kalva <pkalva@livedatagroup.com> writes:
>>
>>> We turned on autovacuums on 8.2 and we have a database which is
>>> read only , it is basically a USPS database used only for address
>>> lookups (only SELECTS, no updates/deletes/inserts).
>>>
>>
>>
>>> This database has about 10gig data and yesterday autovacuum
>>> started on this database and all of a sudden I see lot of archive
>>> logs generated during this time, I guess it might have generated
>>> close to 3-4gig data during this period.
>>>
>>
>> Probably represents freezing of old tuples, which is a WAL-logged
>> operation as of 8.2. Is it likely that the data is 200M transactions
>> old?
>>
> If nothing changed on these tables how can it freeze old tuples ?

It does so very easily, by changing the XID from whatever it was to 2
(which indicates that a tuple has been "frozen.")

I don't imagine you were wondering how it is done - more likely you
were wondering why.

"Why" is to prevent transaction ID wraparound failures.

> Does it mean that once it reaches 200M transactions it will do the
> same thing all over again ?

It won't freeze those same tuples again, as they're obviously already
frozen, but a vacuum next week may be expected to freeze tuples that
are roughly a week newer.

> If I am doing just SELECTS on these tables ? how can there be any
> transactions ? or SELECTS considered transactions too ?

Every query submitted comes in the context of a transaction. If there
wasn't a BEGIN submitted somewhere, then yes, every SELECT could
potentially invoke a transaction, irrespective of whether it writes
data or not.

If you submit a million SELECT statements, yes, that could, indeed,
indicate a million transactions.
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/nonrdbms.html
How much deeper would the ocean be if sponges didn't live there?

On 8/31/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
> Pallav Kalva wrote:
> > Tom Lane wrote:
>
> >> Probably represents freezing of old tuples, which is a WAL-logged
> >> operation as of 8.2. Is it likely that the data is 200M transactions
> >> old?
> >>
> > If nothing changed on these tables how can it freeze old tuples ?
> > Does it mean that once it reaches 200M transactions it will do the same
> > thing all over again ?
>
> No -- once tuples are frozen, they don't need freezing again (unless
> they are modified by UPDATE or DELETE).
>
>

Off-topic question: the documentation says that XID numbers are 32 bit.
Could the XID be 64 bit when running on a 64 bit platform? That would
effectively prevent wrap-around issues.

Regards

MP

Mark Lewis wrote:
> On Fri, 2007-08-31 at 12:25 -0400, Pallav Kalva wrote:
>
>> Can you please correct me if I am wrong, I want to understand how this
>> works.
>> Based on what you said, it will run autovacuum again when it passes 200M
>> transactions, as SELECTS are transactions too and are going on these
>> tables.
>> But the next time when it runs autovacuum, it shouldnt freeze the tuples
>> again as they are already frozen and wont generate lot of archive logs ?
>> Or is this because of it ran autovacuum for the first time on this db ?
>> just the first time it does this process ?
>>
>
> That is correct. The tuples are now frozen, which means that they will
> not need to be frozen ever again unless you insert/update any records.
>
>

My main concern is filling up my disk with archive logs, so from all the
replies I get is that since tuples are already frozen, next time when it
runs autovacuum it wont generate any archive logs.

Is my assumption right ?

Thanks! everybody on all your replies. It's was very helpful.

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Mikko Partio escribió:

> Off-topic question: the documentation says that XID numbers are 32 bit.
> Could the XID be 64 bit when running on a 64 bit platform? That would
> effectively prevent wrap-around issues.

No, because they would take too much space in tuple headers.

--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
Al principio era UNIX, y UNIX habló y dijo: "Hello world\n".
No dijo "Hello New Jersey\n", ni "Hello USA\n".

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly