Introduction
This alert is designed to gather the sizes of all tables within a MySQL instance. It does not trigger any notifications and serves solely as a mechanism for collecting data, which is then stored in the DPA repository.
After the data is collected, you can use a query like the one below to retrieve historical data. Additionally, consider using the custom alert MySQL Table Size Growth, which notifies you when a table's size increases by more than a specified percentage.
Query to Retrieve Table Size Data
DECLARE @CollectorAlertID int, @InstanceID int;
-- Change this query to match the name you used for the collector alert name, this is the default
SELECT @CollectorAlertID = id FROM ignite.con_alert WHERE alertname='MySQL Collect Table Sizes';
-- get alert's last run of sizes for all databases
SELECT ahr.parametername instance_name, ah.actiondate, CONVERT(FLOAT, REPLACE(ahr.levelvalue,',','')) db_size_mb
FROM ignite.con_alert a
INNER JOIN ignite.con_alert_db adb ON adb.alertid = a.id
INNER JOIN ignite.cond d ON d.id = adb.dbid
INNER JOIN ignite.con_alert_history ah ON ah.alertid=a.id
INNER JOIN ignite.con_alert_history_results ahr ON ahr.historyid=ah.historyid
WHERE a.id = @CollectorAlertID
ORDER BY ahr.parametername, ah.actiondate DESC;
Create Collection Alert in DPA
To set up this alert, create a Custom SQL Multiple Numeric alert in DPA, and configure it to resemble the following example:
