CODEWITHSUNDEEP
mysqloptimizationmyisam
Pentium10
Pentium10

Reputation: 207893

How to get rid of the 1GB overhead of MyISAM table without locking too much the system

I had some maintenance task completed on one of the tables, and now it has 1GB of overhead.

Since the table operations run for hours (delete 40% of records, took 4 hours) I do not want to lock the database with the OPTIMIZE table command for hours, so I am looking for alternatives how to deal with this overhead and remove with best method.

The table itself is 3GB, having 204 705 records.

Upvotes: 0

Views: 1048

Answers (2)

Pentium10
Pentium10

Reputation: 207893

I was able to get around this problem by a 6 minute process doing the following:

CREATE TABLE table_reduced LIKE table;
ALTER TABLE table_reduced DISABLE KEYS;

insert into table_reduced
SELECT 
    *
FROM
    table;

ALTER TABLE table_reduced ENABLE KEYS;

RENAME TABLE table TO table_old;
RENAME TABLE table_reduced TO table;

DROP TABLE `table_old`;

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65537

Assuming your table has no triggers on it, one easy way to accomplish an online OPTIMIZE is to use pt-online-schema-change to rebuild the table. Since you said this is a MyISAM table you can just set the engine to MyISAM to accomplish a rebuild without changing anything:

pt-online-schema-change --alter "ENGINE=MyISAM" D=your_schema,t=your_table

Upvotes: 1

Related Questions