Sachin Gupta wrote:
> Our application is kind of heavily multi-threaded and required using
> sqlite to be used as an in memory database for caching purposes. Being
> multi-threaded requires read-write locking mechanism, and WAL seemed
> quite the thing for us. We are expecting high rate of Inserts and Selects
> simultaneously.
>
> Q. Well since WAL cannot be used with in-memory database, which
> other method do you all suggest one should use for providing better
> concurrency?
> Can using Shared Cache Mode be of use?
Maybe SQLite isn't the right tool for you: it's not really designed to
handle huge amounts of concurrent writers. Even in WAL mode, only one
writer may be doing its work at a time.
What do you mean by "kind of heavily" and "high rate"? How many reads &
writes per second do you envision? Do you know roughly what the access
patterns will be like?
I heard a rumor that BerkeleyDB was borrowing parts of SQLite and
pasting it into their stuff -- and I think they have all kinds of
multi-writer concurrency cleverness built into their back-end. Maybe
check that out and see what you find?
But, if you insist on using SQLite in WAL mode, I'd just use an on-disk
database with PRAGMA synchronous=off. That way the OS will keep a lot
(hopefully most) of your database in RAM anyway. You shouldn't care
about corruption on power loss or app crashes because you wanted to use
the database in-memory anyway.
I did this in a recent project and achieved a sustained write rate of
like 180k records per second (each record was around 200 bytes across
15-ish columns), and I think my application was the bottleneck (not
SQLite).
But, again, I only had one writer -- and no one was trying to read while
the writer was working.
Eric
--
Eric A. Smith
We don't like their sound, and guitar music is on the way out.
-- Decca Recording Co. rejecting the Beatles, 1962.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
