MySQL Database Maintenance Stored Procedure

UPDATED VERSION: MySQL Database Maintenance Stored Procedure

Here’s a very simple stored procedure I use to run some maintenance on MySQL tables. It allows you to run OPTIMIZE TABLE or ANALYZE TABLE on all (or most) tables in a MySQL database.

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `db_maintenance`$$
 
CREATE
    DEFINER = 'root'@'%'
    PROCEDURE db_maintenance
    (
		IN p_mode TINYINT,
		IN p_database VARCHAR(128)
    )
    LANGUAGE SQL
    SQL SECURITY INVOKER
    BEGIN
 
		##################################################
		# Author: Rhys Campbell                          #
		# Created: 2012-03-02                            #
		# Description: Performs Analyze or Optimize      #
		# actions on all tables in the provided db.      #
		##################################################
 
		DECLARE done TINYINT;
		DECLARE my_table VARCHAR(128);
 
		# Table cursor
		DECLARE table_cursor CURSOR FOR SELECT t.TABLE_NAME
						FROM INFORMATION_SCHEMA.TABLES t
						WHERE t.TABLE_SCHEMA = p_database
						AND NOT EXISTS (SELECT *
								FROM db_maintenance_table_excludes t2											WHERE t2.database_name = t.TABLE_SCHEMA
														AND t2.table_name = t.TABLE_NAME)
										AND t.TABLE_TYPE = 'BASE TABLE';
 
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
		SET done = 0;
 
		OPEN table_cursor;										
 
		table_loop: LOOP
 
		FETCH table_cursor INTO my_table;
 
		# Leave the loop if we're done
		IF (done = 1) THEN
			LEAVE table_loop;
		END IF;
 
		# Now lets do the table maintenance
		IF(p_mode = 1) THEN # Optimize
 
			SET @q = CONCAT('OPTIMIZE TABLE ', p_database, '.', my_table);
			PREPARE stmt FROM @q;
			EXECUTE stmt;
 
		ELSEIF (p_mode = 2) THEN # Analyze
 
			SET @q = CONCAT('ANALYZE TABLE ', p_database, '.', my_table);
			PREPARE stmt FROM @q;
			EXECUTE stmt;
 
		END IF;
 
		END LOOP table_loop;
 
		# Clean up
		CLOSE table_cursor;
 
    END$$
 
DELIMITER ;

Tables are retrieved from INFORMATION_SCHEMA and I use the below table to hold any tables I want to exclude from automatic maintenance.

CREATE TABLE db_maintenance_table_excludes
(
	database_name VARCHAR(128),
	table_name VARCHAR(128),
	PRIMARY KEY
	(
		database_name,
		table_name
	)
);

Usage is as follows;

# Run OPTIMIZE TABLE
CALL `db_maintenance`(1, 'db_name');
# Run ANALYZE TABLE
CALL `db_maintenance`(2, 'db_name');

2 Comments

  1. harry says:

    Hey!

    Really great Information, thanks for the procedure.

    Unfortunately i wasnt able to use it as is, after creating the procedure and the exclude table (empty), then running the usage examples (with replacing the db_name by my database name,), the following error occurs:

    #1054 – Unknown column ‘t2.database_name’ in ‘where clause’

    I just dont understand what t2 should be, it is not a variable and i cannot be a table name, because your script was ment to be generic, wasnt it?

    Thanks!

  2. Rhys says:

    Hi Harry,

    Looks like something got mangled / typo or soemthing The following line…

    FROM db_maintenance_table_excludes t

    Should have been…

    FROM db_maintenance_table_excludes t2

    Cheers,

    Rhys

Leave a Reply