I participated in the beta evaluation of the BDB SQLite code and one of the
things I tried to get a handle on was the performance difference. At this point,
I cannot publish exactly what I found until I have at least one other person
evaluate my code, run the tests, and confirm the numbers I got (which is being
done). However, I can generalize here and say that there are cases where BDB
offers significant performance improvements over SQLite, specifically in the
area of handling heavy loads that involve write-concurrency.
There are, generally, two measures of "fast" right -- (1) efficiency: how long
does it take for a single process to do XYZ vs. (2) concurrency: how many times
can many processes do XYZ per unit time. The main problem BDB addresses is
concurrency -- large-scale transaction processing. Thus you think of many
concurrent connections writing to and/or modifying the contents of the database.
SQLite by design uses database-level locking so there is a maximum of one
writer who can be working in the database at a time. Thus, SQLite's transaction
rate stays more or less constant with the number of concurrent connections, so
it's scalability in write-intensive applications is really measured by its
efficiency (1).
BDB on the other hand uses page level locking, which allows multiple writers to
be working in the database at a given time (provided that they are working on
separate pages). Thus BDB's rate potentially increases with the number of
connections and so its scalability is both a matter of efficiency (1) and
concurrency (2), which can add up.
Mainly what it boils down to is (write) concurrency. BDB can push more TPS than
SQLite for multiple writers. By transaction, I mean something that modifies the
database (how are they of any real help for read-only operations?). That said,
for read concurrency (apps that mainly do SELECTs), SQLite could very well go
head to head with BDB because locking is no longer a critical issu
answered 2010-05-18T20:29:51.943