Well, it’s happened before and it happened again earlier this week. The cron job that keeps track of what photos made the popular throughout the day mysteriously stopped keeping track. Took some investigating, but I figured out the cause and a quick solution to make sure it doesn’t happen again.

Timing Is Everything

The cron job is scheduled to run every minute. The script calls the Instagram API twice, sleeping for 20 seconds between calls. Lately, the calls to the database have been slowing down. Probably a combination of the number of records in the database and the query used to exclude duplicate records from making their way into the table. This is causing the script run long which in turn is causing cron job collision.

Lock it Down

The solution was fairly simple and involves obtaining an exclusive lock on a file and only letting the script run if it’s able to obtain that lock. So far, it seems to be working and should prevent the issue from happening again.

While this appears to solve the cron issue, it doesn’t really solve the root of the problem; why the script is running long and making it more efficient.

So, if you notice a few days of popular images have gone missing in the month of March, this is why. Sorry about that. The really sad news is that, for now, the data is gone. There’s no way to retrieve that information from Instagram (at least, not that I’m aware of at the moment).

Hello there. Welcome to the instaview.me blog. I wanted to set up a place where I can share my thoughts and insights about instaview.me as well as somewhere users can go to check on the status of the site.

Performance Woes

One of the cool things about instaview is the retention of popular photos. Instagram doesn’t (publicly) keep / share these photos with the public so it was one of my goals to keep a record of all of the photos that made the popular page. This is probably one of the more technically challenging aspects of the site. Keeping track of the photos required some kind of persistent storage; in this case, it’s a MySQL database. Since I started tracking the popular photos back in April (April 30th, 2011 to be exact), I’ve accumulate some 1.2 million records. That’s not a lot of data for a database to handle but it will bring any bottlesnecks to the surface fairly quick.

Let me give you a little history. When the site started out, I was running the site (database and all) on a single 512MB VPS (Virtual Private Server). This means that all the processes running had to do so using less than 512MB of RAM. It’s plenty of room for a small (ish) site with moderate traffic. Around November of 2011, the site started hitting the limits of the server and I began to get emails from monitors about things like CPU usage and disk I/O. It was then that I decided I needed to split the application in two and fired up another VPS just for the database. I had roughly 800k records in the database and this seemed to makes things better, but it was short lived.

About a month ago, the database server started experiencing high I/O usage. At first, it was around 8k IOP/s (this is basically the number disk read/write operations per second). I was concerned at first but didn’t notice much of a performance hit to the site so I only half paid attention to the alerts. I was already working on the latest redesign and figured I’d switch to a NoSQL solution (Mongo) which should help speed up the site. Then, about a week ago, the IOP/s headed through the roof. I quickly went from 8k to 20k, 40k, 80k! Yes, you read that correctly. I was completely I/O bound at the database layer. Of course, by then, the site’s performance fell on on the floor.

I quickly migrated the data to Mongo on my development machine and continued working through the redesign. The whole time I kept asking myself if Mongo would really save the site. I was skeptical so I started looking around the interwebs. After reading several posts and atricles about it, I decided to abandon Mongo a couple days before I planned on launching the new design. I chose to tackle the database performance issue and figure out how to make it faster.

Timestamp Columns Are Awesome… Kinda

Using the appropriate data type for your columns is essential to database performance. Timestamp and DateTime types are pretty important for tracking inserts and changes to your data. They index well enough and are usefull for sorting results by time. Where they are NOT awesome is when you are using them in the selection process and need to scan the table for year-month-day. This is an expensive task. My queries looked something like this:

SELECT ... FROM popular WHERE DATE(timestamp) = '2011-04-30'; 

This basically scans the entire table, calls the DATE function on every timestamp column and compares it to the string. This is bad. This is where I got into trouble. It might be somewhat quick on a server with no real traffic, but when you starting getting traffic, it becomes the bottleneck.

The Solution

How did I solve this? It was pretty easy, actually. When I look up popular photos for a particular day, I’m just looking for records that have a timestamp with a year = , month = and day = . So, I created three new smallint columns, indexed them and modified some queries to do the lookup using those columns instead of the timestamp. There’s no computing a timestamp using the DATE function and it’s a straight num=num comparison. It’s faster and easier on the databse server. To illustrate just how effective this was, take a look at the graph below.

This is what my I/O looked like the past several weeks on this server. You can see the exact time I cut over to the new way of querying the data. There’s a huge drop off in I/O operations per second. Here’s some more nerdy graphs from Munin MySQL monitor.

Again, you can see the I/O drop at the database. Also, the buffer pool dropped significantly, as did the slow queries. Semaphores are practically nil and transactions per second have doubled.

These changes should make the server happier for a little while longer at least. There’s always room for improvement, but I think I can sleep better at night knowing that the disk I/O is back to a more sane level.