Database growth rate (aggregated)

Job details

Parameters

Job Summary

• Purpose: The purpose of this monitoring job is to collect and visualize the database growth rates over time for MariaDB servers, focusing on data and index sizes.
• Why: Understanding the growth rate of database storage requirements is crucial for capacity planning and performance optimization. It assists in preventing storage-related issues that could lead to database downtime or poor performance.
• Manual checking: You can manually check database growth rates by querying the historical data stored in the system, although precise commands are part of automated procedures provided by the job itself.

Job Details

Job Configuration

• Job Object: “database_growth_rate_aggr”
• Description: Collects database size and provides visual representations of growth rates for the server, aggregated across both data and index sizes.
• Version: 2.2
• Company: dbwatch.com
• Default Schedule: Twice daily at 5:15 AM and 5:15 PM.

Dependencies and Cleanup

• This job depends on its own prior completions and an auxiliary table “dbw_db_size_histr_aggr” for holding historical data.
• Cleanup Procedures: In case of job failure, the following SQL commands will be executed to clean the environment:
  • “drop table dbw_db_size_histr_aggr”

Implementation

The job involves the creation of a table to store data size, index size, and history date. The size data is aggregated from the MariaDB “information_schema.tables”, and these data points are inserted into the history table. Thereafter, task execution details including total size are updated.

Reporting

• Version: 2
• Title: “Database growth rate (aggregated)”
• Description & Text: Provides insights into the growth rate of data and index sizes over time for MariaDB databases.
• Schedule: Hourly execution is noted, which may be for continuous monitoring.
• Data Presentation: The gathered data is shown in a chart format breaking down sizes by type (‘Data’, ‘Index’, and ‘Total’) against historical dates.

SQL Query Summary (Report Generation)

The SQL query used for generating the report pulls and rounds off the size data from the “dbw_db_size_histr_aggr” table, classifying it into ‘Data size’, ‘Index size’, and ‘Total size’. The results are then represented in a categorical chart with dates on the x-axis and size types as series.

Conclusion

This dbWatch Control Center monitoring job provides crucial visibility into the growth trends of MariaDB databases, which is essential for effective database and capacity management.