2

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?

1 Answer 1

2

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/

2
  • Why is there such a dramatic performance degradation for using non-sequential keys, then?
    – Bryan Agee
    Feb 28, 2013 at 17:10
  • Good question. Submit a new DBA question for that and point me to it and I'll write up a detailed answer explaining it.
    – jeremycole
    Feb 28, 2013 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.