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.


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

$ sudo filefrag 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"

Anonymous said...



A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,av dvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX