Planning MySQL downtime and other Status variables for review

We all know that while MySQL is stable and can run for a very long time without restarts. Every so often we need to restart to it adjust some variables or for upgrading though. Some might assume that overnight is the best time to do this because their users are asleep.  However, often the overnight also includes backups and other crontab related scripts that could be doing different data mining and etc.

So how can you get a guideline on when you have the least activity on the server?  Now each application and database are different, so you need to taken into account what related activity is running, during the different time frames.  If  you start to log  the "THREADS_CONNECTED" status variable every minute to 5 to 10 minutes, again this depends on how loaded your system is once an hour might be fine, you will soon gather the amount of different MySQL connections across your logged time-frame.

This can be done a numerous of ways. It is already provided in many enterprise level tools and you can write scripts and etc, but just to keep it in MySQL. I will use an event and routine for this simple example.

Create a simple table:

CREATE TABLE `threads_count` (
`threads_count_id` int(11) NOT NULL AUTO_INCREMENT,
`variable_value` varchar(255) DEFAULT NULL,
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`threads_count_id`)
) ENGINE=InnoDB;


Create a simple routine:

delimiter //
CREATE PROCEDURE threads_tally()
BEGIN
INSERT INTO threads_count SELECT NULL , VARIABLE_VALUE , NOW() FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME IN ('Threads_connected');
END//
delimiter ;


Create a simple event:

SET GLOBAL event_scheduler = ON;
CREATE EVENT `threads_tally`
ON SCHEDULE EVERY 5 MINUTE
DISABLE ON SLAVE
COMMENT 'keeps a count of Threads_connected'
DO
CALL threads_tally();
DELIMITER ;


Now but implementing the above example, you will have a table that you can select the values from (likely put into a graph or just for review) so you can see when the least number of connections existed on your server. Depending on your application  and traffic you can keep this running long enough to gain a valid perspective and then disable the event.

select variable_value , time_recorded FROM threads_count;

Now you can course take this a step further, if you do not have enterprise tools, and keep a log of all of your status variables for review:

Table for values:

CREATE TABLE `status_review` (
`status_review_id` int(11) NOT NULL AUTO_INCREMENT,
`variable_name` varchar(255) DEFAULT NULL,
`variable_value` varchar(255) DEFAULT NULL,
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`status_review_id`)
) ENGINE=InnoDB;


Routine to gather values:

delimiter //
CREATE PROCEDURE status_reviews()
BEGIN
INSERT INTO status_review SELECT NULL , VARIABLE_NAME, VARIABLE_VALUE , NOW() FROM information_schema.GLOBAL_STATUS;
END//
delimiter ;


Event to execute routine:

SET GLOBAL event_scheduler = ON;
CREATE EVENT `status_reviews`
ON SCHEDULE EVERY 1 HOUR
DISABLE ON SLAVE
COMMENT 'keeps a eye on status values'
DO
CALL status_reviews();
DELIMITER ;


You might only want certain variable names and values for review and you can adjust the query in the routine accordingly. The run time is dependent on your needs, I used an hour in this example.

This of course it going to generate a lot of information over time so if relevant some cleaning out of these tables over time might be needed. It all depends on how often you log and review the information provided.

While you will see a steady increase of many of the values because of your data growth and system use you will also be able to pinpoint data spikes in the data as and correlate that to your application and system use.

MySQL enterprise for example already will graph all of this type of information for you but this is a way you can also do it yourself with a little time and reviewing of the data.