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

[Joe Wilson]

I noticed a recent post on the SQLite mailing list
(http://www.mail-archive.com/sqlite-users%40sqlite.org/msg12839.html) asking 
how monotone might
speed up its database access. Two simple optimizations that come to mind are:

(1) Use a larger default SQLite page size and/or cache size:

 http://www.sqlite.org/pragma.html

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.

 # original OE monotone database with page size of 1024 bytes...

 $ ls -l OE.db
 -rw-r--r-- 1 Administrator None 80286720 Feb  5 01:36 OE.db

 # OE database converted to use a page size of 8192 bytes:

 $ (echo "PRAGMA page_size=8192;" && ./sqlite3 OE.db .dump) | time ./sqlite3 
OE8192.db
 15.51user 2.29system 0:24.25elapsed 73%CPU (0avgtext+0avgdata 
214528maxresident)k

 $ ls -l OE8192.db
 -rwxr-xr-x 1 Administrator None 62283776 Feb  5 16:30 OE8192.db

 # verify the two databases' contents are the same...

 $ ./sqlite3 OE.db .dump | md5sum
 f41027c9a565005da8d71e6ed6368f9f *-

 $ ./sqlite3 OE8192.db .dump | md5sum
 f41027c9a565005da8d71e6ed6368f9f *-

As an encouraging sign, we can see just the act of creating a 1024 byte 
page-size database takes
four times longer (elapsed time) than the 8192 page size db above:

 $ (echo "PRAGMA page_size=1024;" && ./sqlite3 OE.db .dump) | time ./sqlite3 
OE1024.db
 18.20user 4.70system 1:46.34elapsed 21%CPU (0avgtext+0avgdata 
212992maxresident)k

 $ ls -l OE1024.db
 -rwxr-xr-x 1 Administrator None 79471616 Feb  5 17:30 OE1024.db

 $ ./sqlite3 OE1024.db .dump | md5sum
 f41027c9a565005da8d71e6ed6368f9f *-


(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.

 http://www.sqlite.org/cvstrac/wiki?p=InMemoryDatabase

Note: These 2 optimizations may be used independently of each other.
All timings done on a Windows/Cygwin machine.


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com