That said, I have used some new features in 5.x to implement a monitoring solution for my PapaBlues website, and on top of this, I am managing the site using some Joomla components that I have developed (the whole site is built with Joomla).
I use an EVENT to once per day collect STATUS data. The way this works is that I have a PROCEDURE that is called by the EVENT. I realized that for a recurring event, and mostly for a single shot event, you really want all the code in a PROCEDURE that is then called by the EVENT in question. Why? Simple, you want to test the code, without waiting for the procedure to run. You want to run the code at will. You want to change the procedure code without having to respecify the event. No, this is way to go, trust me.
The Joomla module I have written controls the running of the EVENT, and a screenshot looks something like this:
Here I can enable or diable the EVENT. I can define how often the EVENT runs and when it starts. Which is all I need to do, really. Also, I can, using the "Run" icon, run the collector right now.The way I collect data is by DATE. If the proceure that does the collection runs twice in a day, the previous contents are overwritten. To ensure that not TOO much data is collected, I use a table to control what status variables I want to save. To simply handling of status data, and the collection of data, I have a table that contains what I call "STATUS GROUP", and then there is another table that contains the VARIBLES that are part of each group. This makes it a lot easier to monitor the different status variables.
For each group, I can decide if the variables in that group are added to the history or not. Any variable can of course be in more than one GROUP. The Joomla interface looks like this:
Here we see a status GROUP that contain the COM_ status variables for DML commands. Plain an simple. And when viewing the status, which I do in yet another Joomla screen, I can choose which group to view, or all. Also, I can choose to view data between certain dates and also do a free text search on variables.The tables I use to control this looks like this:
CREATE TABLE IF NOT EXISTS `papa_db_status_group`(
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`updated` TIMESTAMP NOT NULL,
`name` VARCHAR(60) NOT NULL,
`description` VARCHAR(255),
`is_default` BOOLEAN NOT NULL,
`history` BOOLEAN NOT NULL,
UNIQUE(`name`),
PRIMARY KEY(`id`)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `papa_db_status_group_var`(
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`group_id` INTEGER UNSIGNED NOT NULL,
`variable_name` VARCHAR(60) NOT NULL,
UNIQUE(`group_id`, `variable_name`),
FOREIGN KEY(`group_id`) REFERENCES `papa_db_status_group`(`id`),
PRIMARY KEY(`id`)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `papa_db_status_history`(
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`history_date` DATE NOT NULL,
`variable_name` VARCHAR(60) NOT NULL,
`variable_value` VARCHAR(20480),
UNIQUE(`history_date`, `variable_name`),
PRIMARY KEY(`id`)) ENGINE=MyISAM;
As for my procedure that collects the history, it looks like this:
CREATE PROCEDURE `papa_update_status_history`()
BEGIN
DECLARE v_dummy INTEGER;
DECLARE v_today DATE;
DECLARE c1 CURSOR FOR SELECT MAX(1) FROM `papa_db_status_group_var` AS sgv
JOIN `papa_db_status_group` AS sg ON sgv.`group_id` = sg.`id`
WHERE sgv.`variable_name` = 'Uptime_since_flush_status'
AND sg.`history`;
SET v_today = CURRENT_DATE();
/* Update values that already existed. */
UPDATE `papa_db_status_history` AS sh JOIN information_schema.`GLOBAL_STATUS` AS gs
ON sh.`variable_name` = gs.`VARIABLE_NAME` AND sh.`history_date` = v_today
SET sh.`variable_value` = gs.`VARIABLE_VALUE`;
/* Insert values that didn't exist. */
INSERT INTO `papa_db_status_history`(`id`, `history_date`, `variable_name`, `variable_value`)
SELECT NULL, v_today, gs.`VARIABLE_NAME`, gs.`VARIABLE_VALUE`
FROM information_schema.`GLOBAL_STATUS` AS gs
LEFT OUTER JOIN `papa_db_status_history` AS sh ON gs.`VARIABLE_NAME` = sh.`variable_name`
AND sh.`history_date` = v_today
WHERE EXISTS(SELECT 1 FROM `papa_db_status_group_var` AS sgv JOIN `papa_db_status_group` AS sg
ON sgv.`group_id` = sg.`id`
WHERE sgv.`variable_name` = gs.`VARIABLE_NAME`
AND sg.`history`)
AND sh.`id` IS NULL;
/* Make sure that uptime_since_flush_status is always saved. */
OPEN c1;
FETCH c1 INTO v_dummy;
IF v_dummy IS NULL THEN
/* First, try to update an existing value. */
UPDATE `papa_db_status_history` AS sh JOIN information_schema.`GLOBAL_STATUS` AS gs
ON sh.`variable_name` = 'UPTIME_SINCE_FLUSH_STATUS' AND gs.`VARIABLE_NAME` = 'UPTIME_SINCE_FLUSH_STATUS'
AND sh.`history_date` = v_today
SET sh.`variable_value` = gs.`VARIABLE_VALUE`;
/* Insert the value if it doesn't exist. */
INSERT INTO `papa_db_status_history`(`id`, `history_date`, `variable_name`, `variable_value`)
SELECT NULL, v_today, gs.`VARIABLE_NAME`, gs.`VARIABLE_VALUE`
FROM information_schema.`GLOBAL_STATUS` AS gs
LEFT OUTER JOIN `papa_db_status_history` AS sh ON sh.`variable_name` = 'UPTIME_SINCE_FLUSH_STATUS'
WHERE gs.`VARIABLE_NAME` = 'UPTIME_SINCE_FLUSH_STATUS'
AND sh.`id` IS NULL;
END IF;
CLOSE c1;
END
//
And finally, the event looks like this:
CREATE EVENT `papa_update_status_history_event`
ON SCHEDULE EVERY 1 DAY
STARTS CONCAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY), ' 00:00:00')
DISABLE
DO CALL `papa_update_status_history`;
Before I close this, if you wonder why I always collect the current value of the status variable 'UPTIME_SINCE_FLUSH_STATUS', then this is because, when I show my statistics, I also show the value per hour, and for this, I always need this value.
Hope this provides some help for you
/Karlsson
No comments:
Post a Comment