[sqlite] Using WAL without shared memory, no exclusive, but single file descriptor
Have a file system that lacks file sharing and based on responses from this group, I've implemented a VFS that has all connections using the same file share a file descriptor for that file. It seems to me that this has the same effect as a memory mapped file would. Does this mean that I could operate with WAL turned on, even without exclusive access? Thanks, Marc -- ** * * * * Marc L. Allen * "... so many things are * * * possible just as long as you* * Outsite Networks, Inc. * don't know they're impossible." * * (757) 853-3000 #215 * * * * * * mlal...@outsitenetworks.com * -- The Phantom Tollbooth * * * * ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using WAL?
Thanks Olaf. Deeply Appreciated! Will do these tests and revert back ASAP. Thanks & Regards, Sachin -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Olaf Schmidt Sent: Thursday, January 06, 2011 7:16 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Using WAL? "Sachin Gupta" schrieb im Newsbeitrag news:51b335651c9a0c4ea520d47f23a580b414f5d4d...@sinnodmbx001.techmahindra.com... > Thank you for the detailed response. > It sure does help us a lot. > > One more Q for you: > Considering the requirement that we have, if we relax > the requirement of creating a in memory db, can we > instead use the SQLite in WAL mode or Shared Cache > simply for the reason that it provides concurrent access > to read/write processes? > Would it be advisable to use it in our scenario? >From what you wrote (not a very high insertion-rate, and with Read-Requests not all that often, and only for temporary caching-purposes) I don't think SQLites WAL-mode would offer you much over "singlethreaded InMemory-Mode". But just do your tests. Open an InMemory-Connection, insert your max. 1Mio Test-Records there (thereby encoding your TimeStamp-Field in a way, that it does make use of SQLites "Default-index aka RowID"). This should not take more than about 5-15seconds. Then use a high-precision timer, to test how long it takes, to: Insert one single additional Record and delete the one with the smallest "RowID-Timestamp-value" - (I would expect, that this will take only 1msec max). After testing the write-direction, you should also test (and tune) your typical read-queries against this 1Mio-records cache - and if these run under 10-15msec you should be fine with the InMemory- approach. Then, after all these tests were satisfying, you could start implementing the roughly layed-out scheme from my previous post (and I do not yet see the need, to have multiple ReaderThreads, in case you only plan to use them within the same "GUI-Application- *process*")... Multiple "GUI-reader-threads" (one per "ViewBox") *could* make sense, in case you have multiple CPU-cores available and plan to use the retrieved values from your "read-queries" for "faster visual-preparation using expensive drawing-commands" against e.g. "independent cairo-image- surfaces per thread". But if this is not the case (if you don't have multiple CPU-cores, and if your drawing-commands are relative "non-expensive-ones", then hosting your different "GUI-views" in only one Thread (the main-thread of your App-Process) would be sufficient too IMO. Olaf ___ 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
Re: [sqlite] Using WAL?
"Sachin Gupta" schrieb im Newsbeitrag news:51b335651c9a0c4ea520d47f23a580b414f5d4d...@sinnodmbx001.techmahindra.com... > Thank you for the detailed response. > It sure does help us a lot. > > One more Q for you: > Considering the requirement that we have, if we relax > the requirement of creating a in memory db, can we > instead use the SQLite in WAL mode or Shared Cache > simply for the reason that it provides concurrent access > to read/write processes? > Would it be advisable to use it in our scenario? >From what you wrote (not a very high insertion-rate, and with Read-Requests not all that often, and only for temporary caching-purposes) I don't think SQLites WAL-mode would offer you much over "singlethreaded InMemory-Mode". But just do your tests. Open an InMemory-Connection, insert your max. 1Mio Test-Records there (thereby encoding your TimeStamp-Field in a way, that it does make use of SQLites "Default-index aka RowID"). This should not take more than about 5-15seconds. Then use a high-precision timer, to test how long it takes, to: Insert one single additional Record and delete the one with the smallest "RowID-Timestamp-value" - (I would expect, that this will take only 1msec max). After testing the write-direction, you should also test (and tune) your typical read-queries against this 1Mio-records cache - and if these run under 10-15msec you should be fine with the InMemory- approach. Then, after all these tests were satisfying, you could start implementing the roughly layed-out scheme from my previous post (and I do not yet see the need, to have multiple ReaderThreads, in case you only plan to use them within the same "GUI-Application- *process*")... Multiple "GUI-reader-threads" (one per "ViewBox") *could* make sense, in case you have multiple CPU-cores available and plan to use the retrieved values from your "read-queries" for "faster visual-preparation using expensive drawing-commands" against e.g. "independent cairo-image- surfaces per thread". But if this is not the case (if you don't have multiple CPU-cores, and if your drawing-commands are relative "non-expensive-ones", then hosting your different "GUI-views" in only one Thread (the main-thread of your App-Process) would be sufficient too IMO. Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using WAL?
Thank you for the detailed response. It sure does help us a lot. One more Q for you: Considering the requirement that we have, if we relax the requirement of creating a in memory db, can we instead use the SQLite in WAL mode or Shared Cache simply for the reason that it provides concurrent access to read/write processes? Would it be advisable to use it in our scenario? Regards Sachin -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Olaf Schmidt Sent: Tuesday, January 04, 2011 8:03 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Using WAL? "Sachin Gupta" schrieb im Newsbeitrag news:51b335651c9a0c4ea520d47f23a580b414f5cd5...@sinnodmbx001.techmahindra.com... > --- The Application will be using the in memory SQLite > as Cache. You could also consider some sort of "sorted Dictionary" (a Tree-Structure which allows fast "sorted indexed-access") instead of the SQLite-engine in this case. > --- One thread in the application, is listening on the device > and the data sent by the device is saved in the Cache or > inserted to SQLite. This is the writer Thread. Ah Ok, as guessed... > --- Multiple GUI's are connected to the Application which are being > serviced by individual threads. These Threads are interacting with the > cache (SQLite) and providing data to the GUI(s). These are the > Reader Threads. Ok. > --- The rate of data received at the Writer Thread will be varying. > It is not constant as stated by me earlier. No problem, if your previous reply was describing a worst-case data-rate, then you're fine - you will only need to "sleep" within your SQLite-Thread, if "no-next-job" was found in your round-robin-cycling. > --- At any instant of time, if the data rate from device is very high > and simultaneously the GUI is asking for Data, it is quite possible > that Writer and Reader may collide. That's clear, but there's no real collision possible (you don't even need locking). Your writer-devicethread will share a memory-structure with the SQLite-thread, which in its first members is holding a NextJobIndex (the index pointing into a JobQueue-array, which at each index-position describes an entire new DataRow (all the Fields which make up a new record) + a JobState- EnumEntry (JobPreparing / JobPrepared / JobDone / JobFailed). Same thing with your GUI-reader-threads (a Struct with a NextJobIndex-member + a preallocated array which in this case contains the same JobState-EnumEntry at each array-index + a String-Entry for the Select-Command, and a Resultset-pointer which will point to a copy of the data, filled-in wihtin the sqlite_step-loop, when the SQLite- thread is processing this current read-request. Just ensure, that these shared structs hold JobQueue-arrays, which are large enough to hold data for - let's say - 2-3 seconds worst-case incoming rate. > Your Questions: > Q1. Don't know, what you need these Reader-Threads for? > Ans1: The application is client Server based, where there can > be multiple instances of GUI connecting to the Application > (running sqlite). Till here, it is not yet entirely clear, what you mean (Client/Server can mean anything) - please let's talk in terms of "processes" and "threads". When you talk about "multiple instances of GUI" - are these instances running within the same process, which hosts your SQLite-DBCache- and the DeviceWriter-Threads? > The Application would handle display requests coming from > these GUI threads. These are the reader threads. This again sounds as if your "GUI-threads" and the "reader- threads" are identically (and run within the same process). > Q2. So, what is your usecase ... do you want to work with > something like a ringbuffer? Are Deletes involved - and after > what time do they begin "to kick in"? > Ans2: Yes, kind of. We are using timers to maintain the size > of the sqlite cache and limit it to a certain size (~1 Million Records) > so that the DB does not bloat out of hand. We are using timers > to click in every 15 seconds to delete the old records. You will not need any timers for that, the isolated (single) SQLite-Thread can take care of that whilst processing the current insert (keeping a count-of already inserted records in memory, you will not even need a "Select" for determining the current recordcount). > Questions: > Ques. No there will be no such instances, since the InMemory- > DB-Connection is used on only one thread - but that isn't > really a problem, if you "do it right"... > >>>> Does that mean that only one connection can be made > to In Memory DB? Yes, of course - but there's no real problem here with that, because even one thread is
Re: [sqlite] Using WAL?
"Sachin Gupta" schrieb im Newsbeitrag news:51b335651c9a0c4ea520d47f23a580b414f5cd5...@sinnodmbx001.techmahindra.com... > --- The Application will be using the in memory SQLite > as Cache. You could also consider some sort of "sorted Dictionary" (a Tree-Structure which allows fast "sorted indexed-access") instead of the SQLite-engine in this case. > --- One thread in the application, is listening on the device > and the data sent by the device is saved in the Cache or > inserted to SQLite. This is the writer Thread. Ah Ok, as guessed... > --- Multiple GUI's are connected to the Application which are being > serviced by individual threads. These Threads are interacting with the > cache (SQLite) and providing data to the GUI(s). These are the > Reader Threads. Ok. > --- The rate of data received at the Writer Thread will be varying. > It is not constant as stated by me earlier. No problem, if your previous reply was describing a worst-case data-rate, then you're fine - you will only need to "sleep" within your SQLite-Thread, if "no-next-job" was found in your round-robin-cycling. > --- At any instant of time, if the data rate from device is very high > and simultaneously the GUI is asking for Data, it is quite possible > that Writer and Reader may collide. That's clear, but there's no real collision possible (you don't even need locking). Your writer-devicethread will share a memory-structure with the SQLite-thread, which in its first members is holding a NextJobIndex (the index pointing into a JobQueue-array, which at each index-position describes an entire new DataRow (all the Fields which make up a new record) + a JobState- EnumEntry (JobPreparing / JobPrepared / JobDone / JobFailed). Same thing with your GUI-reader-threads (a Struct with a NextJobIndex-member + a preallocated array which in this case contains the same JobState-EnumEntry at each array-index + a String-Entry for the Select-Command, and a Resultset-pointer which will point to a copy of the data, filled-in wihtin the sqlite_step-loop, when the SQLite- thread is processing this current read-request. Just ensure, that these shared structs hold JobQueue-arrays, which are large enough to hold data for - let's say - 2-3 seconds worst-case incoming rate. > Your Questions: > Q1. Don't know, what you need these Reader-Threads for? > Ans1: The application is client Server based, where there can > be multiple instances of GUI connecting to the Application > (running sqlite). Till here, it is not yet entirely clear, what you mean (Client/Server can mean anything) - please let's talk in terms of "processes" and "threads". When you talk about "multiple instances of GUI" - are these instances running within the same process, which hosts your SQLite-DBCache- and the DeviceWriter-Threads? > The Application would handle display requests coming from > these GUI threads. These are the reader threads. This again sounds as if your "GUI-threads" and the "reader- threads" are identically (and run within the same process). > Q2. So, what is your usecase ... do you want to work with > something like a ringbuffer? Are Deletes involved - and after > what time do they begin "to kick in"? > Ans2: Yes, kind of. We are using timers to maintain the size > of the sqlite cache and limit it to a certain size (~1 Million Records) > so that the DB does not bloat out of hand. We are using timers > to click in every 15 seconds to delete the old records. You will not need any timers for that, the isolated (single) SQLite-Thread can take care of that whilst processing the current insert (keeping a count-of already inserted records in memory, you will not even need a "Select" for determining the current recordcount). > Questions: > Ques. No there will be no such instances, since the InMemory- > DB-Connection is used on only one thread - but that isn't > really a problem, if you "do it right"... > Does that mean that only one connection can be made > to In Memory DB? Yes, of course - but there's no real problem here with that, because even one thread is enough in your case IMO (because SQLite is fast enough in InMemory-Mode, to give you the "illusion" of parallel processing of Write- *and* Read-Requests, if you work in small enough chunks (Write-direction inserting only 1-20 records at a time - Read-Direction processing only one single queued Select at a given time, which should be finished within - let's say - 10msec. > Ques: Does SQLite support Connection Pooling: > Both for In Memory DB or Disk Based? No - not in InMemory-Mode - and as said, not really needed in your case. > If not supported, then how can we handle multiple simultaneous > accesses to DB (R-R,R-W,W-W)? Will we have to do > concurrency handling by ourselves? Of course - and it is not difficult, if you work with the (isolated) mem-structures I've described already above. Within the SQLite-Thread there's a loop: While Not SQLiteThreadCancelled ActivitySteps = 0 If WriterQ.JobArray(WriterQ
Re: [sqlite] Using WAL?
"Sachin Gupta" schrieb im Newsbeitrag news:51b335651c9a0c4ea520d47f23a580b414f5baf...@sinnodmbx001.techmahindra.com... > 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) Good so far... because what you want, is only *one* SQLite-thread when working against InMemory-DBs... ;-) > 2. Multiple (around 5-8 Reader Threads) Don't know, what you need these Reader-Threads for - certainly not to achieve better Read-Performance against SQLite when used with an InMemory-DB. They could be helpful, if you use them, to host e.g. multiple socket-connections, which place incoming Select-Strings in a "queue" - and they could also be used, to send *copies* of your resultsets back over the same TCP-connection without "disturbing your SQLite-InMemory-Thread further". Please shade some more light on that "necessitiy". > 3. One Record size of ~2K (slightly on the higher Side) > 4. Insertion rate (MAX) = 1500 per sec That would sum-up to about 3MB per sec, which is an insertion-data-rate, SQLite should have no problem with at all (in InMemory-Mode). The more interesting question is, in what chunks do these ~1500 records per second come in? 150 records any 100msec - or is it 15records any 10msec - or...? In either case I would predict, that the inserts (when using the Binding-interfaces) would take only a tenth of these "incoming-chunks-interval" (remember, that SQLite can achieve sustained insertion-data-rates of more than 15 records per second, as Eric already wrote and as is also my experience, especially against InMemory-DBs and if no "heavy indexing" was defined on the table in question). What is more interesting (since you want to work against Memory), is "where this all ends"? With ~3MB per second, after around 20minutes (1200secs) you would consume already ~3.5GByte of memory, having around 1.8Mio records in your table(s). So, what is your usecase ... do you want to work with something like a ringbuffer? Are Deletes involved - and after what time do they begin "to kick in"? > 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. I assume you mean with "lighter"...: "Not with the same rate as the insertion-frequency." But are they really light? What kind of Selects are these - what's the indexing-scheme to keep your queries fast (assuming your memory will grow into the Gigabyte-Range). > And of course there will be instances where the read and > write are happening simultaneously. No there will be no such instances, since the InMemory- DB-Connection is used on only one thread - but that isn't really a problem, if you "do it right"... Let's assume, your 1500 records per second come in in chunks of 15 (from a device for example, which you could handle in a separate device-thread, so that "waiting for the next data-chunk of the device" will not disturb your SQLite-InMemory-Thread). So the data of such an 15-record-chunk comes in at roughly 10msec each. SQLite in InMemory-Mode and without any heavy indexes on the tables in question will be finished with the inserts of these 15 records in about 1-2msec max. After that the SQLite-Thread would have 8msec of "Idle-Time" (until the next "15-record-chunk" comes in) - and it could use these 8msecs to perform "Select-Strings" which were queued within your "5-8 reader threads" - it would be your responsibility, to perform only that many of these queued selects, that you stay within your 8-10msec- limit Idle-Time (because the next chunk of "write-data" is coming soon) - but if you design your indexes carefully (preferrably occupying only the RowID-Field) - then you could mayhap perform 4 Selects (2msec each) in these 8msec, placing a copy of the read-data from each sqlite_step directly in a memory-area of the current "reader thread you serve". This way you could answer 4 reader-selects in each 10msec-interval, with a "data-actuality" of max. "10msec behind schedule" - but you already told us, that your readers "select-frequency" is not that high, so you will probably have to perform only one of these Selects in the 8msec "Idle-Time-between-chunks" So what are really the "real-time-requirements" in your scenario - do you need to work even more "near to your incoming write-data"? Then maybe shorten the Insert-Intervals to e.g. "5 Records any 3msec" and only one allowed Select in such an interval. Your single SQLite-Thread will perhaps handle that happily too - you will only need to ensure proper queuing in your "satellite-threads around this SQLite- worker" (as are your device-thread, assuming this is the datasource of your insert-records ... and also in your accompanying reader-threads, which would only need to be responsible for queueing Select-Strings and also to queue "resultset-copies-ready-to-send- som
Re: [sqlite] Using WAL?
On Dec 30, 2010, at 21:57, Sachin Gupta wrote: > We don't want that read operations get effected because the write operations > are in progress (and Vice-Versa). Have you determined that my suggestion can't meet your performance requirements? > The way that Oracle handles this using Read Committed; Can the same thing be > achieved using SQLite for an in-memory database? Which oracle in-memory database are you referring to? -- dustin sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using WAL?
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
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. WAL, and journaling in general, arbitrates concurrent access between multiple connections to the same database. You cannot have more than one connection to the same in-memory database. Ergo, journaling mode, WAL or otherwise, is irrelevant for you. > 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? Better than what? SQLite would happily allow you to modify the database at the same time you are stepping through a SELECT statement (but it won't let two modification statements to run simultaneously). Of course, if you modify the same data that you are selecting against, the result of the select is unpredictable. > Can using Shared Cache Mode be of use? http://www.sqlite.org/sharedcache.html Again, shared cache mode presupposes the existence of more than one connection to the same database (otherwise, what would share the cache?) And again, you can only have one connection to the same in-memory database. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using WAL?
On 31 Dec 2010, at 3:41am, Eric Smith wrote: > 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. This seems to be preferred solution #1. One other is to devise your own client/server protocol, optimised for your own expected data flow. Then all your clients can run as separate processes anyway, removing all the problems that using threads introduces. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
Re: [sqlite] Using WAL?
On Dec 30, 2010, at 19:21, Sachin Gupta wrote: > 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? http://www.sqlite.org/sharedcache.html In general, just don't just try to have a bunch of threads throwing stuff at the same data structure and expecting awesome goodness to fall out. Can you structure your application so that there's just one thread holding the state and you pass messages in and out to retrieve and mutate state? I imagine this would be the shortest path to success and definitely the easiest to prove correct. -- dustin sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using WAL?
Thanks. Pasting the extract from the link below: Note that the journal_mode for an in-memory database is either MEMORY or OFF and cannot be changed to a different value. An attempt to change the journal_mode of an in-memory database to any setting other than MEMORY or OFF is ignored. - 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? http://www.sqlite.org/sharedcache.html Experts: Your comments please. Thanks & Regards, Sachin Gupta -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dustin Sallings Sent: Friday, December 31, 2010 1:08 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Using WAL? On Dec 30, 2010, at 10:42, Sachin Gupta wrote: > This is for setting the Journal Mode. But has the WAL mode been set properly? > How can we confirm this? Simon answered this question. > Also, Can WAL mode be used for In Memory Databases? > I am planning to use it in my application. I don't think this makes much sense. Where would the log live? The answer to all of your questions are in the docs here: http://sqlite.org/pragma.html#pragma_journal_mode -- dustin sallings ___ 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
Re: [sqlite] Using WAL?
On 30 Dec 2010, at 6:42pm, Sachin Gupta wrote: > This is for setting the Journal Mode. But has the WAL mode been set properly? WAL /is/ a type of journal mode. That's all it changes: how SQLite does its journalling. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using WAL?
On Dec 30, 2010, at 10:42, Sachin Gupta wrote: > This is for setting the Journal Mode. But has the WAL mode been set properly? > How can we confirm this? Simon answered this question. > Also, Can WAL mode be used for In Memory Databases? > I am planning to use it in my application. I don't think this makes much sense. Where would the log live? The answer to all of your questions are in the docs here: http://sqlite.org/pragma.html#pragma_journal_mode -- dustin sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using WAL?
Thanks, This is for setting the Journal Mode. But has the WAL mode been set properly? How can we confirm this? Also, Can WAL mode be used for In Memory Databases? I am planning to use it in my application. Regards From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin [slav...@bigfraud.org] Sent: Thursday, December 30, 2010 7:50 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Using WAL? On 30 Dec 2010, at 10:51am, Sachin Gupta wrote: > Is there a way to know whether WAL mode is turned on? PRAGMA journal_mode; See http://www.sqlite.org/pragma.html#pragma_journal_mode Simon. ___ 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
Re: [sqlite] Using WAL?
On 30 Dec 2010, at 10:51am, Sachin Gupta wrote: > Is there a way to know whether WAL mode is turned on? PRAGMA journal_mode; See http://www.sqlite.org/pragma.html#pragma_journal_mode Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using WAL?
Is there a way to know whether WAL mode is turned on? Thanks & Regards, Sachin Gupta -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sachin Gupta Sent: Thursday, December 30, 2010 2:57 PM To: General Discussion of SQLite Database Subject: [sqlite] Using WAL? Hi, I just came across a method provided by SQLite, called the WAL - Write Ahead Logging. Anybody has any Good-Bad experiences related to using WAL? Can it be safely used with a heavily threaded application with very high rate of Insert Operations and simultaneously Select Operations also. Thanks & Regards, Sachin Gupta -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, December 29, 2010 12:01 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Cancel execution of a executing Query On 29 Dec 2010, at 5:17am, Sachin Gupta wrote: > I thought so too. But the application that we are working on is kind of > heavily multi-threaded and needs to be thread safe. SQLite is itself thread safe, as long as you don't use the PRAGMAs to defeat all the safety precautions. It might be worth reading http://www.sqlite.org/threadsafe.html and using http://www.sqlite.org/c3ref/threadsafe.html if your code will be run on any platforms you don't control. While there're probably bugs in SQLite that will cause it to hang, the writers would like to know about them and kill them, so please post if you find one. I don't see any entry in the bug database for one right now. > When we "Begin Transaction", does Sqlite return any kind of unique handle or > ID which can be used to poll a list of open transactions and kill or cancel > the transaction if required. I did not find it and I suppose that it does not > also. > > For e.g. > Suppose one thread opens a transaction and does some operations. > Simultaneously another and many other threads open up another transaction. > Now for some reason, the first transaction query hangs, and does not complete > execution. This would cause problems! So to come out of this, we would need a > unique transaction id to kill it. Is there a way to achieve this or is there > a better way to do this? Killing the hung transaction is probably not the right way to recover from this situation. There are a number of things that might cause the transaction to hang, but almost all of them are related to hardware failure. In other words, if thread B kills thread A's transaction so it can execute a transaction of its own, thread B's transaction is probably going to hang in the same way and for the same reason. So the best thing to do is to not do the killing in the first place, and let SQLite handle the problem its own way. The simplest is to look for results like SQLITE_BUSY and SQLITE_LOCKED (see section 1.4 of http://www.sqlite.org/c_interface.html ) which might result if a lock fails for too long. A more sophisticated way is to use the unlock_notify API: http://www.sqlite.org/unlock_notify.html I don't understand all of that, but from experience with other systems, unless you're in a situation where it's absolutely vital to cope with every possible source of error (i.e. you're using SQLite to control a nuclear power-plant or in a processor embedded into a missile) this API is of limited use once your application is released to customers. As the page describes, even without using that API SQLite handles hangs well. The correct way to treat a thread hanging due to an underlying hardware problem is, of course, to let your application hang. This tells your user that their hardware is not to be trusted, and lets them start putting the problem right instead of running on hardware which is about to fail. Simon. ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using WAL?
Hi, I just came across a method provided by SQLite, called the WAL - Write Ahead Logging. Anybody has any Good-Bad experiences related to using WAL? Can it be safely used with a heavily threaded application with very high rate of Insert Operations and simultaneously Select Operations also. Thanks & Regards, Sachin Gupta -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, December 29, 2010 12:01 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Cancel execution of a executing Query On 29 Dec 2010, at 5:17am, Sachin Gupta wrote: > I thought so too. But the application that we are working on is kind of > heavily multi-threaded and needs to be thread safe. SQLite is itself thread safe, as long as you don't use the PRAGMAs to defeat all the safety precautions. It might be worth reading http://www.sqlite.org/threadsafe.html and using http://www.sqlite.org/c3ref/threadsafe.html if your code will be run on any platforms you don't control. While there're probably bugs in SQLite that will cause it to hang, the writers would like to know about them and kill them, so please post if you find one. I don't see any entry in the bug database for one right now. > When we "Begin Transaction", does Sqlite return any kind of unique handle or > ID which can be used to poll a list of open transactions and kill or cancel > the transaction if required. I did not find it and I suppose that it does not > also. > > For e.g. > Suppose one thread opens a transaction and does some operations. > Simultaneously another and many other threads open up another transaction. > Now for some reason, the first transaction query hangs, and does not complete > execution. This would cause problems! So to come out of this, we would need a > unique transaction id to kill it. Is there a way to achieve this or is there > a better way to do this? Killing the hung transaction is probably not the right way to recover from this situation. There are a number of things that might cause the transaction to hang, but almost all of them are related to hardware failure. In other words, if thread B kills thread A's transaction so it can execute a transaction of its own, thread B's transaction is probably going to hang in the same way and for the same reason. So the best thing to do is to not do the killing in the first place, and let SQLite handle the problem its own way. The simplest is to look for results like SQLITE_BUSY and SQLITE_LOCKED (see section 1.4 of http://www.sqlite.org/c_interface.html ) which might result if a lock fails for too long. A more sophisticated way is to use the unlock_notify API: http://www.sqlite.org/unlock_notify.html I don't understand all of that, but from experience with other systems, unless you're in a situation where it's absolutely vital to cope with every possible source of error (i.e. you're using SQLite to control a nuclear power-plant or in a processor embedded into a missile) this API is of limited use once your application is released to customers. As the page describes, even without using that API SQLite handles hangs well. The correct way to treat a thread hanging due to an underlying hardware problem is, of course, to let your application hang. This tells your user that their hardware is not to be trusted, and lets them start putting the problem right instead of running on hardware which is about to fail. Simon. ___ 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
