Skip navigation links

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 3>` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `<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>`)
) ENGINE=MyISAM AUTO_INCREMENT=17485259 DEFAULT CHARSET=latin1

Any advice would be gratefully received.

Options: ReplyQuote


Subject Views Written By Posted
Optimize Table Taking Forever 3199 Thomas Cleveland 04/27/2012 03:03AM
Re: Optimize Table Taking Forever 3855 Thomas Cleveland 04/28/2012 04:17PM
Re: Optimize Table Taking Forever 2660 Rick James 04/30/2012 12:32AM
Re: Optimize Table Taking Forever 2350 Thomas Cleveland 04/30/2012 03:52AM
Re: Optimize Table Taking Forever 2308 Thomas Cleveland 04/30/2012 10:12AM
Re: Optimize Table Taking Forever 1794 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.