On Tue, Jun 10, 2008 at 1:29 PM, A. H. Ongun <[EMAIL PROTECTED]> wrote:
> We have an application requirement for the following: > 1) Maintain an in memory database of real time data. The main table mimics > a Modbus register address scheme and once the table is created and records > are inserted to create the table at startup, all writes are updates for the > state machine. (app. 1000 records). Reads are through multiple Modbus > TCP/IP protocol based devices which result in selects of up to 100 > contiguous records at a time. > 2) At periodic intervals based on events and time intervals a disk (SD > card) database gets updated (inserts). This contains system parameters, and > various different tables. > 3) Occasionally the whole database on disk is written to an external media > (USB) for backup purposes. > 4) Through a web interface user(s) can export portions of a table by > specifying type of data (table) and start and end time in csv format. (cgi > scripts). > All of the above is implemented currently using a "custom" format, and is > really a pain to expand and modify. > Items 1 & 2 are fairly straight forward to implement, they would be in the > same executable as well. > My original thought was to use the sqlite3 shell interface to accomplish > 4. I am concerned a bit about items 3 and 4 though in regards to > performance issues in that I can not afford not to service data requests > that could come several times a second if database could be locked during > operation of 3 and 4. Size of the database can grow to 1-2.4GB. > I am open to any suggestions. > Thanks in advance. > andy Okay, my first question is: What OS is this device running? If you need realtime semantics, you really need an RTOS. If the answer contains the string "Windows" you're in serious trouble, right from the get-go. You mention two databases (#1 has a modbus database, then #2 has a config database), then #3 mentions a "whole database". Which one are you referring to? I know nothing about Modbus, but you mention both reading and writing, and that the reading is being done in bulk by remote devices. How often does this happen? How often are new rows inserted into this Modbus database? My experience with USB memory sticks is: They're SLOW. A typical desktop hard drive does about 30MB/sec, which would require 80 seconds to fully write out a database. A USB memory stick will be maybe a tenth of that. That's over ten minutes to write the whole database out to the USB drive. Based on the limited information I have, the best advice I can give is this: If the rate of inserts is very low (less than one per second), it may be best to maintain two sets of databases: the 'live' ones (modbus in memory and config on SD) and 'backup' ones (on USB stick). When you need to do an insert/update, take all of the data needed for the modification, stuff it into a structure, and stick that structure on a spooling queue. Then, all you need to do is have a thread that pulls items off the spooling queue and applies them to the USB stick's version of the database. * Inserts to the 'live' database aren't blocked by the slow USB stick * People doing reporting can report from the USB stick version. Inserts that come in while the reporting thread has a shared lock will just pile up in the spool, then they can all get processed when the report is done. * Your external backup will probably actually get updated more often. > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
