Aawaara

Migrations with Node.js

Given the minimalist node philosophy this doesn’t come with the package, and given my rails background it’s hard for me to imagine a world without migrations. In case you feel the same way, below is a quick start guide to get you up and running with migrations in a node app within two minutes:

Break it down!

Wiser people have said - if you have a problem, break it down! If the problem is bigger, break it down further. This is true both philosophically and technologically.

I’ll describe a technological incident here in the hope that it could help someone else as well (when they land in this tricky situation, and are looking for a quick resolution). We use delayed_job gem in our web apps for asynchronous processing. It has performed pretty well for us all this while, but one fine day it caved in. We had a barrage of jobs into the dealyed_job table, and the job processing daemons were just unbearably slow. The reason being that there were 600K jobs in the queue, and the rate at which dealyed_job daemons fire the mysql queries every 5 seconds was too much I/O for the database server. Soon the slow query logs were flooded with update queries. I tried building a few indexes to speed up some of those queries, but the improvements weren’t significant enough. What now? I filed a bug on the github repo in hope of greater good for future users, but that wasn’t sufficient enough. Those jobs in the queue were time sensitive in nature, and at the current speed would take days, if not weeks to clear out. We needed a quick fix now, and a long term fix (better solution as compared to delayed_job) later.

Solution (quick fix): I went back in time and realized that delayed_job had performed well for a few thousand jobs in the table. That’s it - we needed to pause, and break it down a bit - in this case, the mysql table. So we copied all the data from delayed_jobs table to a new table (backlog_jobs) and cleared the original table (the delayed_job table from where the jobs are picked). Then we copied the first 5000 jobs from backlog_jobs to delayed_jobs and deleted them from backlog_jobs. They were processed pretty quickly in a matter of minutes. Wallah! This was it. Then we wrote a small cron than runs every 1 minute and checks the count of delayed_jobs table. If there are less than 100 rows, it repeats the process, i.e. copies the next batch of 5000 jobs, and deletes it from backup_jobs. This way I got back to the older performance levels. Below are some handy queries that you will find useful:

# To create a copy of table
CREATE table backlog_jobs like delayed_jobs;
INSERT into backlog_jobs (SELECT * from delayed_jobs);
DELETE from delayed_jobs;

# To copy jobs in batches
INSERT into delayed_jobs (SELECT * from backlog_jobs order by id asc limit 5000);
DELETE from backlog_jobs order by id asc limit 5000;

So the next time you face database related issues, break it down a bit. That’s what database partitioning is all about, and that is what everything finally comes down to ;)

Would like to hear what I may have done wrong to start with, and what I could have done better. Don’t hesitate to teach me a lesson (pun intended)… :P Philosophically speaking, life is an eternal learning process ;)