Changes to InnoDB autoextend coming in MySQL 5.6
Posted in Databases on Oct 8, 2012
I was looking through James Day’s post on upcoming changes to MySQL configuration defaults in version 5.6, and one caught my eye in particular:
innodb_autoextend_increment changes from 8 to 64MB, and now affects
I don’t see any further documentation on this yet; I assume that’s in the works. I’m curious how this will actually behave. What will be the initial size of an empty InnoDB table using
innodb_file_per_table? There might be some unintended consequences. Here’s a couple I can think of:
I suspect that what’s going to happen is something like this: 16kb when empty, then increase 16kb at a time until the table occupies an entire extent, then go 64MB at a time. But extents are contiguous, and if 16kb at a time is added to the table, then I guess they’d have to be compacted once the table grows to the point that an extent should be consumed (but can an extent contain pages from different tables? Insert hand-waving here). I’m a little confused about the details of extents, segments, and so on.
This proposed change feels a little like deja vu for me, given my recent adventure with filesystem preallocation of MyISAM files.
Update: I got confused somehow and thought that this change would be in an upcoming release, but now I see it’s apparently in the RC just released last week. I will test out the behavior and report back.