File Fragmentation & MySQL Performance

Problem Description
---------------------------------------------------
Since several months we were observing a specific Select query that was tuned but running slow on couple of tables in MySQL 5.1.62 (Innodb engine) in our production database. Also, the queries continued to be slow after the database upgrade to MYSQL 5.5.24.

One of the table has 1.4 million rows of size 184 MB. The select query was tuned and using a ref join, scanning ~150 rows only. The Select query was taking 220 milliseconds. Since we had multiple select queries in a loop the time for the transaction inflated to more than 30 seconds.

We ran the Optimize command on the table and the .ibd file size did shrink in order of 20-25 MB. But, the the queries continued to be slow. We then dumped and restored the table and to our surprise the queries ran very fast. The performance gain was 10 times.


The root cause was File fragmentation.

Lessons learnt -
1) Learnt a core difference between a Dump/restore v/s Optimize.
2) Command named filefrag

1) DUMP and RESTORE causes the table to be rebuilt entirely from scratch, in the 5.5 row format.    It would have also gotten rid of any fragmentation that may have existed on the filesystem
(since we weren't doing a simple cp or mv call for the files on the filesystem, as happens with the ALTER/OPTIMIZE TABLE) as well as on any logical volume, and finally on the actual storage device as well.

2) filefrag (Linux)

Following was the output generated for two tables
> filefrag foo.ibd
Output > foo.ibd: 83 extents found, perfection would be 2 extents

> filefrag poo.ibd
Output > poo.ibd: 131 extents found, perfection would be 4 extents