Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
I would consider setting your tempdb to a large and fixed generous size to prevent file fragmentation on growth –  gbn Oct 12 '11 at 2:25
    
@gbn, yes, i guess so, SQL Server does better on fragment control than MySQL MYISAM engine. –  LiuYan 刘研 Oct 12 '11 at 16:42

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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? –  LiuYan 刘研 Oct 12 '11 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. –  RolandoMySQLDBA Oct 12 '11 at 17:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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