Tuesday, April 12, 2005

Berkeley DB Btree vs SQLite Btree (contd 2)

In an earlier blog I mentioned that SQLite does much better than Berkeley DB for writes. When something is too good to be true, it probably is :-). So, when I investigated what's going on, I found that I had passed a wrong flag which disabled the SQLite journal file completely. With that fixed, it turns out that SQLite performs worse than BerkeleyDB for smaller transaction sizes but better than BerkeleyDB for larger transaction sizes.

In my test program (btree-test.c, sqlite-btree.h and btree.h), SQLite pulls ahead for a transaction size of 512 (512 * 128 = 64k bytes) on my ext3 partition. I assume it's the amount of data that's written inside a transaction rather than the transaction size that matters (I haven't verified this yet). Since SQLite database sizes are smaller than that of SQLite (approx 30% smaller in my tests, excluding the BerkeleyDB transaction log), I attribute SQLite's relative poor performance for smaller transactions to the design of the transaction log and the number of syncs to disk. These tests were with SQLite 3.1.6. I get similar results with SQLite 3.2.0.

Please try the programs above and let me know the results. Also let me know if I've goofed up with the code.

2 comments:

plumsauce said...

Hi,

Interesting approach.

A direct api for inserts and sql for selects in the same db.

Do you suspect that there are any concurrency problems by short circuiting the sql engine?

platformlabs.com

anonymous transactions said...

I have had great time reading your article. It’s actually quiet awesome, it is surprising how you have learn t to write your feelings and thoughts in so precise manner. It's good really. anonymous transactions