0

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).

Problems encountered:

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).

Improve this question
15
  • There has to be "some" downtime. At a minimum the application has to be restarted to use the new server (unless you have a proxy in between, or use floating IPs, etc). Do you have backups you could restore? Then setup replication of intermediate changes? Is the storage direct attached, or a SAN? If SAN, can you mirror the LUN and split? What about the application - can it handle having a subset of the data (last years worth) moved first? No options for more local storage?
    – Kevin Bott
    Commented Oct 16, 2018 at 16:46
  • I've updated the original question - hope I do answer your questions
    – LooseFlareBlast
    Commented Oct 17, 2018 at 1:14
  • 1
    Isn't this what replication is for? If you were to use replication you could switch masters.
    – gview
    Commented Oct 17, 2018 at 1:25
  • Replication is an option, but we changed the database schema (we sharded the table now), so the replication option only does is to increase our storage capacity (when you said replicate, I guess you mean replicate into another server with much bigger storage capacity)
    – LooseFlareBlast
    Commented Oct 17, 2018 at 3:11
  • 1
    It sounds to me like you are trying to reinvent a form of backup/restore/sync that is inherently problematic since you don't want to quiesce your database for a period of time. This is why replication is advantageous because replication inherently has the ability to synchronize using the transaction logs. Since you have a large dataset, if a snapshot is not feasible, you might want to look at a Percona XtraBackup or possibly MyDumper which is doing what you are trying to code for backup/restoration.
    – gview
    Commented Oct 18, 2018 at 14:46

2 Answers 2

Reset to default
1

Are you using LVM on Linux servers? In that case, you can have a minimal downtime solution:

Switch the database to master, stop the server just a few seconds to make a LVM snapshot, start the master again, mount the snapshot in a temp directory to copy the data to the new (bigger) server and then configure a slave with that. Then you can start the slave, let it sync with the master until is up to date and finally promote the slave as master.

Detailed howto: https://www.percona.com/blog/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/

Improve this answer
4
  • Does that work with the current disk usage? "94% full now, 6.4TB out of 6.9TB".
    – Kevin Bott
    Commented Oct 17, 2018 at 19:51
  • @KevinBott a LVM snapshot only saves the changes, so you need sufficient space to store the changes in the database for the time that will last the copy. I don't know what rate of modifications he has in the server, but maybe he can calculate in advance if 0.5 TB will be enough
    – Gabriel Gomiz
    Commented Oct 17, 2018 at 20:23
  • @GabrielGomiz We are using LVM. Actually, we do use ZFS for our database, which allowed us to take snapshot. However, the snapshot grows too fast (500MB in a minute) from the ongoing changes in the database. The LVM approach will probably face the same situation unless I misunderstood the blogpost.
    – LooseFlareBlast
    Commented Oct 18, 2018 at 11:59
  • Sounds like the LVM approach would work only if you can copy the snapshot in less than 16 hours. What's the bandwidth? How fast can you copy 6+ TB? (A month ago, I would be agree that LVM is the answer.)
    – Rick James
    Commented Oct 19, 2018 at 23:30
0

You may be able to use OPTIMIZE TABLE. Here's the trick. Sort the tables by size, OPTIMIZE the smallest first. If you are lucky, you will free up enough space before you get to the task done.

If you have deleted a significant fraction of a table, then OPTIMIZE will not require as much space as the original table.

Another trick. This might do the job. (I assume you are using InnoDB?) Instead of using OPTIMIZE, use ALTER TABLE ... ROW_FORMAT = COMPRESSED. This may shrink tables by a factor of 2. (But it may not be less useful on some tables.)

Improve this answer
1
  • The other tables are too insignificant in size to do OPTIMIZE. I'll take a look and try out the COMPRESSED method, thank you.
    – LooseFlareBlast
    Commented Oct 23, 2018 at 3:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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