MySQL Forums
Forum List  »  MyISAM

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
17057
April 27, 2012 03:03AM
8663
April 30, 2012 12:32AM
5449
May 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.