Thread: Additional global stats for pg_database?

Hi!

I'd like to add the ability to show global counters of a couple of
things currently available in pg_stat_all_tables (for example number of
rows added etc) in a global counter for the entire cluster, for
monitoring of an entire cluster without having to connect to each
individual database. 

Should I try adding this to the exsting pg_stat_database view, or should
I look at creating a separate view or two?

//Magnus

Magnus Hagander wrote:
> Hi!
> 
> I'd like to add the ability to show global counters of a couple of
> things currently available in pg_stat_all_tables (for example number of
> rows added etc) in a global counter for the entire cluster, for
> monitoring of an entire cluster without having to connect to each
> individual database. 
> 
> Should I try adding this to the exsting pg_stat_database view, or should
> I look at creating a separate view or two?

I'd say add that to each database entry.  To get the sum of all
databases you'd need some sort of refactoring, because currently when a
backend reads the stats file it only gets its own stats.  One idea is to
create an additional counter in the stats file, stored separately from
database entries.

If you add a tuples updated/deleted/inserted to databases, autovacuum
can make use of that too (it's even mentioned on an XXX comment in the
code somewhere).

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Thu, Mar 15, 2007 at 10:25:57AM -0400, Alvaro Herrera wrote:
> Magnus Hagander wrote:
> > Hi!
> > 
> > I'd like to add the ability to show global counters of a couple of
> > things currently available in pg_stat_all_tables (for example number of
> > rows added etc) in a global counter for the entire cluster, for
> > monitoring of an entire cluster without having to connect to each
> > individual database. 
> > 
> > Should I try adding this to the exsting pg_stat_database view, or should
> > I look at creating a separate view or two?
> 
> I'd say add that to each database entry.  To get the sum of all
> databases you'd need some sort of refactoring, because currently when a
> backend reads the stats file it only gets its own stats.  One idea is to
> create an additional counter in the stats file, stored separately from
> database entries.
> 
> If you add a tuples updated/deleted/inserted to databases, autovacuum
> can make use of that too (it's even mentioned on an XXX comment in the
> code somewhere).

As you can surely tell, I didn't actually check what code would be
needed before I asked ;-) I'll see what/when I can do.

//Magnus