How to free up hard drive space after deleting millions upon millions of records in mariadb

Question

We have an application where we are receiving information seconds apart, we record this information in a table that we call events, which currently weighs 240 GB, this is the largest we have so far.

Some time ago we deleted records to keep records from a specific date. Not long ago we realized that the server that stores the DB was filling up the disk, after deleting millions of millions of records, the disk space remained the same.

Searching the internet we found that executing the following query ALTER TABLE tablename ENGINE = Innodb; would free up the used space of the deleted records.

But we run into the problem that because we have tables that are larger than 200 G, the command takes a long time to execute, causing the new information to not be inserted in time due to the blocking that causes it.

The option we find is the following:

• Make a replica of the database (Master - Slave), from what we saw here we would have to make a backup of the master and pass it to the slave so that it begins to replicate from where it happened, for this the detail we have is that no we want to turn off our current server so that our service remains functional, but we also do not want to have any loss of information. We have a backup of our server a day before, we could use this, but how do we recover the information that was not inserted in a day, what does that backup have if it was created and from where was the slave configured?
• Make backups of our current database and upload them to a new server so as not to use the ALTER TABLE tablename ENGINE = Innodb command, for this we would have to turn off our server.
• Execute the ALTER TABLE tablename ENGINE = Innodb command, we perform a test and in the 10 GB table that we have as a test, it took almost 25 minutes to perform.

Is there a more optimal way to free up space without having to cancel our service and without losing information? Or in the event that it has to be done in the shortest possible time?

This query ALTER TABLE tablename ENGINE = Innodb knows if it locks the tables.

Environment information in production: Ubuntu Ubuntu 16.04.3 LTS mysql Distribution 10.1.21-MariaDB All tables are of type Engine = Inodb

Thank you very much

Answer 1

It is correct that InnoDB tablespace files never shrink. There may be unused space within a large file after you delete a lot of rows, but the file still occupies a lot of space on your filesystem.

The only way to make the file smaller is to copy the remaining data to a new tablespace and then drop the original tablespace. This is what the ALTER statement you described does. Alternative forms of this statement are:

ALTER TABLE <name> FORCE;

OPTIMIZE TABLE <name>;

They all do the same thing. I like the one with FORCE because it's most clear to me what it's doing.

Copy a lot of data to a new tablespace takes a long time, and blocks access to the table while it's running.

What I've done at some jobs in the past is to use the free tool pt-online-schema-change to run this task without blocking access to the table. It may in fact take longer than it does to run the ALTER TABLE directly, but since it does not block access, that does not cause an outage, so no one gets upset that it takes a long time. We have run this on tables much larger than the one you describe, so large that it took days to complete.

You use MariaDB. I don't, and I have never personally used pt-online-schema-change with MariaDB. But I understand from other users that it works the same, since MariaDB is similar enough to MySQL that the features needed by pt-online-schema-change work the same. MariaDB is not the same product as MySQL, but in this case it is close enough.

Answer 2

Plan A: Create a new table, copy the rows over, swap name.

Plan B: See several related techniques here: http://mysql.rjweb.org/doc.php/deletebig