Re: autovacuum not prioritising for-wraparound tables

On Wed, Jan 30, 2013 at 4:05 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On 1/25/13 11:56 AM, Christopher Browne wrote:
>>
>> With a little bit of noodling around, here's a thought for a joint
>> function
>> that I*think* has reasonably common scales:
>>
>>
>> f(deadtuples, relpages, age) =
>> deadtuples/relpages + e ^ (age*ln(relpages)/2^32)
>
>
> Be careful with dead/relpages, because dead tuples increase relpages as
> well. The effect is extremely noticeable on frequently hit tables that need
> to be kept small. If you want to have a deadtuples/size metric, I think it
> would be far better to do deadtuples/non_bloated_table_size.

You may well be right about that; I didn't try to get to second order effects,
just to come up with something that seemed reasonably readily computable
based on existing data that seemed relevant.

One approach to things is to ask if we can "tweak" that model to be materially
better without requiring extra data collection. It seems plausible to do so; we
might get something better by deducting dead tuples. I'll not try to present
this yet; I think it's a better idea to come up with a broadly fuller model.

> Someone else in the thread mentioned that what we really need to be watching
> aren't raw values, but trends. Or you can think of it as watching first (or
> even second) derivatives if you like. I couldn't agree more. I believe there
> are several parts of Postgres that end up with a bunch of hard to tune GUCs
> specifically because we're measuring the wrong things.

My "first order" reaction is that this sounds great. And I reckon it would be
a great idea to come up with a reasonably sophisticated model that covers
all of the behaviours that we see emerging.

Mind you, I'm not sure that we can actually *use* that model; it may require
collecting enough more data that it becomes burdensome to collect the
additional data.

> Take freezing for example. Since the only reason to freeze is XID wrap then
> the *ideal* time to start a freeze vacuum on a table is so that the vacuum
> would end *exactly* as we were about to hit XID wrap.
>
> Obviously that's a completely impractical goal to hit, but notice the
> simplicity of the goal: we only care about the vacuum ending right before
> we'd hit XID wrap. The only way to do that is to monitor how fast vacuums
> are running, how fast XIDs are being consumed, and how quickly the oldest
> XID in each table is advancing. Notice that all of those measurements are
> time derivatives.

Yep, and I think some second derivatives will prove useful too.

The function that I presented took a slightly different tack to things; the
exponential term would try to make sure that tables get frozen WELL in
advance of necessity. That strikes me as being safer in cases where
a database is blowing through XIDs mighty quickly.

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

If you could demonstrate pretty forcibly improved behaviours falling
out of that, that may make it salable. There's quite a bit of work
there, and we would need to accept a burden of still more stats
collection.

> 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.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"