MySQL woes
Got a call from Ubaldo, who is visiting family in Florida… prince.org is down, his site is down, the machine is hosed. I was in the car, driving back from a party down in Mountain View (with the baby screaming, he was hungry, and we were trying to hurry up and get home). I told him I’ll check it out as soon as I got upstairs, wondering what was causing this issue…
Well, it’s the second time mysql has crapped out in a bizarre, troubling way. It’s running, but all the threads are busy–up to the maximum (500 in my case). Can’t connect to it due to error “too many connections” or similar. Even on the local machine, as real and mysql user root, can’t consistently get an extended-status out of it. Finally I got a ‘mysqladmin shutdown’ to sort of work… although it seemed to hang. So I killed it, and noticed about half the threads had been killed… but still, can’t connect to mysql… the client apps all hang. Nothing interesting in the error log. Finally, out of desperation, do a killall -9 mysqld_safe ; killall -9 mysqld. That does it, but we all know things are going to be ugly. Try to restart mysql, it hangs. Reboot machine (I know, this shouldn’t matter). (And it doesn’t.) Sigh.
Start doing myisamchk’s. On several tables, it segfaults! Nice! Check mysql.com–looks like a bug in myisamchk has been fixed in later versions (I’m running 4.1.3). What the hell–download the latest RPMs (4.1.5) and install them. Myisamchk doesn’t segfault anymore! Repair all tables (including one with 2.1million forum posts with a fulltext index… fun!) Read a little on the web while I’m waiting for the myisamchk -r’s to finish… seems the unresponsiveness might be a result of the tables in the mysql db being corrupted… make sure to repair those (again–segfaulted on 1st try pre-upgrade). Restart mysql accidentially before finishing all repairs (don’t ask). It works though! Shut it down nicely this time. Copy all datafiles to another directory, so I can make a full backup offsite later. Restart mysql. Everything’s happy.
I do have a copy of the extended-status output while it was hung at first… will dig through there. One thing that seemed to stand out was the first thread listed said “delayed”. I have recently added one table that I do INSERT DELAYED’s into. I changed the code to not use delayed, we’ll see if that seems to help. This has happened twice in about 5 weeks now… we’ll see if another 5 weeks go by… of course it’s a newer version of mysql, too, so, anything could happen, good or bad.