InnoDB Defragmentation

Performance Optimization
14 April 3:50PM - 4:40PM @ Ballroom A

Experience level: 
Intermediate
Duration: 
50 minutes conference

Rate This Session

We've been seeing innodb pages under-utilized. The problem is, unless the insertion pattern is sequential, an insertion to a full page will result in a page split producing two half full pages. Depending on the application, many times lots of the split pages never get more than 70% full. Furthermore, deletions sometime make pages less than 50% full and many times these nearly empty pages failed to merge with a sibling page. It is not rare to see the pages are on average only 60-70% utilized. This was not a huge problem on disk when storage is cheap. But with flash, wasting more than 30% storage space does not sound that cool. We developed an auto-defragmentation tool which includes a command that defragments a certain index, statistics tracking and analysis, and an automation script using the statistics to automatically select target index to be defragmented. The tool is running on most of our production flash card machines now. We will talk about the theory, the automation, and the problems we need to fix to get this deployed to production, with pretty graphs.


Speakers

Software Engineer, Facebook
Rongrong is currently a software engineer on MySQL Engineering team in Facebook. She has been working on MySQL for over two years now, mostly focusing on InnoDB. So far she has worked on statistics, defragmentation, logical read ahead, compression, and whatever comes up in production.