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?