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