From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: 8.2 Autovacuum BUG ?
Date: Fri, 31 Aug 2007 13:46:09 -0400
Message-ID: <60642vegwu.fsf@dba2.int.libertyrms.com> (raw)
References: <46D83427.60001@livedatagroup.com>
<25038.1188574775@sss.pgh.pa.us>
<46D83C50.3040409@livedatagroup.com>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: 8.2 Autovacuum BUG ?
In-Reply-To: <60642vegwu.fsf@dba2.int.libertyrms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox