Managed Sqlite
Last week I thought it would be interesting to try to
compile Sqlite in Managed C++. That way I could have a completely managed
sqlite provider, instead of having to have a managed wrapper for it. In the
process, I discovered that it was going to be more work, than I wanted to put
into it right now.
However, I did notice a place were efficiency could be
improved in sqlite. It treats the sqlite database file, as a sequence of fixed
size pages. It tracks dirty pages, caches them, reads and writes them to the
file. Because of the block nature of the database, Windows IO, and Windows VMS,
the sqlite db engine really ought to use memory mapped files. The OS’s
own disk IO system is mapping the files and copying them into the buffers used
for reading and writing, so the files is getting mapped anyway.
Sqlite already has a very small memory footprint, but by
using memory mapped files, the memory would be further reduced. Since Windows
already has the db file mapped, many of the page buffers would no longer have
to be allocated. In multiple processes where sqlite is running, if the same
pages are mapped, then those buffers are shared by the OS, so even less memory
would be used.
Another issue that Rico Mariani has routinely brought
up in his own blog, is that often it is better not cache data because if the
data gets paged to disk (which is likely in a paging OS like Windows or Linux),
then bringing that page back from the page file is as time consuming as
rereading it from the db file. So if you have a dirty page in the cache and it
had been paged out, then to write it to the db file, the OS is reading it from
the page file into memory and then writing it back to the disk. With memory
mapped files, when the OS pages out that dirty page, it is automatically
written where it belongs.
Another advantage of using memory mapped files is that you
can do page level locking instead of db level locking. Sqlite currently only
supports db level locking which could severely inhibit its scalability. With page
locking, reads and write could occur at the same time in differently pages, or
even multiple writes at the same time could be achieved if the writes were in
different pages.
–Stefan