0

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?

  • Three questions: 1.- How are you measuring the table sizes? 2.- is the innodb_file_per_table variable enabled?. 3.- MySQL and OS version – Jesus Uzcanga Oct 30 '19 at 20:07
  • Great questions. innodb_file_per_table is enabled, so I am seeing the tablesize in the data directory. I am using 8.0.13 MySQL Community Server right now on Ubuntu 16.04 – Alan Oct 30 '19 at 23:27
  • Great answers. Did you try TRUNCATE TABLE end then OPTIMIZE TABLE? Doing so, you won't need to drop and create it again. TRUNCATE TABLE is a DDL statement used to delete all rows in a table. DELETE * FROM is a DML statement and does not affect physical files. – Jesus Uzcanga Oct 31 '19 at 17:19
  • How big is payload? If each of your 6 rows has a 3GB payload, then 18GB is the real size! – Rick James Nov 2 '19 at 1:00
  • And what is the setting of innodb_file_per_table? Please provide SHOW TABLE STATUS LIKE '...' – Rick James Nov 2 '19 at 1:01
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?

  • Thank you so much Rick, this ended up being the perfect solution, not sure why the documentation doesn't promote this over OPTIMIZE. – Alan Nov 4 '19 at 16:46
  • 1
    @Alan - It is a "reference manual". Such a document's job is to list everything that can be done. It rarely gives "advice" on the "best way" to do things. That's my job. (hehe). – Rick James Nov 4 '19 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.