Re: New HDB backend MDB

On Mon, Dec 12, 2011 at 5:09 PM, Howard Chu <[email protected]> wrote:
> I guess this would only be safe with a log-structured filesystem. For a
> regular filesystem (I used XFS in my benchmarks) there's significant seek
> overhead between the data pages and the meta pages, which is probably the
> main contributor to slowdowns here.

Right.  E.g., with ZFS this is safe.  Just one fsync() per-transaction
will increase your transaction rate significantly.

I believe SQLite3 does one fsync() per transaction in WAL mode, three
in journal mode, and also 3 for every WAL checkpoint.

> The page size is retrieved from sysconf(), it's not explicitly configurable.
> Basically there's no good reason for it to differ from the VM subsystem's
> page size.

But doesn't this tie MDB to the architecture on which it was built?
Also, systems may have multiple different page sizes... (e.g., 4KB for
some things, 4MB for others)... sysconf() might say 4MB, but I'm sure
you don't want 4MB b-tree pages!  But I can see why you care about the
VM page size: matching the VM pagesize will minimize the number of
page faults for contiguous pages, which -who knows- might have an
impact on caching strategy for the VM/filesystem.

On a similar note, how do you handle endianness?  (See below.)

That's one of the nicest things about SQLite3, that you can take a
SQLite3 DB file from one host and look at it on another, even if the
two have different VM pagesizes, different filesystem preferred page
sizes, different endianness.  It'd be better to not lose this
feature!!

All you have to do is: a) have a fixed size for ubberblocks, b) have
the pagesize stored in the ubberblocks, c) take the page size from the
app or else use the filesystem's preferred block size.  Matching
b-tree and filesystem page sizes is a common DB tuning approach -- it
will be interesting to see how b-tree page size vs. VM page size
affects performance.

One more question regarding page size: what's the max page size?
(SQLite3's theoretical max is 64KB, actual max is 32KB.)

As for endianness, the best thing to do is to prefer little-endian and
swab on big-endian systems, with the swabbing getting compiled out on
little-endian systems.

Also, it'd be nice to have file magic for MDB, and nicer still if
MDB-used-to-backend-SQLite3 was recognizable as such from file magic.
I like file(1) to be useful :)  besides, you could then have SQLite3
use the correct backend after tasting the file!

Nico
--