🔒 Locked

[web] MySQL = OPTIMIZE TABLE

Started by Verg Apr 13, 2010 at 9:02 PM 1 replies 2.1k views

Original Post

Apr 13, 2010 at 9:02 PM

I made a boo-boo... maybe. On a vBulletin forum where I act as an admin (not an owner), I ran an OPTIMIZE TABLE command from the control panel. Over an hour later, I'm still waiting for the response. In the meantime, the server is not responding to new page requests... it's too busy to serve pages. The table in question was about 185,000 records at approximately 1K a pop for each record... possibly less. I think one of the fields is a varchar(500) which is why the size might be inflated. ... How long should this take, on an average webserver? I don't know the server; I don't have access to it to send MySQL a command to stop the OPTIMIZE TABLE thread... I just want to know how long I should continue to freak out :( Thanks Chad

unknownhero

Apr 13, 2010 at 9:44 PM

There is a really good reason why there are development and production servers...this is one of them...but suggest help, i wouldn't know off hand.

Try restarting mysql? I heard of some things taking a very very long time on massive databases maybe even locking up mysql.

It shouldn't take that long...but we don't know what the records are...and I dunno what "OPTIMIZE TABLE" does exactly...maybe defragment?? so yeah maybe you're locking up the i/o for the disk(s)

Verg

Apr 13, 2010 at 9:55 PM

OPTIMIZE TABLE does a defragment.

Yeah, like I said... no access to the server so no way to restart MySQL.

The owner is on the east coast, were he's probably in bed by now... or I'd give him a call. At any rate, he'll get my emails in the morning about how to reset things.

http://dev.mysql.com/doc/refman/5.1/en/kill.html

The table itself has data that can be re-generated, so it's not a big loss if we have to DROP and CREATE it again.

This had to do with the table not indexing any more... for some reason, the table stopped using its indexes... one of the things suggested "on the internet" was to ALTER TABLE DISABLE KEYS, then re-index, then ALTER TABLE

ENABLE KEYS

still in the re-index part; who knows how long it will take ultimately.

In the meantime, about 2,000 people are without a web community.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.