MySQL Forums :: MyISAM :: Optimize Table Taking Forever

Advanced Search

Optimize Table Taking Forever
Posted by: Thomas Cleveland ()
Date: April 27, 2012 03:03AM

I know that optimize table is supposed to take quite a long time, but it has now been around 10 hours and I'm still waiting for it to finish.

The table in question stores logs. It had around 17 million records. I deleted just over half of them because disk space was starting to become an issue. I then ran optimize table. It's on a fairly powerful EC2 server so I didn't expect it to take more than a hour or two. It's still going but the CPU and disk usage on the server are very low so I'm starting to wonder if it's actually doing anything. Although space was getting low, there is still about 10gigs left. It gobbled up about another gig or so when I first ran it.

The processlist shows this:

| 235589450 | root  | localhost                                        | <the db> | Query       | 38210   | Repair by sorting                                              | optimize table <the table>

The table definition is:

CREATE TABLE `<table name>` (
  `<field 1>` int(16) NOT NULL AUTO_INCREMENT,
  `<field 2>` int(8) NOT NULL DEFAULT '0',
  `<field 4>` varchar(255) NOT NULL DEFAULT '',
  `<field 5>` varchar(15) NOT NULL DEFAULT '',
  `<field 6>` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`<field 1>`),
  KEY `<key 2>` (`<field 2>`,`<field 3>`),
  KEY `<key 3>` (`<field 2>`,`<field 4>`,`<field 3>`),
  KEY `<key 4>` (`<field 5>`,`<field 3>`)

Any advice would be gratefully received.

Options: ReplyQuote

Subject Views Written By Posted
Optimize Table Taking Forever 8297 Thomas Cleveland 04/27/2012 03:03AM
Re: Optimize Table Taking Forever 7447 Thomas Cleveland 04/28/2012 04:17PM
Re: Optimize Table Taking Forever 5038 Rick James 04/30/2012 12:32AM
Re: Optimize Table Taking Forever 4804 Thomas Cleveland 04/30/2012 03:52AM
Re: Optimize Table Taking Forever 3870 Thomas Cleveland 04/30/2012 10:12AM
Re: Optimize Table Taking Forever 3242 Rick James 05/01/2012 10:14AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.