Tuesday, May 02, 2006

Strange InnoDB fragmentation

Follow-up posted here, have a look at it as well.

On my development machine I have been using an auto-extending ibdata table space file for several months (using MySQL 4.1.12 on Windows 2000 SP4 on an NTFS drive). Over time that file grew to some 20GB and was scattered around the whole hard drive. As this does not exactly boost database performance I decided to swipe everything clean and start with a set of new ibdata files, 2GB each and all created from scratch.

So I shut down the database server and deleted all tablespace files from the data directory. After that I ran a full defrag using O&O Defrag V8 Professional (trial available here) to create a continuous free area on the disk. I then reconfigured the MySQL configuration file and started the server.

It took its time to create 11 ibdata files of 2GB each. Then, just for curiosity, I let the defragger analyse the disk again, and could not believe my eyes: All those newly created files were wildly scattered around!

The following image shows the analysis result, taken immediately after the creation process. Red squares represent the ibdata files; all that space was free (white) before.

Defragger Screenshot

I do not really get it. Right now I am running the defragger again and it is puzzling the files together in a way I would have expected them to be in the first place (this being the whole point of recreating them). This is the mess it is cleaning now:

Cluster Inspector View

Any comments warmly appreciated. Is this an InnoDB/MySQL problem or is Windows/NTFS to blame for this?

No comments: