Re: New HDB backend MDB

On Tue, Dec 6, 2011 at 10:16 PM, Howard Chu <[email protected]> wrote:
>> "
>>  o Surveying the landscape revealed no other database libraries with
>> the desired characteristic
>> "
>>
>> But SQLite3 with WAL (write-ahead logging) has very similar
>> characteristics to your MDB: MVCC, serialized writing, support for
>> in-memory and on-disk databases (MDB doesn't do on-disk).
>
> I'm pretty sure you've gotten the wrong idea. This post may help clarify:

I figured it out by the end of my reply but forgot to edit the
beginning to correct my earlier impression :(

>> Did you consider SQLite3 w/ WAL?
>>
>> My suspicion is that if you don't need (or want) the full overhead of
>> SQL then MDB can probably perform much better than SQLite3 w/ WAL.
>> Have you benchmarked the two?
>
> Yes. I also have a port of SQLite3 using MDB as the underlying backend.
> https://gitorious.org/mdb

Interesting.  I'll take a look.  Does this store each table and index
in a separate file?  Or does MDB support multiple b-trees in one file?
 You said it only does two b-trees per-file, so I gather you're using
multiple MDB files to backend SQLite3.

It'd be nice if SQLite3 was a bit more pluggable w.r.t. backends...
Oracle also has a mod to backend SQLite3 with BDB.

> I've tested SQLite3 pretty extensively. It's no contest. The SQL processing
> overhead is several orders of magnitude slower than plain MDB key/value
> operations. (In fact, using MDB as the backend makes only a couple percent
> difference in SQLite performance; 95+% of the execution time is above the
> Btree layer. I have to admit that I'm quite unimpressed with MDB's impact on
> SQLite. Or put another way, SQLite's performance issues reside much higher
> up the stack.)

I'm assuming you've been re-using compiled statements (i.e., you know
what you're doing).  This means the SQLite3 VM and/or the results of
statement compilation are slow.

>> What makes your write throughput so slow relative to BDB?  Do you
>> fsync() more often than you have to?  When do you fsync()?
>
> We preferentially use fdatasync() and yes, we do it a lot. Upon transaction
> commit, there is a separate sync for the txn data. Then the meta page is
> updated synchronously. The data sync must complete before the meta page
> update, otherwise the DB integrity cannot be guaranteed.

Depending on the filesystem you may be able to safely do just one sync
per-transaction.

> I've tested operation where the meta update is lazy - i.e., we only sync
> after the data write, and allow the meta write to float in cache and flush
> out with the next data write/sync. Throughput increased about 33%. (I don't
> remember whether I exposed this as a library option though.) And of course
> you can run with no syncs at all, if you really don't care.

You should expose sync options (SQLite3 does...).

> The on-disk format is outlined in the presentation slides. Yes, the on-disk
> format is exactly identical to the in-memory format, since the memory is
> just a mmap'd view of the disk. The layout is page-based, as any disk
> database would be.

Can you handle large DBs in 32-bit mode code?  Or must you mmap() in
the whole DB?  I guess it doesn't matter -- it's really time to
encourage people to switch to 64-bit code.

Nico
--