As everyone that uses mysql knows, cardinality of indexes and partitioning of table space is a problem, especially when having heavy usage database. The result are the declining performance of queries, in my case query took 15-16 second instead of 2. We could always do OPTIMIZE TABLE on a specific table, but I wanted a system solution.
I was familiar with EXECUTE_IMMEDIATE concept from oracle and was lucky to find this post. With minnor modification (if not using default collation original won't work) it provided all I needed.
-- -------------------------------------------------------------------------------- -- OPTIMIZE_SCHEMA_TABLES -- -------------------------------------------------------------------------------- DELIMITER $$ CREATE PROCEDURE `cms`.`OPTIMIZE_SCHEMA_TABLES` ( inSchemaName VARCHAR(999) ) BEGIN DECLARE vDynSQL VARCHAR(999) DEFAULT ''; DECLARE vTableName VARCHAR(999) DEFAULT ''; DECLARE vDone BOOLEAN DEFAULT FALSE; DECLARE cDB_TABLES CURSOR FOR SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = CONVERT(inSchemaName USING utf8); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET vDone=TRUE; OPEN cDB_TABLES; REPEAT FETCH cDB_TABLES INTO vTableName; SET vDynSQL = CONCAT('OPTIMIZE TABLE ', inSchemaName, '.', vTableName); CALL EXECUTE_IMMEDIATE(vDynSQL); SET vDynSQL = ''; UNTIL vDone END REPEAT; CLOSE cDB_TABLES; END;
-- -------------------------------------------------------------------------------- -- Routine DDL -- -------------------------------------------------------------------------------- DELIMITER $$ CREATE DEFINER=`cms`@`localhost` PROCEDURE `EXECUTE_IMMEDIATE`(IN tSQLStmt TEXT) BEGIN SET @executeImmediateSQL = tSQLStmt; PREPARE executeImmediateSTML FROM @executeImmediateSQL; EXECUTE executeImmediateSTML; DEALLOCATE PREPARE executeImmediateSTML; END
Only thing left to do was to put it in a cron.
No comments:
Post a Comment