Add a centralized mode

[marco44]

Collect all data into a central database, with one GUI for all hosts.

[Victor-D]

+1

[mtrofimm]

+1

[decibel]

I have a somewhat similar need; I need to be able to pull catalog and stats information from remote systems to do remote analysis.

I think the ideal way to handle this would be to have custom data types that roughly match the catalogs. I say "roughly" because there's considerations like OIDs; how do you join stuff together.

The second need is essentially a "snapshot" of stats/catalog data. This would be a collection of all relevant performance data at a point in time. Representing this comprehensively would make it easy to do things like SELECT snapshot_2 - snapshot_1 to find what's changed between two snapshots.

Both of these features can be stand-alone extensions that PoWA used.

This has been on my TODO list for a while, so I'd be happy to collaborate on it.

[rjuju]

Do you think of remote "offline" analysis, or remote analysis with ability to query the original database?

We already maintain a local copy of (dbid, dbname) and (queryid, querytext), but I assume you'd also need this for tables, indexes, functions and so on, with the full definitions?

For your second need, powa 3 already snapshots tables, indexes and functions statistics (meaning PgStat_StatTabEntry and PgStat_StatFuncEntry). The UI part is still a work in progress. Do you have any other stuff you'd like to add?

[decibel]

Do you think of remote "offline" analysis, or remote analysis with ability to query the original database?

Completely offline. I've got some crufty scripts I've written that enable this, but they're... ugly. :)

We already maintain a local copy of (dbid, dbname) and (queryid, querytext), but I assume you'd also need this for tables, indexes, functions and so on, with the full definitions?

Yeah, I need a full copy of the catalog.

Getting catalog data isn't that difficult obviously... the trick is when you then try to tie it to a snapshot, and join things without using any reg* casts (since obviously those won't work).

Though... now that I think about it... with an actual system for storing remote catalog data you could easily create a new set of reg* casts that reference the exact info that you need them to.

For your second need, powa 3 already snapshots tables, indexes and functions statistics (meaning PgStat_StatTabEntry and PgStat_StatFuncEntry). The UI part is still a work in progress. Do you have any other stuff you'd like to add?

Yeah, this is another case where someone would probably want more than just that.

What I'm really proposing here is to start working on a set of extensions that are built for supporting these kinds of monitoring activities. People keep re-inventing this wheel. Off the top of my head, I know that OmniTI has built all this stuff at least once and so has EDB. I'm certain there's others that have done the exact same thing.

This all seems rather wasteful to me... :)

BTW, I looked briefly at your code and it appears that you're using record datatypes to store and pass data around, so that's a great starting point. I think a good starting point would be to separate that stuff into some separate modules, then expand those modules independently.

[rjuju]

Getting catalog data isn't that difficult obviously... the trick is when you then try to tie it to a snapshot, and join things without using any reg* casts (since obviously those won't work).

It's not hard, but the current design of powa-archivist is to only connect the its database, and have the UI connect to the other database to fetch catalog information.

For your second need, powa 3 already snapshots tables, indexes and functions statistics (meaning PgStat_StatTabEntry and PgStat_StatFuncEntry). The UI part is still a work in progress. Do you have any other stuff you'd like to add?

Yeah, this is another case where someone would probably want more than just that.

These functions are only useful if you want to get the stat entries of databases you're not connected to. It looked specific enough to put this in the extension instead of creating a new one. Getting every else statistics doesn't require specific C code, we can add the record and snapshot function easily. If the design you have in mind implies to connect to every database and have per-database data, a simpler solution could be used.

What I'm really proposing here is to start working on a set of extensions that are built > for supporting these kinds of monitoring activities. People keep re-inventing this wheel. Off the top of my head, I know that OmniTI has built all this stuff at least once > and so has EDB. I'm certain there's others that have done the exact same thing.

Yes, it'd be very nice. Is the current limitation (do everything from a single database connection) ok for you?

BTW, I looked briefly at your code and it appears that you're using record datatypes > to store and pass data around, so that's a great starting point. I think a good starting > point would be to separate that stuff into some separate modules, then expand those > modules independently.

Everything is already modular (based on pg_stat_statements, pg_qualstats, pg_stat_kcache and pg_track_settings for now, adding new datasource is trivial).

The record definitions and snapshot functions are defined in powa-archivist, but we could make it work for on demand snapshot only without the need to load it in shared_preload_library for instance.

[decibel]

On 3/7/16 8:04 AM, Julien Rouhaud wrote:

These functions are only useful if you want to get the stat entries of databases you're not connected to. It looked specific enough to put this in the extension instead of creating a new one. Getting every else statistics doesn't require specific C code, we can add the record and snapshot function easily. If the design you have in mind implies to connect to every database and have per-database data, a simpler solution could be used.

I'm not really following here, but yes, I'm thinking it would be good to support collecting from all databases in a cluster. BUT, I think it's OK if it's one database per connection is fine.

What I'm really proposing here is to start working on a set of
extensions that are built > for supporting these kinds of monitoring
activities. People keep re-inventing this
wheel. Off the top of my head, I know that OmniTI has built all this
stuff at least once > and so has EDB. I'm certain there's others
that have done the exact same thing.

Yes, it'd be very nice. Is the current limitation (do everything from a single database connection) ok for you?

I don't see why it would need to be more than one per database, though it might be nice in the future (if you had a cluster with 100 databases you'd probably want to collect from more than 1 at a time...)

The record definitions and snapshot functions are defined in powa-archivist, but we could make it work for on demand snapshot only without the need to load it in shared_preload_library for instance.

Thinking about this in the shower this morning, I came up with something that looks like this...

-- catalog snapshots CREATE TYPE snap_class AS ( -- Snapshot of pg_class snapshot_time timestamptz, relid oid, relname name, relnamespace oid, ... ); CREATE TYPE snap_type AS ... etc, etc

-- stats snapshots CREATE TYPE snap_tables AS ( -- Snapshot of table stats snapshot_time timestamptz, relid oid, seq_scan bigint, ... -- Omit schemaname and relname ); ....

And you could have a "grand unified snapshot" that pulls everything together:

CREATE TYPE snap_all AS ( database_name name, cluster_name text, port_number int, version numeric, listen_addresses text, snap_class snap_class, snap_type snap_type, ..., snap_tables snap_tables );

The idea of "snap_all" is to make it easy to ship or store a complete snapshot of an entire database. It wouldn't be hard at all to create a function that grabs all the individual snapshots and wraps them up in a snap_all that is returned to the caller. Having that type would also make it easy to marshal data to/from JSON if you wanted to.

One of the cool things having these types would allow is doing things like snap_class-snap_class to give you the delta between two snapshots. Using those, it wouldn't be hard to build snap_all-snap_all. You could also do a version of that (/ ?) that subtracts and then divides everything by the time difference so that you end up with metrics like

'seq_scan_per_second'.

Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com

[rjuju]

So, it took an embarrassing amount of time, but this is now done as of version 4, so I'm now closing this issue!