Thursday, January 24, 2008

filefrag a DBA's Best Friend

EXT3 has performance problems as the filesystem gets fragmented-although this is counter intuitive to the design of EXT3-fragmentation really happens.

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:

Unknown said...

reiserfs or xfs -- seriously

Anonymous said...

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

Dathan Pattishall said...

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.

veganloveburger said...

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.

daniel said...

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"