Database Woes: Use your Tools, kid!
I've been wrestling for a few days with problems with one of my websites, related to database problems, spam, and other junk.
KSCareers.org is a site that indexes online college-level courses and sections available from Kansas colleges and tech schools -- organizing them by the career clusters that they expore. The site involves a LOT of data, and a steady stream of new data that needs to be imported -- new courses and sections on a regular basis. I'm making good use of the Table Wizard and Migrate modules to import that data, and I have been trying to go through that process again for the summer and fall courses on the site, but discovered a HUGE problem
My Database was too big.
Here's the thing. I actually perform my updates on my local machine, then use backup-migrate (not the same thing as Migrate) to move the updated database to the live server. This keeps me from making a mistake that hoses the production site, which is a very good thing.
BUT, I've discovered a few problems that have kept Backup-migrate from working -- my database was just too big.
I discovered a few problems -- for one thing, I had Forums turned on. I wasn't doing anything with the forums, but they were turned on, and had been discovered by the spambots a few months back. There were HUNDREDS of spam posts in these hidden forums, and that meant a bunch of totally unnecessary user accounts and content on the site. I spent a good chunk of time deleting that spam. I'll also be installing Badbehavior and Antispam to try to deal with this (since I've had problems with Mollom being useful behind school firewalls)
But my database was still huge -- and I was seeing a ton of overhead when I looked at the database in Phpmyadmin. The solution, as it turns out, was pretty quick and easy, and it took place within PhPmyadmin: Optimize the tables. With so much content having been created and deleted, and other junk going on with the site, I was seeing a ton of "overhead" in my tables. But I was able to reduce the size of my database by a factor of ten with a quick "select all, optimize tables" routine, which reduced my badly screwed up table by a factor of 10. This makes a HUGE difference in the responsiveness of the site, and it also has reduced the size of the database to a level where Backup-Migrate doesn't choke on it anymore.
