"Sachin Gupta" <scgu...@ipolicynetworks.com> 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 150000 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- somewhere"). So, the SQLite-Thread (hosting the InMemory-Connection) is only responsible, to look around - checking the device-thread-queue for the next incoming 5-record-chunk - and checking (round robin) the reader-threads for new incoming Selects (performing only one at a time, then the next look will always go to the device-queue again). Olaf _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
