1

We have a keep-growing table which using MYISAM storage engine.

Almost every half year, this table will crash, and need to be repaired.

I checked the defragment result reported by windows defragment program, the most fragmental files are:

-------------------------------------------------------------------------------- 
碎片 文件大小 最零碎的文件 
3,812 213 MB \MySQLData\data\cmszivr\calllog.MYI 
24 541 MB \Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf 
... 

you can see, the index file caused 3812 fragment when it's 213 MB size. compared to SQL Server (24 fragment, 541 MB size), it's too bad.

I don't know if the fragmental issue caused mysql-server/mysql-table crashed, but it's must not be good for storage, and could be a potential reason to make server unstable.

So, is there a way to prevent it? (change config? change storage engine?)

2
  • I would consider setting your tempdb to a large and fixed generous size to prevent file fragmentation on growth
    – gbn
    Oct 12, 2011 at 2:25
  • @gbn, yes, i guess so, SQL Server does better on fragment control than MySQL MYISAM engine. Oct 12, 2011 at 16:42

1 Answer 1

2

When you perform any OS defragmentation or virus scan operations, please make sure mysql is shutdown. No utilities (mysql-based or otherwise) should be running against any table while the server is up. MySQL is not good to run on a PC that is not dedicated to MySQL DB only, especially in the presence of defragmentation or virus scan software running. Temp tables have also been known to get whacked by virus scan software.

At the very least, move the MyISAM table to a folder away from the standard datadir.

You should consider defragmenting the MyISAM table using:

OPTIMIZE TABLE calllog;

or you can do it in stages:

CREATE TABLE calllognew LIKE calllog;
ALTER TABLE calllognew DISABLE KEYS;
INSERT INTO calllognew SELECT * FROM calllog;
ALTER TABLE calllognew ENABLE KEYS;
ALTER TABLE calllog RENAME calllogold;
ALTER TABLE calllognew RENAME calllog;
DROP TABLE calllogold;

You may need to consider upgrading to MySQL 5.5 for the latest MyISAM storage engine code.

If you want to switch to InnoDB, you must configure it so that each table has its own tablespace.

2
  • Thanks, i haven't did defrag yet, just analyze fragment. OPTIMIZE TABLE may do defrag now, but when index file grow again, fragment will occur again. Is there any improvement on MyISAM engine in MySQL 5.5? Oct 12, 2011 at 16:55
  • 1
    @LiuYan : Oracle's is more focused on InnoDB than MyISAM. So, I am not that sure MyISAM will improve significantly as time goes on. I would like to say this: If the calllog table is written to frequently, you should switch to InnoDB ASAP because MyISAM performs a full table lock with each INSERT/UPDATE/DELETE. InnoDB will perform a row lock, which is often less intrusive in a heavy-write environment. Oct 12, 2011 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.