So, the site was down for a couple of hours today, and it is going to be acting a little funny for the next little while. You will notice that pages will sit not loading for up to 20-30 seconds at a time, and then suddenly everything will work fine for a short while. This is expected and, sadly, appears to be unavoidable.

Shortest version: PHP is a hateful language and the source of all misery in the world.

Short version: don't try to delete 3.5 million rows from a table in MySQL all at once, especially one that's accessed every time someone visits a page on your website.

Longer version: When someone visits the site without a cookie (for instance, someone visiting the site for the first time, someone who has logged out and back in, spambots, viewing from seperate browsers, whatever), Drupal generates a random number for the client to use as its session identifier. It shoves this number into a database table, along with a timestamp of when it gave that number out. Every so often, it is meant to do a search for old, expired sessions, and clean them out of the database, because otherwise it would just grow and grow forever and become monstrously full of useless data.

This has not been happening. Ever. In the five years the site has been running.

It turns out that the "garbage collection" process controlled via an internal PHP configuration setting, which Debian Linux turns off because the way it works is kind of dumb (instead of being scheduled on a predictable basis, as would be sensible, it happens at random when someone tries to view a page). Unfortunately, the alternative they set up doesn't work with Drupal.

So I turned it on. And tried to load the site. And then it turns out that while it's cleaning up the session table, the site can't access it. And the site needs to access it on every pageview to see if you're logged in or not.

Well, I figured it had a lot of data to crunch through, so I decided to let it do its thing. And I waited. And waited. And 2.5 hours later it still wasn't done. I looked around online and decided that maybe the more sensible course of action was, instead of sorting through and deleting 600mb of data all at once, maybe I could write a little script to just delete a little at a time, and let other things talk to the database once in a while. Like my website.

So that's what it's doing. Unfortunately there's so much data to wade through that even deleting 1000 rows at a time takes like 20 seconds. This process will hopefully speed up as more old sessions get deleted, until eventually, sometime, maybe tomorrow?, there will be no noticeable pauses at all when browsing the site. Won't that be great? I'm looking forward to it.

Anyway, thought I'd let people know what was going on.