Packr started as a proof of concept three and a half years ago. It was my second iOS app entirely made with Xojo. The project back-end (database and API) initially relied on a server that was absolutely not made for production use. Because of lack of time, knowledge and certainly a big amount of unconsciousness I kept on delaying the migration to a production server and a managed database.
It was urgent to migrate Packr API and database from an aging 32-bit CentOS 6.9 server running on Atom N2800 with 2GB of RAM and 500GB HDD to some optimized modern equipment.
At the time of this post, Packr has grown to be an enormous success for a solo developer- 650,000 downloads from over 100 countries, featured multiple times by the App Store and a Xojo Design Award in 2018. Unfortunately it has also grown into a database beast: +15GB of data including one table that holds more than 40 million records.
Preparing The Migration
Dumping the whole database takes over two hours because of the poor resources of the server, an old version of MySQL, badly optimized InnoDB tables and the amount of data to transfer.
Locking all writes to the database while migrating to a new server for two hours would mean stopping 1,000+ people from using the app (based on average hourly active users over the past month). I had to keep in mind that iPhone users tend to post negative reviews about an app as soon as it stops working. But very few of them post 5⭐️ reviews when everything works well. Thus migrating over two hours is a no-go, Packr’s average rating of 4.7 would take a big blow. The migration should be done as quickly as possible with absolutely no data loss.
After setting up the API on the new server and creating a new database using the latest backup, I tested the app in the iOS Simulator and on my own device to make sure that everything worked correctly. Of course… It didn’t. When migrating from MySQL 5.5 to 8.0, there are many caveats that prevent queries, views and inserts from performing correctly. A few days later, everything was fixed and the app was running smoothly on the new API and database. By then I still had more than a week’s worth of updates/inserts that were still lying on the old server and not available on the new server.
Speeding Up Migration
The next step was to identify exactly which database tables are often changed, hereafter referred as dynamic tables, to only copy new inserts and updates from old server to new one. One might wonder why I do not mention deletes. Packr API never deletes anything. Instead of deleting, all tables have an ‘archived’ column set to 1. This allows for data recovery in case someone mistakenly deletes an item in the app.
After identifying all dynamic tables, I was able to build a small Xojo helper app to help migrate everything to a new database. One of the most important functions is to only dump data from the dynamic tables, then re-import to the new database. The Xojo helper app would run the following command for each table:
/Applications/MySQLWorkbench_6310.app/Contents/MacOS/mysqldump --host=IPADDRESS --port=3306 --user=USERNAME --set-gtid-purged=OFF --protocol=tcp --lock-tables=FALSE --compress=TRUE --default-character-set=utf8 --skip-triggers --replace=TRUE --no-create-info -w"lastupdate>'2019-09-03 00:00:00'" "DBNAME" "TABLENAME" > /Users/jleroy/dumps/db-migration-20190903/Diffs/TABLENAME_diffs.sql
The ‘magic’ parameter is -w”lastupdate>’2019-09-03 00:00:00′”
Each table in the database has the following column:
lastupdate timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP This means that the dump will export using the following query:
SELECT * FROM tableName WHERE lastupdate>'2019-09-03 00:00:00'
I do not know if having such a column in each table is a best practice, but it definitely made things easier. If your database doesn’t have a similar column, MySQL triggers are the solution, keep reading for more information.
I was quite confident a year ago that this process would be very fast. Unfortunately one year later, with 20 million additional rows in the biggest table, the process was too slow: it took more than 30 minutes to dump data from the last 24 hours, then another 10 minutes to import.
In order to speed up the dump, I decided to create a clone of the DB schema that would only hold updates and deletes of the dynamic tables. Adding ‘After INSERT’ and ‘After UPDATE’ triggers to these tables would enable having an exact copy of altered rows in the new schema.
Triggers:
DELIMITER //
CREATE TRIGGER tableNAME_inserts AFTER INSERT ON tableNAME
FOR EACH ROW BEGIN
INSERT INTO NEWSCHEMA.tableNAME select * from tableNAME where ID = NEW.ID;
END;//
DELIMITER ;
DELIMITER //
CREATE TRIGGER tableNAME_updates AFTER UPDATE ON tableNAME
FOR EACH ROW BEGIN
REPLACE INTO NEWSCHEMA.tableNAME select * from tableNAME where ID = NEW.ID;
END;//
DELIMITER ;
In the case where your API also allows deletions from any table, it is necessary to create an additional table to keep track of deleted IDs.
The trigger would then be:
DELIMITER //
CREATE TRIGGER tableNAME_deletions BEFORE DELETE ON tableNAME
FOR EACH ROW BEGIN
INSERT INTO NEWSCHEMA.tableNAME_delete select * from tableNAME where ID = OLD.ID;
END;//
DELIMITER ;
The Stressful Moment
Everything was ready for the migration, but one important piece of knowledge. When should the migration be done? What day and at what time? This was easy information to get, analyzing all API calls throughout the last 6 weeks gave me the exact time where, on average, there were the least amount of connections. Migration was planned for Wednesday morning at 3am UTC+2.
The big day finally came, the original plan was to migrate in less than 2 minutes and then update the DNS records to point to the new server. Thanks to using Cloudflare to manage the DNS records, there is absolutely no delay for DNS propagation. I guess they know how to get things right, compared to many web host companies that warn you that the DNS records can take up to 2 hours to propagate.
Just before starting the migration, the middleware API was set to read-only. Meaning that users opening Packr could still open the app and view their packing lists but nothing could be modified.
After migrating the DB in less than 90 seconds, the DNS records were updated on Cloudflare and a new TLS certificate was installed on the new server. The API was then set back to read-write and I tested that Packr worked correctly on three different devices. Overall, the migration took 2.5 minutes, not far from the initial plan of less than 2 minutes.
If you like travelling and need to organize your packing list, download Packr on the App Store.
Jérémie Leroy has been using Xojo since 2008, he won two Xojo Design Awards in the iOS App category and has released over 10 iOS apps made with Xojo on the App Store. He also released iOSDesignExtensions on Github to help style and polish your Xojo made iOS apps.