0

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.

Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • Why do you want to? 1Gb is just not enough to be worth worrying about. I doubt rebuilding the table will improve performance much, just save a tiny amount of immensely cheap disc space (I am assuming that you do not have 10,000 such tables here) – MarkR Jul 16 '12 at 14:17

2 Answers2

1

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
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
0

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`;
Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • ```RENAME TABLE `table` TO `table_old`, `table_reduced` TO `table`;``` would perform an atomic rename with no chance of raising a table not found error. – Wandering Zombie Feb 09 '14 at 09:37