Eric, Following are the setup details that we are presuming that our application will be put through test:
1. One writer thread (To avoid multiple Writers. We don't actually need multiple writers) 2. Multiple (around 5-8 Reader Threads) 3. One Record size of ~2K (slightly on the higher Side) 4. Insertion rate (MAX) = 1500 per sec 5. One main table and some helper Tables. We are presuming that the write operations would be pretty heavy but the read operations will be somewhat lighter. And of course there will be instances where the read and write are happening simultaneously. We don't want that read operations get effected because the write operations are in progress (and Vice-Versa). The way that Oracle handles this using Read Committed; Can the same thing be achieved using SQLite for an in-memory database? Thanks & Regards, Sachin -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eric Smith Sent: Friday, December 31, 2010 9:11 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Using WAL? 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
