The Lospec Server Slowness Has Been Resolved

If you're a daily Lospec user you might have noticed some slowness issues with requests lately. The site has always had some occasional slowdowns where a request might take a few seconds, but last week it got to a point where at some peak times they were taking minutes, if they ever got returned at all.

Fortunately I think we've implemented some optimizations that seem to have greatly improved things, and the site is now far snappier and is working more like it used to.

This problem was a bit hard for me to diagnose, as Lospec previously didn't really have any performance monitoring aside from the basic hardware usage charts and error logs.

The first step to solving this was implementing a performance monitoring service called newrelic, which not only gave more detailed hardware monitoring, but also easily hooked into my code to report response times, and even track custom events. It was super easy to set up and I was able to do enough under their free tier to solve this issue. I'm not affiliated, just love the product.

At first it seemed the issue was trying to read the homepage from the harddrive for each request. Perhaps this was part of it, but after changing the code to keep the homepage in memory, the problem persisted.

It soon became pretty clear that the problem was actually not on the Lospec server, but with requests to the database. At first it looked like all requests were slowing down and maybe the database was just not powerful enough. But the database is already by far the most expensive part of hosting Lospec, so I was hoping to avoid having to increase its resources. That would most likely only be a temporary fix anyway. I then started gathering metrics on individual database queries to see which ones were the slowest.

I sat down with the lovely Dannflor, one of our biggest Patrons and now a close friend, and he helped me start to dig through some of the code and analyse some of the database queries. After a few hours we had made some minor improvements to the queries and configuration of the database, but the slowness issues persisted. He did however teach me one very important skill which was to get a printout of exactly what the database was doing for a specific request, and analyse that to see which steps were taking the most time.

After some more iterations (and a whole day), I finally had found what I believed to be the smoking gun: for every request to one of our filterable search forms, the database was first grabbing every single document, then looking up the user who submitted it. This isn't a problem when doing very specific searches, like for a certain tag, or viewing a submission queue, but when viewing without filters, it became a huge problem. 

The biggest cause was with requests to the gallery, which has grown to over 10,000 accepted pieces of art (which is awesome by the way). But this meant for every single request, even fetching additional pages, the database was loading 10,000 documents and then matching a user to each one. This didn't always take too long, sometimes just a few seconds, but when a lot of people were browsing at once, the database server would get overloaded and the CPU usage would reach 100%, and then every database request on the site would slow down (which is almost every page).

I spent a few hours shuffling some things around and rewriting some code so that the user documents wouldn't be needed for sorting. After pushing out that fix, there was a huge drop in the average time spent on each database request, and the site started responding like a normal website again. I think it's safe to say the problem is now resolved, which is a huge relief.

Thanks again to Dannflor who helped out a ton with solving this issue, and to everyone's patience with us we worked out this issue. Long live Lospec.