System : MariaDB (MySQL 5.5)
Parameters : innodb_file_per_table=1
The database I have now is approaching its full capacity (94% full now, 6.4TB out of 6.9TB)
I can't run OPTIMIZE TABLE to reclaim space as there is insufficient space to hold the temporary tables.
I have prepared another new server to migrate the bulk data over. However, I do not wish to have any downtime during this period.
Hence, I attempted to do the following :
1) In the most utilized and huge tables, I began to delete old/unnecessary records to withstand the incoming insertions (since by DELETING records, MySQL will reuse space)
2) Started migrating of the data over by running a script that queries the records and insert them into the new server by doing batch inserts (every 5000). The reason why I am running a script is because the database schema different in the new server (I sharded the table).
1) The insertion rate of the incoming records is faster than the deletion rate, which means my .ibd table is still growing
2) The batch insert migration script is still too slow and it definitely can't finish in time before the original database server gets full (almost 4TB+ data to migrate)
I can most probably run more migration scripts in parallel, and see if I can delete the old records at a faster rate.
However, is there any alternative approaches I can take or improvements to do?
Edit : The application currently points to the old and new server, and after every a batch of complete migration of records, I'll update the application such that it'll connect to the new server instead. The storage is not a SAN, but directly connected. As we ran out of disks ports, we can't add more to the local storage. Also, we do not have regular backups (a huge mistake at our end).