Ask HN: Server under heavy load. Any downside to this hack?

The server hosting one of our live apps is under high loads and gets into a state where it stops responding to http requests several times a day. These outages last typically anywhere from a few minutes all the way up to half an hour or so.

The major culprit here is MySQL load and we've been working on optimizing this (those interested can refer to my previous thread on this: https://news.ycombinator.com/item?id=6348903).

For now we've found that restarting the httpd and mysqld services brings things back to normal almost immediately.

While we continue to work on a - more elegant - solution, we're thinking of writing a bash/shell script that runs on hourly cron, checks load average (via uptime or /proc/loadavg) and if found higher than a threshold, restarts the services.

Can anyone think of any downside to this (used at least as a temporary measure)?

Good call - thanks :)

We are actually more than worried too, because we _know_ we're losing plenty of requests daily so trying to cut down the losses temporarily while we figure out a more long-term solution.

For the rest, this thread might give you some more info on the issue: https://news.ycombinator.com/item?id=6348903

>You need to examine the restart process and analyze

Like I said in OP, we've identified MySQL to be the primary bottleneck and are already working on resolving this.

>whose transactions are abandoned.

At least some saving grace here... we're not selling anything... it is purely a content site

Thanks for your input.

I've had load issues in Postgres, not MySQL before and it was due to autovacuum running on tables getting updated/inserted frequently. I'm not sure what the equivalent in MySQL is, but if you have a ton of insert/update queries, consider archiving your tables after a certain period of time, so that your main table doesn't have ton of rows. You can consider sharding of course, but also consider sharding the actual tables in the same database. An insert query on a table with 1000 rows will take much much less time than an insert query on a table with 100 million rows, all things considered.

Also, consider creating a buffer in the application layer that buffers inserts/updates and executes them once as a single transaction, if they don't need to be executed immediately. It puts less stress on the database. Of course, this would require a lot of rewriting in your app, so not sure if you want to go through this route.

Indices are another area. I'm sure plenty of people have told you to optimize your indices, but also consider REMOVING unnecessary indices. Do you have an index on a text column, or multiple varchar columns? Those can be killer after awhile because inserts will slow down. Consider changing indices on varchar columns to indices on an int column by hashing those strings.

A quick suggestion: Install NewRelic (it's free for a certain period), and check out the database transactions that are taking up the most CPU load. Sometimes there's that 1 query you overlooked that is table scanning and could be the main culprit.

Also, are you using Rails by any chance? If so, there are other areas I can suggest.

And please post your server specs. Maybe your VPS just does suck (no offense), and the easiest route is just to upgrade your server.