Migrating ~500k documents ~22GB of data from RethinkDB to MySQL for Stardew Planner

So today is the day I took it to my mission to make the migration finally happen.

Background: So I’m the guy who made a online planning tool for Stardew Valley video game. Read PCGamer article. For some reason, I chose RethinkDB. If I recall correctly, the thought process was something in the lines of: “each plan is a big chunk of JSON, so why not use document based DB”. And as I had already had my bad expierence with MongoDB, I thought to try out RethinkDB. It was all nice and sunny in the start but about a year and around 300k plans later, the problems started to occur.

First thing that I noticed, was that RethinkDB was eating memory due to large indexes and cache. So I had to configure the server to restart every 6 hours to keep it running smoothly. This was the moment I knew, I needed to migrate my data away from RethinkDB. Time passed and after another 200k plans the server started to act up again and without much of investigation needed, it was clear that the problem was yet again RethinkDB. But how does one migrate 500k documents (worth around 22GB of data) from nosql database to sql database? I thought of running rethinkdb dump and then restoring it locally to start trying the migration possibilities, but this proved to be impossible as the rethinkdb dump command was so slow, that I could have taken a flight to new york (planner is hosted there), ask them to put my server data on USB stick and fly back home to see that the dump was 1% done.

So instead I wrote a migration script which basically worked like this: Take a row out from rethinkdb (and delete it), then insert it into mysql. Rinse and repeat 500 thousand times. If you ever worked with data migration, you would already know that this is not a correct way of doing it. If the inserting to mysql fails, we have a data loss because it has already been deleted from RethinkDB. Luckily my data is not that impportant and losing a plan or two from 500 thousand wouldn’t really matter much. So instead of making the migration slower, I just delete them right away.

You might also think, why would you even delete them from RethinkDB, just ask the data using offset which you would increment after each row. The fact is, that it’s not possible, because running r.offset(30000) will take tens of minutes if not hours. That is why I’m always getting the last row and deleting it (basically running .pop() on the dataabse). It’s fast and if my calculations are correct the full migration should take anything from 1.5 hours to 3 hours.

If you are interested how the script looks like, then feel free to check it out on planners GitHub.

~15:00PM Made a new instance from one of the snapshots of live server in DigitalOcean. Insalled and set-up mysql. Started migration.

15:52PM Migration stopped at 194167 rows.

First things first, I went to my migration log and tailed it hoping to get back a readable error. To my luck, got back a very straight forward mysql error:ER_RECORD_FILE_FULL Even though I guessed what it meant, I Googlet it just in case to make sure I know what is going on and as you might also think, this is error is related to either mysql file size configuration cap or actual disk space usage.

A quick df showed 100% of disk usage on the server. So easy enough right, just ran out of disk space.

But when thinking little about it, it doesn’t make any sense. Migration script DELETES the record from RethinkDB after having it migrated into mySQL. So logically, when mySQL database is getting bigger, RethinkDB must get smaller. Even though there might be a difference in size per row depending on how mysql or rethink stores the documents, I bet it can’t be this big. So I went to investage and without much hassel I came to conclusion that RethinkDB is not deleting the rows from disk data:

Live:

Migration:

In words, the migration server RethinkDB table holds around 310k documents (which is correct, as about 190k is migrated to mysql, so these have been deleted from rethinkdb). But the rethinkDB file size is still 22GB, just as in live server. In which the RethinkDB table document count is ~500k.

So how exactly are these files the same size? Is RethinkDB not writing deletes into the file, or rather actually deleting the data from it…

So after a bit of investigation I found a command in RethinkDB that shows the statistics for given table and I found something here that is called “garbage_bytes”

So garbage_bytes is around 2.2gb and preallocated_bytes is around 7.5gb. Now how do I get RethinkDB to free these…? To my annoyance, there is very little information about these statistics on the RethinkDB documentation page, not sure why it surprises me though.

16:30PM So I ain’t got all sunday here to debug this RethinkDB shit. It’s time for a new plan. So i’m going to blow up the current migration server, boot up new one. Create a block volume (40gb?), attach it to the new server, configure mysql to hold it’s files on the block volume, run the migration again, ???, profit without disk size problems. This also allows me to use a smaller server later, because with block volume I won’t be needing a 40GB disk space droplet. The planner itself can easily run on a smaller machine.

16:47PM So I’m done with this block volume… and it was super easy, mainly why I like digital ocean very much is that they have a tutorial for these kinds of things and non-system administrators like me can make stuff happen.

1. So I destroyed by migration test droplet and created a new one from one of the backup snapshots I have from live.
2. When creating the new droplet, DO let me already choose a block volume for it
3. After the droplet had been launched, I went to Droplet -> Volumes -> Show configuration and copy & pasted some command into the terminal.
4. Then I followed this guide to move mysql data volume to the block volume https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04
5. Profit, without black magic. Time to start the migration again.

~22:30PM So the migration is finished. It looks like everything checks out and is well. I already though of using this same block volume for tomorrows backup, but I’ll rather go with plan. I’ll put the planner into maintenance mode, create new snapshot of the droplet and start the migration on there with “fresh data”. Later I’ll downsize the droplet because I don’t need so much disk space anymore.

Next day ~10AM Took fresh snapshot from live, added warning for upcoming maintenance, prepped new droplet with block volume for migration and fired it iup.

16:11PM Migration seems to have been finished. Total plan count is 510348. I’m now going to try to squeeze the planner itself into smaller server and then destroy live and move fully onto mysql. Will also do some checks after that to see if all the plans are available (going to randomly check top 100 farm links).

16:52PM DONE! Migration done and seems it all went well. Well, now to hope the MySQL will hold up nicely, but I’m having good feelings about it.

sudo apt-get remove rethinkdb …