Table space fragmentation has generally 2 origins :
- File System fragmentation : the data file is spread physically on many non contiguous locations on the disk.
- Internal Fragmentation : the data and index pages have “holes” : this happens when rows are deleted or updated, especially at random.
As a result, performance is affected by table space fragmentation. Data typically takes more space on disk and in memory. The disk is more busy than it should.
File System fragmentation can be detected using the filefrag command on Linux (and similar on different OS). When using MyISAM, MYI files are usually very fragmented on the FS, much more than the MYD files.
ls -al frag total 883304 drwx------ 2 aadant common 4096 Sep 30 18:41 . drwxr-xr-x 17 aadant common[Read more...]