List:General Discussion« Previous MessageNext Message »
From:Mikko Noromaa Date:September 18 2003 10:57am
Subject:Fragmentation problem with MYD/MYI files
View as plain text  

I have a large MyISAM database with 22 million rows. The MYD file is now 4
GB in size, and the MYI file is 2 GB. I am constantly adding rows to this
database and rarely deleting any. Thus the database size grows constantly.

MySQL seems to increase the database files (MYD/MYI) in very small
increments. This has led to massive fragmentation of the files. Just
recently my MYD file was in over 10000 fragments!

I am running MySQL 4.0.14b on Windows XP Pro. I have a 40 GB NTFS disk with
20 GB free space. I thought that NTFS could handle a situation like this
without fragmenting files, but apparently that is not the case!

Is it possible to configure MySQL so that it would allocate a large amount
of extra space for the MYD/MYI files, and then use this space as necessary?
This way the files would need to be grown only very rarely. It would be
ideal if I could configure the increment in which MySQL increases the file
sizes (for example, 100 MB, or a percentage of current file size).

If this is not possible, I'd suggest to add such a feature to MySQL. I
believe it would give a tremendous performance-boost for application where
the databases keep growing.


Mikko Noromaa (mikkon@stripped) - tel. +358 40 7348034
Noromaa Solutions - see

Fragmentation problem with MYD/MYI filesMikko Noromaa18 Sep
  • Re: Fragmentation problem with MYD/MYI filesJeremy Zawodny19 Sep
    • Re: Fragmentation problem with MYD/MYI filesDan Nelson19 Sep