Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 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/

share|improve this answer
    
Why is there such a dramatic performance degradation for using non-sequential keys, then? –  Bryan Agee Feb 28 '13 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 '13 at 23:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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