Re: Probable faq: need some benchmarks of pgsql vr.s mysql

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(at)gmail(dot)com> wrote:

> On Tue, Nov 2, 2010 at 7:17 AM, Martin Farach-Colton <martin(at)tokutek(dot)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
>
> It isn't that simple as I described earlier in this thread. InnoDB has
> 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(at)gmail(dot)com
>