Re: Why our counters need to be time-based WAS: WIP: cross column correlation ...

On Mon, Feb 28, 2011 at 10:04:54AM -0800, Josh Berkus wrote:
> Take, for example, a problem I was recently grappling with for Nagios.
> I'd like to do a check as to whether or not tables are getting
> autoanalyzed often enough. After all, autovac can fall behind, and we'd
> want to be alerted of that.
>
> The problem is, in order to measure whether or not autoanalyze is
> behind, you need to count how many inserts,updates,deletes have happened
> since the last autoanalyze. pg_stat_user_tables just gives us the
> counters since the last reset ... and the reset time isn't even stored
> in PostgreSQL.

The solution I use for that in to use munin to monitor everything and
let it generate alerts based on the levels. It's not great, but better
than nothing.

The problem, as you say, is that you want to now the rates rather than
the absolute values. The problem with rates is that you can get wildly
different results depending on the time interval you're looking at.

For the concrete example above, autoanalyse has to be able to
determine if there is work to do so the information must be somehwere.
I'm guessing it's not easily available? If you had a function
is_autovacuumcandidate you'd be done ofcourse.

But there's ofcourse lots of stats people want, it's just not clear how
to get them. What you really need is to store the stats every few
minutes, but that's what munin does. I doubt it's worth building RRD
like capabilities into postgres.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
> - Charles de Gaulle