Database growth rate (detailed)

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor the detailed growth rate of databases in the MySQL servers. The job finds out which databases are growing at what rate and lists the top ten largest databases. It collects detailed growth rate information including the name of the database, total size (data + index), and the history date. The job will present a chart showing the sizes of these databases over time, so as to give an overall view of how the databases have grown.
• Why: This job is important because it helps in capacity planning. By knowing which databases are growing and at what rate, it’s easier to plan for server space, anticipate potential issues related to database size, and distribute resources appropriately. It can help avoid problems associated with rapid unexpected growth of databases; for example, running out of storage space.
• Manual checking: This information can be manually checked by issuing several SQL commands, such as querying the information_schema.tables view and computing size of data_length and index_length where table_schema equals the specific database.

SELECT SCHEMA_NAME, SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES GROUP BY SCHEMA_NAME;

This SQL command gives the size of each database.

Job Description

• Name: Database growth rate (detailed)
• Category: Capacity
• Description: Collects detailed growth rate information.
• Version: 2.1
• Default Schedule: Twice a day at 5am and 5pm.
• Dependencies:
  • This task has a dependency on itself.
  • Other dependencies include the size history detail rank and historical size detail of the database.

Job Implementation

• This job creates tables such as dbw_db_size_histr_det and dbw_db_size_histr_det_rank to store the detailed growth information of the databases.
• It creates a stored procedure ‘database_growth_rate_det’ that uses the cursor to iterate through each database on the server and populate the tables created with the required details of their size and growth rate.
• If there’s a failure in job execution, it will clean up by dropping these tables and the stored procedure.

Report Generation

• Title: Database growth rate (detailed)
• Description: Database growth rate (detailed).
• Reports are generated every hour, presenting a chart to visually display the overall size and growth rate of the top five databases in reference to their historical date.
• It uses dataset obtained from combining dbw_db_size_histr_det and dbw_db_size_histr_det_rank tables where their db_name is the same. The datasets display against their size and historical date. It focuses on the top 5 largest databases.
• The chart type is a category chart with Size plotted against the History date for each Aggregation type (Databases), providing a visual representation of the database growth over time.