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

I'd like to make one clarification. Insertion performance *is* Tokutek's
specialty. Customers have found that TokuDB outperforms InnoDB by up to 80x
on insertion performance (
http://tokutek.com/customers/a-social-networking-case-study/) They use this
insertion performance to keep a rich set of indexes, which in turn speeds up
queries.

As for insertion rates into InnoDB vs Postgresql and the reasonableness of
1k/s insertions, both InnoDB and postgresql use B-trees for indexing. (PG
also has some other data structures for indexing, but they are used in
special cases). On insertion, a B-tree puts a row into a leaf *which must be
in memory*. Disk seeks to retrieve leafs make B-trees slow for many types
of data.

When are B-trees fast? When data fits in memory or when the key is
sequential/pre-sorted (avoids need to retrieve leafs). Out of the sweet
spot, you need to move the disk head on every insertion, and 1k/sec
insertions is not uncommon. A report of 30k/sec insertions for a B-tree
suggests to me an in-memory database, an auto-increment primary key with no
secondary indexes, or some other special case that avoids disk seeks. 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