Procedure to recreate table automatically to free InnoDB space

Question

I have a few MySQL tables for a website running PHP Laravel that keeps getting bigger and bigger. For example the jobs table is currently at 18GB, and has a total of 6 rows in it. I have a log table that regularly gets over 200GB before I delete it and recreate it. I have tried the command OPTIMIZE TABLE jobs without any change in size.

For the jobs table, things keep getting added to it, then as they are processed, they are removed, but the space is never reclaimed or reused. Is this normal?

Here is the definition of the table:

CREATE TABLE `jobs` 
             ( 
                          `id`    bigint(20) NOT NULL auto_increment, 
                          `queue` varchar(255) characterSET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
                          `payload` longtext characterSET utf8mb4 COLLATE utf8mb4_0900_ai_ci, 
                          `attempts`     tinyint(3) unsigned DEFAULT NULL, 
                          `reserved_at`  int(10) unsigned DEFAULT NULL, 
                          `available_at` int(10) unsigned DEFAULT NULL, 
                          `created_at`   int(10) unsigned DEFAULT NULL, 
                          PRIMARY KEY (`id`) using btree, 
                          UNIQUE KEY `UNIQUE` (`queue`,`reserved_at`) using btree 
             ) 
             engine=innodb auto_increment=415621 DEFAULT charset=utf8mb4 COLLATE=utf8mb4_0900_ai_ci row_format=dynamic

I was playing the other day with a table in SQLite and when I altered the structure by adding a column, the way it did it was with the following steps:

PRAGMA foreign_keys = 0;
CREATE TABLE sqlitestudio_temp_table AS 
SELECT * 
FROM   netid;DROP TABLE netid;CREATE TABLE netid 
             ( 
                          automodifiedtimestamp VARCHAR (20), 
                          type                  VARCHAR (1), 
                          netid                 VARCHAR (4), 
                          usage                 VARCHAR (1), 
                          _unused1              VARCHAR (3), 
                          extracol              VARCHAR (50) 
             );INSERT INTO netid 
            ( 
                        automodifiedtimestamp, 
                        type, 
                        netid, 
                        usage, 
                        _unused1 
            ) 
SELECT automodifiedtimestamp, 
       type, 
       netid, 
       usage, 
       _unused1 
FROM   sqlitestudio_temp_table;
DROP TABLE sqlitestudio_temp_table;
PRAGMA foreign_keys = 1;

It got me thinking that the same could be done with MySQL, which would free up the empty space that seems to be an ongoing issue for many.

Rather than dropping the table in the beginning, maybe create a new table as done in step one, disable foreign keys, apply the indexing from the first table, drop first table, then rename as follows:

mysql> delimiter //
mysql> CREATE PROCEDURE RecreateTable(IN TableName)
    -> BEGIN
    ->   // SHOW CREATE TABLE TableName; -> use results to recreate indexes, keys and such into variable ==> ALTER_TABLE_STRING
    ->   CREATE TABLE temp_table AS SELECT * FROM TableName;
    ->   foreign_keys = 0;
    ->   ALTER TABLE temp_table + ALTER_TABLE_STRING
    ->   DROP TABLE TableName;
    ->   ALTER TABLE temp_table RENAME TableName;
    ->   foreign_keys = 1;
    -> END//

Is this possible and reasonable? I have a production database that accepts JSON posts from hundreds of offices every 5 minutes. If I were to take the system down for a full backup / restore (which would take 10-15 minutes) it could cause some of those offices to go offline and wouldn't come back for a few hours or days even. If there was a solution like this that could be done as a regular job, that would be better.

I tried the first step on a table that is currently 447GB with 300,000 rows. It took 15 seconds and dropped the size to 485MB. Altering the table from there would take a few milliseconds probably, which would make this a production ready process to manage InnoDB sizes without a full backup.

Any tips? Has this problem already gotten a solution I don't know of? Better ideas?

Answer 1

As for the ALTER... ALTER TABLE ... ALGORITHM=COPY; will do the alter, and make sure to also copy the table over, thereby effectively doing an OPTIMIZE in the process.

TRUNCATE loses the data; do you really want to do that?