Mysql – How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)

fragmentationmyisamMySQLwindows

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?)

Best Answer

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.