This morning, when I crawled out of bed and tackled the morning coffee sites to be followed by 3d work before our busy day commenced, I noticed that my server was slow.
There goes my 3d time again, sigh. Now I would have quickly investigated, if it hadn't taken FOR FREAKING EVER to log into the server, and I had to leave before it had logged in, and unsurprisingly the pipe had broken and annoyingly the problem hadn't resolved itself when I was able to tackle it again later in the day.
I eventually logged in again and my initial readings of show processlist; and sudo tail /var/log/mysql-slow.log indicated that it was the search on Red Planet that was causing the problems. I did some reading up on that and found out that Drupal's core search doesn't scale well to large numbers of nodes and a good replacement is Apache Solr (and there's a module for integration). However Drupal doesn't scale to millions of nodes, RP only has low thousands. I also discovered old thread from a few years ago where people were reporting MySQL wigging out and eating cpu cycles for no apparent reason and one of them writing a script to make it restart when it hit a certain threshold as it seemed to stabilise after a restart. The thread was old enough that I figured that particular problem had to have been fixed by now. After discussing with the codemonkey, he agreed that the type of search it appeared to be doing could take a while, but then asked if the Drupal search was doing [here he rattles off an sql query and I stare blankly at him] and asked how many things the search was indexing. He asked to see these queries and so I VERY SLOWLY showed him the processlist and tailed the log file again, only this time it was showing that the activity feed on RP was causing the problems.
He guessed that maybe some spider or bot or something was clicking every link on the page as the same queries were being repeated, or perhaps browsers were timing out and causing the queries to be repeated.
I decided to set time-based caching for the front page views blocks on RP just in case it helped (with the amount of activity that site has, the activity feed isn't exactly live anyway). I logged in to RP...went and had a shower...refreshed the page when I got back...still spinning.
In desperation, I sudo service mysql restart-ed.
The server did nothing for a little while.
I wondered if I'd made things worse as the page I had been attempting to load suddenly partially loaded and then abruptly ajax errored because the database had gone away.
Then the terminal said mysql had restarted.
And everything was back to normal speed.
Set a caching time of 1hr on the front page blocks on Red Planet just in case.