Good point Mark.
There's an inherent performance cliff associated with B-trees when you drop out of memory. The InnoDB insertion buffer is a way to soften that cliff, but it doesn't eliminate the underlying cause. An insertion buffer will help -- sometimes a lot -- because you can bunch up your insertions and hit the same B-tree leaf with more than one insertions. When won't it help?
- When you have a lot of secondary indexes, because you dilute the effect of insertion bunching across the indexes.
- When your insertion pattern looks random, because a random key will have a lot less leaf bunching.
But even without these "buffer killers", you still drift down towards the limiting situation of one-seek-per-insertion as the database grows. So the insertion buffer moves out the pain point but doesn't address the underlying problem.
On Mon, Nov 8, 2010 at 10:52 AM, MARK CALLAGHAN
<mdcallag@gmail.com> wrote:
On Tue, Nov 2, 2010 at 7:17 AM, Martin Farach-Colton <
martin@tokutek.com> wrote:
> The B-tree bottleneck is inherent in the data structure, not a function of
> InnoDB vs postgresql vs ....
> Fractal trees do not perform disk seeks for each insertion, and they are
> therefore very good at insertions.
> Martin Farach-Colton
> Tokutek, Inc
the insert buffer:
http://www.google.com/search?hl=en&q=insert+buffer+innodb
InnoDB can do a disk seek for each PK/unique index on a table during
an insert. It does not do disk seeks for each secondary index.
--
Mark Callaghan
mdcallag@gmail.com
