Skip site navigation (1) Skip section navigation (2)

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

From: Martin Farach-Colton <martin(at)tokutek(dot)com>
To: pgsql-advocacy(at)postgresql(dot)org
Subject: Re: Probable faq: need some benchmarks of pgsql vr.s mysql
Date: 2010-11-02 14:17:20
Message-ID: AANLkTinjfpzKJTGhfiH749yKbOtS3jmpOGyucUTVEPy1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-advocacy
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

Responses

pgsql-advocacy by date

Next:From: Alvaro HerreraDate: 2010-11-02 18:31:59
Subject: Re: [pgsql-advocacy] Tasks for the Google Code-In
Previous:From: MARK CALLAGHANDate: 2010-11-01 15:23:35
Subject: Re: Probable faq: need some benchmarks of pgsql vr.s mysql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group