MySQL / MariaDB event to optimize database tables

Question

I'm interested in creating a recurring event to optimize my database tables every night.

I got to SELECT Concat('OPTIMIZE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='database_name';. How can I execute every line via in a MySQL event?

I prefer to use the MySQL / MariaDB events and not involve bash into this. Thank you!

you can call a Storedprocedure an in the SP you can iterate your table an execute them with prepared staements. — Bernd Buffen, Jan 11 '16 at 14:05
I would like to maintain the process dynamic so the above command is great. Just need a way to execute what I'm generating with that concat. — Silithus, Jan 11 '16 at 19:13
SAMPLE: (not working): CREATE PROCEDURE `test_stmt`(IN sql_text TEXT) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SELECT CONCAT(sql_text, ' is not valid'); END; SET @SQL := sql_text; PREPARE stmt FROM @SQL; DEALLOCATE PREPARE stmt; END; see: https://mariadb.com/kb/en/mariadb/prepare-statement/ — Bernd Buffen, Jan 11 '16 at 19:16
It doesn't fit very well my intentions. I would have to create a procedure for every table which is a bad idea since a database might change. Also, creating procedures to delete them afterwards and recreate them everytime seems just stupid. — Silithus, Jan 12 '16 at 11:10
Did you consider implications of what you want to do? It's an extremely bad, bad, bad idea to do this. I could explain at length why, and for the moment it might seem great to you. In a few weeks time, on a busy server you will want to shoot yourself for doing this. — Mjh, Jan 12 '16 at 11:26
@Mjh could you please go into details? I will be running this on a pretty relaxed server sometime when there little user activity or not at all. — Silithus, Jan 12 '16 at 15:06
I could but I see you're satisfied with answered. `OPTIMIZE` will stress your hard drive and in case of InnoDB, it will rebuild the table and indexes. The more data you have, the more it works. All you can hope to achieve is to save some minimal amount of disk space for no gain. And you do this automatically, at a time when you don't know whether MySQL is doing something or not (you assumed it won't be, but can you guarantee that 100%?). You also can't predict when it will finish. Personally, I wouldn't do it at all, the gains are negligible compared to what can go wrong. And things go wrong.. — Mjh, Jan 12 '16 at 15:10
The databases in cause do not use InnoDB but MyISAM and are used for logging various things. We could assure there is no activity, at least generated by us, by holding the logs information somewhere in a cache and dump them into the database when the optimize will be done. — Silithus, Jan 12 '16 at 15:22

score 1 · Accepted Answer · answered Jan 12 '16 at 12:31

so, this is your query. you only must change the SELECT statement and then you can call it

ELIMITER //
CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE val VARCHAR(2000);
  DECLARE curs CURSOR FOR SELECT sqlstring FROM table_a;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN curs;

  read_loop: LOOP
    FETCH curs INTO val;
    IF done THEN
      LEAVE read_loop;
    END IF;

  SET @sql := val;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  END LOOP;

  CLOSE curs;
END; //

DELIMITER ;

call it

call p1();

score 0 · Answer 2 · answered Jan 12 '16 at 15:03

Managed to make it run with:

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE val VARCHAR(2000);
  DECLARE curs CURSOR FOR SELECT Concat('OPTIMIZE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='database_name';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN curs;

  read_loop: LOOP
    FETCH curs INTO val;
    IF done THEN
      LEAVE read_loop;
    END IF;

  SET @sql := val;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  END LOOP;

  CLOSE curs;
END

MySQL / MariaDB event to optimize database tables

2 Answers2