Jump to content
  • Advertisement
Sign in to follow this  
Verg

[web] MySQL = OPTIMIZE TABLE

This topic is 3171 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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

Share this post


Link to post
Share on other sites
Advertisement
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)

Share this post


Link to post
Share on other sites
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 <table> DISABLE KEYS, then re-index, then ALTER TABLE <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.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!