Re: Measuring relation free space

On Sun, Nov 6, 2011 at 04:08, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Attached patch adds a new function to the pageinspect extension for
> measuring total free space, in either tables or indexes.  It returns the
> free space as a percentage, so higher numbers mean more bloat.  After trying
> a couple of ways to quantify it, I've found this particular measure
> correlates well with the nastiest bloat issues I've ran into in production
> recently.  For example, an index that had swelled to over 5X its original
> size due to autovacuum issues registered at 0.86 on this scale.  I could
> easily see people putting an alert at something like 0.40 and picking
> candidates to reindex based on it triggering.  That would be about a million
> times smarter than how I've been muddling through this class of problems so
> far.
>
> Code by Jaime Casanova, based on a prototype by me.  Thanks to attendees and
> sponsors of the PgWest conference for helping to fund some deeper
> exploration of this idea.

Looks pretty useful.

One quick stylistic comment - we don't generally use "* 1.0" to turn
an int into a double - just use a cast.

> -Given this is doing a full table scan, should it hook into a ring buffer to
> keep from trashing the buffer cache?  Or might it loop over the relation in
> a different way all together?  I was thinking about eyeing the FSM instead
> at one point, didn't explore that yet.  There's certainly a few ways to
> approach this, we just aimed at the easiest way to get a working starter
> implementation, and associated results to compare others against.

Hooking into a ring buffer seems like an almost requirement before you
can run this on a larger production system, wouldn't it? I don't know
how hard that is code-wise, but it certainly seems worthwhile.

> -Should there be a non-superuser version of this?  We'd certainly need to
> get a less cache demolishing version before that would seem wise.

Not sure that's necessary - at least not for now. Many other
diagnostics functions are already superuser only...

> -There were related things in the pageinspect module, but a case could be
> made for this being a core function instead.  It's a bit more likely to be
> used in production than the rest of that extension.

A case can be made for a lot of things in contrib to be in core ;) I
say let's keep it in pageinspect, but then also have you finish off
that "split up the contrib" patch :-)

> -What if anything related to TOAST should this handle?

Similar data for TOAST relations would be intersting, no? But that's
easily done from userspace by just querying to the toast table
specifically, I assume?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/