Re: New HDB backend MDB

On Mon, Jan 2, 2012 at 2:38 PM, Jeffrey Hutzelman <[email protected]> wrote:
> On Mon, 2012-01-02 at 10:58 -0600, Nico Williams wrote:
>> BTW, I wonder if SQLite3's slowness is to be attributed to its page
>> cache, and the unnecessary data copies (in page sized chunks!) that
>> result.  Ripping out the page cache will probably be a fair bit of
>> work, so that doing it without the buy-in of the SQLite3 dev team is
>> probably out, and they only do work that consortium members pay for...

Oops, I'd meant that to only go to Howard.

> Which doesn't mean they wouldn't be willing to take significant work
> done by someone else.  But obviously I wouldn't just randomly start on
> such a project, especially without evidence that's really the problem.
> As you know, caching is tricky -- too little can be as bad as too much,
> and on some platforms (SPARC), asking the kernel to do work for you
> might be more expensive than keeping another copy.
>
> In any case, I suspect a significant problem is heavy use of fsync().
> POSIX could really use some non-crappy filesystem read/write barriers.

No, that's not the problem.  In WAL mode you only get one fsync()
per-COMMIT.  In journal mode you get three fsync()s per-COMMIT, which
is clearly quite slow.  But Howard has numbers that show MDB is quite
a bit faster for reading and writing with similar numbers of fsync()s
per-transaction (at least IIRC), but when using MDB as the backend for
SQLite3 the resulting improvement is small.

>>  Someday, when I have the time, I'll profile SQLite3.
>
> Yup.  I have to wonder if Google^WAOSP has put any effort in that.

Why should they?  They use SQLite3 in an OS for small devices where
memory is at a premium -- they want a defined-size page cache in the
application, they want predictable memory use and small memory
footprint, and they want those more than they want high performance.

What I'd like is an option to not have a page cache at all, just
mmap() in the whole DB.  That might or might not be useful on small
devices -- I'm not sure, but it will generally require 64-bit address
spaces to get decent DB sizes (either that or a lot of effort to code
up a "windowing" mmap-based interface using multiple mmap()s of
different parts of the DB).  On larger systems this should reduce the
amount of time SQLite3 spends copying data, which I suspect is fairly
large -- either that or somehow the SQLite3 VM adds a lot of overhead,
but it's easy to imagine that data copying is the problem for the
simple reason SQLite3 is going to be copying several pagefuls of data
for every table/index lookup/insert/modify/delete operation.

But first we'll have to profile this carefully.  DTrace will be
necessary as it will be important to measure the amount of time spent
doing bulk copyouts from kernel-land to user-land in read()/pread().

Nico
--