velospace blog

The velospace forums launched in late October 2008. Between then and Thursday February 10, 2011 the forums were visited by users with over 1.9 million unique IP addresses. Vanilla, the software that the forums run on, logs each of these IP addresses into a table called “IpHistory.”

The IpHistory table is used to, among other things: calculate user statistics, provide data for the “Who’s Online” sidebar, and so on. The point is that the table is regularly used on the back end. The IP history information is very important from a server administration perspective because we track “bad” users by IP (and other patterns and metrics).

However, with 1.9 million rows being searched every time statistics or the “Who’s Online” sidebar was being called created a huge strain on the velospace server. The MySQL process would start gobbling up gigabytes of RAM, when under a normal load the process would only use around 400 megabytes of RAM. The solution was to move the 1.9 million records out of active use into an archive, which we can still search and use for server administration. We then flushed the IpHistory table, removing all its data.

velospace popped back up immediately after flushing the table. It took approximately 12 hours of troubleshooting to trace the problem down to the IpHistory table. As detailed elsewhere on this blog, the Drupal side of things has hiccups once in a while – though things have been quiet since we upgraded to the most recent version of MySQL. In this case, it took several hours to confirm that the Drupal side was not the source of the problems.

In addition to cleaning out the IpHistory table, some tweaks were made to the site’s my.cnf file, and to some of the Drupal modules we have running. We hope the changes make the site more stable.

- Greg