Changes to InnoDB autoextend coming in MySQL 5.6
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 *.ibd files.
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:
- Create a table and it’s 64MB from the start. This would cause some people to be unable to use InnoDB. In fact, as it currently stands, an empty table is 16kb, and I’ve heard some people say that’s a problem for them because they create many small tables and the 16kb minimum just eats up all their disk space. This type of problem would get a lot worse in the scenario where 64MB is the minimum size.
- Create a table and it’s 16kb, then increases by 64MB. This might have the same problem.
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.
Baron,
For some of the background on this see http://bugs.mysql.com/bug.php?id=31592 . It’s intended in part to help larger table scans, particularly on spinning disks, where the high disk i/o can cause significant production disruption.
As you can see, there was some consideration given to being cleverer about the extension size initially but it was ultimately implemented just as a simple change. We can revisit that if it turns out to have broad adverse effects.
If you try smallish benchmarks you may see a small performance drop, depending on how significant the extension is to the total amount of work being done in the benchmark. Perhaps 1-3%, depending on specific benchmark, though it’s possible to deliberately construct worse results than those by paying attention to thresholds to amplify the effect.
Most of the work is done in the background in 5.6 but there is still a brief time when a lock is taken. They are mostly a concern for those with lots of very fast queries and low tolerance for query run time variation. Full background pre-extending will eliminate this but it’s not in 5.6.
Those with really fast solid state storage might find it better to reduce the size, since they aren’t going to be affected much by fragmentation. Those with big tables on spinning disks might prefer bigger. Those with small tables might prefer smaller, like 1m, and can set that.
While we’ve changed the default to innodb_file_per_table being on in 5.6, people are also free to turn it off again if they like. That’s part of the ultimate solution for those suffering badly from small table issues.
You might also find it of interest to explore the potential of the feature mentioned at https://blogs.oracle.com/mysqlinnodb/entry/choose_the_location_of_your and how it does and doesn’t interact with this.
Have fun experimenting with this! :)
Views are my own, for an official Oracle view seek out a PR person.
James Day, MySQL Senior Principal Support Engineer, Oracle
James Day
8 Oct 12 at 11:14 pm
I’ve started up the RC and created an empty InnoDB table. The size of the .ibd file is 96kb. Why? I was expecting 16kb or 64MB based on what you wrote.
Xaprb
11 Oct 12 at 11:09 am
Hmm – MySQL 5.5 creates a 96kb .ibd file too. I thought I remembered an empty table was 16kb at one point in time. Am I wrong, or has something changed since, say, MySQL 5.0?
Xaprb
11 Oct 12 at 11:12 am
It turns out that my original blog entry was wrong and this was never changed to apply to ibd files. I’ve corrected the blog post. Sorry about the confusion!
The initial size in 5.0.60 is 96k. The ibd minimum space rule is 64k + n*16k for each index including the PK and doesn’t seem to have been changed. Thanks for asking about the 96k part and prompting me to check on that.
James
James Day
15 Oct 12 at 4:32 pm