Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys?

Question

I understand that one of the primary issues with using a non-sequential primary key is that every disk/memory write that does not come after the last known key causes a re-write of everything from the point of insertion on.

I'm looking at using a modified uuid that will be semi-sequential--ie, a scope-related number will be prepended to the uuid in order to provide some gap optimization for data in a common scope.

What would be ideal is to force InnoDB to write rows a certain distance apart based on A) the sequence of known keys, and B) table growth estimates.

A simplified example is this:

Disk w/o this tuning:

|< start of disk
| row 2592
| row 33093
| row 34928
| row 50983
| row 390853
| row 391985
|
| free space ...
|
|< end of disk

Disk with this tuning:

|< start of disk
| row 2592
|
|
|
| row 33093
|
| row 34928
|
|
| row 50983
|
|
|
| row 390853
|
| row 391985
|
|< configurable end of allocated disk
| 
| free space ...
|
|< end of disk

In the second case, most writes would not require a re-write. Even if you run out of space between, we could then do a full re-alocation and cut the number of row re-writes down by a considerable factor.

Does something like this exist?

Answer 1

Your understanding is completely wrong, so the question does not really make sense. Rows beyond the value of the point of insertion are not rewritten with insertion. Rows aren't even re-ordered within the page level for insertion; insertion happens into empty space within the page, and the rows are linked together in ascending order through a singly-linked list.

It may be informative to check out this series of blog posts I wrote: http://blog.jcole.us/innodb/