Monday, May 15, 2006

InnoDB fragmentation is NTFS fragmentation

In a previous post I wrote about a strange fragmentation of newly created InnoDB data files. Turns out that it's not InnoDB's fault, but Windows'.

I did some more tests and used a little script to just fill a bunch of files with 2GBs of garbage on a freshly defragmented partition. Same result: The files were not contiguous as one would expect, but scattered around the free space. So this clearly has something to do with NTFS's allocation strategy. If someone knows more about this, please feel free to leave a comment or drop me a line via email.

So if you really want contiguous data files for your MySQL database, first defrag your partition to get contiguous free space. After the create the data files and shutdown the database. Now defrag again, to have the data files be put together. From my experience more files of smaller size are easier to handle, because in order to have the defragmentation tool put them together it needs enough contiguous space to effectively put a copy there. I switched from ibdata files with 2GB each to 4x the number of files with 512MB each.

No comments: