Skip site navigation (1) Skip section navigation (2)

Re: autovacuum not prioritising for-wraparound tables

From: Jim Nasby <jim(at)nasby(dot)net>
To: Christopher Browne <cbbrowne(at)gmail(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: autovacuum not prioritising for-wraparound tables
Date: 2013-01-30 22:50:22
Message-ID: 5109A3AE.30502@nasby.net (view raw or flat)
Thread:
Lists: pgsql-hackers
On 1/30/13 4:37 PM, Christopher Browne wrote:
>>  From a more practical standpoint, I think it would be extremely useful to
>> >have a metric that showed how quickly a table churned. Something like dead
>> >tuples per time period. Comparing that to the non-bloated table size should
>> >give a very strong indication of how critical frequent vacuums on that table
>> >are.
> That's a nice idea, though I'm pretty sure that this requires capturing and
> storing a lot more statistical data, which presumably worsens things
> (presuming the data gets stored in tables), making the database bigger,
> adding an extra set of data that needs to get collected, queried, and
> trimmed, and chewing through still more XIDs.

We don't necessarily need added storage (or at least nothing serious). 
It could potentially be as simple as remembering when we last took a 
measurement and using that to also store number of blahs per second. 
That's only 2 added data points (time and the rate). Of course, there 
are pitfalls to something that simple, though some of those pitfals have 
simple solutions (such as using estimated weighted means).

In this case I think we would be better served by trying to define a 
good metric first, and then worrying about how to actually capture and 
store it.

>> >I don't have a good metric in mind for freeze right now, but I do want to
>> >mention a use case that I don't think has come up before. When building a
>> >londiste slave (and presumably all the other trigger replication systems
>> >suffer from this), each table is copied over in a single transaction, and
>> >then updates start flowing in for that table. That can easily result in a
>> >scenario where you have an enormous volume of tuples that will all need
>> >freezing at almost exactly the same time. It would be nice if we could
>> >detect such a condition and freeze those tuples over time, instead of trying
>> >to freeze all of them in one shot.
> I wonder if these are perhaps good candidates for being frozen immediately;
> COPY FREEZE was added in not so long ago; it doesn't perfectly cover
> this, but if I squint at it a bit...
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=8de72b66a2edcf12c812de0a73bd50b6b7d81d62
>
> I don't see a lot of value to having the data COPYed over to a subscriber
> NOT getting frozen immediately.

Yeah, COPY FREEZE would probably be highly valuable in this case.


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2013-01-30 23:00:13
Subject: Re: backend hangs at immediate shutdown (Re: Back-branch update releases coming in a couple weeks)
Previous:From: Jim NasbyDate: 2013-01-30 22:44:34
Subject: Re: autovacuum not prioritising for-wraparound tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group