A music catalogue went down in September. Not slow — gone. Pages timed out, the database would not answer, and when the dust settled the old server's data was unrecoverable.

I spent the next few weeks getting it back online. Here is what that actually involved, because "restore from backup and you are done" is not how it went.

The crash itself

The box was under a load average north of 300. Restarting services brought the site up for about forty minutes at a time, which is not a fix — it is a timer. The useful thing it bought me was a window to read logs instead of guessing.

That turned out to be harder than expected: the site had no logging of its own. Everything was landing in a shared log mixed with other traffic, and because a CDN sat in front without the right configuration, every visitor IP in those logs was the CDN's, not the visitor's. The outage had been invisible for months because the instruments were wrong.

The actual cause was one query

Once the logs were readable, the pattern showed within an hour. Every song page was picking its "related songs" by asking the database to randomly order the entire table and then keep ten rows. On a few thousand rows that is harmless. On four hundred thousand, the database builds a temporary copy, assigns a random value to every single row, sorts all of them, and discards almost everything — for one page view.

Multiply that by crawler traffic and there is no CPU left for anything else.

The fix was to stop asking the database to do the shuffling: pull a small set of candidate IDs using an index, randomise them in application code, then fetch only the rows actually needed. Same result on screen, a fraction of the work underneath.

I also added two limits at the database level so a single expensive query can never take the whole instance down again — a cap on concurrent connections, and a hard ceiling on how long any statement is allowed to run. That second one converts "the server is dead" into "some queries failed", which is a much better Tuesday.

Rebuilding without losing the index

Here is the part most people underestimate. For a catalogue site, the real asset is not the files — it is the URL space. Hundreds of thousands of addresses that search engines already know, rank and send traffic to. Rebuild under new URLs and you are starting from zero, no matter how good the new site is.

So the rule for the rebuild was simple: the schema can change, the code can change, the URLs cannot. The new system was shaped around the old addresses rather than the other way round, and pages were republished gradually instead of all at once, so crawlers saw a site recovering rather than a site that had been swapped out underneath them.

Errors that were hiding in plain sight

Along the way a few older problems surfaced that had nothing to do with the crash — a misconfiguration that was quietly returning a permission error to certain requests, duplicate records that had been multiplying through automated imports, and missing database indexes that nobody noticed while traffic was low. None of them caused the outage. All of them were making recovery slower.

Caching came last

Caching was the obvious thing to reach for on day one, and I am glad I did not. Putting a cache in front of a broken query layer only hides the bug until traffic shifts. Once the query was fixed, caching was worth adding — response times dropped from around a tenth of a second to effectively instant, and server errors served to crawlers fell off a cliff.

What I took away
Fix your instruments before you fix the problem. Unreadable logs cost me more time than the bug did.
Random ordering at the database level is fine until it suddenly is not, and the threshold arrives without warning.
Put a hard time limit on queries. It is one setting and it buys you a different class of outage.
When you rebuild, your URLs are the one thing you are not allowed to break.
Cache after the fix, never instead of it.

The site is Raagtune , a music catalogue I Helped. It is online, and the logs make sense now.