Really there is no safe de-fragmenter tool out there for ext3. There is this one but I will not use it.
Fragmentation can get so bad that performance from EXT-3 can drop 7 fold!!
Here is some good info on fragmentation and a comparison of various other file systems.
Now for filefrag, I use this to see how bad an innodb file is fragmented.
TABLE.ibd: 4020 extents found, perfection would be 298 extents
Our wiz of a system administrator Kevin M., who I am teaching to become a mySQL DBA got me hooked on this utility: and he came up with a good method to fix this.
cp TABLE.ibd to a new location
rm TABLE.ibd
cp TABLE.ibd from new location back to the Database Directory
Tada fragmentation is nearly gone. FSCK will help as well.
5 comments:
reiserfs or xfs -- seriously
Doesn't seem to work for me:
$ sudo filefrag ibdata1
ibdata1: 752 extents found, perfection would be 16 extents
$ sudo cp -p ibdata1 ibdata1.new
$ sudo filefrag ibdata1.new
ibdata1.new: 4908 extents found, perfection would be 16 extents
Talking to Kevin M. your filesystem does not have enough contiguous blocks; your going to need to copy your data to another partition or NFS with enough space
run mkfs.ext3 -Tlargefile4 -LMYSQL [device]
or use xfs
I cannot use xfs since we use RHEL and its not built into our kernel nor supported.
I'd never heard of this before, and now I'm like a kid with a magnifying glass this morning, using it all over the place to see what I find.
filefrag is great.
I wouldn't recommend reiserfs as it is probably more likely to have fragmentation than ext3 and is even lighter on fragmentation tools. Its better for some application, but databases is probably not one of them.
If your interested it keeping this lower without regular copying of files, you may want to experiment with "innodb_autoextend_increment" and "innodb_file_per_table"
Post a Comment