Skip to main content

Hello,

I’m trying to monitor PosgreSQL autovacuums per 1 or 5 minutes to get a graph, without alerting (without thresholds).

I already have the request to get a number (from DB start I guess) with “--mode=sql” :

```

/usr/lib/centreon/plugins/centreon_postgresql.pl --plugin=database::postgres::plugin --mode=sql --host=HOSTNAME --username='monitoring' --password='secret' --port='5432' --database='dbname' --sql-statement="SELECT SUM(autovacuum_count) AS total_autovacuum FROM pg_stat_all_tables"

OK: SQL statement result : 113703. | 'value'=113703;;;; 'sqlrequest.execution.time.seconds'=0.008s;;;0;

```

Or with “--mode=collection” too with a custom JSON file. I know this mode have a retention mode in statefile but I don’t know how to use it to get previous value to subtract it to current value to have a ratio from last check.

 

How can I do that? Maybe I don’t use the right check and a better way exists?

Hi,

Personally, I check two things :

1°) the activation of the autovacuum:


2°) the last execution of a vacuum:

 

 

With this way I don't get the number of autovacuums but their frequencies :

 

To answer your question, a simple way to subtract the last check value from the current value could be to write a Postgresql function. Each time the plugin calls it, it would store the last value in the database.

 

Regards

Hi,

It’s not exactly what I want. My customer want a metric to show the number of auto-vacuums done on its database.

Finally, I execute these requests with sql mode ( --mode='sql' ):

- for auto-analyzes:

SELECT COUNT(*)

FROM UNNEST(STRING_TO_ARRAY(PG_READ_FILE(PG_CURRENT_LOGFILE()), E'\n')) AS log_entry

WHERE log_entry LIKE '%automatic%analyze%'

  AND log_entry ~ TO_CHAR(NOW(), 'YYYY-MM-DD HH24:')

  AND SUBSTRING(log_entry FROM 18 FOR 2)::INT BETWEEN EXTRACT(MINUTE FROM NOW() - INTERVAL '5 minutes') AND EXTRACT(MINUTE FROM NOW());

- for auto-vacuums:

SELECT COUNT(*)

FROM UNNEST(STRING_TO_ARRAY(PG_READ_FILE(PG_CURRENT_LOGFILE()), E'\n')) AS log_entry

WHERE log_entry LIKE '%automatic%vacuum%'

  AND log_entry ~ TO_CHAR(NOW(), 'YYYY-MM-DD HH24:')

  AND SUBSTRING(log_entry FROM 18 FOR 2)::INT BETWEEN EXTRACT(MINUTE FROM NOW() - INTERVAL '5 minutes') AND EXTRACT(MINUTE FROM NOW());

 

These two requests needs extra rights:

GRANT EXECUTE ON FUNCTION pg_current_logfile() TO centreonro;

GRANT EXECUTE ON FUNCTION pg_read_file(text) TO centreonro;

GRANT EXECUTE ON FUNCTION pg_read_file(text, bigint, bigint) TO centreonro;

GRANT EXECUTE ON FUNCTION pg_read_file(text, bigint, bigint, boolean) TO centreonro;

Reply


Terms & ConditionsCookie settings