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

[Joe Wilson]

Some more data on various SQLite page sizes' effect on 
database size, this time on the monotone repository itself:

$ ls -l *.db
-rwxr-xr-x 1 Administrator None 50421760 Feb  5 21:56 mt.db
-rwxr-xr-x 1 Administrator None 49896448 Feb  5 22:04 mt1024.db
-rwxr-xr-x 1 Administrator None 37847040 Feb  5 22:03 mt8192.db


$ monotone --version
monotone 0.26pre1 (base revision: unknown)

$ time monotone --db=mt.db --key="" pull off.net net.venge.monotone
monotone: setting default server to off.net
monotone: setting default branch include pattern to 'net.venge.monotone'
monotone: setting default branch exclude pattern to ''
monotone: doing anonymous pull; use -kKEYNAME if you need authentication
monotone: connecting to off.net
monotone: first time connecting to server off.net
monotone: I'll assume it's really them, but you might want to double-check
monotone: their key's fingerprint: 70a0f283898a18815a83df37c902e5f1492e9aa2
monotone: finding items to synchronize:
monotone: bytes in | bytes out | certs in | revs in
monotone:    5.4 k |     0.6 k |        0 |       0
monotone: bytes in | bytes out |    certs in |   revs in
monotone:   26.0 M |     0.7 k | 12646/12646 | 3364/3364
monotone: successful exchange with off.net

real    106m4.984s
user    39m10.640s
sys     0m15.078s

The monotone process was idle half the time waiting for 
netsync (no I/O, 0.0% CPU).

$ ls -l mt.db
-rwxr-xr-x 1 Administrator None 50421760 Feb  5 21:56 mt.db

# copy database using page size of 8192...

$ (echo "PRAGMA page_size=8192;" && ./sqlite3 mt.db .dump) | time ./sqlite3 
mt8192.db
10.84user 0.81system 0:17.17elapsed 67%CPU (0avgtext+0avgdata 
312832maxresident)k
0inputs+0outputs (5597major+0minor)pagefaults 0swaps

$ ls -l mt8192.db 
-rwxr-xr-x 1 Administrator None 37847040 Feb  5 22:03 mt8192.db

# copy database using page size of 1024...

$ (echo "PRAGMA page_size=1024;" && ./sqlite3 mt.db .dump) | time ./sqlite3 
mt1024.db
12.90user 2.56system 0:42.92elapsed 36%CPU (0avgtext+0avgdata 
212736maxresident)k
0inputs+0outputs (2107major+0minor)pagefaults 0swaps

$ ls -l mt1024.db 
-rwxr-xr-x 1 Administrator None 49896448 Feb  5 22:04 mt1024.db

# verify database data is the same...

$ ./sqlite3.exe mt.db .dump | md5sum
c5ccc578f50a3a2278e393007cb79dba *-
$ ./sqlite3.exe mt8192.db .dump | md5sum
c5ccc578f50a3a2278e393007cb79dba *-
$ ./sqlite3.exe mt1024.db .dump | md5sum
c5ccc578f50a3a2278e393007cb79dba *-

Timings on Windows/Cygwin.

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