Vacuum alert
Job details
Name: |
Vacuum alert |
Platform: |
Postgres |
Category: |
Capacity |
Description: |
Checks the size of the log file. |
Long description: |
This job checks if vacuum has been performed recently. |
Version: |
3.0 |
Default schedule: |
10 7 * * |
Requires engine install: |
No |
Compatibility tag: |
.[type=‘instance’ & databasetype=‘postgres’]/.[newer_than_ninetwo = ‘1′] |
Parameters
Name |
Default value |
Description |
ignore_databases |
10 |
The list of databases to be ignored (separated by comma). |
warning_threshold |
10 |
Will give a warning if the number of hours since last vacuum exceeds this limit. |
alarm_threshold |
10 |
Will give an alarm if the number of hours since last vacuum exceeds this limit. |
ignore_schemas |
10 |
Comma separated list of schemas to ignore (format [database_name].[schema_name]). |
min_table_size |
10 |
Minimum table size (in KB) to be checked if vacuumed. |
min_DML_operations |
10 |
Minimum number of DML operations (insert, update and delete) on a table to be checked if vacuumed. |
Job Summary
- Purpose: The purpose of this job is to monitor and alert if the vacuum operation on PostgreSQL tables has not been performed within specified thresholds.
- Why: This job is important to ensure that the PostgreSQL database’s performance and storage efficiency are not degraded due to outdated table data. Vacuum operations are critical for reclaiming storage and maintaining the efficiency of database operations.
- Manual checking: You can check this manually in the database by issuing these SQL commands:
SELECT pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum FROM pg_stat_all_tables WHERE pg_stat_all_tables.last_vacuum < now() - interval 'desired period' OR pg_stat_all_tables.last_autovacuum < now() - interval 'desired period';
Details of the Monitoring Task
- The job is configured to check against PostgreSQL instances specifically (Postgres versions newer than 9.2).
- It filters and ignores specified databases and schemas from the check based on configured parameters.
- The job provides customizable thresholds for warning and alarm alerts based on the elapsed time since the last vacuum, taking into consideration the minimum table size and DML (insert, update, delete) operations count thresholds.
- It generates a list of tables which have not been vacuumed within the specified threshold times, categorizing the output into warnings and alarms based on severity.
Notifications and Output
- A status flag indicating the outcome of the check (0 for ok, 1 for warning, 2 for alarm).
- Lists databases checked, specific tables that have gone beyond warning or alarm thresholds, and any errors encountered during the check.
- Output tables in the monitoring tool will show the last vacuum times and highlight tables requiring attention.
Implementation Specifics
- The job runs on a default schedule (parsed from XML but typically defined within the system settings), and has a designated timeout period to ensure performance stability.
- Reports and presentation details are configured to provide a clear and actionable output, displaying the essential vacuum status of tables and databases that meet specified criteria.
The set-up and operation of this monitoring job are crucial for maintaining the health of PostgreSQL databases, particularly in environments with regular bulk data updates, ensuring ongoing performance and storage optimizations through timely vacuum operations.