Re: autovacuum not prioritising for-wraparound tables

From: Andres Freund <andres(at)2ndquadrant(dot)com>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: Kevin Grittner <kgrittn(at)ymail(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: autovacuum not prioritising for-wraparound tables
Date: 2013-02-03 01:40:04
Message-ID: 20130203014004.GF28016@awork2.anarazel.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 2013-02-01 15:09:34 -0800, Jeff Janes wrote:
> As an aside, it does seem like log_autovacuum_min_duration=0 should
> log whether a scan_all was done, and if so what relfrozenxid got set
> to. But looking at where the log message is generated, I don't know
> where to retrieve that info.

What about the following, very rough and quick, patch:

LOG: automatic vacuum of table "postgres.public.data_1": index scans: 1
pages: 2703 removed, 2702 remain, 5405 (100.00%) scanned
tuples: 499999 removed, 500001 remain
full-scan: 1, freeze-limit: 28824, new-frozen-xid: 28824
buffer usage: 29957 hits, 2 misses, 4 dirtied
avg read rate: 0.020 MB/s, avg write rate: 0.040 MB/s
system usage: CPU 0.01s/0.67u sec elapsed 0.77 sec
...
LOG: automatic vacuum of table "postgres.public.data_1": index scans: 1
pages: 2703 removed, 5404 remain, 5411 (66.74%) scanned
tuples: 499999 removed, 1000071 remain
full-scan: 0, freeze-limit: 28828, new-frozen-xid: -
buffer usage: 34085 hits, 3 misses, 4 dirtied
avg read rate: 0.027 MB/s, avg write rate: 0.036 MB/s
system usage: CPU 0.01s/0.73u sec elapsed 0.86 sec

It obviously needs more polish:

- I opted for using the 64bit representation of xids, seems to be better
in a log which very well might be looked at only after some
wraparounds
- exporting 'txid' from adt/txid.c is pretty ugly. I don't like the
invention of the type in general, but making it visible outside of
txid.c is even uglier, but using both, plain uint64 and txid inside
txid.c isn't nice either.
- txid_from_xid should be renamed, don't have a good idea to what right
now.
- is there agreement on the additionally logged information?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2013-02-03 01:41:42 Re: autovacuum not prioritising for-wraparound tables
Previous Message Tomáš Vondra 2013-02-02 22:54:04 Re: PATCH: Split stats file per database WAS: autovacuum stress-testing our system