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.

6 comments:

Keith said...

reiserfs or xfs -- seriously

Matthijs 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 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"

sexy said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情趣,情趣,情趣,情趣,按摩棒,跳蛋,充氣娃娃,情境坊歡愉用品,情趣用品,情人節禮物,情惑用品性易購

免費A片,AV女優,美女視訊,情色交友,免費AV,色情網站,辣妹視訊,美女交友,色情影片,成人影片,成人網站,A片,H漫,18成人,成人圖片,成人漫畫,情色網,日本A片,免費A片下載,性愛

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

情趣用品,A片,免費A片,AV女優,美女視訊,情色交友,色情網站,免費AV,辣妹視訊,美女交友,色情影片,成人網站,H漫,18成人,成人圖片,成人漫畫,成人影片,情色網


情趣用品,A片,免費A片,日本A片,A片下載,線上A片,成人電影,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,微風成人區,成人文章,成人影城,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,臺灣情色網,色情,情色電影,色情遊戲,嘟嘟情人色網,麗的色遊戲,情色論壇,色情網站,一葉情貼圖片區,做愛,性愛,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,美女交友,做愛影片

av,情趣用品,a片,成人電影,微風成人,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,愛情公寓,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,aio,av女優,AV,免費A片,日本a片,美女視訊,辣妹視訊,聊天室,美女交友,成人光碟

情趣用品.A片,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,色情遊戲,色情網站,聊天室,ut聊天室,豆豆聊天室,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,免費A片,日本a片,a片下載,線上a片,av女優,av,成人電影,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室