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:
or you can do it in stages:
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.