Doug's Home On The Web "Now monochrome monitor compatible!"

It's that time of the year again, to clean out Anthrocon's ride share and room share forums.

I dislike deleting old content, because that just causes lots of bad links from Google (and possibly other sites). Not to mention that my logs fill up with 404s unnecessarily. Instead, this SQL will do the job nicely.

CREATE TABLE temp_nids (nid INT(10) UNSIGNED);

INSERT INTO temp_nids (
   SELECT DISTINCT nid FROM term_node WHERE nid IN (
      SELECT n.nid FROM node AS n
      LEFT JOIN term_node AS tn 
         ON n.nid=tn.nid
      LEFT JOIN term_data AS td 
         ON tn.tid=td.tid
      WHERE 
      (
         td.name LIKE 'Room Share%' 
         OR td.name LIKE 'Ride Share%'
      )
      AND FROM_UNIXTIME(n.created) < '2011-07%'
   )
)

DELETE FROM term_node WHERE nid IN (SELECT nid FROM temp_nids);

The innermost SELECT statement will return the posts made from those forums before last year's convention. It is then wrapped up in a SELECT DISTINCT just to make extra sure that we have valid NIDs. That's probably not strictly necessary, but there aren't any major performance issues with smaller databases, either. :-)

All of those nids are inserted into the temp_nids table, which is then used by the DELETE statement at the end to remove all terms from those nodes, effectively "detaching" those posts from the forums they were originally made in.

End result: the posts aren't removed from the site, just from the forums.

Looking back after this morning's stampede, I thought I'd share with folks how the webserver held up, since I know I am not the only geek out there. And, truth be told, I was a bit nervous myself, since I wasn't quite sure just how much traffic we would get and if the webserver would survive, or turn into a smoking crater.

Well, here's what we got:

The first hump is a manual backup I did last night. The second is the automatic backup that runs every morning, where the database and files are rsynced to a machine at another data center. The third hump at 9 AM was when we opened hotel reservations. 1.4 Megabits/sec doesn't look too bad, until you look at:

The 336 simultaneous connections a second was far more interesting. That's about 16 times the normal number of connections to the webserver.

So, what were the effects? Let's look at MySQL first: