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 http://www.nm-sol.com/