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