Re: [Monotone-devel] ideas to speed up monotone SQLite database access

[Matt Johnston]

On Sun, Feb 05, 2006 at 02:39:34PM -0800, Joe Wilson wrote:
> (1) Use a larger default SQLite page size and/or cache size:
> 
> SQLite's default page size is 1024 bytes, which may not be
> optimal for monotone's typical file deltas and usage
> pattern. For instance, OpenEmbedded's monotone database is
> reduced from 80 megs to around 62 megs just by using a
> larger page size. A smaller database footprint often
> translates into better speed.

I've seen similar space benefits here on a
net.venge.monotone* database, though don't really understand
the mechanism behind the improvement... Trying with a few
smaller DBs I've got here I don't see any significant change
in size - the 8k pragma looks worth applying.  I've tried a
few larger page sizes up to 32k, though those didn't give
significant gains for any databases I tried, and resulted in
databases that were >25% larger for some cases.

> (2) Use a ":memory:" database for the initial monotone
> pull and then dump the resultant database to file. This
> would eliminate the disk latency entirely. Since most
> monotone databases I've seen are generally under 100 megs,
> this is a viable option. Naturally, the file-based
> database must be locked during the entire pull/dump
> operation. Unfortunately, this would prevent more than one
> user from accessing the database at a time.

Assuming the OS has sufficient memory available and sensible
buffering, shouldn't it be caching "recently-written" db
file portions already?  We're currently committing a
transaction after every N bytes or M packets transferred,
which seems to be a good tradeoff between getting output
written to disk but avoiding excessive disk latency for
sync()s etc. Most other disk-intensive operations are
already wrapped in transactions, there may be a few that
still require attention.

Matt