[pgcluu_collectd] dump pg_stats view

[yhuelf]

It is useful to dump the pg_stats views. The dump can be imported in a table created like this:

CREATE TABLE my_client_pgstats ( export_time TEXT, dbname TEXT, schemaname TEXT, tablename TEXT, attname TEXT, inherited BOOLEAN, null_frac REAL, avg_width INT, n_distinct REAL, most_common_vals TEXT[], most_common_freqs REAL[], histogram_bounds TEXT[], correlation REAL );

Note that the columns most_common_vals and histogram_bounds have the type TEXT[] instead of anyarray.

The point is for the DBA to study the statistics, and nothing else.

[darold]

Hi Frederic,

Goof idea but there is one one problem I can see. If there is lot of table*column entries repeating the dump at each loop will kill the perfs. I think the following should be removed from the metric definition:

        'repeat'  => 1,
        'start-end' => 1,

like this the stats will be collected only at startup. repeat is to execute this command at each loop and start-end to execute the command at start and end of the pgcluu_collectd execution for a stat diff.

Depending on the amount of table*columns we also might need to disable this dump. For example with option --no-pg_stat-dump

[yhuelf]

Hi Gilles,

Thanks for your help!

I fully agree that we only want one dump at startup (initially I thought a second one at the end would be useful, but nervermind).

But when I do what you say, I notice that the file is rewritten on every run anyway, and there's only the stats for the postgres database in the dump. I would like to get the stats for each database. Is there a bug somewhere, or have I completely misunderstood?

OK for the option --no-pg_stat-dump, I will add it.

[darold]

Thanks, I will see what happen with the repeat option.

[yhuelf]

Thanks, I've had another look, but I can't figure out the logic (and I'm not yet fluent in Perl).

I pushed another commit yesterday for the --no-pg_stats-dump option.