Анализ и оптимизация таблиц mySQL в Event Scheduler’e
  
Author:

Процедурка для анализа/оптимизации всех таблиц в одной или во всех базах данных в пределах mySQL-сервера:

CREATE PROCEDURE `mysql_maintenance_all_tables`(
        IN db_name VARCHAR(64),
        IN do_optimize TINYINT(1),
        IN do_analyze TINYINT(1)
    )
   NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
DECLARE
l_loop_end INT DEFAULT 0; DECLARE IN_TABLE_SCHEMA VARCHAR(100); DECLARE IN_TABLE_NAME VARCHAR(100); DECLARE cur1 CURSOR FOR SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES WHERE /* if db_name is not provided, it will analyze/optimize all tables except the system ones */ IF(db_name IS NULL OR db_name='', TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql'), TABLE_SCHEMA=db_name); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET l_loop_end = 1; OPEN cur1; REPEAT
FETCH
cur1 INTO IN_TABLE_SCHEMA,IN_TABLE_NAME; IF NOT l_loop_end THEN IF (do_optimize) THEN SET @qry = CONCAT('OPTIMIZE TABLE ', IN_TABLE_SCHEMA, '.', IN_TABLE_NAME); PREPARE stmt FROM @qry; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; IF (do_analyze) THEN SET @qry = CONCAT('ANALYZE TABLE ', IN_TABLE_SCHEMA, '.', IN_TABLE_NAME); PREPARE stmt FROM @qry; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END IF; UNTIL l_loop_end END REPEAT; CLOSE cur1; END;

Я редко использую эту процедуру при помощи прямого вызова, предпочитая оптимизировать всё автоматически, через определённые промежутки времени.

Вешаем вызов процедуры оптимизации на планировщик событий:

CREATE EVENT `scheduler_daily_optimize`
  ON SCHEDULE EVERY 1 DAY STARTS '2012-05-07 05:15:15'
  ON COMPLETION NOT PRESERVE
  ENABLE DO
BEGIN
  /* и анализ и оптимизация для всех таблиц базы "my_db_name"*/
  CALL mysql_maintenance_all_tables('my_db_name', 1, 1);
END;

Таким образом процесс оптимизации будет запускаться ежедневно, в 5 утра 15 минут 15 секунд.

Если кто-то захочет поюзать описанный функционал, но ещё никогда не использовал Scheduled Events, не забываем включить его в конфигурации mySQL. Прописываем следующую строку в my.cnf (my.ini в Windows):

event_scheduler = ON

и перезапускаем mysql

/etc/init.d/mysql restart

Кто поюзает вышеописанное — ставьте лайк :)


Send by E-mailSend by E-mail   Print versionPrint version
Comments(0)

No comments yet… Be the first to leave comment on this topic!

or
You may sign in using:
Enter with Facebook Enter with Google Enter with VK