My index "optimizations" to save space last week are causing more issues, despite my research. Decided to reindex the main auction table yet again, to be even more like how it was before I broke it. Hope I have enough space.
This time, I'll leave the site open, since reads will still be supported during reindexing. Updates will be queued until the reindexing is done.
Found out that the InnoDB Plugin is a drop-in replacement to the built-in InnoDB engine in MySQL, and supports fast index creation. Great, so this time I don't have to do that stupid table copy! For some reason, my version of MySQL (5.1.56 on Fedora 13) doesn't have the plugin included, even though it should be there. Shiny. Had to track down an appropriate RPM for the MySQL I'm using, and extract the plugin file. I was smart enough to try this on my dev server first. Worked there. Did the same method in production. Worked again. Now I'm using the snazzy InnoDB plugin. Hooray.
So I go about dropping the item index and creating it anew. Oh, what's this? It's doing a table copy anyway. Yay, thanks for wasting 2 hours with promises that can't be kept! Ugh. I cancel that, realizing I don't have enough disk space to do an entire table copy. Now I've scripted deleting the auction data older than 9 days ago. Hopefully that'll give me enough space for the new copy.
Site should stay up during the change. Updates will be iffy. End result is that this morning's laggy searches, causing max_user_connections overflows, should stop.
I also wrote and am running a script that checks every minute for web queries that last longer than 90 seconds, and kills them. Usually one query will get stuck, and lock enough tables/records (on a read? shrug.) to make everyone else wait. Killing those queries is not the nicest thing to do, but at least it doesn't cause problems for everyone else for hours.
August 11, 2011: Curses, InnoDB!
No comments yet